BigQuery merge scans and runaway BI spend on a 3.2TB fact
A retail media company runs dbt on BigQuery. fct_events is 3.2 TB, partitioned by event_date, with no clustering. It is incremental with unique_key='event_id' and a merge strategy; the is_incremental() filter reprocesses the last three days. The daily run used to scan 60 GB and now scans about 2.9 TB, and warehouse spend has quadrupled in six weeks without a change in row volume. The team added six new models that join fct_events to dim_customer on customer_id, and 1,400 BI queries per day hit fct_events directly, most of them filtered on event_type and customer_id for the last 30 days. A proposal is on the table to switch to insert_overwrite and add cluster_by=['event_id']. Finance wants the run under 400 GB scanned per day; the team also needs to keep late-arriving events, which can arrive up to 10 days after event_date.
1. Explain the 60 GB to 2.9 TB jump mechanistically, then evaluate the `insert_overwrite` proposal and the proposed cluster key.
Read the worked answer
Why the jump. A BigQuery MERGE prunes the target only when the partition column appears as a predicate that the planner can push into the target scan. Here the incremental filter restricts the source to three days, but the target is matched on event_id, which is not the partition column and not clustered. BigQuery has to scan every partition to find matching event_ids: roughly the full 3.2 TB every run. The six new models joining on customer_id widen it further, because customer_id is also neither the partition nor a cluster column, so each join is a full scan. Row volume did not change; the access pattern did.
insert_overwrite - adopt. It writes whole partitions instead of joining on a key, so it touches only the days you select. With a three-day window that is roughly 3/365 of the table, comfortably inside the 400 GB budget. The catch is exactly the one the team flagged: replacing whole partitions discards anything that arrives after the window closes. With up to 10 days of late arrivals, widen the reprocessed window to 11 days rather than 3, or keep a 3-day window plus a weekly backfill job that reprocesses a rolling 14 days. Validate with a daily reconciliation comparing event counts in the last 14 partitions against the source.
cluster_by=['event_id'] - reject. event_id is a unique key; clustering on it helps point lookups and nothing else. All the expensive queries filter on event_type and customer_id, so clustering on event_id adds maintenance cost with no scan reduction. Cluster on the columns the workload filters by instead.
2. Given competing access patterns - six dbt joins on `customer_id` and 1,400 BI queries filtered on `event_type` and `customer_id` for 30 days - choose the clustering and serving design. What do you do about the BI queries themselves?
Read the worked answer
BigQuery clustering helps only when the filter references the leading cluster column, so the choice is not free.
Cluster on customer_id first, event_type second. The dbt joins on customer_id are the expensive internal workload and will benefit directly. BI queries filtered on customer_id also benefit; BI queries filtered on event_type alone will not, because they skip the leading column. Quantify the split before committing - inspect INFORMATION_SCHEMA.JOBS_BY_PROJECT for the filter columns actually used and their frequencies. If event_type-only queries dominate by volume, invert the order and accept slower joins, or serve them from a separate table.
Serve BI from an aggregate. Build fct_events_daily at day × customer_id × event_type - likely two to three orders of magnitude smaller than 3.2 TB - and repoint the 1,400 daily queries that only need counts and spend. Keep fct_events for the six dbt models that need event grain. A BigQuery materialized view is an option for a narrow, stable aggregation, but its SQL surface is limited and refresh cost is easy to underestimate.
Guardrails, because aggregation will not cover every query. Set maximum_bytes_billed on the analyst and BI roles (for example 50 GB per query) so a missing partition filter fails fast instead of billing silently; require a partition filter where possible; and give BI a dedicated role so its cost is attributable. Publish a fct_events_30d convenience model if 30-day windows dominate - a pre-filtered table is cheaper than trusting 1,400 queries to filter correctly.
3. Finance wants scanned bytes under 400 GB per day. How would you verify the redesign actually delivers, and what would you do if late-arriving data forces a wider window than the budget allows?
Read the worked answer
Verification first. Instrument before and after using INFORMATION_SCHEMA.JOBS_BY_PROJECT: total bytes billed per day, bytes per user and per dbt invocation, and the top 20 queries by bytes. Slice by labels set from dbt query tags so you can attribute scans to fct_events builds versus BI. Track for two weeks after the change; the first few days are unrepresentative because clustering and the new partitions need to settle and BI caching shifts.
Check three specific things: that the incremental build's own bytes dropped to roughly the window fraction of the table; that the six joining models no longer full-scan; and that BI bytes moved to the aggregate rather than simply disappearing into a different table.
If late data forces a wider window. Do the arithmetic explicitly: 11 days of partitions is roughly 3% of 3.2 TB per the partition layout, which stays under budget - the three-day window was never the binding constraint, the key-based merge was. If the window genuinely has to be large, use a hybrid: insert_overwrite over the recent window daily, plus a separate weekly backfill job that reprocesses a 30-day range only for partitions where the source reports late arrivals. That keeps the daily job small and quarantines the expensive scan to a scheduled, budgeted job.
If it still does not fit, the honest options are reducing retention on the hot table (export older partitions to cheaper storage), splitting event types into separate tables so each build touches less, or accepting the cost and getting it approved with numbers. Do not silently widen the window and let the budget slip.