You've got a dbt incremental model that works beautifully in development. It processes only new rows, runs in 30 seconds, and your stakeholders are happy. Then you hit production. Your event stream has a 72-hour SLA on late-arriving data, your warehouse table has 3 billion rows partitioned by day, and that elegant little WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) filter is now causing full table scans that cost $40 a run and take 45 minutes. Welcome to incremental models at scale.
The gap between "incremental models work" and "incremental models work efficiently at scale" is wider than most tutorials let on. It involves understanding how your warehouse's query planner interacts with dbt's generated SQL, how partition pruning actually works (and why it often silently fails), and how to design a lookup strategy that handles late-arriving data without scanning your entire history. These aren't just performance tweaks — they're architectural decisions that determine whether your data platform is economical and reliable or quietly burning money and producing stale metrics.
By the end of this lesson, you'll know how to design production-grade incremental models that handle late-arriving data gracefully, enforce partition pruning reliably, and scale to billions of rows without linearly increasing cost. You'll understand the internals well enough to debug query plans, make informed trade-offs between completeness and cost, and build incremental patterns that survive the messy reality of real event pipelines.
What you'll learn:
unique_key-based deduplication and merge-based strategies, and when each one destroys your query performanceThis lesson assumes you're comfortable with:
If you haven't written an incremental model before, work through dbt's own incremental model documentation first. This lesson picks up where that leaves off.
Before we optimize anything, we need to understand what we're optimizing. Partition pruning is the mechanism by which a query engine skips reading entire partitions of data because the query's filter predicates eliminate them at planning time. In a 3-billion-row events table partitioned by event_date, a query with WHERE event_date = '2024-01-15' should touch roughly 1/365th of the data. The keyword is should.
Partition pruning requires the query planner to evaluate filter predicates at planning time, before it starts executing. This means the predicate must be:
Here's the classic dbt pattern that breaks partition pruning:
-- What dbt generates with a naive incremental filter
SELECT *
FROM {{ source('events', 'raw_clickstream') }}
WHERE event_timestamp > (
SELECT MAX(event_timestamp)
FROM {{ this }}
)
The SELECT MAX(event_timestamp) FROM {{ this }} subquery requires the planner to execute a query against your target table before it can evaluate the filter on your source table. In BigQuery and Snowflake, this collapses into a correlated subquery pattern that prevents the optimizer from pruning partitions on the source table. You get a full scan.
Let's look at what actually happens in each warehouse:
BigQuery: BigQuery partitions are pruned based on pseudo-columns (_PARTITIONTIME or _PARTITIONDATE) or on columns declared as the partition column in the DDL. For pruning to work, the filter must be a constant expression or use query parameters — not a subquery result. A WHERE event_date > (SELECT MAX(...)) pattern generates a "non-constant filter on partition column" warning in the query plan, and BigQuery falls back to a full scan.
Snowflake: Snowflake uses micro-partitioning, not traditional partitioning. Its pruning is more flexible — it can prune based on min/max metadata even when the filter isn't a perfect constant. But it still fails when the filter value is a subquery result, because Snowflake's optimizer can't determine the range of the subquery until runtime.
Databricks/Delta Lake: Delta Lake uses file-level statistics stored in the transaction log. Partition pruning happens through partition predicate pushdown and data skipping. Like the others, dynamic filter values from subqueries defeat data skipping at planning time. Delta has a "dynamic file pruning" feature for joins, but this doesn't help with scalar subquery filters.
The fix is to move your filter computation outside the source query and materialize it as a concrete value before the main query runs. In dbt, this means using the is_incremental() macro in combination with explicit, pre-computed filter boundaries.
Here's the corrected pattern:
{{
config(
materialized='incremental',
incremental_strategy='merge',
unique_key='event_id',
partition_by={
"field": "event_date",
"data_type": "date",
"granularity": "day"
},
cluster_by=['user_id', 'event_type']
)
}}
{% if is_incremental() %}
{% set lookback_days = var('incremental_lookback_days', 3) %}
{% set max_date_query %}
SELECT DATE_SUB(MAX(event_date), INTERVAL {{ lookback_days }} DAY)
FROM {{ this }}
{% endset %}
{% set results = run_query(max_date_query) %}
{% set min_date = results.columns[0].values()[0] %}
{% endif %}
SELECT
event_id,
user_id,
event_type,
event_timestamp,
DATE(event_timestamp) AS event_date,
session_id,
properties
FROM {{ source('events', 'raw_clickstream') }}
{% if is_incremental() %}
WHERE event_date >= '{{ min_date }}'
{% endif %}
Notice what changed: we pre-execute a query to get the min date value, then interpolate it as a string literal into the final SQL. The generated SQL that BigQuery or Snowflake sees looks like this:
WHERE event_date >= '2024-01-12'
That's a constant. The query planner can prune partitions at planning time. You went from a full table scan to touching 3 days of partitions.
Warning: The
run_query()macro executes during the dbt compilation phase, which means it runs against your production database even duringdbt compile. This is usually fine, but be aware that it adds a small query to your warehouse billing and requires that the target table already exist (which it won't on a full refresh). Always wraprun_query()calls inside{% if is_incremental() %}blocks.
Late-arriving data is the fundamental tension in incremental model design. Your pipeline processes yesterday's data today. But some events from yesterday arrive today, some arrive tomorrow, and a handful arrive next week because a mobile app was offline. How far back do you look without scanning your entire history?
Before you pick a lookback window, you need to empirically understand your data's arrival latency. This is a query you should run on your raw event data before designing your incremental logic:
-- Measure arrival latency: how late do events actually arrive?
WITH event_arrival AS (
SELECT
DATE(event_timestamp) AS event_date,
DATE(_ingestion_timestamp) AS arrival_date, -- or loaded_at, or _fivetran_synced
DATE_DIFF(
DATE(_ingestion_timestamp),
DATE(event_timestamp),
DAY
) AS days_late
FROM {{ source('events', 'raw_clickstream') }}
WHERE event_timestamp >= CURRENT_DATE - 90
)
SELECT
days_late,
COUNT(*) AS event_count,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_total,
ROUND(100.0 * SUM(COUNT(*)) OVER (ORDER BY days_late) / SUM(COUNT(*)) OVER (), 2) AS cumulative_pct
FROM event_arrival
GROUP BY days_late
ORDER BY days_late
A typical mobile app event pipeline might look like:
| days_late | pct_of_total | cumulative_pct |
|---|---|---|
| 0 | 71.2% | 71.2% |
| 1 | 18.4% | 89.6% |
| 2 | 6.1% | 95.7% |
| 3 | 2.8% | 98.5% |
| 7 | 0.9% | 99.7% |
| 14 | 0.2% | 99.9% |
| 30+ | 0.1% | 100.0% |
This tells you that a 3-day lookback covers 98.5% of late arrivals. A 7-day lookback covers 99.7%. Whether that remaining 0.3% matters depends entirely on your business context. For a fraud detection model, maybe it does. For a marketing attribution report, probably not.
For pipelines where late arrival distributions aren't uniform across event types, you can implement tiered lookback windows per model or even per partition within a model. Here's a macro that makes lookback configuration clean and reusable:
-- macros/get_incremental_min_date.sql
{% macro get_incremental_min_date(
date_column='event_date',
default_lookback_days=3,
var_name='incremental_lookback_days'
) %}
{% if is_incremental() %}
{% set lookback_days = var(var_name, default_lookback_days) %}
{% set query %}
SELECT
COALESCE(
DATE_SUB(MAX({{ date_column }}), INTERVAL {{ lookback_days }} DAY),
DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
)
FROM {{ this }}
{% endset %}
{% set results = run_query(query) %}
{% set min_date = results.columns[0].values()[0] %}
'{{ min_date }}'
{% else %}
-- Full refresh: no date filter
'1970-01-01'
{% endif %}
{% endmacro %}
Now in your models:
-- models/marts/user_events_daily.sql
{{
config(
materialized='incremental',
incremental_strategy='merge',
unique_key=['user_id', 'event_date', 'event_type'],
partition_by={
"field": "event_date",
"data_type": "date",
"granularity": "day"
}
)
}}
SELECT
user_id,
event_type,
DATE(event_timestamp) AS event_date,
COUNT(*) AS event_count,
SUM(revenue_cents) / 100.0 AS revenue,
MIN(event_timestamp) AS first_event_at,
MAX(event_timestamp) AS last_event_at
FROM {{ ref('stg_events__clickstream') }}
WHERE event_date >= {{ get_incremental_min_date(
date_column='event_date',
default_lookback_days=3,
var_name='clickstream_lookback_days'
) }}
GROUP BY 1, 2, 3
This gives you:
--vars for specific runs or CI jobsCOALESCE handles the case where MAX() returns NULL)Tip: Different models in the same pipeline can have different lookback windows. Your raw event model might need 7 days because it aggregates mobile events. Your order-processing model might need only 1 day because your payments system has strict SLAs. Model-specific configuration via
var_nameparameters makes this explicit rather than hiding it in global variables.
Here's something most incremental model tutorials gloss over: your lookback strategy must be idempotent. If you re-run your incremental model with a 3-day lookback twice on the same day, the second run must produce the same result as the first. This seems obvious, but it breaks down in subtle ways.
Consider an aggregate model that computes daily revenue. If a late event arrives and you re-run with a 3-day lookback, you'll re-compute and merge the affected date partitions correctly. But what if your unique_key isn't granular enough?
-- WRONG: This unique_key allows duplicates if event_type changes
unique_key=['user_id', 'event_date']
-- RIGHT: Include all dimensions that determine row identity
unique_key=['user_id', 'event_date', 'event_type']
If you're using incremental_strategy='merge', the merge key determines which existing rows get updated. A unique key that's too coarse means multiple source rows match the same target row, and you'll get non-deterministic results depending on merge execution order.
The merge strategy is the most powerful incremental strategy in dbt, but it's also the most expensive when misused. Understanding what the generated SQL actually looks like — and what your warehouse does with it — is essential for optimizing at scale.
When you configure incremental_strategy='merge' with a unique_key, dbt generates a SQL MERGE statement (or an equivalent transaction in warehouses that don't support native MERGE). For BigQuery, it looks like this:
MERGE `project.dataset.user_events_daily` AS DBT_INTERNAL_DEST
USING (
-- Your model's SELECT query
SELECT
user_id,
event_type,
event_date,
event_count,
revenue,
first_event_at,
last_event_at
FROM (
SELECT
user_id,
event_type,
DATE(event_timestamp) AS event_date,
COUNT(*) AS event_count,
SUM(revenue_cents) / 100.0 AS revenue,
MIN(event_timestamp) AS first_event_at,
MAX(event_timestamp) AS last_event_at
FROM `project.dataset.stg_events__clickstream`
WHERE event_date >= '2024-01-12'
GROUP BY 1, 2, 3
)
) AS DBT_INTERNAL_SOURCE
ON (
DBT_INTERNAL_SOURCE.user_id = DBT_INTERNAL_DEST.user_id
AND DBT_INTERNAL_SOURCE.event_type = DBT_INTERNAL_DEST.event_type
AND DBT_INTERNAL_SOURCE.event_date = DBT_INTERNAL_DEST.event_date
)
WHEN MATCHED THEN UPDATE SET
event_count = DBT_INTERNAL_SOURCE.event_count,
revenue = DBT_INTERNAL_SOURCE.revenue,
first_event_at = DBT_INTERNAL_SOURCE.first_event_at,
last_event_at = DBT_INTERNAL_SOURCE.last_event_at
WHEN NOT MATCHED THEN INSERT
(user_id, event_type, event_date, event_count, revenue, first_event_at, last_event_at)
VALUES (
DBT_INTERNAL_SOURCE.user_id,
DBT_INTERNAL_SOURCE.event_type,
DBT_INTERNAL_SOURCE.event_date,
DBT_INTERNAL_SOURCE.event_count,
DBT_INTERNAL_SOURCE.revenue,
DBT_INTERNAL_SOURCE.first_event_at,
DBT_INTERNAL_SOURCE.last_event_at
)
The MERGE operation must scan the target table to find matching rows. With a proper partition filter on the source side (our lookback window), the source scan is bounded. But the target scan is bounded only if BigQuery can infer partition boundaries from the ON clause. If event_date is both the partition column and part of the unique key, BigQuery can prune target partitions during the merge. If it's not in the ON clause, you'll get a full scan of the target table on every run.
This is the most common performance cliff in incremental model scaling. Your source scan is 3 days. Your target scan is 3 years. Make sure your partition column is part of the unique_key and appears explicitly in the merge ON clause.
Merges are expensive in proportion to the number of rows they process in the target table. For a model with 100 million rows in the target and a 3-day lookback touching 5 million rows, you're looking at merge operations that scan 5 million source rows against a subset of the 100 million target rows. This is manageable.
But consider a case where your unique key includes a high-cardinality column that doesn't map to your partition column. If user_id and event_type are the only unique key columns but your partition is event_date, the merge engine has to scan all target partitions to find potential matches for a given user. This is the fan-out problem — a single source row might theoretically match a target row in any partition, so BigQuery can't prune.
The solution is to always include the partition column in your unique key when using merge strategy:
-- This prevents partition pruning on the merge target
unique_key=['user_id', 'event_type']
-- This enables partition pruning on the merge target
unique_key=['user_id', 'event_type', 'event_date']
For very high-volume models where even a bounded merge is expensive, consider the append-then-deduplicate pattern. Instead of merging, you append new rows and then deduplicate at read time using a view or a downstream model.
-- models/intermediate/int_events__appended.sql
-- This model uses append strategy - no merge, no dedup
{{
config(
materialized='incremental',
incremental_strategy='append',
partition_by={
"field": "event_date",
"data_type": "date"
}
)
}}
SELECT
event_id,
user_id,
event_type,
event_timestamp,
DATE(event_timestamp) AS event_date,
revenue_cents,
_loaded_at
FROM {{ source('events', 'raw_clickstream') }}
{% if is_incremental() %}
WHERE event_date >= {{ get_incremental_min_date(default_lookback_days=3) }}
{% endif %}
-- models/marts/fct_user_events.sql
-- This model deduplicates at read time using window functions
{{
config(
materialized='table', -- or incremental, depending on size
)
}}
WITH ranked_events AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY event_id
ORDER BY _loaded_at DESC
) AS rn
FROM {{ ref('int_events__appended') }}
)
SELECT
event_id,
user_id,
event_type,
event_timestamp,
event_date,
revenue_cents / 100.0 AS revenue
FROM ranked_events
WHERE rn = 1
This pattern trades storage (you're storing duplicates in the intermediate table) for compute efficiency (no merge operations). It works well when:
event_id for updates or correctionsWarning: The append-then-deduplicate pattern creates a growing intermediate table. You need a periodic maintenance strategy — either a scheduled full refresh of the intermediate table, or a partition-replacement strategy where you rewrite old partitions that contain known duplicates.
For aggregate models where you're recomputing metrics per partition, the insert_overwrite strategy (called replace in some contexts) is often dramatically faster than merge. Instead of matching individual rows, you replace entire partitions atomically.
In BigQuery, this is the insert_overwrite strategy. In Databricks, it's the replace_where pattern. In Snowflake, it requires manual implementation with a transaction.
-- models/marts/fct_daily_revenue.sql
{{
config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={
"field": "event_date",
"data_type": "date",
"granularity": "day"
}
)
}}
SELECT
event_date,
event_type,
SUM(revenue_cents) / 100.0 AS total_revenue,
COUNT(DISTINCT user_id) AS unique_users,
COUNT(*) AS total_events
FROM {{ ref('stg_events__clickstream') }}
{% if is_incremental() %}
WHERE event_date >= {{ get_incremental_min_date(default_lookback_days=3) }}
{% endif %}
GROUP BY 1, 2
When dbt runs this with insert_overwrite, it determines which partitions are touched by the source query and atomically replaces only those partitions in the target table. The generated SQL in BigQuery looks like:
INSERT OVERWRITE `project.dataset.fct_daily_revenue`
PARTITION (event_date)
SELECT
event_date,
event_type,
SUM(revenue_cents) / 100.0 AS total_revenue,
COUNT(DISTINCT user_id) AS unique_users,
COUNT(*) AS total_events
FROM `project.dataset.stg_events__clickstream`
WHERE event_date >= '2024-01-12'
GROUP BY 1, 2
There's no target table scan. The planner reads the source (bounded by the lookback), computes aggregates, and writes the results directly to the target partitions. For aggregate models, this can be 10x faster than merge.
Insert overwrite works perfectly for aggregate models because aggregation is idempotent — recomputing an aggregate with the same inputs gives the same result. But for raw event models where you need row-level deduplication, insert overwrite won't help. If you overwrite a partition with late-arriving events, you'll get duplicate rows unless you re-aggregate all events in that partition.
This is the fundamental trade-off:
| Strategy | Deduplicates rows | Partition pruning | Performance at scale |
|---|---|---|---|
merge |
Yes | Only if partition col in unique_key | Moderate — target scan required |
append |
No | Yes | Fast — source only |
insert_overwrite |
Effectively (re-aggregates) | Yes | Fast — no target scan |
For raw event tables: use merge with partition column in the unique key, or append with downstream deduplication.
For aggregate tables: use insert_overwrite.
dbt 1.9 introduced a new incremental strategy called microbatch that takes a fundamentally different approach to the lookback problem. Instead of computing a single lookback window and running one query, microbatch automatically splits your incremental run into multiple queries — one per time period (hour, day, month) — and processes them independently.
With microbatch, you declare an event_time column and a batch_size. dbt determines which batches need to be processed (based on what's already in the target table and the current time), then runs your model's SELECT query once per batch with automatically injected time filters.
-- models/marts/fct_events_hourly.sql
{{
config(
materialized='incremental',
incremental_strategy='microbatch',
event_time='event_timestamp',
begin='2024-01-01',
batch_size='hour',
lookback=3 -- re-process the last 3 batches on each run
)
}}
SELECT
DATE_TRUNC(event_timestamp, HOUR) AS event_hour,
event_type,
COUNT(*) AS event_count,
COUNT(DISTINCT user_id) AS unique_users,
SUM(revenue_cents) / 100.0 AS revenue
FROM {{ source('events', 'raw_clickstream') }}
GROUP BY 1, 2
With this configuration, dbt automatically injects filters like:
WHERE event_timestamp >= '2024-01-15 14:00:00'
AND event_timestamp < '2024-01-15 15:00:00'
Each batch is processed independently. This has several advantages:
Microbatch is powerful but it introduces operational complexity. Each batch is a separate query with its own planning and execution overhead. For hourly batches over a 3-day lookback, that's 72 separate queries per run. For most warehouses, this is fine — query overhead is small compared to actual data processing time. But for warehouses with high per-query overhead (looking at you, Snowflake with warehouse startup costs), this can actually be slower than a single well-optimized query.
The lookback parameter determines how many historical batches get reprocessed on each run. Setting lookback=3 with batch_size='hour' means each run reprocesses the last 3 hours. This handles late-arriving data within the lookback window. Data arriving later than the lookback window won't be reprocessed unless you manually trigger a backfill.
# Backfill a specific time range with microbatch
dbt run --select fct_events_hourly --event-time-start 2024-01-10 --event-time-end 2024-01-13
Tip: Microbatch is best suited for high-volume pipelines where per-batch isolation (restartability and observability) is worth the added query overhead. For simpler cases, a well-crafted lookback window with
insert_overwriteis often cleaner and faster.
One of the hardest problems in incremental model development is verifying correctness. Your model might look right in development but accumulate drift over time because:
Create a separate "audit" run of your incremental model that periodically runs a full refresh against a small time window and compares the results to your production incremental table.
-- models/audit/audit_fct_daily_revenue.sql
-- This model always does a full computation for the trailing 30 days
-- Run it weekly to validate incremental accuracy
{{
config(
materialized='table',
tags=['audit', 'weekly']
)
}}
WITH incremental_results AS (
SELECT
event_date,
event_type,
total_revenue,
unique_users,
total_events
FROM {{ ref('fct_daily_revenue') }}
WHERE event_date >= CURRENT_DATE - 30
),
freshly_computed AS (
SELECT
event_date,
event_type,
SUM(revenue_cents) / 100.0 AS total_revenue,
COUNT(DISTINCT user_id) AS unique_users,
COUNT(*) AS total_events
FROM {{ ref('stg_events__clickstream') }}
WHERE event_date >= CURRENT_DATE - 30
GROUP BY 1, 2
),
comparison AS (
SELECT
COALESCE(i.event_date, f.event_date) AS event_date,
COALESCE(i.event_type, f.event_type) AS event_type,
i.total_revenue AS incremental_revenue,
f.total_revenue AS fresh_revenue,
ABS(COALESCE(i.total_revenue, 0) - COALESCE(f.total_revenue, 0)) AS revenue_delta,
i.unique_users AS incremental_users,
f.unique_users AS fresh_users
FROM incremental_results i
FULL OUTER JOIN freshly_computed f
ON i.event_date = f.event_date
AND i.event_type = f.event_type
)
SELECT
*,
CASE
WHEN revenue_delta > 0.01 THEN 'MISMATCH'
WHEN incremental_revenue IS NULL THEN 'MISSING_IN_INCREMENTAL'
WHEN fresh_revenue IS NULL THEN 'EXTRA_IN_INCREMENTAL'
ELSE 'OK'
END AS audit_status
FROM comparison
WHERE revenue_delta > 0.01
OR incremental_revenue IS NULL
OR fresh_revenue IS NULL
ORDER BY revenue_delta DESC
Pair this with a dbt test that fails if any audit mismatches are found:
# models/audit/schema.yml
models:
- name: audit_fct_daily_revenue
tests:
- dbt_utils.expression_is_true:
expression: "audit_status = 'OK'"
config:
severity: error
dbt's store_failures configuration lets you persist test failures to a table, which you can then monitor over time:
# dbt_project.yml
tests:
+store_failures: true
+schema: test_failures
This creates a test_failures.audit_fct_daily_revenue_expression_is_true table that accumulates failures across runs. You can build a dashboard on top of this to detect gradual drift in your incremental models.
You're going to build a production-grade incremental model for an e-commerce order pipeline. The requirements:
raw.orders table with an updated_at timestampfct_orders_daily computes daily order metrics by status and channelRun this query against your raw orders table to understand the actual late-arrival distribution:
SELECT
DATE_DIFF(DATE(updated_at), DATE(created_at), DAY) AS update_lag_days,
COUNT(*) AS order_count,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM raw.orders
WHERE created_at >= CURRENT_DATE - 90
GROUP BY 1
ORDER BY 1
Based on the results, decide on your lookback window. For this exercise, assume 7 days is needed.
-- models/marts/fct_orders_daily.sql
{{
config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={
"field": "order_date",
"data_type": "date",
"granularity": "day"
},
cluster_by=['channel', 'order_status'],
tags=['daily', 'finance']
)
}}
{% if is_incremental() %}
{% set lookback_days = var('orders_lookback_days', 7) %}
{% set min_date_query %}
SELECT
COALESCE(
DATE_SUB(MAX(order_date), INTERVAL {{ lookback_days }} DAY),
DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
)
FROM {{ this }}
{% endset %}
{% set results = run_query(min_date_query) %}
{% set min_date = results.columns[0].values()[0] %}
{% endif %}
WITH orders AS (
SELECT
order_id,
customer_id,
channel,
order_status,
DATE(created_at) AS order_date,
DATE(updated_at) AS last_updated_date,
order_total_cents,
discount_cents,
shipping_cents,
is_first_order,
created_at,
updated_at
FROM {{ ref('stg_orders__orders') }}
{% if is_incremental() %}
-- Filter on BOTH created_at date AND updated_at date
-- to capture newly created orders AND updated existing orders
WHERE DATE(updated_at) >= '{{ min_date }}'
OR DATE(created_at) >= '{{ min_date }}'
{% endif %}
)
SELECT
order_date,
channel,
order_status,
COUNT(DISTINCT order_id) AS order_count,
COUNT(DISTINCT customer_id) AS customer_count,
SUM(order_total_cents) / 100.0 AS gross_revenue,
SUM(discount_cents) / 100.0 AS total_discounts,
SUM(shipping_cents) / 100.0 AS total_shipping,
COUNTIF(is_first_order) AS new_customer_orders,
MAX(updated_at) AS last_refreshed_at
FROM orders
GROUP BY 1, 2, 3
# models/marts/schema.yml
models:
- name: fct_orders_daily
description: "Daily order metrics by channel and status, with 7-day late-arrival handling"
config:
contract:
enforced: true
columns:
- name: order_date
data_type: date
description: "Date the order was created"
tests:
- not_null
- name: channel
data_type: string
tests:
- not_null
- accepted_values:
values: ['web', 'mobile', 'marketplace', 'in_store']
- name: order_status
data_type: string
tests:
- not_null
- name: gross_revenue
data_type: float64
tests:
- not_null
- dbt_utils.expression_is_true:
expression: ">= 0"
tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns:
- order_date
- channel
- order_status
After running the model, inspect the query plan to confirm partition pruning is working. In BigQuery:
-- Run this in BigQuery console after your model executes
SELECT
creation_time,
total_bytes_processed,
total_bytes_billed,
statement_type,
SUBSTR(query, 1, 200) AS query_snippet
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_type = 'QUERY'
AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
AND query LIKE '%fct_orders_daily%'
ORDER BY creation_time DESC
Compare total_bytes_processed between runs. A properly pruned incremental run should process significantly fewer bytes than a full refresh.
Symptom: Incremental runs are slow and scan the same bytes as a full refresh.
Diagnosis: Run EXPLAIN (Databricks/Snowflake) or check the BigQuery query plan for "full table scan" indicators.
Fix: Replace subquery filters with pre-computed literal values using run_query() as shown in the partition pruning section above.
Symptom: Duplicate rows accumulate in your target table, or metrics grow without bound over time.
Diagnosis: Query SELECT unique_key_cols, COUNT(*) FROM model GROUP BY unique_key_cols HAVING COUNT(*) > 1.
Fix: Include all dimension columns that determine row identity in the unique key. Always include the partition column.
Symptom: Revenue figures for recent dates change substantially when you do a full refresh.
Diagnosis: Run the arrival latency analysis query shown earlier. Compare results to your current lookback window.
Fix: Increase the lookback window based on empirical data, not assumptions. Document the data's arrival SLA and the chosen lookback window in your model's description.
Symptom: Model runs correctly in isolation but produces wrong results when downstream models run immediately after.
Root cause: The {% if is_incremental() %} block in Jinja is evaluated at compile time based on whether the target table exists. If you have two models where Model B depends on Model A, and both are incremental, the filter in Model B might be based on stale data from Model A's previous state, not the current run.
Fix: For models with complex dependency chains, consider materializing intermediate models as tables rather than incremental models, or use dbt's --defer flag in development to use production artifacts as the baseline.
Symptom: Incremental runs fail with column mismatch errors after source schema changes.
Fix: Use on_schema_change='sync_all_columns' in your model config to automatically propagate new columns from source to target:
{{
config(
materialized='incremental',
on_schema_change='sync_all_columns'
)
}}
Be careful with this setting in production — dropping columns is irreversible. For production models, 'fail' (the default) is safer. Explicitly handle schema changes with a full refresh on a scheduled maintenance window.
Many teams run dbt build --full-refresh in CI to ensure correctness. For tables with years of data, this makes CI take hours. The fix is to use a dedicated CI schema with a subset of data:
# profiles.yml (CI profile)
ci:
target: ci
outputs:
ci:
type: bigquery
project: my-project-ci
dataset: dbt_ci_{{ env_var('GITHUB_PR_NUMBER', 'local') }}
threads: 8
With a CI-specific schema that contains only 30 days of data, full refreshes are fast and you still validate correctness. Use dbt's --vars to set a reduced date range in CI:
dbt build --full-refresh --vars '{"incremental_lookback_days": 7, "ci_mode": true}'
Let's consolidate what we've built. Production-grade incremental models at scale require decisions at multiple layers:
At the query layer: Partition pruning requires constant predicate values. Replace subquery filters with pre-computed literals using run_query(). Ensure your partition column is part of the merge unique key to enable target-side pruning.
At the strategy layer: Match your incremental strategy to your use case. Use insert_overwrite for aggregates (fast, no target scan). Use merge with a partition-column-inclusive unique key for row-level deduplication. Use append plus downstream deduplication when merge is too expensive.
At the data quality layer: Empirically measure your data's arrival latency before choosing a lookback window. Build audit models that periodically validate incremental accuracy against fresh computations. Use store_failures to detect accumulating drift.
At the operational layer: Make your lookback window configurable via vars so you can adjust for specific backfill scenarios. Implement the get_incremental_min_date macro pattern to standardize behavior across models.
dbt Snapshots for Type 2 SCD: For dimension tables that need full history, dbt snapshots complement incremental models. Understanding when to use each is crucial for modeling slowly changing dimensions correctly.
Partition-level operations in Databricks: Delta Lake's OPTIMIZE and ZORDER commands can dramatically improve query performance on partitioned Delta tables. Running these post-dbt-build on recently modified partitions is a powerful optimization pattern.
dbt's semantic layer and metrics: Once your incremental factual models are solid, explore whether the dbt semantic layer can serve your aggregate use cases, potentially eliminating some incremental aggregate models entirely.
Orchestration patterns for late-arriving data: Tools like Airflow and Dagster have native support for data-driven scheduling. Triggering dbt runs when new data arrives (rather than on a fixed schedule) can reduce lookback window requirements and improve data freshness.
Cost attribution and monitoring: Build a BigQuery or Snowflake cost monitoring model that tracks bytes processed per dbt model per run. This lets you detect regressions in incremental model efficiency before they become costly surprises.
The incremental model patterns in this lesson have been tested in pipelines processing hundreds of billions of rows per day. The fundamentals hold at any scale — the difference is how precisely you apply them.
Learning Path: Modern Data Stack