Duplicate-ID exclusion (V3)

Duplicate-ID exclusion stores the negative result of deduplication: the identities of losing rows. The consumer-facing view returns RAW minus those identities. The internal name "deletion vector" describes this logical exclusion set; no raw row is physically deleted.

The large-table shape

X_RAW
  |
  +-> X_DUP_HIST  plain table of loser identities below the seam
  +-> X_DUP_LIVE  dynamic table of loser identities above the seam
  +-> X_DEDUP_COVERED_TO  reader-safe coverage watermark
  |
  +-> X view = covered RAW
               anti-join X_DUP_HIST
               anti-join X_DUP_LIVE

X_DUP_HIST is built in complete, bounded partitions and then frozen. X_DUP_LIVE selects ROW_NUMBER() > 1 over a small live tail. The customer view anti-joins both sets and applies a coverage predicate.

Why it exists

Measurements on EWE showed that copy-forward builds spent most of their time writing rows, while duplicates were commonly well below one percent. Storing a narrow loser set therefore reduced the build and storage footprint by orders of magnitude. RAW remains pristine, so a corrected winner rule can be re-derived.

The live examples are TABLE_TRAN_SCHEDULE_DETAILS_BLOB, DEAL_SPLIT and SIM. For SIM, the prior survivor materialisation occupied roughly 1.7 TB; its duplicate identity sets occupied roughly 0.48 GB after conversion.

Coverage is part of correctness

An anti-join without coverage can expose a new duplicate before X_DUP_LIVE refreshes. The view must therefore withhold rows beyond the LIVE table's consumed timestamp. _DEDUP_FINISHED_AT = _INGESTED_AT is sound on the returned rows only because this bound proves their duplicate status is already known.

The watermark is published through loader-owned snapshot infrastructure. A reader-visible view must not depend directly on role-scoped dynamic-table metadata. If the watermark is absent, the view must fail closed.

Join columns and pruning

_ID is the row-identity condition. Duplicate sets also carry useful clustering and key columns so filters can be pushed into the anti-joins. Those additional equalities are redundant for logical identity but important for pruning. A deduplication design is incomplete until representative read shapes—including payload reads and point lookups—have been measured.

Seam and backfill conditions

If the seam column belongs to the deduplication key, one key cannot normally straddle the seam. If it does not, history and live can each select a survivor for the same key, so an explicit history-yields-to-live rule is required.

A business-time seam also creates backfill blindness: a newly ingested row may carry an old business timestamp below the frozen seam and be evaluated by neither set. Coverage withholding delays exposure but does not by itself recompute frozen history. The monitor and operating procedure must detect this case.

Trade-offs

V3 minimizes materialised storage and preserves reversible RAW, but every read must scan the relevant RAW partitions and evaluate anti-joins. Availability is coupled to LIVE health and watermark publication. It is therefore inappropriate for duplicate-dense tables or workloads whose filters cannot prune RAW.