dbt Models Querying Partitioned BigQuery Tables Without Partition Filters

The Short Answer

A dbt model that references a partitioned BigQuery table but filters on a wrapped or incorrect column does not trigger partition pruning — BigQuery scans the entire table and bills you for every byte. The problem is invisible in dbt: the model compiles, runs, and produces correct results. The only signal is a quietly growing bill.

Partitioning is one of the highest-leverage cost controls in BigQuery. If a query uses a qualifying filter on the value of the partitioning column, BigQuery can scan only the matching partitions and skip the rest — a process called partition pruning. When it works, the savings are dramatic. On a 2 TB table partitioned by day with ~50 GB per partition, a correctly filtered query costs roughly $0.25 while a full table scan costs $10 — a 40x difference for the same result. When it does not work — because a dbt model wraps the partition column in a function, filters on the wrong column, or includes no date predicate at all — you pay the $10 every time, silently.

A pattern we see repeatedly in early-stage companies on BigQuery: the data team adds partitioning to their largest tables, calls it "done", and moves on. Nobody checks whether the downstream dbt models are actually triggering pruning. Six months later, the bill has tripled and nobody knows why.

Why dbt Models Silently Bypass Partition Pruning

There are three ways a dbt model can fail to prune partitions, and none of them cause a compilation error or a test failure.

1. The partition column is wrapped in a function.

Queries with predicates like WHERE FORMAT_DATE('%Y-%m-%d %H', ts) = '2025-03-28 20' or WHERE EXTRACT(MONTH FROM ts) = 3 do not prune partitions because they manipulate the partitioning column with unsupported functions. This catches a lot of people because it feels intuitive — you are filtering on a date — but BigQuery cannot statically resolve the expression to a partition range at planning time.

The same trap appears in dbt macros. A utility macro that generates a date filter using DATE(event_timestamp) on a TIMESTAMP-partitioned column looks fine in Jinja but degrades to a full scan at runtime.

2. The filter is on the wrong column.

For ingestion-time partitioned tables, queries must filter on _PARTITIONTIME or _PARTITIONDATE to prune — not on an arbitrary timestamp column in the schema unless that column is also the partition key. This is the single most common mistake we see in fintech and e-commerce data stacks: a table is partitioned by ingestion time, but every downstream dbt model filters on event_date or created_at. The filter is semantically correct but physically useless.

3. No partition filter at all.

Staging and intermediate dbt models frequently omit date filters by design — they are meant to be fully refreshed. On small tables, this is fine. On large production tables with years of history, it is a full table scan on every dbt run. Someone on the team runs a query without a WHERE clause on the partition column and suddenly scans the entire table — all that optimisation work, bypassed by a single careless query.

How to Find the Offending Models: INFORMATION_SCHEMA Diagnostic

This is the query you run first. It pulls from INFORMATION_SCHEMA.JOBS and surfaces models that processed unusually large volumes of bytes, ordered by cost impact. Run it against your project and look for anything above your expected threshold.

SELECT
  job_id,
  user_email,
  query,
  ROUND(total_bytes_processed / POW(1024, 4), 4)      AS tb_processed,
  ROUND((total_bytes_processed / POW(1024, 4)) * 6.25, 2) AS est_cost_usd,
  total_slot_ms,
  creation_time,
  REGEXP_EXTRACT(query, r'`([^`]+)`')                  AS first_table_ref
FROM
  `region-eu.INFORMATION_SCHEMA.JOBS`   -- swap region as needed
WHERE
  DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND total_bytes_processed > 10 * POW(1024, 3)        -- > 10 GB threshold
ORDER BY
  total_bytes_processed DESC
LIMIT 50;

Copy the table references from the first_table_ref column and cross-check them against your partitioned tables. The next query tells you which of those tables have partitioning defined:

SELECT
  table_name,
  partition_column,
  partition_type
FROM
  `your_project.your_dataset.INFORMATION_SCHEMA.PARTITIONS`
GROUP BY 1, 2, 3
ORDER BY table_name;

