BigQuery full table scans: fixing partition pruning

BigQuery on-demand pricing is simple on paper: $6.25 per tebibyte scanned in US multi-region, with the first 1 TiB each month free. The part that trips people up is how the scan is measured. Billing depends on whether the storage layer actually skipped partitions, not on whether the SQL looks filtered.

A 4 TB events table partitioned by event_date, queried 40 times a day by dashboards, dbt models, and a couple of Connected Sheets, is a $750/day problem the moment those queries miss the partition column, or wrap it in a function BigQuery cannot prune on. Over a month that is north of $20,000, before anyone has looked at SELECT *, clustering, or reservations.

SELECT
  user_id,
  event_name,
  event_ts
FROM `proj.analytics.events`
WHERE DATE(event_ts) = CURRENT_DATE();
-- Table is PARTITION BY event_date.
-- DATE(event_ts) is not the partition column, so every
-- day-partition is read. 4 TB billed, not "today."

Most teams find this by accident: a finance question about a spike, a console screenshot of one job that scanned terabytes, and the discovery that the WHERE clause was on the wrong column, or wrapped in TIMESTAMP_TRUNC(event_ts, DAY) instead of the partition column itself.

The rest of the invoice is real but usually smaller. SELECT * on a pruned day partition is a width problem measured in tens or hundreds of gigabytes. A stale 2 TB snapshot is a storage line item, not a compute one. An oversized reservation is political and slow to change. A missed partition filter on the fact table everyone already queries is the one finding that can move five figures in a week.

Cache hits do not help here. A Looker explore with a changed date filter, a dbt incremental advancing @run_date, and a Sheets refresh re-pulling 'today' all miss the result cache. You pay the full scan every time the predicate changes, even slightly, and the 1 TiB free tier is gone by the second dashboard of the morning.

Regional list prices change the dollar figure, not the mechanism: EU multi-region and Tokyo are not $6.25/TiB, but a partition is still either pruned or it is not. The dollar math in this post uses US multi-region on-demand, since that is the number most invoices and public pricing tools quote. Swap in your region's rate; the 4 TB × 40 jobs arithmetic holds its shape.

Partition elimination happens at the storage layer, before slots start reading column data. For ingestion-time (_PARTITIONTIME / _PARTITIONDATE) and column-based DATE/TIMESTAMP/DATETIME partitions, BigQuery can skip entire partitions when the filter is a simple comparison against the partition column.

SELECT user_id, event_name, event_ts
FROM `proj.analytics.events`
WHERE event_date BETWEEN @start_date AND @end_date
  AND event_name = 'checkout';
-- Storage skips every partition outside [@start_date, @end_date].
-- Clustering on event_name can skip blocks inside those days.

It cannot skip partitions when the filter is wrapped in a function, compared against a subquery that is not constant-foldable, applied only after a JOIN, or written against a different timestamp column that happens to correlate with the partition key. FROM events WHERE DATE(event_ts) = CURRENT_DATE() on a table partitioned by event_date is the classic miss: the query looks date-scoped and still reads every partition.

-- 1) Function wrapping the partition column
WHERE DATE(event_date) = CURRENT_DATE()

-- 2) Filter on a correlated timestamp instead
WHERE event_ts >= TIMESTAMP(CURRENT_DATE())

-- 3) Date dim join BigQuery cannot fold into a partition range
WHERE d.calendar_date = CURRENT_DATE()

Prunable shapes are boring on purpose: equality, IN of literals, BETWEEN, and comparisons against CURRENT_DATE() / DATE_SUB() / query parameters that fold to a constant range. event_date = @day, event_date BETWEEN @start AND @end, and event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) are the shapes you want in every production query against a date-partitioned fact table.

INTEGER_RANGE partitions follow the same rule with a different column type. The filter has to compare the integer partition column directly, not a hash computed in the SELECT list, and not MOD(user_id, 4000) if the table was partitioned on RANGE_BUCKET(user_id, GENERATE_ARRAY(...)). If you cannot point at the partition expression in the DDL and the same identifier in the WHERE clause, assume you will pay for the full table.

Clustering is not a substitute for partition pruning. A table clustered on account_id, event_name but partitioned by event_date still reads every date partition that survives the date filter; clustering only skips blocks inside those days. Get the partition predicate to storage first, then worry about cluster keys.

