In BigQuery's Connected Sheets, what happens when a pivot table refreshes?

## What it is Connected Sheets is a Google Sheets feature that binds a sheet to a BigQuery table or view. The analyst gets the familiar spreadsheet surface — pivot tables, charts, formulas, filters — while the data stays in BigQuery. It exists because the alternative people default to is exporting a CSV, which immediately becomes a stale, ungoverned copy of production data sitting in someone's Drive. ## What a refresh does Every interaction that changes the displayed result is compiled into SQL and submitted as a BigQuery query job. That includes the obvious refresh button, but also adding a column to a pivot, changing a filter value, and scheduled refreshes configured to run on a timetable. Only the aggregated result comes back into the sheet's cells. Three consequences follow. **It is billed like any query.** The job is charged to the billing project selected when the connection was set up — bytes scanned under on-demand pricing, or slot time against a reservation. A pivot over a wide fact table costs the same as the equivalent hand-written aggregate. **It is governed like any query.** The job runs under a Google identity — the person interacting, and for a scheduled refresh the person who set the schedule up. Dataset permissions, authorized views and row access policies all apply. A user who cannot query the table cannot see it through a spreadsheet either, which is why this is the sanctioned alternative to CSV export. **It is as fresh as the last refresh.** The sheet is not live. Between refreshes the cells hold whatever the last job returned, and anyone reading the sheet is reading a snapshot with a timestamp. ## Preview, extract and the sheet's limits Connected Sheets offers a preview of sample rows so an analyst can see the shape of the data without pulling it all. It also offers an **extract**: a bounded subset of rows materialised into the sheet, after which interaction is instant and free but the data is a fixed snapshot until re-extracted. Extracts are right for small, slow-moving reference data and wrong for anything an analyst expects to be current. A sheet cannot hold a large table under any circumstances — spreadsheets have hard cell limits far below warehouse scale. That constraint is a feature: it forces the aggregation to happen in BigQuery, which is where it belongs. ## Cost management The surprise bill from Connected Sheets is almost always shaped the same way: many sheets, each with a scheduled refresh, each pivoting a raw event table. Nobody's individual sheet is unreasonable; the aggregate is. The mitigations are ordinary BigQuery hygiene applied to a new consumer: - Expose a **summary table or view** at the grain analysts actually pivot on, and connect sheets to that rather than to the raw fact table. - Ensure the source is **partitioned** on the date column analysts filter by, and **clustered** on their common dimensions, so filters prune. - Review **scheduled refresh** frequency: a sheet over a nightly-loaded table refreshing hourly does seven-eighths of its work for nothing. - Consider a **BI Engine reservation** in the same location, since Connected Sheets is exactly the repetitive narrow-column access pattern it accelerates. - Apply **custom quotas** so a single user's sheets cannot run away with the month's budget. ```sql CREATE OR REPLACE TABLE `analytics.orders_daily` PARTITION BY order_date CLUSTER BY country, product_line AS SELECT order_date, country, product_line, COUNT(*) AS orders, SUM(total_amount) AS revenue FROM `analytics.orders` GROUP BY order_date, country, product_line; ``` ## How to talk about it in an interview The point to make is not that the feature exists but why it matters organisationally: it moves spreadsheet users from *copying* warehouse data to *querying* it. The copy is what breaks governance — a CSV has no row-level security, no lineage, no freshness, and no way to revoke access. Connected Sheets keeps the data where the controls are and gives up nothing the analyst actually needed.