Skip to content

How to migrate legacy V1 deduplication

This guide describes the control flow for moving one V1 table to V2 or V3. It is not authorization to execute a production cutover; use the table-specific plan and operator gate for exact DDL.

Prerequisites

  • A completed mechanism-selection record.
  • Captured V1 task, stream, procedure, key, row-count and flag distribution.
  • A retained row-level oracle from _IS_DUPLICATE where available.
  • Captured pipe DDL, ownership and grants.
  • A rollback object and explicit rollback commands.
  • A low-impact cutover window and named operator.

1. Baseline V1

Capture counts for total, duplicate, survivor, NULL/unprocessed and per-key invariants. Verify there is one survivor per processed key and no later-ingested row marked as a loser. Record task state and pipe health.

2. Suspend mutation

Suspend the table's V1 task before creating its V2/V3 dynamic table. Do not rely on running both mechanisms in parallel: V1 updates rewrite source micro-partitions, which can invalidate the replacement's incremental-refresh economics. Keep the task, stream and procedure definitions until validation is complete.

3. Establish the _RAW source and consumer-name cutover

Where the base name is still a table, rename it to <X>_RAW and update/recreate the pipe so ingestion continues into that object. Drop inherited constraints from renamed-aside oracle columns if the new pipe no longer populates them. Minimize the interval during which the original name is absent.

4. Build the chosen mechanism

  • V2: build winning rows with ROW_NUMBER() = 1.
  • V3: build losing identities with ROW_NUMBER() > 1.
  • For HIST/LIVE: finish, reconcile and freeze history before enabling the live branch and consumer view.

Choose the key and the order deliberately:

  • Never partition on a renamed-aside *_OLD rollback column. A column rename carries the table's PRIMARY KEY constraint with it, so after a timestamp migration SHOW PRIMARY KEYS can name _SOURCE_AT_LTZ_OLD, a column the pipe no longer writes. Keying on it puts every new row in one partition per business key. Take the key from the legacy table's primary key, with the live column name, and re-point the new table's constraint.
  • Order by the latest file, not only by _INGESTED_AT. V1 treated the latest ingest as the latest version, which is only true when files arrive one by one. A bulk COPY or backfill ingests files in scan order, so rank SPLIT_PART(_FILENAME, '/', -1) DESC, _INGESTED_AT DESC, _ID DESC.
  • Validate survivors against the legacy account per file-basename day: compare the key set, the file and row number, and the payload, not just counts.

Create objects as the intended loader/owner. Owner's-rights procedures cannot see an object merely because ACCOUNTADMIN created it.

5. Create the compatibility view

Create X under the original name. Preserve column order/types, add literal FALSE AS _IS_DUPLICATE where required, and expose the correct _DEDUP_FINISHED_AT. Use COPY GRANTS or immediately replay captured grants.

For V3, install the reader-safe coverage watermark before granting the view.

6. Validate

Require:

  • exact row-count reconciliation;
  • duplicate-key freedom;
  • an empty two-way row-identity or payload difference where affordable;
  • a non-vacuous oracle comparison against a retained object, not the newly replaced view name;
  • ACTIVE and required INCREMENTAL dynamic-table state;
  • positive reader tests and negative internal-surface privilege tests;
  • pipe health and catch-up after cutover; and
  • representative read-cost measurements.

If exhaustive equivalence is deliberately waived, record the substitute evidence and residual risk as an operator decision.

7. Observe, then retire

Keep rollback objects through the agreed observation window. Retire legacy tasks, streams, columns, old survivor tables and temporary controls only after explicit sign-off. Update the catalogue status and retain the dated execution record.