Parameterized queries are fine as long as the parameter is a direct comparison operand on the partition column. They fail when the parameter is concatenated into a string, fed to FORMAT_DATE, or used only on a joined dimension. Dry-run bytes in the console are the cheapest check: if estimated bytes are close to table size, the prune did not happen, no matter how clean the SQL looks.

-- Console dry-run / jobs.query dryRun: compare totalBytesProcessed
-- to table size. If they match, storage did not skip partitions.
SELECT COUNT(*)
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE();
-- Expect ~1/retention of table bytes, not the full 4 TB.

BigQuery lets you set require_partition_filter = TRUE on a table so queries that omit a partition filter fail instead of scanning everything. That is the right default for fact tables, and it is also incomplete: it does not catch queries that include a filter BigQuery cannot use, it does not help unpartitioned tables with a DATE column everyone filters in BI tools but never encoded into the table layout, and it cannot be retrofitted onto tables that downstream tools query without a date parameter. Connected Sheets and some Looker explores will start failing outright.

CREATE TABLE `proj.analytics.events`
PARTITION BY event_date
CLUSTER BY account_id, event_name
OPTIONS (
  require_partition_filter = TRUE
);

ALTER TABLE `proj.analytics.events`
SET OPTIONS (require_partition_filter = TRUE);

The practical version of this control is to fail closed on new fact tables, then audit the jobs that still scan the whole table anyway. Most teams skip that second part, because INFORMATION_SCHEMA.JOBS is large and noisy and not organized around 'this table was billed as a full scan.'

The error users see is a hard failure: Cannot query over table without a filter over column event_date. Treat the first week after ALTER TABLE ... SET OPTIONS as an incident window, not a surprise. Announce it, point callers at a contract view that already has the date predicate, and keep an unrestricted view for the rare forensic query that legitimately needs the full table.

Views do not inherit the option the way people hope. A view that filters event_date will satisfy the requirement for its callers. A view with no predicate will fail for every caller once the base table requires a filter, and a view that wraps DATE(event_ts) will pass the requirement and still full-scan. The option checks that a filter is present, not that it is usable.

Scheduled queries with a hardcoded WHERE event_date >= '2000-01-01' also pass. The job_stages READ will still show every partition: the filter satisfies the rule without pruning anything. require_partition_filter stops the SELECT * FROM events with no WHERE clause at all. It does not stop a Looker explore that filters the wrong timestamp.

-- Compiles. require_partition_filter is satisfied.
-- Storage still reads every day because the predicate is a no-op range.
SELECT user_id, event_name
FROM `proj.analytics.events`
WHERE event_date >= DATE '2000-01-01';

The cousin of the missing partition filter is a table that was never partitioned at all. An 800 GB orders table with an order_created_at TIMESTAMP, queried with WHERE order_created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY), still reads the whole table: the filter runs after the read, not before it. You pay for 800 GB every time.

SELECT order_id, amount
FROM `proj.analytics.orders`   -- no PARTITION BY
WHERE order_created_at >= TIMESTAMP_SUB(
  CURRENT_TIMESTAMP(), INTERVAL 30 DAY
);
-- Filter runs after the 800 GB read. You still pay for the heap.

Partitioning the table by DATE(order_created_at) and filtering that column, or clustering if access is not strictly date-ranged, is the fix. Diagnosis is the harder part: join job referenced_tables to table DDL, notice the table has a date-like column, notice the query did not filter it, and estimate what fraction of bytes would have been skipped. Doing that by hand across 30 days of jobs is a weekend project, and doing it once and never again is how the waste comes back.

CREATE TABLE `proj.analytics.orders`
PARTITION BY DATE(order_created_at)
CLUSTER BY account_id
AS SELECT * FROM `proj.analytics.orders_heap`;

SELECT order_id, amount
FROM `proj.analytics.orders`
WHERE DATE(order_created_at)
  >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

A table can be unpartitioned with a DATE column nobody filters, or a TIMESTAMP that BI tools filter in a way that never becomes a storage predicate. Both are UNPARTITIONED_DATE_SCAN candidates. A large heap with no temporal column at all is a different finding, a layout advisory with no honest dollar figure, since there is no date range to assume.

