Duplicated order lines and a nightly full refresh that never ends

A --full-refresh rebuilds the model body from source, so duplicates that survive it cannot be blamed on the merge step. The fingerprint - exactly two rows per order_line_id with different status values - points at a one-to-many join inside the model body: orders joined to a status-history or refunds table on order_id rather than at the order-line grain, or a join to a payment-attempts table where several attempts share one order_line_id.

Confirm before touching strategy:

1. Materialize the body as a view and run select order_line_id, count(*) from body group by 1 having count(*) > 1. Rows returned here mean the problem exists before dbt's merge ever runs.
2. Bisect the joins: start from the base CTE and add one left join at a time, re-checking count(*) versus count(distinct order_line_id) after each. The join that moves the count is the culprit.
3. Check the sources themselves. If refunds already holds several rows per order_line_id, an incremental contract on order_line_id is invalid until you dedupe with row_number() over (partition by order_line_id order by updated_at desc) and filter to 1.

Then fix the grain in the model, run one deliberate full refresh into a clone schema, compare counts to source, and only then return to incremental. A merge receiving duplicate keys in a single batch is not a safe resting state - depending on adapter it errors or writes nondeterministically - so uniqueness must be settled first.

The predicate compares incoming rows to the maximum updated_at ever written into the target. Any row written later but carrying an older timestamp - the 30-day-late refunds, a replication replay, a correction - sits below that watermark permanently and is silently skipped. The strict > also loses ties: two rows sharing the exact watermark value are both excluded. Worse, because {{ this }} reflects what was merged, the watermark advances even when a legitimate late row was excluded, so the gap never self-heals.

Replace it with a lookback window anchored to the watermark, guarded by is_incremental() so the initial build and full refreshes still read everything:

{% if is_incremental() %}
where updated_at >= (
  select dateadd('day', -14, max(updated_at)) from {{ this }}
)
{% endif %}

Size the window from the data, not intuition: compute the p99 of ingested_at - updated_at on the source (or max of it) and set the window at or above that. If the arrival tail is genuinely 30 days, either widen the window or - better - use a monotonic ingestion column such as _loaded_at as the watermark, so a late business-time update is still picked up because its load time is new.

Cost is the trade: reprocessing 14 days may merge 8M rows instead of 2M, still far cheaper than a 3.5-hour rebuild. Replaying is only safe because the merge is idempotent on a now-unique order_line_id, so keep >= and back it with a unique test on that key at error severity - overlap damage should fail the job, not reach finance.

Drop the nightly --full-refresh. It is a 3.5-hour workaround for a grain bug, it runs with almost no headroom under a 4-hour account timeout, and it lands directly in front of a 07:00 reconciliation. After the fan-out fix, perform one deliberate full refresh - in a clone schema, off-hours, or via a schema swap at deploy - verify the row count, then let the incremental job run nightly again.

Changes that genuinely reduce merge cost:

• Add a cluster key on the column the incremental filter uses (order_date or updated_at) so Snowflake prunes micro-partitions instead of scanning 210M rows every night.
• Keep the where clause tight and avoid select * layering; every unnecessary column inflates the merge payload.
• Consider splitting the model: an append-only stg_order_line_events incremental on ingestion time, plus a thinner fct_order_lines built from a bounded window. Merges get cheap and history becomes replayable.
• Threads do not help a single merge. On an XS warehouse 4 threads mostly queue behind one statement; resize the warehouse for the merge window and scale back down afterwards.

Operational guardrails: run a dbt build --select fct_order_lines step ahead of the main job so a uniqueness failure blocks the pipeline early, set the job timeout below the warehouse limit, and log row counts per run into a small monitoring model. The next regression then shows up as a visible delta rather than as a finance ticket.