Late data is why incremental models need a lookback.

01 · THE GAP

The latest date is not the same as complete data.

An incremental model often filters the source after the maximum date already present in its target. That works only if records arrive in perfect business-date order.

Retail feeds do not always behave that way. A delayed order may arrive after the warehouse has moved on to a newer date. A strict high-water mark never reads that row, so the omission becomes permanent.

02 · THE WINDOW

Reprocess the recent past on every run.

When the model is incremental, it reads source orders whose business date falls within three days of the latest date in the target. New dates still flow through, while recently delayed records get another chance to appear.

{% if is_incremental() %}
where ordered_date >= (
  select max(ordered_date) - interval '3 days'
  from {{ this }}
)
{% endif %}

The window is explicit and adjustable. It should reflect the real arrival pattern: wide enough to cover expected delays, but bounded so the model does not rebuild all history.

03 · THE REPLACEMENT

A lookback is safe only when repeated rows do not duplicate.

Reading the same recent orders again would be dangerous with append-only output. The model instead uses order_id as its unique key and dbt’s delete-and-insert incremental strategy.

For every order in the trailing window, the existing target row is replaced by the newly transformed row. The model can capture a correction or late record while keeping one current fact per order.

• Keep monetary values as integer cents through transformation.
• Retain cancelled and returned orders instead of hiding business history.
• Use a snapshot where slowly changing customer attributes need history.
• Reconcile order facts back to their staged inputs.