Partition expiration is the other half of layout. If the business only queries the last 90 days, PARTITION BY DATE(...) with partition_expiration_days = 180 is cheaper than keeping five years of heap for the rare forensic query that should hit a cold archive instead. Expiration is not pruning; it just means the bytes stop existing. Dropping old partitions does not teach today's jobs to skip the ones that remain.

BigQuery's per-table partition limit is 10,000 for date and ingestion-time partitions, up from an older 4,000-partition ceiling. INTEGER_RANGE and over-granular TIMESTAMP partitioning can still blow past what you intended. Daily partitions on a seven-year fact table are fine. Hourly partitions on a low-volume table mostly add metadata overhead for no pruning benefit. Match the grain to the filters you actually run.

Clustering-only is a legitimate alternative when access is 'this account_id, any time' rather than 'this date range, any account.' Do not partition a 2 TB dimension on a date nobody filters just to quiet the scan rule. Partition for time-ranged facts, cluster for selective lookups, and plan on require_partition_filter once the rewrite ships so the fix cannot regress.

Query text can mislead in both directions. A query can mention the partition column in a CTE that never reaches the base read, or look unfiltered in SQL while inheriting a partition filter from a view or from BigQuery's constant folding. Grepping INFORMATION_SCHEMA.JOBS.query for the partition column name will both over-count and under-count.

WITH recent AS (
  SELECT * FROM `proj.analytics.events`
  WHERE event_date = CURRENT_DATE()   -- looks pruned…
)
SELECT e.*
FROM `proj.analytics.events` AS e    -- …this READ is not
JOIN recent USING (user_id);

The reliable signal is job_stages: the READ operator against the source table reports whether a filter was pushed to storage. SlashPeak fetches job_stages with the rest of the jobs query, minimizes the plan to source-table READ stages, and only flags jobs where the plan is present. Jobs without plan data are skipped rather than guessed, since a false positive on a 4 TB table wastes an engineer's afternoon.

Views are the usual source of confusion. A view that filters event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) can make caller SQL look unfiltered while the billed READ is pruned. A view that wraps DATE(event_ts) does the opposite: caller SQL looks date-aware while the billed READ is a full scan. Trust the READ operator's filter substeps, not the view definition you remember writing.

job_stages is a nested JSON array on INFORMATION_SCHEMA.JOBS_BY_PROJECT, the same shape as the Jobs API query plan. What matters is the steps that read a managed table and whether those steps list a predicate on the partition column; shuffle, AGGREGATION, and JOIN stages are noise for this finding. Minimize to source-table READs, or the full plan will drown any spreadsheet you dump it into.

SELECT
  job_id,
  total_bytes_billed / POW(1024, 4) AS tib_billed,
  ARRAY_LENGTH(job_stages) AS stage_count,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND cache_hit = FALSE
  AND total_bytes_billed >= POW(1024, 4)
ORDER BY total_bytes_billed DESC;

EXPLAIN is not a substitute for the billed plan. EXPLAIN shows an estimate; the job that actually ran is what you pay for, and total_bytes_billed is what records it. A planner change, a slot shortage, or a runtime filter can make EXPLAIN and the invoice disagree, so cost review uses JOBS.total_bytes_billed plus the stages of that same job_id.

MERGE, UPDATE, and DELETE are skipped by the full-scan rule on purpose. Their billed READ is often the target table being rewritten, and the right fix is an incremental predicate on the MERGE itself, not a WHERE clause as if it were a SELECT. Mixing those into FULL_TABLE_SCAN produces tickets nobody can close.

Fix order matters. Changing a dashboard filter is cheaper than rewriting a 200-model dbt project, and adding require_partition_filter to a table that Connected Sheets hits will cause failures the analytics team sees first, so sequence the work accordingly.

Start with attribution on the finding: which principal, which workload source, which environment. A Looker explore on prod.events is a different ticket from a dbt incremental on staging.events. If you rewrite the wrong producer, billed bytes on the table will not move and you will conclude the finding was wrong.

Rewrite the hot SELECT jobs so the partition column is a direct comparison. Push the date range into the view, the Looker explore, the dbt var, and the Sheets custom query. Do not wrap the column, and do not filter a different timestamp and hope correlation is good enough for storage.

