Skip to content

EWE raw-data deduplication mechanism catalogue

This is the maintained assignment and status reference for the new EWE account. Counts and rates are dated measurements, not promises about future data. Re-run the selection procedure before assigning a new table.

Live V2/V3 tables

Consumer object Mechanism Topology Historical measurement Current status
ADJUSTED_PRICES V2_SURVIVOR HIST_LIVE about 99.5% duplicates; about 192:1 raw-to-survivor read amplification Live; _INGESTED_AT LIVE seam repaired 2026-09-09
TABLE_CLOSING_PRICES V2_SURVIVOR SINGLE_DT about 60% duplicates; small table Live
TABLE_TRAN_SCHEDULE_DETAILS_BLOB V3_DUPLICATE_ID HIST_LIVE 36,048,187 raw; 5,423 duplicate rows; about 0.02% Live
DEAL_SPLIT V3_DUPLICATE_ID HIST_LIVE about 63.98B raw; rate varies by row type Live; accepted residual validation risk recorded in sign-off pack
SIM V3_DUPLICATE_ID HIST_LIVE 22,544,137,667 below-seam rows; 88,428,593 duplicates; 0.3922% overall Live; prior survivor rollback objects retired

Legacy V1 estate

The 2026-09-05 inventory identified 31 populated tables using V1 flag mutation. The 2026-09-06 execution record states all 34 raw-database tasks were left suspended after the timestamp migration. Their procedures, streams and flags are retained as migration definition and evidence; V1 is not the forward path.

ADR-010 proposes converting the populated V1 tables as follows:

  • seven large, duplicate-sparse tables to V3;
  • twenty-four duplicate-dense or small tables to V2; and
  • one unbounded dynamic table per converted table, rather than importing the HIST/LIVE topology where measurements show it is unnecessary.

That allocation is proposed target state, not a claim that the 31-table conversion has already run. The exact list and measured counts remain in ADR-010 and docs/evidence/v1-stream-task-dedup-inventory-and-measurements-2026-09-05.md.

2026-09-13 stream/task retirement

Following operator sign-off, the V1 stream and task for every table that now has a live V2/V3 replacement were dropped: 30 DEDUP_<table> stream+task pairs plus two stream-only leftovers (DEDUP_DEAL_SPLIT, DEDUP_SIM, whose tasks were already absent) — 32 objects in total. Pre-drop GET_DDL/SHOW GRANTS evidence, the exact drop order, and after-state verification are recorded in EXECUTION-RECORD-2026-09-13-v1-dedup-stream-task-retirement.md.

Held back, not retired: TABLE_USER_BUSINESS_CASE and TABLE_IDX_ADJUSTMENT_MATRIX_HEADER still carry a V1 stream (the former has no V1 task; the latter has both), and neither has a V2/V3 replacement — these two tables currently have no live dedup mechanism at all and need a mechanism-selection decision before their V1 remnants can be retired. The shared V1 orchestration task LOGIC.RESET_STALE_STREAMS and the shared V1 procedures (LOGIC.RAW_DATA_DEDUP, LOGIC.GET_NEXT_DEDUP_BATCH) were left in place pending a separate retirement decision, since they are not table-specific.

Deduplication keys for the V3 mega-tables

Table Deduplication key Seam basis
TABLE_TRAN_SCHEDULE_DETAILS_BLOB TRAN_ID, PROFILE_SEQ_ID, PARAM_SEQ_ID, BLOB_VERSION, _BLOB_TYPE_HASH _INGESTED_AT; not a key member, so cross-seam handling is required
DEAL_SPLIT Production partition: INTERNAL_PORTFOLIO_ID, _NUMERA_ROW_TYPE, TRAN_ID, TRAN_VERSION, _SOURCE_AT, _SOURCE_ROW_NUM _SOURCE_AT; key member, with backfill-blindness monitoring required
SIM SIM_DATE, PFOLIO_ID, SIM_DEF_ID, REVAL_TYPE_ID, REFERENCE, _SOURCE_AT, SIM_RUN_ID, SIM_VERSION, SCENARIO_ID, NUMERA_ROW_TYPE, VALUE_HASH SIM_DATE; key member, with backfill-blindness monitoring required

INTERNAL_PORTFOLIO_ID is present in DEAL_SPLIT's deployed PARTITION BY and anti-join predicates. Later measurement found that the true business grain excludes it, but also found no (TRAN_ID, TRAN_VERSION) spanning more than one portfolio, so the deployed six-column partition does not split a measured true group. Preserve the deployed contract until a separate semantic change decides otherwise; do not silently rewrite it from the inferred grain.

Status vocabulary

Status Meaning
Live The consumer view is deployed and was verified against the new account.
Legacy suspended V1 objects exist but their task is not maintaining new rows.
Proposed Recorded in an accepted/proposed design but not asserted as deployed.
Retained rollback Superseded objects intentionally remain available for reversal.
Retired Superseded object has been dropped with operator authorization.

Evidence precedence

For current state, use the newest dated execution or resolved-bug record. Use Planform ADRs for decisions and assumptions. Use SQL under discovery/raw-data/ as executable evidence only when its header does not mark it draft or superseded. The older bgka-iac dynamic-table guide and migration tracker describe an earlier positive-only design and are not authoritative for this catalogue.

Supporting records