6 ms·

How does pricing work here? Say I have a pivot table on underlying 10TB data. I share the sheet with 10 people, each one refreshes it 100 times a day. Do I pay

How does pricing work here? Say I have a pivot table on underlying 10TB data. I share the sheet with 10 people, each one refreshes it 100 times a day. Do I pay for each data refresh, ie 10TB * 10 * 100= 10000TB/day, that would be $50000/day if I read the pricing table right. Can someone more familiar with bigquery verify that?

gundmc 8y ago
We'll have to wait for details about connected sheets, but BQ itself caches queries by default for 24 hours and you're not charged for cache hits. I assume something similar applies.
- polskibus 8y ago
  That would assume the underlying data does not change. If you use BiqQuery with ingest runnig 24/7, you often don't want to be served from cache. Besides, filters and slicers can be modified on a per user basis, that would trigger recalculations. Again - not sure if a cache would handle this.
  - lukasb 8y ago
    BigQuery is usually intended for data warehouse use cases, where missing the most recent 23 hours of data is less of a big deal. It's intended to feed things like exec dashboards, not production systems.
    - hulahoof 8y ago
      I believe the views will refresh when the underlying data changes, so if you are streaming or inserting frequently the costs could add up
      - SmirkingRevenge 8y ago
        Big query isn't a great use case if data that needs to be queried repeatedly and is updating that frequently, I don't think. With streaming inserts, you probably should generally only be doing inserts on the latest date/time based partition, so that you can take advantage of cached queries for all data but the most recent date/time partition.
  - kyrra 8y ago
    It's important to remember how BigQuery actually stores it's data, with a columnar base datastore called Capacitor[0]. While you write data as a row, BigQuery does a certain amount of preprocessing on that data to turn it into column-oriented data. Writing a single new row is non-trivial, so it tends to write data in batches (though there is some other magic going on here I probably don't understand). [0] https://cloud.google.com/blog/products/gcp/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/products/gcp/inside-capacitor-...
dajonker 8y ago
That depends on how many columns your pivot table uses. Less columns = less data = lower price. If the implementation is smart enough it could use BigQuery partitions for further cost savings.
- danielecook 8y ago
  Bigquery also uses caching which presumably is used here.
- polskibus 8y ago
  Is the data size calculated based on raw data on which queries operate (for example all transactions with amount column), or on query result size (for example sum(amount) -single row) ?
  - lmkg 8y ago
    BigQuery bills based on amount of data read. BigQuery is a column-store database, so a read will always touch every row of a table, but only the columns actually used. So e.g. "SELECT * LIMIT 1" will read the entire table, while "SELECT SUM(transaction.total)" will only read 1 column out of potentially hundreds. The latter query will be billed identically as a filtered query like "SELECT SUM(transaction.total) WHERE transaction.total > 10." Filtering on a different column will be billed more, because it needs to read the second column as well, regardless of how many rows (if any!) contribute to the result set. Idiomatic BigQuery will make use of partitioning, so that a large dataset will span multiple tables in a way that you only read the tables of interest. (E.g. the Google Analytics integration partitions by date, so reporting on 1 month of data will only read 30 tables out of the 1,500 you might have.)
    - noer 8y ago
      It's worth noting that depending on the amount of Data you capture in GA, each date table could be anywhere from 1s to 10s of GBs, and it can get pretty pricey to query from those tables.
noer 8y ago
I had used Data Studio to provide visualizations of data I had in BQ. I wasn't able to create anything useful without creating a bunch of custom views in BQ. Data Studio seemed to refresh the data a few times a day, which queried the BQ views . It ended up being annoying enough from a pricing perspective that I stopped using Data Studio.
- havermeyer 8y ago
  Give BI Engine a try! https://cloud.google.com/bi-engine/docs/ https://cloud.google.com/bi-engine/docs/. The model is that you pay for storage--currently free during the beta--but any queries from Data Studio don't incur costs. It was just announced during Next this week. As a disclaimer, I'm an engineer on the BigQuery team (but not BI Engine specifically).
  - polskibus 8y ago
    what happens if the data in bigquery changes, for example a row inserted? Are there any additional costs related to invalidation of the in-memory cache (ie the BI Engine) ?
    - havermeyer 8y ago
      Tables created in projects that have BI Engine enabled support streaming inserts, DML operations, etc. just like other BigQuery tables. Any queries you run using BigQuery itself incur regular query costs (https://cloud.google.com/bigquery/pricing#queries https://cloud.google.com/bigquery/pricing#queries) but there are no extra charges beyond the storage costs (https://cloud.google.com/bi-engine/pricing https://cloud.google.com/bi-engine/pricing) for BI Engine to manage the data for you behind the scenes.

gundmc 8y ago

We'll have to wait for details about connected sheets, but BQ itself caches queries by default for 24 hours and you're not charged for cache hits. I assume something similar applies.

polskibus 8y ago
That would assume the underlying data does not change. If you use BiqQuery with ingest runnig 24/7, you often don't want to be served from cache. Besides, filters and slicers can be modified on a per user basis, that would trigger recalculations. Again - not sure if a cache would handle this.
- lukasb 8y ago
  BigQuery is usually intended for data warehouse use cases, where missing the most recent 23 hours of data is less of a big deal. It's intended to feed things like exec dashboards, not production systems.
  - hulahoof 8y ago
    I believe the views will refresh when the underlying data changes, so if you are streaming or inserting frequently the costs could add up
    - SmirkingRevenge 8y ago
      Big query isn't a great use case if data that needs to be queried repeatedly and is updating that frequently, I don't think. With streaming inserts, you probably should generally only be doing inserts on the latest date/time based partition, so that you can take advantage of cached queries for all data but the most recent date/time partition.
- kyrra 8y ago
  It's important to remember how BigQuery actually stores it's data, with a columnar base datastore called Capacitor[0]. While you write data as a row, BigQuery does a certain amount of preprocessing on that data to turn it into column-oriented data. Writing a single new row is non-trivial, so it tends to write data in batches (though there is some other magic going on here I probably don't understand). [0] https://cloud.google.com/blog/products/gcp/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/products/gcp/inside-capacitor-...

dajonker 8y ago

That depends on how many columns your pivot table uses. Less columns = less data = lower price. If the implementation is smart enough it could use BigQuery partitions for further cost savings.

danielecook 8y ago
Bigquery also uses caching which presumably is used here.
polskibus 8y ago
Is the data size calculated based on raw data on which queries operate (for example all transactions with amount column), or on query result size (for example sum(amount) -single row) ?
- lmkg 8y ago
  BigQuery bills based on amount of data read. BigQuery is a column-store database, so a read will always touch every row of a table, but only the columns actually used. So e.g. "SELECT * LIMIT 1" will read the entire table, while "SELECT SUM(transaction.total)" will only read 1 column out of potentially hundreds. The latter query will be billed identically as a filtered query like "SELECT SUM(transaction.total) WHERE transaction.total > 10." Filtering on a different column will be billed more, because it needs to read the second column as well, regardless of how many rows (if any!) contribute to the result set. Idiomatic BigQuery will make use of partitioning, so that a large dataset will span multiple tables in a way that you only read the tables of interest. (E.g. the Google Analytics integration partitions by date, so reporting on 1 month of data will only read 30 tables out of the 1,500 you might have.)
  - noer 8y ago
    It's worth noting that depending on the amount of Data you capture in GA, each date table could be anywhere from 1s to 10s of GBs, and it can get pretty pricey to query from those tables.

noer 8y ago

I had used Data Studio to provide visualizations of data I had in BQ. I wasn't able to create anything useful without creating a bunch of custom views in BQ. Data Studio seemed to refresh the data a few times a day, which queried the BQ views . It ended up being annoying enough from a pricing perspective that I stopped using Data Studio.

havermeyer 8y ago
Give BI Engine a try! https://cloud.google.com/bi-engine/docs/ https://cloud.google.com/bi-engine/docs/. The model is that you pay for storage--currently free during the beta--but any queries from Data Studio don't incur costs. It was just announced during Next this week. As a disclaimer, I'm an engineer on the BigQuery team (but not BI Engine specifically).
- polskibus 8y ago
  what happens if the data in bigquery changes, for example a row inserted? Are there any additional costs related to invalidation of the in-memory cache (ie the BI Engine) ?
  - havermeyer 8y ago
    Tables created in projects that have BI Engine enabled support streaming inserts, DML operations, etc. just like other BigQuery tables. Any queries you run using BigQuery itself incur regular query costs (https://cloud.google.com/bigquery/pricing#queries https://cloud.google.com/bigquery/pricing#queries) but there are no extra charges beyond the storage costs (https://cloud.google.com/bi-engine/pricing https://cloud.google.com/bi-engine/pricing) for BI Engine to manage the data for you behind the scenes.