Only after the known producers compile with a real predicate do you fail closed. require_partition_filter is the regression test; expect a short burst of failures as it does its job. Keep a documented, BATCH-only path for the rare query that legitimately needs many partitions.

Re-scan. Estimated savings on the table should fall as remaining jobs shrink. If the dollar figure is unchanged, you fixed SQL that was not the billed READ: a CTE that never hit storage, a view that was not what Looker used, or a scheduled query that still wrapped DATE(event_ts). Go back to job_stages for the representative job on the finding.

SlashPeak never reads table contents. It queries INFORMATION_SCHEMA for the last 30 days of job history (including execution plans), table DDL, and storage. The partition and date-filter rules run on that metadata, group hits by table, and convert billed bytes to a monthly run-rate using BigQuery's on-demand list price.

Each finding includes workload attribution: which users and service accounts ran the jobs, whether they look like dbt, Looker, Connected Sheets, or ad-hoc console work, and whether referenced datasets look like prod, staging, or dev. That is the difference between 'this table is expensive' and 'the finance Looker explore on prod.events is the thing to change.'

The free 30-day trial (no credit card; our team approves each request) runs the full 30-day scan, including these two checks. You do not need a sales call to see whether full-table scans are your largest line item: for most BigQuery projects we see, they are.

Savings math is deliberately coarse: about 80% of billed bytes on matching jobs, priced at on-demand $/TiB, scaled to a month. It assumes a typical date filter would have skipped most partitions. It is not your CUD, it is not Editions, and it is not a promise that every remaining byte disappears. It is a ranking signal so you fix the 4 TB table before the 8 GB one.

Jobs without plan data are skipped. Cache hits are skipped. DML is skipped. Sub-1 GiB scans are skipped. Those cuts exist so a staff engineer can trust the list enough to spend an afternoon on the first row. A tool that flags every SELECT against events will not get a second scan.

Take a PARTITION BY event_date table whose current size is 4 TiB of active bytes, 400 daily partitions, roughly 10 GiB a day. On-demand list is $6.25/TiB. One unpruned SELECT against that table is 4 × $6.25 = $25.00 of compute, ignoring the 1 TiB monthly free tier you already burned on something else.

Now count producers. A Looker explore refreshes 8 times during business hours. Three dbt models read events in the morning warehouse. Two Connected Sheets extracts pull 'the last 90 days' but wrap DATE(event_ts). A data-science notebook reruns a 30-day window without using event_date. That is not 40 identical jobs, but it is 40 paid jobs that all fail to prune. 30 of them are full 4 TiB reads; 10 already filter event_date and bill ~10–80 GiB.

Dollar math for the 30 misses: 30 × $25 = $750/day. Over 30 days that is $22,500 of on-demand compute for reads that should have been a handful of partitions. The 10 well-pruned jobs are ~$15–50/day combined. They are not the problem. The problem is the 30 that look filtered in SQL and are not filtered in storage.

-- 4 TiB * $6.25 = $25 per full scan
-- 30 full scans/day = $750/day
SELECT
  4.0 AS tib_per_scan,
  6.25 AS usd_per_tib,
  30 AS full_scans_per_day,
  4.0 * 6.25 AS usd_per_scan,
  4.0 * 6.25 * 30 AS usd_per_day,
  4.0 * 6.25 * 30 * 30 AS usd_per_30d;

Apply a real predicate (event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) and each of those 30 jobs drops from 4 TiB to about 70 GiB (7 × 10 GiB). Cost per job goes from $25.00 to 70/1024 × $6.25 ≈ $0.43. Thirty jobs: ~$13/day instead of $750, a $737/day, ~$22,100/month delta from one column name.

-- 7 * 10 GiB = 70 GiB ≈ 0.068 TiB * $6.25 ≈ $0.43/job
SELECT
  70 / 1024 AS tib_per_scan,
  (70 / 1024) * 6.25 AS usd_per_scan,
  (70 / 1024) * 6.25 * 30 AS usd_per_day;

SlashPeak will not quote $22,100 exactly. It will take the billed bytes of matching jobs, apply an ~80% savings ratio (you might filter 14 days, not 7; some jobs are already partial), and show a monthly run-rate. The point of the worked example is that you can reconstruct the same order of magnitude from table size × job count × $6.25 without opening a vendor. If your number is $2,000 and the table is 4 TiB with 40 daily jobs, you are still leaving a zero on the table.

