Skip to content

How to choose an EWE raw-data deduplication mechanism

Use this procedure for a new source table or before changing an existing table's mechanism. Do not select V2 or V3 from a percentage quoted for a different table.

1. Establish the contract

Record the consumer-facing name, complete deduplication key, winner order, expected compatibility columns, timestamp types and representative query shapes. Confirm the key from source metadata and existing consumers; do not infer it only from a primary-key declaration or an old join predicate.

2. Measure the lifetime corpus

For the complete retained corpus, measure:

  • raw row count;
  • survivor count using the proposed key and winner rule;
  • duplicate count and rate;
  • raw rows / survivor rows read amplification;
  • active bytes and representative row width; and
  • distribution by ingestion day, source date and row type.

Annotate the population and measurement date. Backfill duplicates remain part of the table even when steady-state daily increments are clean.

3. Choose which side to materialise

  • If survivors are clearly the minority, choose V2.
  • If duplicates are clearly the minority and RAW reads remain affordable, choose V3.
  • If the table has at most roughly one million rows, default to V2 for simplicity unless measurements show a material benefit from V3.
  • If the result is near the middle, benchmark both representative read and refresh workloads; percentage alone does not decide it.

4. Choose the topology

Prototype one unbounded dynamic table on an appropriately small warehouse. Record wall time, bytes scanned, local and remote spill, resolved refresh mode and the first steady-state refresh.

  • Use SINGLE_DT if initialization and refresh are comfortably bounded.
  • Use HIST_LIVE if one-shot initialization is unsafe or repeated refresh would revisit a large historical corpus.

For HIST_LIVE, prove that each build slice contains complete window partitions. Choose the seam from arrival semantics or a dedup-key member, and explicitly handle any key that can straddle it.

5. Test reads and availability

Benchmark at least:

  • a narrow aggregate;
  • a payload/VARIANT projection;
  • a normal time-range query;
  • a point lookup on common business keys; and
  • concurrent representative reads.

For V3, suspend or hide the watermark source in a test context and prove the view withholds uncovered rows. Test a backdated arrival below the proposed seam.

6. Record the assignment

Update the mechanism catalogue with the method, topology, measurement population, key, seam, coverage basis and deployment status. Link the evidence rather than copying large counts into several design documents.