Skip to content

Survivor materialisation (V2)

Survivor materialisation stores the positive result of deduplication: one winning row per deduplication key. It is the preferred forward mechanism when the raw corpus is duplicate-dense, because reading a small survivor set is cheaper than repeatedly scanning and anti-joining a much larger raw set.

The shape

For a manageable table:

X_RAW -> X_DEDUP (dynamic table of winners) -> X (consumer view)

For a table whose historical corpus is too expensive for one dynamic-table initialization:

             +-> X_DEDUP_HIST (plain, built and frozen)
X_RAW -------+
             +-> X_DEDUP_LIVE (bounded dynamic table)
                         |
                         +-> X (consumer view over both survivor sets)

The dynamic-table query selects ROW_NUMBER() = 1. It should project the full consumer row when that avoids a large _ID join back to RAW. Non-deterministic refresh timestamps do not belong inside its SELECT list; they can prevent incremental refresh.

When it wins

V2 is attractive when:

  • survivors are the minority;
  • the resulting read amplification of V3 would be high;
  • consumer queries should read a purpose-built, deduplicated object; or
  • the table is small enough that operational simplicity dominates storage economics.

ADJUSTED_PRICES is the clearest example: almost the whole historical raw corpus is duplicate, so materialising duplicate IDs would store the majority and force every read to scan hundreds of millions of raw rows to return only a few million survivors. TABLE_CLOSING_PRICES uses V2 because it is small and the simple positive shape is operationally sufficient.

Trade-offs

V2 gives cheap, predictable reads but rewrites and stores every survivor. On a large duplicate-sparse table that is exactly the wrong side to materialise. A revised winner rule also requires rebuilding the materialised survivor set.

The split topology introduces seam correctness, promotion and rollback concerns. The 2026-09-09 ADJUSTED_PRICES incident demonstrates why the LIVE seam must be an ingestion/arrival boundary: using GMT_START_DT, a forward-curve value, caused each refresh to revisit years of data.

Required properties

  • The key is explicit and version-controlled.
  • The winner order is explicit.
  • The dynamic table resolves to INCREMENTAL where the design requires it.
  • The view retains the original consumer-facing name and column contract.
  • CREATE OR REPLACE VIEW uses COPY GRANTS, or captured grants are reapplied.
  • HIST and LIVE cover the intended corpus without overlap or gaps.
  • Initial and steady-state costs are both measured.