Do not annualize $22k into $264k and put it in a board deck. Job mix changes. Someone will add require_partition_filter and the miss count will collapse. Someone else will add a second Looker explore tomorrow. Use a 30-day run-rate, fix the producers, re-scan. The worked example is a calibration check, not a forecast.

You can reproduce the finding without a product. You cannot do it by grepping query text for event_date. The minimum join is jobs that billed serious bytes, tables those jobs referenced, and DDL that tells you whether the table is partitioned and on which column.

Start with JOBS_BY_PROJECT for 7–30 days: QUERY, DONE, cache_hit = FALSE, total_bytes_billed above 1 GiB. Pull job_id, user_email, total_bytes_billed, referenced_tables, statement_type, and job_stages if your principal can read them. Exclude your own scanner jobs or you will rank the audit.

SELECT
  job_id,
  user_email,
  statement_type,
  ROUND(total_bytes_billed / POW(1024, 4), 2) AS tib_billed,
  ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 2) AS usd_list,
  referenced_tables
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND cache_hit = FALSE
  AND statement_type NOT IN ('MERGE', 'UPDATE', 'DELETE')
  AND total_bytes_billed >= POW(1024, 3)
ORDER BY total_bytes_billed DESC
LIMIT 50;

Join referenced_tables to TABLES.ddl (or TABLE_OPTIONS) in the table's dataset. Parse PARTITION BY. If the table is partitioned and the job billed a large fraction of table size, you have a candidate, not proof. Proof is a READ stage with no storage filter on the partition column, or a dry-run that estimates the full table.

SELECT
  table_name,
  REGEXP_EXTRACT(ddl, r'PARTITION BY ([^\s]+)') AS partition_expr,
  REGEXP_CONTAINS(ddl, r'require_partition_filter\s*=\s*TRUE')
    AS require_filter
FROM `proj.analytics`.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
  AND ddl LIKE '%PARTITION BY%'
ORDER BY table_name;

For unpartitioned heaps, look at COLUMNS for DATE/TIMESTAMP/DATETIME fields and at whether any filter in the plan (or, weakly, in the SQL) mentions them. A 800 GB table with order_created_at that never appears in a predicate is the second rule. A 800 GB table with no temporal column is a layout review, not a date-filter finding.

Group by table, not by job. Forty jobs against events are one problem. Ranking by job_id makes you fix a notebook instead of the Looker explore that will recreate it at 9:05 tomorrow. Sum bytes, convert to TiB, multiply by $6.25, scale the window to 30 days. That is the conversation with finance.

Partitioning a 20 GB dimension 'for consistency' usually costs more than it saves. Partitioning has a write cost: a streaming buffer, DML that touches many partitions, slot time on the daily load. The break-even point is a table large enough and queried with a time range often enough that skipped bytes beat that overhead. Under roughly 100 GB and rarely filtered by date, clustering or doing nothing is cheaper than a layout project.

Setting require_partition_filter on a table whose only consumers cannot send a date parameter backfires. Connected Sheets pointed at the raw table, a partner extract that selects everything, and an ELT tool probing schema unfiltered will all fail closed, and someone will revert the option at the first complaint. Put a contract view in front, point those tools at the view, and fail closed on the base table instead.

Rewriting TIMESTAMP filters to DATE(event_ts) on a table partitioned by TIMESTAMP_TRUNC(event_ts, DAY) without checking the partition expression is a common mistake: the partition column has a specific name, and wrapping it in DATE() can disable pruning depending on how the spec was declared. Read the DDL first, then write the WHERE clause to match it exactly.

LIMIT 1000 or TABLESAMPLE will not fix a full scan of a 4 TB table. LIMIT does not cap bytes billed on the partitions already read, and sampling changes the answer to the business question. If the question needs today, filter event_date = CURRENT_DATE(); if it needs a sample, say so in the metric definition rather than applying a cost hack to the fact table.

SELECT *
FROM `proj.analytics.events`
WHERE DATE(event_ts) = CURRENT_DATE()
LIMIT 1000;
-- 1,000 rows returned. 4 TB still billed if event_date
-- never reached storage. Check total_bytes_billed.

