dbt Incremental Models

The model's SQL runs in 2 modes. The is_incremental() macro returns true only when all 3 conditions hold: the model already exists as a table in the database, the full refresh flag wasn't passed, and the model is configured with materialized='incremental'. Otherwise it returns false and the whole query runs, which is why the SQL must be valid in both modes.

Inside the true branch, {{ this }} refers to the target table, so the usual filter compares the source with the newest value already loaded. A typical filter keeps rows at or after the latest updated_at. It uses >= so that rows stamped at the same moment as the last load are picked up again, and coalesce to a distant date so an empty table loads everything.

The dbt documentation on incremental strategies describes merge as inserting records whose unique_key isn't in the destination and updating records whose key is. By default a matched row is overwritten in full. merge_update_columns restricts the update to the columns you list, and merge_exclude_columns lists the ones to leave alone.

Merge scans the whole destination table to decide what to update or insert, which the documentation calls expensive for large tables. incremental_predicates is a list of SQL expressions that narrows that scan. Inside those expressions, DBT_INTERNAL_DEST is the alias for the existing rows and DBT_INTERNAL_SOURCE the alias for the incoming ones. dbt does not check their syntax. Merge without a unique_key also behaves like append, so a forgotten key produces duplicates with no error.

delete+insert reaches the same end in 2 steps: it deletes the rows for the keys it is about to load, then inserts them. The documentation notes it may be less efficient for larger datasets. For slowly changing dimensions, it points to dbt snapshots, not this strategy.

A unique_key is only as good as the data behind it. The documentation warns that key columns shouldn't contain nulls, or the model may fail to match rows and generate duplicates. Wrap a nullable column in coalesce, or build a single surrogate key.

Pass several columns as a list, for example unique_key=['user_id', 'session_number'], rather than a concat expression. And the key has to be unique in the new rows too. On Snowflake, the merge statement fails with a nondeterministic merge error when the configured key isn't actually unique, and the adapter page suggests delete+insert as the 2 step alternative. Deduplicate inside the model with a row_number() window before the final select, and the error doesn't come back.

A filter on max(updated_at) assumes rows arrive in order. A row stamped yesterday that reaches the warehouse today sits below the high-water mark and is skipped on every later run. The model looks healthy because nothing fails, while the table slowly drifts from its source.

The common remedy is a window that reaches back past the mark, such as updated_at >= max(updated_at) - interval '3 days', paired with a unique_key so the reprocessed rows replace themselves instead of duplicating. The width of the window is a trade: a wider window catches later arrivals and rescans more data on each run. Choose it from how late your slowest source can be, and check it against the cost of the scan.

Microbatch splits a time-series model into batches, one query per batch. The dbt documentation on microbatch lists it for dbt v1.9 or higher. You set 3 required configs. event_time is the column that records when each row occurred, and begin is where the first build starts. batch_size is one of hour, day, month or year. lookback defaults to 1 and reprocesses earlier batches to catch late records. concurrent_batches lets batches run in parallel.

The documentation's own example sets lookback=3 on a daily model, so a run on October 1 queries September 28 through October 1: 4 batches. dbt filters each upstream ref that defines an event_time, and an upstream model without one is read in full on every batch. Each batch is written with the adapter's batch mechanism: merge on Postgres, delete+insert on Snowflake and Redshift, and insert_overwrite on BigQuery and Spark. Postgres also needs a unique_key. BigQuery needs partition_by, as does Spark.

A backfill uses the event time start and event time end flags of dbt run together, since the documentation doesn't allow one without the other, and dbt retry reruns only the batches that failed. Values are read as UTC, and custom time zones aren't supported yet.

on_schema_change takes 4 values: ignore (the default), fail, append_new_columns and sync_all_columns. With the default, a column you add to the model doesn't appear in the existing table, and a column you remove makes dbt run fail. append_new_columns adds new columns without dropping missing ones. sync_all_columns also removes columns and covers data type changes.

The open source tool has limits here. No option backfills old rows for a newly added column, so the old rows hold nulls until you update them or run a full refresh. Only top-level columns are tracked, so on BigQuery a change inside a nested column does not count as a schema change.

Running dbt run with the full refresh flag and a selector for the model drops the existing table and rebuilds it as on a first run. A trailing + in the selector also runs downstream models, and downstream incremental models are fully refreshed too, which can be a large bill. Setting full_refresh to true or false in the model's config overrides the flag, and setting it to false protects a model you never want rebuilt by accident.

Rebuild after you change the model's logic. The filter only reads new rows, so old rows keep the output of the old logic until a refresh replaces them. For microbatch models the documentation advises full_refresh: false and a targeted backfill with both event time flags.

An incremental model trades correctness you get for free in a table or view for run time, and the trade is only worth making when the rebuild is slow or costly. A model that finishes in seconds gains nothing from a filter that can drift. Models whose rows are rewritten by logic upstream, such as a window function across the full history, are poor candidates because a partial run can't recompute them.

Microbatch isn't always the answer either: the documentation says to consider other strategies when you lack a reliable event_time column or want full control over the incremental logic. A custom strategy is possible from dbt v1.2 by defining a get_incremental_STRATEGY_sql macro, though the documentation notes custom strategies aren't supported on the BigQuery and Spark adapters.

Interviewers ask incremental models as a design question: how would you load a growing table without rebuilding it, and what breaks. The strong answer names the key and the filter column, then covers late data and the rebuild path. The SQL under it is a keyed upsert, so the SQL interview questions and SQL practice problems cover the reasoning, and the SQL round guide shows how it is asked. For the tool itself, see the dbt interview questions.