workspace/skills/dbt-incremental-patterns/SKILL.md

name, description

name	description
dbt_incremental_patterns	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

{{ 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:

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:

{% 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:

{{ 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

# 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.