Question

A finance team uses Connected Sheets to explore a 4 TB BigQuery sales table. Multiple analysts have built dozens of scheduled hourly refreshes, each running pivot tables and charts against the full table. The data engineering team is now receiving alerts about excessive BigQuery scan costs and occasional query failures caused by hitting concurrent query limits. The underlying data only changes once per day after the nightly ETL completes. What is the most effective way to reduce cost and stabilize the workload while preserving analyst self-service?

Accepted Answer

Create a daily-partitioned, pre-aggregated summary table refreshed once after the nightly ETL, point Connected Sheets at it, and set refreshes to daily instead of hourly.

Answer

Ask analysts to switch from Connected Sheets to CSV extracts downloaded manually each morning to avoid running any BigQuery queries.

Answer

Enable BigQuery BI Engine on the project so all Connected Sheets refreshes are served from the in-memory cache regardless of query volume.

Answer

Increase the project's concurrent query and slot quotas so the hourly refreshes no longer fail.