BigQuery dashboard refresh costs hundreds a day: SELECT star and no partition filter

Our GCP billing export showed BigQuery analysis costs of 11,400 USD in August, against about 2,300 USD in a normal month. The billing data pointed to one project, analytics-prod, and the INFORMATION_SCHEMA.JOBS view pointed to one service account:

SELECT user_email,
       count(*) AS jobs,
       round(sum(total_bytes_billed) / pow(1024, 4), 1) AS tib_billed
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP('2025-08-01')
  AND job_type = 'QUERY'
GROUP BY user_email
ORDER BY tib_billed DESC
LIMIT 5;

user_email                                       jobs     tib_billed
dashboards@analytics-prod.iam.gserviceaccount.com 35,712   1,447.3
etl@analytics-prod.iam.gserviceaccount.com         9,880     188.0

1.4 PiB billed by the dashboard tool in one month.

Reproducing it

The top queries by bytes billed were all from a new operations dashboard. One tile:

SELECT *
FROM analytics.events
WHERE DATE(event_ts) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND event_name = 'checkout_completed'

Running it with a dry run:

bq query --use_legacy_sql=false --dry_run 'SELECT * FROM analytics.events WHERE DATE(event_ts) >= ...'
# Query successfully validated. Assuming the tables are not modified, running this query will process 41.2 TB of data.

What we ruled out

• A spike in ETL. ETL volume was flat.
• Storage costs. Storage was a separate, stable line.
• Query cache. Results were not cached because the query used CURRENT_DATE() and the underlying table changed every few minutes with streaming inserts.

Root cause

The events table is partitioned by DATE(ingest_time), not by event_ts. The dashboard filtered on event_ts, so BigQuery could not prune partitions and scanned all 40 TB. SELECT * read all 64 columns, including large JSON payload columns, when the tile only counted rows. With 12 tiles refreshing every 15 minutes, a few scans per refresh added up to over a petabyte a month at on-demand pricing.

What is happening under the hood

With on-demand pricing, BigQuery bills by bytes processed: the total size of the columns read in the partitions scanned, regardless of how many rows the result has. Two things decide that number:

• Column pruning: BigQuery is columnar, so only referenced columns are read. SELECT * reads every column. LIMIT does not reduce bytes scanned for regular tables.
• Partition pruning: only partitions matched by a filter on the partitioning column are read. A filter on a different timestamp column does not prune.

Clustering helps further by skipping blocks within partitions when filtering on clustered columns.

Environment

Minimal reproduction

-- scans all partitions: filter is not on the partitioning column
SELECT * FROM analytics.events WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

The fix

1. Filter on the partitioning column and select only needed columns.

SELECT COUNT(*) AS checkouts
FROM analytics.events
WHERE ingest_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY)   -- prunes partitions
  AND event_ts   >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)    -- exact business filter
  AND event_name = 'checkout_completed';

The extra day on ingest_time covers late-arriving events. Dry run: 38 GB instead of 41.2 TB.

2. Require partition filters on large tables so a query without one fails instead of scanning everything:

ALTER TABLE analytics.events SET OPTIONS (require_partition_filter = TRUE);

3. Cluster by commonly filtered columns:

CREATE TABLE analytics.events_v2
PARTITION BY DATE(ingest_time)
CLUSTER BY event_name, country
AS SELECT * FROM analytics.events;

After the switch, the checkout tile scanned 2.1 GB.

4. Aggregate tables for dashboards. Dashboards now read from small hourly aggregate tables built by a scheduled query, instead of the raw events table. Most tiles scan a few megabytes.

5. Cap bytes billed for the dashboard service account's queries. The BI tool sets maximum_bytes_billed per query, so a bad query fails with an error instead of costing hundreds:

Query exceeded limit for bytes billed: 107374182400. 1099511627776 or higher required.

We also set a custom quota on query usage per day for the dashboard project.

Alternatives and trade-offs

• Capacity pricing (editions with slots). Predictable cost for steady heavy workloads; does not fix inefficient queries, it just moves the limit to slot contention.
• Materialized views for common aggregations, maintained by BigQuery. Good for simple aggregations; have restrictions on query shapes.
• BI Engine for in-memory acceleration of dashboard queries. Useful for interactive dashboards on smaller aggregate tables.

How to test the fix

Dry runs for every dashboard query before and after, in CI against the dashboard definitions exported as SQL:

for f in dashboards/ops/*.sql; do
  bytes=$(bq query --use_legacy_sql=false --dry_run --format=json < "$f" | jq -r .statistics.totalBytesProcessed)
  echo "$bytes $f"
done | sort -rn

A check fails if any tile exceeds 10 GB.

Confirming in production

• Dashboard service account bytes billed fell from about 48 TiB per day to 0.3 TiB per day.
• September BigQuery analysis cost: 2,050 USD.
• Dashboard tiles load faster, from 8 to 20 seconds down to under 2.

Regression guard

• require_partition_filter on all tables over 1 TB.
• maximum_bytes_billed set for all BI and ad hoc service accounts.
• A daily query on INFORMATION_SCHEMA.JOBS alerts on any principal billing more than 2 TiB in a day.

Security implications

The dashboard service account had read access to the full events table, including raw payload columns with user identifiers it never needed. Moving dashboards to aggregate tables let us remove that access, which reduces exposure if the BI tool's credentials leak. Column-level security or authorised views are good ways to keep sensitive columns out of reach of reporting tools.

Prevention checklist

• Filter on the partitioning column; check with dry runs.
• Never SELECT * on wide tables in scheduled or dashboard queries.
• Require partition filters on large tables.
• Cap bytes billed per query for automated workloads.
• Serve dashboards from aggregates, not raw events.

References

• BigQuery pricing — https://cloud.google.com/bigquery/pricing
• Estimate and control costs — https://cloud.google.com/bigquery/docs/best-practices-costs
• Querying partitioned tables and require_partition_filter — https://cloud.google.com/bigquery/docs/querying-partitioned-tables
• INFORMATION_SCHEMA.JOBS view — https://cloud.google.com/bigquery/docs/information-schema-jobs