Now you have a shortlist: expensive jobs that touched partitioned tables. Open the query text from INFORMATION_SCHEMA.JOBS and check whether the WHERE clause filters directly on the partition column — without wrapping functions, without casting, without arithmetic.

To confirm whether pruning is actually happening, use a dry run against the suspect query. In the BigQuery console, paste the query and look at "This query will process X bytes". Then add or fix the partition filter and observe the drop. You can confirm pruning before you pay: run a dry run and compare totalBytesProcessed with and without the partition filter.

A more targeted diagnostic: surface dbt model queries specifically by looking for the /* {"app": "dbt" comment that dbt appends to every query it runs:

SELECT
  job_id,
  REGEXP_EXTRACT(query, r'model\.([^\s"]+)')  AS dbt_model,
  ROUND(total_bytes_processed / POW(1024, 4), 4) AS tb_processed,
  ROUND((total_bytes_processed / POW(1024, 4)) * 6.25, 2) AS est_cost_usd,
  creation_time
FROM
  `region-eu.INFORMATION_SCHEMA.JOBS`
WHERE
  DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND query LIKE '%/* {"app": "dbt"%'
  AND total_bytes_processed > 10 * POW(1024, 3)
ORDER BY
  total_bytes_processed DESC
LIMIT 50;

That dbt_model column will name the exact model costing you money. You now have a prioritised hit list.

---

Running that query manually is fine for a one-off audit. Checking every model across a growing dbt project on an ongoing basis is not — fintel-scan is a free, open-source CLI (MIT licensed) that runs this check and fourteen others locally without needing a live warehouse connection: uvx fintel-scan.

---

How to Fix Each Pattern

Fix 1: Wrapped partition column

If your table is partitioned by a TIMESTAMP column called event_timestamp, your WHERE clause must filter on that column directly:

-- BROKEN: wrapping defeats pruning
WHERE DATE(event_timestamp) >= '2024-01-01'

-- FIXED: filter directly on the timestamp column
WHERE event_timestamp >= TIMESTAMP('2024-01-01')

The key rule is to isolate the partition column when expressing a filter — filters that require data from multiple fields to compute, or that wrap the column in a function, will not prune partitions.

If your dbt model uses a macro like {{ date_filter('event_timestamp') }} that generates DATE(event_timestamp) = ..., fix the macro output — one change, every model that calls it inherits the fix.

For a DATE-type partition column, DATE(event_timestamp) is fine if event_timestamp is a TIMESTAMP — because the partition column itself is DATE. But if the partition column is the TIMESTAMP column directly, the wrapping removes pruning eligibility.

Fix 2: Wrong column (ingestion-time tables)

For ingestion-time partitioned tables, add the pseudo-column filter alongside your semantic filter:

-- Add the pseudo-column filter to trigger pruning
WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)

Better still, migrate these tables to column-based partitioning on your event timestamp. For most new pipelines, column-based partitioning on an event timestamp is easier to query and reason about — and eliminates the two-filter pattern entirely.

Fix 3: Staging models with no filter

For staging models that genuinely need to process recent data only, add an incremental filter. If the model is already materialized='incremental', make sure the incremental predicate references the partition column:

{% if is_incremental() %}
  WHERE event_timestamp >= (
    SELECT DATE_SUB(MAX(event_timestamp), INTERVAL 3 DAY)
    FROM {{ this }}
  )
{% endif %}

The 3-day lookback handles late-arriving data without scanning history. Tune the window to your SLA.

For full-refresh staging models that must scan everything, consider whether the table needs to be partitioned differently — or whether the staging model should be replaced with a lightweight view and the materialisation pushed downstream where filters are always present.

How to Make Sure It Never Happens Again

BigQuery has a built-in solution: you can require partition filters on a table. When enabled, any query that does not include a filter on the partition column will be rejected before it runs — no accidental full table scans, no surprise cost spikes.

Enable it on your largest production tables:

ALTER TABLE `your_project.your_dataset.your_table`
SET OPTIONS (
  require_partition_filter = TRUE
);

Note: this will break any existing dbt models querying that table without a valid partition filter. Run the diagnostic above before you enable this — use the enforcement as a forcing function to fix models proactively, not reactively.

Additional guardrails to build into your workflow:

• Add a dbt custom generic test that checks whether models referencing a flagged partitioned table include a partition column in their SQL. This does not catch all filter patterns, but it surfaces models with no date predicate at all.
• Set a cost threshold alert in BigQuery's Cloud Monitoring or via a weekly INFORMATION_SCHEMA.JOBS scheduled query that pages your team when any single dbt model job exceeds a per-run byte budget.
• Document partition columns in your dbt source YAML. Add a meta: partition_column: event_timestamp tag to each partitioned source. It does not enforce anything, but it gives new engineers the right information at the right time — when they are writing the WHERE clause.

In our work with early-stage fintech and e-commerce companies, the most durable fix is require_partition_filter = TRUE on large tables combined with a cost alert. The top optimisations — partition filters, column selection, materialized views, and query caching — can reduce BigQuery costs by 50–80% in most organisations, and missing partition filters are almost always the largest single item.

We have seen this play out clearly in client engagements. A global payments company came to us with a BigQuery bill that had tripled quarter-on-quarter. The largest contributor was a cluster of dbt intermediate models referencing a 3 TB events table — partitioned by event_timestamp, but filtered with DATE(event_timestamp) throughout. Every dbt run was scanning the full table. Fixing the filter expressions and enabling require_partition_filter cut the per-run cost on that model group by roughly 90%.

If you are curious how this interacts with other dbt cost patterns — over-materialised staging layers, for instance — the post on dbt Staging Layer Materialisation in BigQuery: Fix the Cost Leak covers that angle in detail. And if you suspect CTE re-evaluation is compounding the problem, BigQuery CTEs Re-evaluated Every Time: The dbt Cost You're Missing is worth reading alongside this one.

Frequently Asked Questions

Q: How do I know if a dbt model is actually triggering partition pruning in BigQuery?

A: The most reliable method is a dry run: paste the compiled SQL from dbt compile into the BigQuery console and check the "This query will process X bytes" estimate. Then add or correct the partition filter and compare. If the byte count drops substantially, pruning was not happening before. You can also query INFORMATION_SCHEMA.JOBS and look at total_bytes_processed for recent runs of that model.

Q: Does wrapping the partition column in DATE() always break pruning?

A: It depends on the column type. If the partition column is a TIMESTAMP and you filter with DATE(ts) >= '2024-01-01', pruning is defeated because BigQuery cannot statically resolve the function to a partition range. If the partition column is itself a DATE type, filtering on it directly (without wrapping) is correct. The rule is: filter on the partition column as-is, with no function wrapping, no arithmetic, no concatenation.

Q: Can I use require_partition_filter on existing tables, and will it break my dbt project?

A: Yes — you can alter an existing table to enable require_partition_filter = TRUE. It will immediately cause any dbt model querying that table without a valid partition filter to fail with an error at runtime. This is intentional: use it as an enforcement mechanism after running the diagnostic above and fixing all offending models first.

Q: What is the cost difference between a pruned and an unpruned query in BigQuery?

A: It scales directly with how much of the table history you are skipping. On a 2 TB table partitioned by day at ~50 GB per day, filtering to a single day costs roughly $0.25 at on-demand rates versus $10 for a full scan — a 40x difference. Multiply that by the number of dbt model runs per day and across a project, and the monthly impact is substantial.

Q: My dbt model is incremental — does that mean partition pruning is working automatically?

A: Not necessarily. An incremental model only avoids full-table rebuilds in dbt terms — it does not guarantee that the SQL it generates includes a qualifying partition filter. You need to explicitly ensure that the is_incremental() block generates a WHERE clause that references the partition column directly, without function wrapping, and that the same filter is present in the non-incremental first run if the table is large.

If your team is watching a BigQuery bill grow quarter-on-quarter with no clear culprit, missing partition filters in dbt models are one of the first places to look — and they are almost always fixable within a day once you know where they are. At Fintel Analytics, we have run this exact audit for fintech, payments, and e-commerce clients and consistently found it to be among the highest-ROI interventions in an early-stage data stack. If you want a second pair of eyes on your BigQuery cost profile, our team is ready to help.