Looker Studio spend spike after self-service launch
Meridian Retail opened BigQuery and Looker Studio to 300 analysts six weeks ago. Before that, monthly BigQuery on-demand spend was about $2,000. This month it is on track for $18,000. The central fact table, events, is 8 TB and partitioned by event_date but not clustered. Most dashboards filter on event_name and country, and many queries start with SELECT * because analysts copied a template. Forty dashboards refresh every 15 minutes; several are viewed only once a day. Query history shows that 70% of bytes scanned come from 12 dashboards, and many scans read all partitions because filters are applied in Looker Studio after the query. The team has a flat-rate slot commitment of 500 slots during business hours and on-demand otherwise. Finance wants cost predictability; analysts want dashboards to stay fast. No one wants to rewrite 40 dashboards immediately.
1. Diagnose the most likely reasons bytes scanned are high despite partitioning. What evidence would you pull first?
Read the worked answer
Start with INFORMATION_SCHEMA.JOBS_BY_PROJECT, focusing on total_bytes_billed, query, referenced_tables, and labels. Look for three patterns. First, the query wraps event_date in a function or compares it as a string, which prevents partition pruning. Second, filters are applied outside the scanned subquery, for example a custom SQL block that selects the full table and lets Looker Studio filter afterward. Third, SELECT * scans every column, and the table is wide. Also check refresh frequency: 40 dashboards every 15 minutes is 3,840 refreshes per day, and 12 dashboards dominate.
The judgement is to separate volume from frequency. A query that scans 8 TB once is bad, but a 200 GB query every 15 minutes can cost more. Group jobs by dashboard or user label and compare bytes per run against runs per day. Do not assume partitioning is working just because the column exists; verify that the filter appears in the scanned SQL.
2. Recommend immediate controls and a medium-term redesign. Explain tradeoffs between materialized views, BI Engine, and clustering.
Read the worked answer
Immediate controls: set custom quotas per team or user, require a partition filter on the events table, and fix the top 12 dashboards by replacing SELECT * and moving filters into the scanned query. Reduce refresh frequency to hourly for dashboards not viewed continuously. These changes are reversible and target the 70%.
Medium term: cluster on event_name, country because those are the common filters; clustering reduces bytes scanned within partitions. Create materialized views for the repeated aggregates, accepting storage and refresh cost in exchange for much smaller scans. Use BI Engine for a small set of hot, interactive dashboards, but size the reservation to the hot data and do not treat it as a cure for full-table scans. Keep raw exploration on the 500-slot reservation during business hours so on-demand spend is bounded. The tradeoff is freshness versus cost: materialized views and BI Engine improve speed and reduce repeated scans, while clustering helps ad hoc queries without pre-aggregation.
3. How would you govern dashboard refresh schedules and cost accountability without blocking analysts?
Read the worked answer
Introduce a tiering policy: certified operational dashboards can refresh every 15 minutes; departmental dashboards hourly; exploratory dashboards daily or on demand. Implement refreshes through scheduled queries or materialized views rather than each dashboard independently hitting the raw table. Stagger start times to avoid a thundering herd. Publish a cost dashboard from INFORMATION_SCHEMA showing bytes billed by team label and by dashboard. Give each team a custom quota that is generous enough for normal work but caps runaway queries. Require labels on scheduled queries.
The judgement is to make the default path cheap rather than rely on policing. Provide a curated data mart or materialized view that analysts can use instead of the 8 TB table. Allow exceptions for the 12 heavy dashboards while they are migrated. This keeps self-service open but makes cost visible and bounded.