A 90-Second Dashboard and Silently Dropped Revenue

Forty refreshes an hour times 300 users is 12,000 loads per hour of the same aggregation over 900 GB. Nothing you can buy makes that sensible; the fix is to stop recomputing it.

Materialize at the dashboard's grain. Build a denormalized table, one row per shipment with the six dimensions already joined, partitioned on shipment date and clustered on the columns the dashboard filters by (carrier, lane, status). A dashboard that reads one or two partitions of a pre-joined table returns in a second or two. Refresh it incrementally: MERGE only the partitions whose source rows changed.

Why not a materialized view? An MV would be the right tool if the join were a plain equi-join. Here the join needs carrier normalization (see next task), which is expression-heavy and fragile inside an MV, and MV refresh falls back to full recompute when it cannot be proven incremental. Use an MV for simple aggregations; use a scheduled incremental table for the fuzzy join.

BI Engine is a secondary lever, and only if it fits. A reservation of a few GB cannot hold a 900 GB table; it can hold a 90-day slice of an aggregated table. Decide the reservation size from the size of what the dashboard actually touches, not from the fact table size.

Do not skip the boring part: check the query plan for the 90 seconds before redesigning - if it is a full scan of shipments six times over, the answer may be as small as correcting the date filter shape.

The bug is that the join is silently lossy. Two changes: make the match succeed more often, and make the failures loud.

Canonicalize in a mapping table, not in a regex. Lowercase, strip punctuation, strip trailing numeric suffixes (-01, _2), then resolve against an explicit carrier_alias table that ops owns: acme, acme-01, and ACME CORP all map to canonical ACME. The mapping table is data, so a new source format is a row insert, not a code deploy. Keep the raw carrier_id on every row for audit.

Change the join to a LEFT JOIN with an explicit unknown bucket. Unmatched rows land in carrier_key = 'UNMAPPED' and remain countable. Then revenue totals reconcile to fact row counts by construction, and the reconciliation check is trivial: sum(revenue) by carrier must equal sum(revenue) overall.

Add a pipeline assertion. If the unmapped rate exceeds a threshold - say 1%, down from the current 4% - fail the run and page the owner. A silent 4% loss became invisible precisely because nothing asserted on it. The threshold should be a published number, and the pipeline should write the unmapped-rate time series so you can see a source system change the day it happens rather than at quarter close.

Report the unmapped bucket to ops weekly with sample raw IDs - that is the queue that drives the alias table toward zero.

80/20 first: 70 minutes for 14 derived columns is not a compute problem, it is a startup-and-full-scan problem. Measure the split between cluster provisioning, input read, and actual transform before choosing a replacement - if 40 of the 70 minutes is Dataproc Serverless cold start plus reading the whole fact table, porting the SQL will not help until the read is incremental.

Decision rule:

• If the 14 columns are expressible as SQL - and most derived-column logic is, with window functions handling the ranking/lag cases - move it to an incremental BigQuery MERGE or a dbt incremental model. You remove cluster startup, inherit BigQuery's slot scheduling, and get lineage and tests in the same place as the rest of the warehouse. This is the default answer.
• Keep Dataproc if the logic is genuinely procedural, uses a library BigQuery does not have, or needs ML feature computation over the same data. In that case keep Spark but feed it only changed partitions and cap the cluster lifetime.

Regardless of engine: the job must read only partitions that changed. If the two upstream columns are updated for, say, yesterday and the last three days of late arrivals, the reprocessed set is three partitions, not the whole table - that is where 70 minutes goes to under 5.

The trap to name: shrinking the job to recompute only the columns that depend on the two changed source columns is not safe in general, because downstream columns may depend transitively on them. Recompute the whole derived set for the affected partitions; that is the correct unit of work.