# Idempotent incremental refresh design for $merge (high-water marks, delete handling) > Source-anchored research for Idempotent incremental refresh design for $merge (high-water marks, delete handling). Run status: COMPLETED. Parent: [Read Offload via Caching and Precomputed Views ($merge/$out, Computed Pattern)](https://llms-explorer.com/tree/read-offload-via-caching-and-precomputed-views-merge-out-computed-pattern/) · 5 facets · 14 facts · page: https://llms-explorer.com/tree/idempotent-incremental-refresh-design-for-merge-high-water-marks-delete-handling/ ## Definitions - A change-stream resume token is an opaque bookmark, distinct from an application updated_at cutoff. MongoDB’s implementation notes distinguish event tokens from high-watermark tokens and require resuming with the same stream settings and pipeline; this is an alternative checkpoint vocabulary, not proof of atomic sink application. (low confidence) — [source](https://github.com/mongodb/mongo/blob/master/docs/change_streams.md) ## How it works - A bounded extraction can capture an old watermark L and an upper watermark U, process L < changed_at <= U, then save U after success. This Microsoft loading pattern transfers to a MongoDB refresh only when the source change field reliably advances on inserts and updates; it does not establish snapshot consistency. (medium confidence) — [source](https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-incremental-copy-data-warehouse-lakehouse) ## Problems, failure modes and limitations - For repeatable bucket refreshes, recompute the complete selected bucket and use a deterministic output key with whenMatched: "replace" and whenNotMatched: "insert". Replaying unchanged inputs then replaces existing rows and fills missing rows after partial failure. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md) - A $merge failure can leave completed writes in place, and $merge cannot run inside a transaction. Therefore do not assume the output writes and a separate checkpoint commit form one atomic operation; design replay to tolerate failure between them. (low confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md) - Watermark scans cannot observe a physically deleted source row, and $merge only acts on emitted results. A refresh therefore needs a separate delete signal or repair path; when a bucket emits no result, replacement alone cannot clear an existing target row. (high confidence) — [source](https://learn.microsoft.com/en-us/fabric/data-factory/incremental-copy-job) - Application design inference: retain the previous checkpoint until the entire $merge batch succeeds; if writes succeed but checkpoint persistence fails, replay that batch with replacement. MongoDB permits partial output on failure, while Microsoft sequences watermark persistence after successful copying. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md) ## Comparisons and alternatives - Late arrivals can fall behind a maximum-timestamp cutoff. dbt documents a lookback window plus unique-key updates to avoid duplicates, but records arriving beyond that window still escape processing. Applied to bucket refreshes, this motivates bounded replay and an explicit backfill policy rather than assuming the watermark proves completeness. (medium confidence) — [source](https://docs.getdbt.com/best-practices/materializations/4-incremental-models) ## Facts and statements - Design implication: use updated_at to discover affected buckets, then read their full membership before replacing totals. Grouping only changed documents and replacing an existing bucket drops unchanged contributions; adding batch totals instead requires duplicate-application protection. (medium confidence) — [source](https://www.mongodb.com/docs/manual/core/materialized-views.md) - An incremental timestamp filter requires a field that changes reliably on inserts and updates. An indexed business-event date alone does not establish that an older record was modified; MongoDB bucket examples support explicitly rerunning historical periods. (medium confidence) — [source](https://learn.microsoft.com/en-us/fabric/data-factory/incremental-copy-job) - MongoDB’s retryable-reads specification excludes aggregation pipelines containing $merge or $out from ordinary retryable reads. Refresh correctness must not rely on retryReads automatically making such a pipeline safe to replay. (low confidence) — [source](https://github.com/mongodb/specifications/blob/master/source/retryable-reads/retryable-reads.md) - Boundary policy must be explicit: dbt demonstrates >= to reread timestamp ties, whereas Microsoft demonstrates an exclusive lower and inclusive upper bound. For $merge, intentional overlap is safe only with repeatable replacement semantics; neither boundary convention alone handles arbitrarily late source visibility. (medium confidence) — [source](https://docs.getdbt.com/docs/build/incremental-models) - Adding a batch subtotal to an existing total in a whenMatched pipeline is not replay-idempotent: applying the same batch twice adds it twice. This follows from MongoDB's running-total example; complete bucket replacement provides a replayable alternative. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md) - Match the output grain with stable keys. MongoDB requires a supporting unique index on the $merge on fields; dbt likewise distinguishes keyed updates from append behavior and warns about duplicate incoming keys. Design inference: reduce each batch to one intended result per output key before writing. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md) - A strict forward watermark can miss historical arrivals or corrections whose cursor does not advance: dbt recommends widening the filter for late changes, and the MongoDB daily-summary example explicitly reruns historical days. A finite overlap only covers the lateness it actually includes. (medium confidence) — [source](https://docs.getdbt.com/best-practices/how-we-handle-cdc/2-choosing-incremental-or-snapshots)