dbt incremental model getting duplicate rows when late data arrive, how to fix?

Question

Using dbt on BigQuery. Orders model is incremental, source table get updated when order status change (paid, shipped, refunded). Model look like this:

{{ config(materialized='incremental') }}

select order_id, status, amount, updated_at
from {{ source('shop', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Problem 1: same order_id appear multiple times after status change. Problem 2: some rows come late from upstream (updated_at older than max in target) and never get picked up.

Tried full refresh every night but table is 300M rows, too slow and expensive. What is the correct pattern here?

Emma Lindqvist · Accepted Answer

Two separate fixes: 1. Duplicates: set unique_key='order_id' in the config. On BigQuery the default incremental strategy is merge, so a new version of a row updates the existing one instead of being appended. 2. Late rows: use a lookback window instead of a strict > on the max: where updated_at > (select timestamp_sub(max(updated_at), interval 3 day) from {{ this }}) With unique_key in place, reprocessing the last 3 days is safe, those rows are just merged again. Pick the window from how late your data really arrives. Also, if the source can contain two versions of the same order in one batch, dedupe before the merge: qualify row_number() over (partition by order_id order by updated_at desc) = 1 otherwise the merge fails because one target row matches several source rows.

Arjun Mehta · Answer

Same pattern for settlement files, late rows always come. We keep a 5 day lookback and nobody has complained about the extra cost yet

dbt incremental model getting duplicate rows when late data arrive, how to fix?

Accepted answer

Lin Wei

Get the best of #my-model-is-fine weekly

Comments