dbt Incremental Models for Marketing Data: Choose the Strategy Before the First Run

A dbt incremental model needs two decisions: which source rows to process, and how those rows change the existing target. Choose the model's grain, stable key and change timestamp before selecting append, merge, delete+insert, insert_overwrite or microbatch. Then test an initial build, a later update and the same run twice. A fast query that misses an old CRM correction is not a successful incremental design.

Jafrin Akter AloLast updated 1 November 202613 min read

On this page

This guide owns the first model and its acceptance checks. If a deployed model already contains incorrect history, use the dbt incremental backfill guide for a bounded repair, retained-history checks and downstream release planning.

For the wider model sequence, start with dbt for marketing data. Our warehouse implementation work and dbt implementation service define the source contracts and tests alongside the model, so a smaller scan does not come at the cost of incomplete reporting.

When should a marketing model become incremental?

Make a model incremental when rebuilding the full available input is costly enough to justify maintaining a correct change-selection and replacement process.

A small daily spend table may be simpler to rebuild. A large event fact or frequently updated CRM table may benefit from processing a smaller set. Measure runtime and scanned data first. The materialization adds state to your workflow: the result now depends on both today's query and yesterday's stored table.

Write four short statements before changing the config:

• What one output row represents, including account, date and currency where they belong in the grain.
• How the source reveals a new row, a changed row and a deletion.
• Which retained input can reconstruct the intended result.
• What proves a repeated run leaves the same business state.

dbt runs your selected transformation on the first build and applies the incremental path once a suitable target exists. The SQL must be valid in both cases. Incremental model configuration.

Which strategy fits ad spend, web events and CRM changes?

Choose the replacement unit your data needs, then confirm that the installed adapter supports it.

The following are design choices for normalized marketing data, not defaults for every connector. The strategy reference describes the operations behind these names.

For ad spend, the grain may be platform, advertising account, campaign, reporting date, currency and breakdown. If the source republishes a whole day, replacing a complete day can fit. If it provides reliable row changes, a keyed update may fit better. Do not replace a daily total using only the day's new arrivals.

For web events, append works only when your selection and delivery rules prevent replayed occurrences. An overlap window needs deduplication or a keyed replacement strategy. For CRM current state, keep one accepted version per namespaced record key before the incremental model runs. The latest arrival is not always the latest authoritative version. See late-arriving data and revision rules.

Do the same strategies work on every warehouse?

No. dbt's current support matrix describes its v2 and v1 Latest tracks; an older adapter can differ. Check your installed runtime and adapter, then inspect the generated SQL. Adapter support matrix.

The last distinction matters before copying a config. BigQuery's overwrite strategy requires partition configuration and replaces entire selected partitions. Snowflake explicitly describes its overwrite as whole-table replacement, not partition replacement. A recent-days source filter with Snowflake insert_overwrite could therefore leave only those days in the table. Sources: BigQuery configuration, Snowflake configuration, DuckDB configuration, Postgres configuration.

How do you define the unique key and lookback?

Use the complete stable identity of an output row, and choose a lookback from the source's delivery and revision behavior.

unique_key tells a row-matching strategy how to identify records. It does not choose the winning source version or prove that the data is unique. Test incoming and target keys for duplicates and nulls. For a composite key, check each component; a missing account ID should not become a shared “unknown account” key just to make a merge run. Unique-key configuration.

Separate business time from processing time. A deal dated 1 October can change on 30 October. Selecting by a reliable landing or change timestamp can find that change while preserving its original business date. Selecting only recent business dates cannot.

A lookback rereads a bounded overlap to catch delayed visibility, repeated timestamps or source corrections that fall within that rule. Three days is an example below, not a dbt default or a universal marketing-data guarantee. Measure the age of changed records and keep a route for older exceptions. A maximum timestamp proves the newest observed value, not that every earlier source load completed.

Resolve conflicting versions upstream. If two payloads claim the same source revision, investigate instead of choosing an arbitrary row. If you receive raw history with several legitimate revisions per key, construct a reviewed current-state relation first. A row_number() ordered only by arrival time can allow an old payload to overwrite a newer source state.

What does a small row-level model look like?

A current-state CRM model can use delete+insert with a stable key and a landing-time overlap. The example below targets dbt-duckdb and DuckDB SQL. It is one model to add to an existing configured dbt project, not a connector or a warehouse setup procedure.

