CDC into Fabric: preserve order and avoid duplicates

Concrete technique: treat CDC as an ordered append-only stream and carry the source transaction identity (LSN/tx_id) plus a per-transaction operation sequence (op_seq) for every row. Make your staging + merge logic explicitly sequence-aware so you only apply a row if its (tx_id, op_seq) is newer than what’s already in the target.

What I do in practice — simple, reliable, and survives retries:

• Capture: store every change as an append-only row with pk, tx_id (LSN), op_seq, is_delete, and raw payload. Include a source timestamp but don’t rely on it for ordering.
• Stage: ingest into a small staging table and collapse duplicates within the same tx by keeping the max(op_seq) per (pk, tx_id). This removes noise from ingestion retries.
• Apply (idempotent MERGE): MERGE into the target using pk; in the WHEN MATCHED branch only update/delete when incoming.tx_id > target.tx_id OR (incoming.tx_id = target.tx_id AND incoming.op_seq > target.op_seq). Insert when no match. Treat deletes as a distinct op applied the same way.

Two practical extras that saved me from painful outages:

• Maintain a per-partition processed watermark (highest tx_id applied). It lets you ignore older batches deterministically and speeds incremental runs.
• Persist the applied (tx_id, op_seq) on the target row so subsequent merges are cheap comparisons, not full payload diffs.

The mistake I see most often: relying on last_updated timestamps or naïve upserts that don’t check sequence. That works until a retry or out-of-order delivery creates duplicates, resurrected rows, or lost deletes.

If you want to go deeper on sequencing patterns and example MERGE snippets for Fabric, the Fabric Data Engineering training (the Fabric Data Engineering training) covers these workflows.