Build your first incremental dbt model

You built a fact table as materialized: table. Every night it rebuilds the full history, takes twenty minutes, and costs more than it should. This guide walks through converting it to an incremental model on Databricks, with the decisions you have to make at each step.

Before you start

Incremental models are a step up in complexity. Four things must be true before it is worth the cost:

• The table is large. Rule of thumb: full refresh takes longer than ten minutes, or the underlying source has more than a hundred million rows.
• The source has a reliable watermark column (a monotonic timestamp like updated_at or _loaded_at).
• You can answer "what is a unique row?" confidently. That answer becomes your unique_key.
• You are willing to run --full-refresh occasionally. Incremental models are not a silver bullet; they accumulate drift.

1. Pick the incremental strategy

dbt-databricks supports six strategies. The first four cover 95% of cases.

Start with merge if you have updates, append if you do not. You can always switch later.

2. Configure the model

Here is the annotated shape. Your fact table probably has different column names.

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    on_schema_change='append_new_columns',
    tblproperties={'delta.autoOptimize.optimizeWrite': 'true'}
) }}

with source as (
    select * from {{ ref('stg_orders') }}
),
ranked as (
    select *,
           row_number() over (
               partition by order_id
               order by updated_at desc
           ) as rn
    from source
    {% if is_incremental() %}
      where updated_at >= (
          select coalesce(max(updated_at), '1900-01-01')
          from {{ this }}
      )
    {% endif %}
),
final as ( select * except(rn) from ranked where rn = 1 )

select * from final

Four pieces do real work:

1. unique_key — what identifies a row uniquely. For an order it is order_id; for an event table, typically event_id.
2. is_incremental() block — filters the source to rows the target does not have yet. On the first run (table does not exist), this block is skipped and everything loads.
3. on_schema_change: append_new_columns — if upstream adds a new column, add it to the target rather than failing. Additive-only; type changes still require --full-refresh.
4. Deduplication via row_number — protects against duplicate-arrival rows from CDC. Without it, merge raises MERGE_CARDINALITY_VIOLATION.

3. Scope is_incremental() aggressively

The single most important line is the where clause inside is_incremental(). If it is loose, the model reads the entire source on every run and you just bought incremental complexity for no speed.

4. First run

With an empty target, dbt builds the whole table. This takes as long as a table full refresh did. Subsequent runs only touch new rows.

dbt build --select fct_orders

Verify the model ran as incremental on the second run:

dbt run --select fct_orders
# Look for: "1 of 1 OK incremental model dev.marts.fct_orders"

5. When to --full-refresh

Incremental models accumulate drift. Rebuild them from scratch when:

• You change the column list, change a column type, or change business logic.
• Upstream source data was corrected for historical dates.
• on_schema_change: fail triggered; you understood the change; now you want to apply it.

dbt run --select fct_orders --full-refresh

6. Add tests

An incremental model needs a unique test on the unique key and a not_null test on everything declared not-null. Declare them in the model's yml file:

version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: updated_at
        tests:
          - not_null

dbt build runs these automatically after the model compiles. A test failure halts downstream models, which is almost always what you want.

Common mistakes

See also

• Materializations reference — exhaustive options for each strategy.
• Model authoring standards — Causeway's rules for incremental models.
• Failure triage — what to do when an incremental run fails in prod.