Its prerequisite is a typed relation named raw.deals_current, already loaded and validated at a consistent cutoff. It holds one accepted current row per deal_id; IDs are unique within this fictional source. If your IDs repeat across portals, namespace them or use the complete composite key instead.

Here is a fictional starting extract. All amounts are USD cents. It can be loaded into that relation for a development test; a source with different fields needs a staging transformation before using the model.

deal_id,stage,amount_minor,currency,business_date,landed_at,is_deleted
demo-a,open,10000,USD,2026-10-01,2026-10-27 00:00:00,false
demo-b,open,5000,USD,2026-10-20,2026-10-28 00:00:00,false
demo-c,won,7000,USD,2026-10-29,2026-10-29 00:00:00,false

Save this as models/fct_crm_deals.sql:

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='deal_id',
    on_schema_change='fail'
) }}

select
    deal_id,
    stage,
    amount_minor,
    currency,
    business_date,
    landed_at,
    is_deleted
from {{ source('crm', 'deals_current') }}
{% if is_incremental() %}
where landed_at >= (
    select coalesce(
        max(landed_at) - interval '3 days',
        timestamp '1900-01-01 00:00:00'
    )
    from {{ this }}
)
{% endif %}

On the initial build, the conditional filter is absent. On a subsequent run, the model rereads the overlap and the adapter replaces the selected keys. >= includes the boundary timestamp. coalesce gives an empty existing target a starting point instead of comparing every row with null. The illustrative input contains no dates before 1900; choose a fallback consistent with your actual source history.

This model keeps tombstones. Consumers that need active deals filter on not is_deleted. Filtering deleted rows out before replacement would leave the older active target row untouched. A physical disappearance from this partial selection is not an instruction to delete the target record.

Add the source declaration and key checks in models/schema.yml:

version: 2
sources:
  - name: crm
    schema: raw
    tables:
      - name: deals_current
        columns:
          - name: deal_id
            data_tests: [not_null, unique]
          - name: landed_at
            data_tests: [not_null]
          - name: amount_minor
            data_tests: [not_null]
          - name: currency
            data_tests: [not_null]
          - name: is_deleted
            data_tests: [not_null]
models:
  - name: fct_crm_deals
    columns:
      - name: deal_id
        data_tests: [not_null, unique]
      - name: landed_at
        data_tests: [not_null]
      - name: amount_minor
        data_tests: [not_null]
      - name: currency
        data_tests: [not_null]
      - name: is_deleted
        data_tests: [not_null]

These are structural checks. Add source completeness, stage validity, currency meaning and future-timestamp checks to the source contract. A non-null currency is not proof that amounts are comparable. dbt's data-test documentation explains the built-in assertions; our marketing dbt test guide covers the wider acceptance suite.

In the intended development target, run the source tests before building the model. Continue to the second command only if the source tests pass; configure the same stop condition in the job that will run this sequence.

dbt test --select source:crm.deals_current
dbt build --select fct_crm_deals

dbt build runs selected models and tests. An upstream test can block selected downstream work, but selection and dependencies determine that behavior. Do not assume a separately scheduled reporting or activation job waits just because a dbt test failed. Build behavior.

How can is_incremental() filters go wrong?

The common failure is excluding a row the target still needs, even though the SQL runs successfully.

is_incremental() applies when the target already exists as a table, the model is incremental and a full refresh is not in effect. A first run in an empty development schema therefore cannot prove the update path works. Both query branches need valid SQL and separate acceptance cases. Incremental branch behavior.

Source filtering and target matching are separate. A source filter controls which rows enter the incoming batch. An incremental_predicates condition can restrict the target side of a merge. dbt's documentation shows those predicates inside the match condition; it does not validate their SQL for you. Source filters and destination predicates.

Suppose the incoming batch contains demo-a, whose business date is 1 October. If the target match also requires a business date after 24 October, the existing row is invisible to that match. The merge can take the insert path or fail under a constraint, even though deal_id is configured. Test an old existing key before adding target pruning. The example model deliberately has no destination restriction.

When is microbatch a better choice?

Use microbatch when the model naturally produces complete, independent time slices and batch-level retries help operate a large time-series workload.

