A Looker Studio dashboard on BigQuery bills far more than expected — how do you diagnose and cut it?

## Why dashboards are the classic cost surprise Analysts think in pages; BigQuery bills in jobs. A single report page with twenty tiles produces twenty query jobs. Change one filter and it produces twenty more. Open it in ten browsers and the multiplier is a hundred. Each job is billed for the bytes its scan reads, and because BigQuery is columnar, the bytes depend on which columns each tile touches — not on how few rows the tile displays. That last point is the misconception worth naming out loud: adding `LIMIT 100` to a chart's query changes nothing about the cost, because the limit is applied after the scan. Aggregating over a year of a wide fact table to draw twelve bars costs the same whether the chart shows twelve bars or twelve thousand. ## Diagnose before you optimise Everything you need is in `INFORMATION_SCHEMA`. The jobs views are partitioned by `creation_time`, so always bound the range: ```sql SELECT user_email, COUNT(*) AS jobs, SUM(total_bytes_billed) / POW(1024, 4) AS tb_billed FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND job_type = 'QUERY' GROUP BY user_email ORDER BY tb_billed DESC; ``` Group a second pass by the normalised query text to find the individual tile that dominates, and look at the `labels` column — jobs from BI tooling and scheduled refreshes can be distinguished from human ad-hoc work, and any pipeline you control should be labelling its own jobs anyway. The goal is a sentence like "four tiles on one report account for most of the weekly spend", because that tells you exactly what to fix. ## Fix at the source **Aggregate.** A dashboard almost never needs event-level rows. Build a daily (or hourly) summary table at the grain the charts actually display, refreshed by a scheduled query or a materialized view, and point the report there. Scanning a rollup of millions of rows instead of a fact table of billions is a change of orders of magnitude, and it is the single highest-leverage move. **Partition and cluster.** If the dashboard filters on a date range, the source must be partitioned by that date column so the filter prunes; requiring a partition filter on the table stops anyone accidentally scanning all history. Clustering on the dimensions that appear in the report's other filters — country, product line — lets BigQuery skip blocks within the selected partitions. **Read fewer columns.** Custom SQL sources written as `SELECT *` pull every column into every tile. Enumerate what the tile needs. ## Reuse instead of recompute **Report caching.** Looker Studio caches results and re-serves them for a configurable freshness window. Many dashboards are set to refresh far more often than the underlying data changes — a report on a table loaded nightly does not need fifteen-minute freshness. Aligning the freshness setting to the load schedule can remove most repeated jobs outright. **Credential mode.** A data source can run under the owner's credentials or each viewer's. Owner credentials let many viewers share one cached result and give you a single billing project to watch; viewer credentials are what you need when row-level security must filter per person, at the cost of far less cache sharing. That is a security-versus-cost decision to make deliberately rather than by default. **Extracts.** For a small, slowly changing dataset, an extracted data source materialises a bounded snapshot that the report reads without touching BigQuery at all. ## BI Engine BI Engine is BigQuery's in-memory acceleration layer. You reserve capacity in a project and location, and eligible queries are served from an in-memory columnar representation of the hot tables rather than by scanning storage — which is precisely the dashboard access pattern of the same narrow columns over and over. You pay for the reserved capacity by the hour, so it is a fixed cost that replaces repeated scan cost, and it also cuts latency, which is usually what the analysts complained about first. Not every query shape is accelerable; unsupported ones fall back to normal execution, so treat it as an accelerator rather than a guarantee, and use the preferred-tables option to keep the reservation focused on the tables that matter. ## Guardrails Whatever you fix, add a floor under future mistakes: custom quotas that cap bytes billed per user per day, a required partition filter on the big tables, and a scheduled query that reports the top jobs by bytes billed into a monitoring dashboard of its own. And if dashboard traffic is both large and steady, moving that workload onto reserved capacity converts an unpredictable per-query bill into a fixed one — the load shape here, many small repetitive queries, is exactly what reservations exist for.