Quorum Health: BigQuery cost model before commitment

Current spend check: 46 TB/day at $6.25/TB on-demand is about $287/day, roughly $8,600/month. That matches the reported figure, so the number is trustworthy and the model below is calibrated.

Ranked by savings per unit of effort:

1. Partition by DATE(event_date) and cluster by the most common filter column. Highest return, lowest risk. The 12 dashboards are 70% of scans, about 32 TB/day. A dashboard looking at a 90-day window over ~3 years of data prunes to under 10% of the table, so 32 TB/day becomes roughly 2-3 TB/day. Total daily scans fall from 46 TB to about 16 TB, and monthly cost from ~$8,600 to about $3,000. Migration is a CREATE TABLE ... PARTITION BY DATE(event_date) AS SELECT job or a partition swap; no application change.
2. require_partition_filter = true on the table. This is not optional. Pruning only works if queries filter on the partition column with a constant; one dashboard using event_timestamp instead of event_date scans the full 2.8 PB and erases the savings for the month. The table-level flag turns a silent regression into a hard error.
3. Looker-side discipline: aggregate awareness in Looker so the 12 dashboards hit aggregate tables or materialized views rather than the raw events table. This is the second-largest lever and the one that also fixes the 70% concentration risk.
4. Ad-hoc governance for the 60 analysts (20%, ~9 TB/day): per-user and per-project custom quotas in the project, maximum_bytes_billed defaults on the BI tool's service account, and a curated set of views so the common questions do not become full scans. Also expose INFORMATION_SCHEMA.JOBS to the analysts - visibility changes behaviour faster than a quota does.
5. BI Engine reservation sized in GB for the 12 dashboards. Worth it after step 1, when the dashboards are small and hot; buying it first is buying cache for a full table scan.
6. The ELT job is 10% of scan cost - do not optimize it first, even though it is the most visible thing to an engineer who wants to rewrite the pipeline.

Before the commitment decision: complete steps 1 and 2 at minimum, re-measure for two weeks, then model slots against the post-optimization workload. Committing a 12-month floor against a workload you already know is 5x too expensive locks in the waste and destroys your negotiating position with the CFO.

Commit the floor, autoscale the peak, and use a 1-year term - the growth curve rules out a 3-year commitment.

Measurement first, not estimation. Pull total_slot_ms per job from INFORMATION_SCHEMA.JOBS over 30 days post-optimization, bucket into hourly slot usage, and look at P50, P90, and P99. On-demand billing and slot billing are both functions of the same work; the only question is whether the shape of your usage is stable enough to buy at a discount.

The shape I expect here: 12 dashboards refreshing every 15 minutes create a very predictable periodic load with a modest peak-to-median ratio. Ad-hoc analyst traffic rides on top and is spiky but small. That profile favours a reservation with a baseline near the median and autoscaling above it. Pure on-demand wins only when usage is genuinely unpredictable or the monthly total is small.

Concretely:

• 1-year term, not 3-year. Data grows 8%/month and cost has been growing 30%/quarter. A 3-year floor priced off today's workload will be wrong in both directions - too high if step 1 and 2 land, too low in year two. Take the smaller discount for the option value.
• Baseline sized to P50 hourly usage, with autoscaling enabled for the peak. Unused committed slots are paid for and thrown away; that is the asymmetry that makes over-committing worse than on-demand.
• Edition choice driven by which features are actually needed (autoscaling limits, reservation sharing across projects, and the governance features differ by edition). Check current SKU terms before signing; do not let an edition decision be made by whoever ran the spreadsheet.
• Region scoping: commitments are per region. If analytics later expands to a second region for data-residency reasons, the floor does not follow it.

The assumption that flips the recommendation: if the two-week re-measurement shows slot usage is concentrated in the overnight ELT with a long, low-utilisation tail, autoscaling will spend most of the month at baseline and on-demand stays competitive or cheaper. In that case I would not commit at all and would instead cut the workload further. The CFO's condition is not 'buy slots' - it is 'show it beats on-demand', and showing that it does not is an acceptable answer.

At 900 rows/s the volume is trivial; the problem is that streaming breaks the two optimizations you just paid for.

• Streaming buffer and the current-day partition. Newly streamed rows land in a write-optimized buffer. Queries that touch today's data read that buffer, and cost accounting for it is not the same as a clean partition scan. Dashboards that include 'today' will not prune the way a 90-day historical query does.
• DML on just-written rows is expensive. The obvious fix - a scheduled MERGE or UPDATE to enrich streamed rows - rewrites the affected partition, and repeated small DML against the current partition is a slot consumer that shows up on the bill without showing up on anyone's dashboard.
• The 24/7 write pattern makes this permanent, not a nightly batch problem. There is always a hot partition.

What I change:

1. Ingest into a landing table (small, partitioned by hour, short retention) using the Storage Write API rather than legacy insertAll. The Storage Write API gives you exactly-once semantics with offsets, so retries do not duplicate rows - at 900 rows/s a duplicate-generating retry loop is a data-quality bug that also costs slots to clean up.
2. A scheduled MERGE from the landing table into the partitioned events table on a 5-15 minute cadence, writing into closed partitions wherever possible. This moves the DML cost from 'rewrite the hot partition every few minutes' to 'append into a stable partition'. After enrichment, delete the landing partition.
3. Watch INFORMATION_SCHEMA.JOBS_TIMELINE for the ingestion path before and after. If the streaming/merge path is consuming a meaningful share of slot time, it belongs in the baseline reservation, because it is the most predictable load in the building - which actually makes it the best thing to commit slots against.

What I explicitly do not do: skip partitioning because 'streaming breaks it anyway'. The 70% dashboard traffic is historical and prunes cleanly regardless; the streaming path is a separate, smaller problem and should not hold the larger fix hostage.