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.

Give BI Engine a try! 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).

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) ?

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) but there are no extra charges beyond the storage costs (https://cloud.google.com/bi-engine/pricing) for BI Engine to manage the data for you behind the scenes.

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.

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.

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-...

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.

I believe the views will refresh when the underlying data changes, so if you are streaming or inserting frequently the costs could add up

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.

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.

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) ?

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.)

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.

1s to 10s of GB is pretty much nothing in BigQuery-land. (unless you are using them in production, which as noted elsewhere is a bad idea)

Especially with column-based querying and partitioning.

Bigquery also uses caching which presumably is used here.