Skip to content

How to diagnose EWE raw-data deduplication

Start from the consumer view and work inward. A healthy internal object does not prove that the reader can see a healthy result.

1. Test as the real reader

Use the consuming role with secondary roles disabled. Query a known, bounded slice of the consumer-facing view. Then prove that the same role is denied on *_RAW, *_DUP_HIST, *_DUP_LIVE, control tables and watermark snapshots.

If the view unexpectedly returns zero rows, test *_DEDUP_COVERED_TO first. A NULL watermark intentionally withholds data and can make a healthy RAW table look empty.

2. Identify the mechanism and topology

Consult the catalogue before choosing diagnostics:

  • V1: task, stream, NULL flag backlog and procedure history.
  • V2 single: survivor dynamic-table refresh and view projection.
  • V2 HIST/LIVE: frozen-history coverage, live seam and union overlap.
  • V3: duplicate-set subset, live coverage and anti-join pruning.

3. Check dynamic-table health

Verify scheduling state, resolved refresh mode, latest data timestamp, refresh duration, bytes scanned, rows produced, local/remote spill and skipped/failed history. Compare against the object's own baseline, not another table's.

A refresh that repeatedly scans a historical corpus usually indicates an unbounded or semantically wrong seam. Confirm the predicate uses arrival semantics and that no non-deterministic projection has forced full refresh.

4. Check correctness invariants

For V2, verify one row per dedup key. For V3, verify every duplicate identity exists in RAW, no returned key has multiple candidates, HIST/LIVE cover the intended ranges, and uncovered arrivals are withheld.

Test seam straddling and a backdated arrival explicitly. Timestamp equality does not prove deterministic winner selection when _INGESTED_AT ties.

5. Check ownership and grants

Confirm the loader owns objects used by owner's-rights procedures and metadata snapshot tasks. Confirm replacement views retained the reader grant. Do not fix a reader-visible watermark by granting the reader MONITOR on internal dynamic tables; repair the loader-owned metadata bridge.

6. Check downstream interpretation

Remember that _IS_DUPLICATE = FALSE on V2/V3 is a compatibility projection. _IS_DUPLICATE IS NULL is not a meaningful backlog metric on those views. Use coverage and dynamic-table health instead.

Record the failing invariant, query ID, role, object DDL and timestamp before changing anything. A dated bug/execution record should supersede stale discovery SQL rather than silently editing history.