A 4 PB BigQuery Table and a Five-Fold Bill

A full scan of 4 PB = 4,000 TB at $6.25/TB on-demand is about $25,000. So $90k/month is roughly 3.5 full scans per month - the dashboards are scanning essentially the whole table, every time.

Why: partitioning by _PARTITIONTIME prunes only when the filter references the partition column. WHERE DATE(event_ts) = ... references nothing the partitioner knows about, so every query falls back to a full scan. The load-time partition also does not match the analytical question - a backfill or a late-arriving batch lands in today's partition regardless of the event date.

Redesign: recreate events partitioned on the event timestamp itself - PARTITION BY TIMESTAMP_TRUNC(event_ts, DAY) - so the existing filter shape becomes a pruning predicate. Then require a filter must reference the partitioning column, not just any predicate. Set require_partition_filter = true so an unfiltered query fails at planning time instead of spending $25k.

Critical detail the dashboard team must accept: after this change, WHERE DATE(event_ts) = '2024-05-01' is a function applied to the partition column and can defeat pruning on some engines and predicate shapes. Rewrite the query as a half-open range on the raw column: event_ts >= '2024-05-01' AND event_ts < '2024-05-02'. This is a one-line change per dashboard and it is not optional.

Then consider clustering, e.g. on team_id, because with 400 daily partitions clustering only pays off for filters that hit one or two partitions. Verify with dry runs, not intuition.

One table cannot serve a 400-day rule and a 7-year rule cheaply, so split the physical layout while keeping a stable logical surface.

Two tables:

• events - 400-day partition expiration. This is what dashboards and product teams query.
• events_regulatory - same schema, no expiration, populated by a scheduled copy of rows where is_regulatory = true. 2% of 4 PB is ~80 TB, which is large but is the cost of the legal requirement, and it should be stated as such rather than hidden. Because those partitions are never rewritten, most of that 80 TB sits in long-term storage pricing automatically after 90 days untouched - that is the mechanism that makes the number acceptable.

Guard the copy job: it must run before the 400-day expiration deletes anything, and it must be idempotent per partition. A missed copy is unrecoverable data loss, so treat its failure as a page, not a retry.

Access: put each team's rows behind an authorized view that filters on team_id, and grant analysts the view, not the table. Column-level policy tags on sensitive fields. Note the honest limitation: a row-filtering view over a shared table is only efficient if team_id is clustered or the view also constrains the date range - otherwise you have moved the full-scan problem, not solved it.

require_partition_filter stays on for the base table, which also protects the views.

Not yet - and this is the judgement the question is really testing.

Buying slots today would buy enough capacity to keep running full scans. That converts a $90k on-demand bill into a commitment that is priced for a workload that should not exist. Fix pruning first, then re-measure.

Sequence:

1. Fix partitioning and the query shapes. Re-run a month of representative queries and record the new scanned-bytes total. The likely landing zone is a small fraction of today's, because a single day of events is roughly 4 PB / a few hundred partitions - orders of magnitude below 4,000 TB per query.
2. Only then compare the post-fix steady state against capacity. A 100-slot baseline commitment is on the order of a few thousand dollars a month at list slot-hour rates, with a discount for a 1-year commitment - attractive against $90k, and unattractive against a post-fix bill of $10k if the workload is spiky and mostly idle.
3. Match the pricing model to the shape: dashboard workload is bursty and idle overnight, which favors on-demand or an autoscaling reservation with a small baseline; a genuinely 24/7 ETL-heavy profile favors a flat commitment.

Add the governance that prevents a repeat regardless of pricing model: per-team custom quotas or a project-per-team with maximum_bytes_billed on scheduled queries, plus a dry-run gate in CI for any new dashboard query.