Partitioning on a column that is not in the filters gives you clean DDL and the same full scans. Partitioning by ingestion date when every dashboard filters a business date that lags ingestion by hours or days is the usual version of this mistake. The partition key has to be the column the expensive jobs already use, or a column you can make them use, or you end up with two dates and still pay for the heap.

Skip the finding when the job is a one-off backfill you will never rerun. Thirty days of JOBS will still show it, but a $400 backfill that rebuilt history in June is not a $12,000/year problem. Check frequency and workload source before scheduling a rewrite: recurring Looker and dbt jobs belong on the fix list, while a single console job from a departed contractor does not.

dbt incremental models often filter the source in the model SQL and then compile a MERGE whose target READ has no partition predicate. The incremental-looking YAML is not the billed plan. unique_key on event_id without incremental_predicates on event_date is how a 'daily incremental' rewrites 4 TB. That waste shows up as MERGE_REWRITE as well as a full scan of the SELECT that built the source. Fix the compiled MERGE, not the markdown in the PR.

-- models/events.sql (incremental, unique_key = event_id)
-- compiled MERGE ON T.event_id = S.event_id
-- no T.event_date predicate → target READ is the heap
SELECT *
FROM {{ source('raw', 'events') }}
WHERE event_date >= DATE_SUB({{ var('run_date') }}, INTERVAL 1 DAY);

Looker explores built on a SELECT * view of events will generate DATE(event_ts) or a dimension-group on a timestamp that is not the partition column. LookML can look date-aware (a day dimension, a default 7-day filter) and still emit SQL BigQuery cannot prune. Point the explore at a contract view that already predicates event_date, or add a sql: ${TABLE}.event_date >= ... filter that cannot be removed in the UI without a second, explicit always_filter.

Connected Sheets and custom-query extracts are the third, quieter source. A sheet that pulls events for a pivot does not know about PARTITION BY; users type a date in a cell and wrap DATE(event_ts) because that is the column they see. Each refresh is an INTERACTIVE full scan during business hours, which also collides with the warehouse reservation. Replace the extract with a scheduled, already-pruned table, or a custom query that compares event_date to a parameter.

CREATE OR REPLACE VIEW `proj.analytics.events_bi` AS
SELECT user_id, event_date, event_name, account_id
FROM `proj.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
-- Point Looker / Sheets at events_bi.
-- Base table: require_partition_filter = TRUE.

Dataform, Airflow, and scheduled queries fail the same way dbt does: the date filter is a documented convention in a comment, and conventions regress. Make the partition filter a required parameter with no default of DATE '2000-01-01'. If the orchestrator cannot pass a date, it should not query the fact table.

BI Engine and the result cache can hide the problem in a demo. A dashboard that is fast because it is cached becomes a multi-terabyte job the moment someone changes the date picker and the cache misses. Judge cost from total_bytes_billed on the cache-miss jobs, not from how fast the cached version felt.

Day 1 is measurement, not DDL. Run a 30-day metadata scan, with SlashPeak or the INFORMATION_SCHEMA queries in this post, and rank tables by estimated full-scan dollars. Pick one table; picking three means you rewrite none. Confirm the partition column, the top producers, and that the representative job's plan actually missed storage-level prune.

Day 2 is the cheap producer. Change the Looker explore or the Sheets custom query so event_date is a direct comparison. Dry-run and compare estimated bytes to yesterday's total_bytes_billed. Leave require_partition_filter alone for now, and ship the BI change while the warehouse run still uses the old SQL so you can diff jobs, not feelings.

Day 3 is dbt and scheduled SQL. Add the partition predicate as a required var on the hot models. If the model is an incremental MERGE, add the partition column to the match condition and to incremental_predicates, then compile, run in staging, and read job_stages. Source-only filters do not count.

Day 4 turns on the control, announced ahead of time. Run ALTER TABLE ... require_partition_filter = TRUE after the known producers are live, watch the error rate for a few hours, and fix stragglers with contract views rather than reverting the option. Keep one documented, BATCH-only path for the rare forensic query.

Days 5 through 7 are the loop: re-scan, and the finding's dollar figure should drop. If a new Connected Sheets extract appears, that is the process working, since the next producer is now visible instead of hiding under one large finding. Stick to partition predicates on the table that already hurts rather than starting a new clustering project.