An incremental dbt model filters on event_time >= max(event_time) in the target. Events that arrive a day late never appear, and orders whose status changes show up twice. Explain both bugs and rewrite the model.

The filter assumes rows arrive in time order and never change. A late event has an event_time older than the newest one already loaded, so the filter excludes it on every run after. An updated order is a new row for the same order, and with no unique_key dbt appends it next to the old one. Fix both: filter on when the row arrived or changed (updated_at or a load timestamp) with a lookback window, set unique_key='order_id' with the merge (or delete+insert) strategy so a changed order replaces its old row, and run a full refresh once to repair history. For large time-series data, the microbatch strategy with a lookback does the same per day.

1. Why late events vanish

{% if is_incremental() %}
  where event_time >= (select max(event_time) from {{ this }})
{% endif %}

On Monday night the newest event loaded is from Monday 23:59. On Tuesday an event from Sunday arrives. Its event_time is before Monday 23:59, so this filter skips it, and every later run skips it too.

2. Why updates duplicate

Without a unique_key, most adapters only append. The order placed on Monday (status placed) and the same order on Wednesday (status shipped) are 2 rows. dbt's docs say it directly: to capture updated records you need a unique key, or they come in as duplicates.

3. The rewrite

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}

select order_id, status, amount, event_time, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
  -- rows changed since the last load, with 3 days of overlap for late arrivals
  where updated_at >= (select dateadd(day, -3, max(updated_at)) from {{ this }})
{% endif %}

• updated_at moves when an order changes or arrives late, so both are picked up.
• The 3-day overlap catches rows whose timestamp lags the load. Overlap is safe because the merge on order_id replaces rows instead of adding them: the model is idempotent.
• unique_key must be unique in the new rows too; if stg_orders can hold 2 rows for one order, keep the latest first (qualify row_number() over (partition by order_id order by updated_at desc) = 1), or the merge can fail.

4. Repair history once

dbt build --select fct_orders+ --full-refresh

It drops and rebuilds the table, and the + rebuilds what depends on it.

5. The microbatch alternative

{{ config(materialized='incremental', incremental_strategy='microbatch',
          event_time='event_time', batch_size='day', lookback=3, begin='2025-01-01') }}

Each day is a batch that dbt replaces as a whole, and lookback=3 reprocesses the 3 days before the latest, so late events land in their own day. Upstream models need event_time configured, or each batch scans them in full.

What interviewers want: why max(event_time) drops late rows, why no unique_key duplicates updates, filtering on updated_at with an overlap, merge on a unique key making the overlap safe, deduplicating the new rows, a one-time full refresh, and microbatch with lookback.