Microbatch requires a supporting adapter and is available from dbt v1.9. Its main settings include event_time, begin and batch_size. lookback counts preceding batches: with daily batches, a value of three rereads three prior days as well as the current batch; with hourly batches, it describes hours. These are different from the example model's landing-time filter. Microbatch configuration.

Configure event_time on upstream references that should be automatically time-filtered. A large unconfigured parent can be scanned for each batch. Do not add the event's time filter to a current-state dimension merely to reduce scans if that would remove the row needed for the join. Decide whether the model needs current or historical dimension state.

Microbatch does not make an arbitrary late CRM correction appear in a recent event-time batch. If the changed record belongs outside the configured lookback, the workflow needs an older-range repair. Set begin to the intended available-history boundary, keep the documented UTC time assumptions, and test batch boundaries and empty results. The backfill guide covers operating a historical repair after the model is established.

How should the first incremental run compare with a full refresh?

Compare both paths against the same frozen input and independently expected rows, not just equal overall totals.

For the fictional CRM fixture, the first result has three deals and USD 220 of deal value. Next, change demo-a to stage won and increase its amount from USD 100 to USD 120 with a new landed_at of 30 October, while keeping its 1 October business date. Add demo-d with USD 30 and a 30 October landing time. The accepted result is four rows and USD 270. Rerunning that same input must keep four rows and USD 270.

Use this complete second source snapshot for that update test:

deal_id,stage,amount_minor,currency,business_date,landed_at,is_deleted
demo-a,won,12000,USD,2026-10-01,2026-10-30 00:00:00,false
demo-b,open,5000,USD,2026-10-20,2026-10-28 00:00:00,false
demo-c,won,7000,USD,2026-10-29,2026-10-29 00:00:00,false
demo-d,open,3000,USD,2026-10-30,2026-10-30 00:00:00,false

Build the initial fixture in an isolated development target, apply the changed source state and run normally. Preserve that incremental result. Build a separate full result from the same source snapshot and model logic, then compare key presence, values, currencies and counts. Keep both sides available until discrepancies are explained.

A full-refresh request rebuilds the selected incremental resource when its effective config permits it. full_refresh: false overrides the command-line flag; true forces a rebuild. Confirm the actual setting before using a full result as your reference. Full-refresh configuration.

An empty table, a never-created table and a failed replacement are separate cases. Exercise them. The target's successful published watermark should not advance when the replacement fails. Check transaction behavior on the actual adapter rather than assuming every warehouse performs the same sequence atomically.

For a daily aggregate, add group reassignment and an empty group to the fixture. The row-level example does not establish correct aggregate repair. Use the complete-group backfill method instead of extending this model by summing only newly arrived rows.

What should pass before the model becomes a production dependency?

Approve the model when its source contract, update behavior and operating checks agree on the same output.

Read diagram as text

• Define grain source changes and replacement unit to Source key and change evidence reliable?.
• Source key and change evidence reliable? to Repair the source contract before proceeding: No.
• Repair the source contract before proceeding to Define grain source changes and replacement unit.
• Source key and change evidence reliable? to Choose a supported adapter strategy: Yes.
• Choose a supported adapter strategy to Test initial incremental and repeat runs.
• Test initial incremental and repeat runs to Expected rows and full comparison agree?.
• Expected rows and full comparison agree? to Inspect selection matching and deletion handling: No.
• Inspect selection matching and deletion handling to Define grain source changes and replacement unit.
• Expected rows and full comparison agree? to Older changes and failures have a recovery path?: Yes.
• Older changes and failures have a recovery path? to Define reconciliation rollback and repair ownership: No.
• Define reconciliation rollback and repair ownership to Test initial incremental and repeat runs.
• Older changes and failures have a recovery path? to Release with tests monitoring and a named owner: Yes

Open full diagram

Measure query cost after those checks. A narrowed source read can still scan much of the destination, and frequent small jobs can add overhead. The BigQuery pricing guide helps separate scanned data, capacity and orchestration costs when that is your warehouse. For implementation help, request a detailed dbt scope with the source contract, current query and acceptance cases.

Written by

Jafrin Akter Alo

Data Engineer and Co-founder at Martechdevs and Growth and Analytics Partners

Jafrin writes practical guides to marketing technology, customer data, analytics and AI workflows. Martechdevs is a Growth and Analytics Partners company.

View LinkedIn profile