An 8 TB event table that scans 1.1 TB for a single-customer query

Why ingestion_time is wrong. Ingestion-time partitioning is only a proxy for event time when data is never backfilled. Because 6-month-late backfills land in today's ingestion partition, a filter on event_date prunes nothing - the query fans out across every partition that happens to hold matching rows, up to the whole table when a backlog is replayed. It also creates one partition per load, so the partition set fragments and the streaming buffer complicates DML.

What partitioning alone buys. Repartition on DATE(event_date): about 730 daily partitions at ~11 GB each. A 90-day query prunes to roughly 90 x 11 GB = 990 GB. That matches the observed 1.0-1.2 TB almost exactly - so partitioning alone does not fix the cost. It only prevents the pathological full-table scans and makes partition-level operations (retention, purge scoping) possible.

What clustering buys. Cluster on customer_id first - it is the most selective predicate, has 120k distinct values, and appears in 95% of queries - then a secondary column such as event_type if it is a common filter. Order matters: the leading column delivers the pruning. Within a daily partition a single customer is roughly 11 GB / 120,000 = ~92 KB. BigQuery prunes at column-chunk granularity, so expect two to three orders of magnitude of reduction, not exact isolation - the physical block size sets the floor.

Therefore measure, do not assume. Run representative queries before and after with INFORMATION_SCHEMA.JOBS bytes_billed. If a single day's blocks are too coarse to separate one customer, add the daily aggregate layer (next task) rather than pushing harder on clustering. Also set require_partition_filter = TRUE so the filter is never optional.

Date-sharded tables: reject. Wildcard queries scan every shard unless _TABLE_SUFFIX is filtered; there is no single-table access control or atomic DML; purging one customer means looping over 730 tables and tolerating partial failure; and DDL/quota overhead grows monthly. This is a pre-partitioning pattern with no remaining advantage.

Daily aggregate materialized view: adopt for the dashboard layer. Something like customer_id, event_date, event_type, COUNT(*), SUM(...) is roughly 1/50th of raw bytes, so a 90-day single-customer query drops from ~1 TB to single-digit MB. The constraints matter: the MV is only used when the query matches its shape (limited join and no window-function support), and refresh is automatic but not on your schedule - so validate staleness against the freshness requirement. Keep the clustered base table for drill-down detail; the MV is an accelerator, not a replacement.

BI Engine: latency tool, not a cost tool. Its in-memory reservation is on the order of tens to hundreds of GB, one to two orders of magnitude below 8 TB. It cannot hold this table, and it does not make an arbitrary 90-day predicate cheap. It is the right addition once the MV and clustered table exist, to make dashboards feel instant.

Composition: clustered, event-date-partitioned base table (source of truth and detail) + materialized view (aggregate dashboards) + BI Engine (latency) - plus partition-filter enforcement, which is what actually prevents the accident.

Enforcement, layered:

• require_partition_filter = TRUE on the base table and the MV - a query without a partition predicate errors instead of scanning.
• Expose an authorized view with the filter baked in as the only analyst-facing surface; grant no direct table access.
• A project-level custom quota on query_usage bytes plus maximum_bytes_billed on the BI and scheduled-query service accounts, so a runaway query fails at a known cost.
• Alert on INFORMATION_SCHEMA.JOBS rows where total_bytes_billed exceeds a threshold - catch the next bad pattern rather than the last one.

Backfill. Writing by event_date means late loads land directly in the correct partition with no skew - the exact property the ingestion-time design destroyed. Batch backfills must carry the same partition predicate, run on FlexRS, and be capped by maximum_bytes_billed.

Legacy streaming writers - this is the purge blocker. Rows written with the legacy API sit in the streaming buffer, where DELETE, MERGE and UPDATE cannot touch them for up to ~90 minutes. A customer whose rows are buffered cannot be purged on demand. Migrate those writers to the Storage Write API using application-created (pending) streams, whose committed data goes directly to managed storage and is immediately eligible for DML. The default stream keeps the buffer behaviour, so the stream type is the detail that matters.

Purge. DELETE FROM events WHERE customer_id = ? AND event_date BETWEEN ... with clustering touches only matching blocks per partition - seconds to minutes. A partition drop is not an option because one customer's rows span all 730 partitions, and a drop would delete other customers' data. Log every purge to an audit table and assert zero remaining rows afterwards.