122 lines
3.5 KiB
Markdown
122 lines
3.5 KiB
Markdown
---
|
|
name: dbt_incremental_patterns
|
|
description: "Patterns and best practices for dbt incremental models: delete+insert strategy, watermark filters, is_incremental() blocks, and full-refresh protection. Reusable across any dbt project."
|
|
---
|
|
|
|
# dbt Incremental Model Patterns
|
|
|
|
Use incremental models when source tables are too large to rebuild from scratch on every run. They process only new or changed records.
|
|
|
|
---
|
|
|
|
## Standard Config Block
|
|
|
|
```sql
|
|
{{ config(
|
|
materialized = 'incremental',
|
|
unique_key = ['key_col_1', 'key_col_2'],
|
|
on_schema_change = 'sync_all_columns',
|
|
incremental_strategy = 'delete+insert'
|
|
) }}
|
|
```
|
|
|
|
| Config | What it does |
|
|
|--------|-------------|
|
|
| `unique_key` | Columns that uniquely identify a row — used to delete matching rows before re-inserting |
|
|
| `on_schema_change = 'sync_all_columns'` | Automatically adds/drops columns when the model definition changes |
|
|
| `incremental_strategy = 'delete+insert'` | Deletes matching rows then re-inserts — safe for late-arriving data |
|
|
|
|
---
|
|
|
|
## `is_incremental()` Filter Block
|
|
|
|
Limit the data processed on incremental runs using a watermark on a date/timestamp column:
|
|
|
|
```sql
|
|
where event_date >= '2020-01-01'
|
|
|
|
{% if is_incremental() %}
|
|
and event_date >= (
|
|
select max(event_date)
|
|
from {{ this }}
|
|
)
|
|
{% endif %}
|
|
```
|
|
|
|
- The subquery `(select max(...) from {{ this }})` compiles to the current table's max value.
|
|
- The outer filter (before `{% if %}`) runs on the initial full load.
|
|
- Place the `{% if is_incremental() %}` block **inside** the `where` clause.
|
|
|
|
---
|
|
|
|
## Watermark Pattern (preferred)
|
|
|
|
For better performance, compute the watermark outside the final query:
|
|
|
|
```sql
|
|
{% set max_date_query %}
|
|
select max(event_date) from {{ this }}
|
|
{% endset %}
|
|
|
|
{% if is_incremental() %}
|
|
{% set max_date = run_query(max_date_query).columns[0].values()[0] %}
|
|
{% endif %}
|
|
|
|
-- ... main query ...
|
|
where 1=1
|
|
{% if is_incremental() %}
|
|
and event_date > '{{ max_date }}'
|
|
{% endif %}
|
|
```
|
|
|
|
> If using `sie_dbt_utils`, prefer `{{ sie_dbt_utils.incremental_filter('event_date') }}` — it compiles the watermark at build time.
|
|
|
|
---
|
|
|
|
## Full-Refresh Protection
|
|
|
|
Prevent accidental full-refresh in production by adding this at the top of the model:
|
|
|
|
```sql
|
|
{{ sie_dbt_utils.full_refresh_protection() }}
|
|
-- or, without sie_dbt_utils:
|
|
{% if flags.FULL_REFRESH and target.name == 'prod' %}
|
|
{{ exceptions.raise_compiler_error("Full refresh is disabled in production.") }}
|
|
{% endif %}
|
|
```
|
|
|
|
---
|
|
|
|
## When to Use Each Materialization
|
|
|
|
| Materialization | When to use |
|
|
|-----------------|-------------|
|
|
| `view` | Staging and mart wrappers — always fresh, low overhead |
|
|
| `incremental` | Large tables with frequent appends (>1M rows, updated daily) |
|
|
| `table` | Small reference tables, distribute layer — needs stable snapshot |
|
|
| `ephemeral` | Intermediate CTEs reused across multiple models |
|
|
|
|
---
|
|
|
|
## Incremental Run Commands
|
|
|
|
```bash
|
|
# Normal incremental run (only new records)
|
|
dbt run --select <model_name>
|
|
|
|
# Force full rebuild (use with caution in prod)
|
|
dbt run --select <model_name> --full-refresh
|
|
|
|
# Run only modified models and downstream (CI pattern)
|
|
dbt run --select state:modified+
|
|
```
|
|
|
|
---
|
|
|
|
## Common Pitfalls
|
|
|
|
- **Missing `unique_key`**: Without it, `delete+insert` can't match rows → duplicates.
|
|
- **No `{% if is_incremental() %}`**: Every run re-processes the full history → slow.
|
|
- **Wrong column in watermark**: Use a column that reflects when the record was last modified, not created.
|
|
- **`on_schema_change` not set**: Schema changes will error or silently drop columns.
|