Teradata / Oracle Snowflake Migration: Assessment, Code Translation & Dual-Run Validation

A Snowflake migration off a legacy Teradata or Oracle warehouse is the project every data platform team eventually inherits, and it is the one they most often underestimate — because the hard part was never standing up Snowflake, it was proving that the new system returns the same numbers the business has trusted for a decade. A decade of stored procedures, dialect-specific SQL, hand-tuned load jobs, and downstream dashboards all hard-code assumptions about the old engine: how QUALIFY ranks rows, how an Oracle MERGE upserts, how a NUMBER(38) rounds, how a nightly BTEQ script lands its deltas. Move the data and translate the SQL and you are still only halfway — the migration is not "done" when Snowflake has the tables; it is done when a controlled dual-run validation has shown, cycle after cycle, that every row count, every aggregate, and every checksum agrees, and only then does a wave of consumers cut over.

This guide is the senior-data-engineering walkthrough for running that program end to end, framed the way interviewers probe it: the up-front migration assessment that inventories the source and scores its complexity, the SQL code translation from Teradata and Oracle dialects into Snowflake SQL and Snowflake Scripting, the bulk plus incremental data movement, the tiered reconciliation that turns data validation from a hope into a gate, and the wave-based cutover with a rollback plan and a decommission gate so the legacy system is retired on evidence, not on optimism. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse the rewrites on the data transformation practice library →, and stress-test the modelling fundamentals on the database practice library →.

---

On this page

• Why the migration path — not the tool — determines everything
• Migration assessment and inventory
• SQL code translation — Teradata and Oracle to Snowflake
• Data migration and dual-run reconciliation
• Cutover, rollback, and decommission
• Cheat sheet — Snowflake migration recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the migration path — not the tool — determines everything

A Snowflake migration is a five-phase program, not a lift-and-shift — and the phases you skip are the ones that page you at cutover

The one-sentence invariant: a warehouse migration is a five-phase program — assess the source, translate the SQL, move the data, dual-run and reconcile both systems, then cut over wave by wave — and the phase teams under-invest in is never "stand up Snowflake" but "prove the new numbers equal the old numbers," which is why validation, not translation, is where migrations succeed or fail. The Snowflake account is provisioned in an afternoon. The decade of Teradata BTEQ scripts, Oracle PL/SQL packages, dialect-specific SQL, and downstream consumers that trust the old answers is what takes quarters — and every one of those consumers hard-codes an assumption about the source engine's exact behaviour that a naive migration silently breaks.

The four axes interviewers actually probe.

• Assessment depth. Did you inventory every object (tables, views, procedures, macros, sequences), measure data volumes, and read the workload — the actual query logs — before scoping? Or did you eyeball the schema and guess? Interviewers open here because a migration scoped without the query logs always misses the 5% of gnarly stored procedures that consume 80% of the effort.
• Translation strategy. Automated converter first, manual for the residue — or hand-porting everything? The senior answer is "automate 70–90% with a converter, then hand-finish the dialect residue and prove semantic equivalence with tests," not "the converter did it, ship it." Textual conversion that compiles is not the same as a query that returns identical rows.
• Validation rigor. How do you prove Snowflake matches the source? The weak answer is "we spot-checked a few dashboards." The senior answer is a tiered reconciliation — row counts, then aggregates, then full row-hash checksums — run every cycle during a dual-run validation window, with tolerances and drill-down, feeding a sign-off gate.
• Cutover and rollback. Big-bang or phased? Is the source still authoritative until you are sure? The senior answer never says "flip everyone at midnight." It says "cut over by wave, keep the source authoritative and rollback-ready until N clean reconcile cycles, then decommission behind a gate."

The 2026 reality — tooling does the mechanics, judgment does the migration.

• Assessment is catalog-plus-logs driven. Vendor accelerators and native DBMS_METADATA / DBC catalog queries produce the object inventory automatically; the human work is complexity scoring and wave planning — deciding what moves first and what is too entangled to move yet.
• Translation is converter-assisted. SnowConvert-style tools translate the bulk of Teradata and Oracle DDL/DML/procedural code into Snowflake SQL and Snowflake Scripting; the residue — proprietary functions, QUALIFY edge cases, PL/SQL packages with autonomous transactions, Oracle-specific MERGE semantics — is manual and is where seniority shows.
• Data movement is COPY-INTO driven. Extract from the source, land compressed files in a stage (S3/ADLS/GCS), COPY INTO in bulk, then run an incremental catch-up so the two systems track each other during the dual-run window.
• Validation and cutover are the parts no tool owns. A reconciliation harness and a wave-based cutover runbook with rollback triggers are yours to build; they are exactly what a senior interview drills, because they are exactly what separates a migration that ships from one that gets rolled back in a post-incident review.

What interviewers listen for.

• Do you name all five phases unprompted and put validation at the centre? — senior signal.
• Do you insist on reading the query logs, not just the schema, during assessment? — required answer.
• Do you say "automate the translation, then prove semantic equivalence with tests" rather than "the converter handles it"? — senior signal.
• Do you describe dual-run reconciliation — count, aggregate, hash — as the thing that gates cutover? — required answer.
• Do you refuse a big-bang cutover and keep the source rollback-ready until a decommission gate? — senior signal.

Worked example — the five-phase migration map

Detailed explanation. The single most useful artifact for a migration interview is a phase map that names each phase, its exit criteria, and its failure mode. Every senior migration discussion converges on this map; having it in your head keeps you from conflating "the data is loaded" with "the migration is validated." Walk through building the map for a hypothetical Teradata EDW and Oracle FINANCE estate landing on Snowflake.

• The estate. ~1,200 Teradata tables + 400 BTEQ/stored-proc jobs; ~300 Oracle tables + 120 PL/SQL packages.
• The target. One Snowflake account, databases mirroring the source schemas, warehouses sized per workload.
• The constraint. The finance close must never see a wrong number; that domain migrates last and validates hardest.

Question. Lay out the five phases with an exit criterion and the failure mode each phase guards against.

Input.

Phase	Primary output	Exit criterion	Failure mode if skipped
1. Assess	object inventory + complexity score + wave plan	every object classified, waves drawn	scope blows up on hidden procs
2. Translate	converted DDL/DML/procs + unit tests	each object compiles + passes tests	"compiles" but returns wrong rows
3. Move data	bulk load + incremental catch-up	history loaded, deltas tracking	stale data during dual-run
4. Dual-run validate	reconciliation harness + clean cycles	N clean count/agg/hash cycles	cutover on unverified data
5. Cutover	wave switch + rollback + decommission	consumers switched, source retired	big-bang outage, no rollback

Code.

Snowflake migration — phase map (memorise this)
===============================================

  ┌─────────┐   ┌───────────┐   ┌──────────┐   ┌────────────┐   ┌──────────┐
  │ ASSESS  │──▶│ TRANSLATE │──▶│ MOVE DATA│──▶│ DUAL-RUN   │──▶│ CUTOVER  │
  │ inventory│   │ auto+manual│   │ COPY INTO│   │ reconcile  │   │ wave+     │
  │ + score  │   │ + tests    │   │ + delta  │   │ count/agg/ │   │ rollback  │
  │ + waves  │   │            │   │ catch-up │   │ hash       │   │ + retire  │
  └─────────┘   └───────────┘   └──────────┘   └────────────┘   └──────────┘
       │              │               │               │               │
   exit: every    exit: each      exit: history   exit: N clean   exit: consumers
   object         object          loaded + delta  reconcile       switched, source
   classified     compiles +      tracking        cycles          decommissioned
                  passes test                     (gate)          behind gate

  Source stays AUTHORITATIVE from phase 1 through the decommission gate in phase 5.

Step-by-step explanation.

1. Phase 1 (assess) exits only when every object is classified — not "most." The failure mode it guards is scope explosion: the 5% of stored procedures with autonomous transactions or dynamic SQL that the schema-only view never revealed, discovered mid-project when the timeline is already committed.
2. Phase 2 (translate) exits when each object compiles in Snowflake and passes a unit test. "Compiles" alone is the trap: a converted QUALIFY or MERGE can be syntactically valid Snowflake and still rank or upsert differently. The test — same input, assert same output — is the real exit criterion.
3. Phase 3 (move data) is the mechanically simplest phase and the one teams over-weight. Bulk-load the history via COPY INTO, then stand up an incremental catch-up so Snowflake tracks the source during the dual-run window. Exit: history present and deltas flowing.
4. Phase 4 (dual-run validate) is the centre of gravity. Both systems run the same workloads on the same days; the reconciliation harness compares them in tiers. Exit is not "it looked right once" but "N consecutive clean cycles" — the gate.
5. Phase 5 (cutover) switches consumers wave by wave, keeps the source authoritative and rollback-ready, and retires the source only behind a decommission gate. The whole program keeps the source authoritative from phase 1 to that gate — that single rule is what makes a migration reversible until it is proven.

Output.

Milestone	"Looks done" trap	Actually done when
Snowflake provisioned	"we're migrated!"	nothing is validated yet
Data loaded	"numbers are there"	numbers are equal, proven
SQL translated	"it compiles"	it returns identical rows
Dashboards repointed	"cutover complete"	N clean cycles + rollback retired

Rule of thumb. Never call a migration "done" at data-load. Draw the five-phase map, put validation at the centre, and keep the source authoritative until a decommission gate. The phase you are tempted to skip — dual-run validation — is the phase that pages you at cutover.

Worked example — what interviewers actually probe

Detailed explanation. The senior migration interview has a predictable arc: an ambiguous opener ("how would you move our Teradata warehouse to Snowflake?"), then progressive narrowing to test whether you know the phases and, crucially, whether you treat validation as the gate. Candidates who name dual-run reconciliation and a rollback plan score highest; candidates who describe "lift and shift the tables" score lowest. Walk through the grading rubric.

• Ambiguous opener. "How would you migrate our Teradata + Oracle estate to Snowflake?" — invites the five-phase map.
• Follow-up 1. "How do you scope it?" — probes assessment (inventory + query logs + complexity).
• Follow-up 2. "How much of the SQL can you automate?" — probes translation strategy.
• Follow-up 3. "How do you know Snowflake is correct?" — probes dual-run reconciliation.
• Follow-up 4. "How do you cut over 300 dashboards safely?" — probes wave cutover + rollback.

Question. Draft a five-minute senior migration answer that covers all five phases without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Scoping	"look at the schema"	"inventory + query logs + complexity score + waves"
Translation	"the converter does it"	"auto-convert 70–90%, hand-finish residue, unit-test each"
Validation	"spot-check dashboards"	"tiered reconcile: count → aggregate → row-hash, every cycle"
Cutover	"flip it over a weekend"	"wave by wave, source authoritative, rollback-ready"
Done	"data is loaded"	"N clean cycles + decommission gate"

Code.

Senior migration answer template (5 minutes)
=============================================

Minute 1 — name the five phases up front
  "Assess, translate, move data, dual-run validate, cut over. The hard
   phase is validation, not standing up Snowflake."

Minute 2 — assessment
  "Inventory every object from the DBC / data-dictionary catalog, read
   the actual query logs to find the hot and the hairy code, score each
   object's complexity, then plan waves — pilot a low-risk domain first."

Minute 3 — translation
  "Run an automated converter for the 70-90% of DDL/DML/procedural code
   it handles; hand-finish the dialect residue — Teradata QUALIFY and SET
   tables, Oracle MERGE, sequences, PL/SQL. Every converted object gets a
   unit test that asserts the same output as the source, not just 'it
   compiles.'"

Minute 4 — data + dual-run
  "Bulk-load history with COPY INTO from a stage, run an incremental
   catch-up, then dual-run: both systems live, reconcile in tiers — row
   count, then aggregates, then row-hash checksums — every cycle. Clean
   cycles accumulate toward a sign-off gate."

Minute 5 — cutover + rollback
  "Cut over wave by wave. The source stays authoritative and
   rollback-ready until N clean reconcile cycles and consumer sign-off.
   Only then do we freeze and decommission the source. No big-bang."

Step-by-step explanation.

1. Minute 1 frames the whole answer around phases with validation at the centre. Weak candidates dive into Snowflake features ("we'd use auto-scaling warehouses…") before naming the program shape; naming the five phases signals you have run one.
2. Minute 2 insists on the query logs, not just the schema. This is the tell that separates people who scoped a real migration — where the workload reveals the expensive procedures — from those who read a docs page.
3. Minute 3 states the automate-then-prove-equivalence stance. The specific dialect residue (QUALIFY, SET/MULTISET, MERGE, sequences, PL/SQL) shows fluency; "unit-test each converted object" shows you know that compiling is not correctness.
4. Minute 4 puts the tiered reconciliation at the heart of dual-run. Naming three tiers — count, aggregate, hash — and "every cycle" shows you treat validation as continuous evidence, not a one-time check.
5. Minute 5 refuses the big-bang and keeps rollback alive to a decommission gate. This is the reliability axis; showing you keep the source authoritative until proven is the single strongest senior signal in a migration interview.

Output.

Grading criterion	Weak score	Senior score
Names five phases in minute 1	rare	mandatory
Reads query logs in assessment	rare	required
Automates + tests translation	occasional	senior signal
Tiered reconciliation as the gate	rare	senior signal
Wave cutover + rollback + gate	rare	senior signal

Rule of thumb. The senior migration answer is a five-minute monologue: five phases, validation at the centre, automate-then-prove translation, tiered reconciliation as the gate, wave cutover with rollback to a decommission gate. Rehearse it once; deploy it every interview.

Worked example — the "which wave first" decision tree

Detailed explanation. Given a large estate, the senior architect runs a short decision tree to order the waves. Codifying it makes the plan defensible: any stakeholder can hand you a domain and you can place it. Walk the tree with three canonical domains — a low-risk marketing mart, a heavily proc-driven finance close, and a shared conformed-dimension layer everything depends on.

• Q1. Does anything downstream depend on this domain's outputs? → yes = it cannot go early alone; no = pilot candidate.
• Q2. How dialect-heavy is its code (procs, macros, PL/SQL)? → low = easy wave; high = late wave with extra test budget.
• Q3. How business-critical / correctness-sensitive is it? → low = early; high (finance close) = last, hardest validation.
• Q4. Is it a shared dependency (conformed dimensions)? → yes = must migrate before its dependents, but with a compatibility bridge.

Question. Walk the tree for the three domains and record the wave each lands in.

Input.

Domain	Q1 dependents?	Q2 dialect-heavy?	Q3 critical?	Q4 shared dep?
Marketing mart	no	low	low	no
Finance close	some	high	high	no
Conformed dims	many	medium	high	yes

Code.

# Wave-ordering helper (illustrative)
def place_wave(has_dependents: bool,
               dialect_heavy: bool,
               business_critical: bool,
               shared_dependency: bool) -> str:
    """Return the migration wave for a domain."""
    if shared_dependency:
        return "Wave 0 — migrate first, behind a compatibility bridge"
    if business_critical and dialect_heavy:
        return "Wave 3 (last) — hardest validation, extra test budget"
    if not has_dependents and not dialect_heavy and not business_critical:
        return "Wave 1 (pilot) — low risk, proves the machinery"
    return "Wave 2 — standard risk"


print(place_wave(False, False, False, False))
# → Wave 1 (pilot) — low risk, proves the machinery

print(place_wave(True,  True,  True,  False))
# → Wave 3 (last) — hardest validation, extra test budget

print(place_wave(True,  False, True,  True))
# → Wave 0 — migrate first, behind a compatibility bridge

Step-by-step explanation.

1. The marketing mart has no dependents, low dialect complexity, and low criticality — the ideal pilot. Its real value is proving the machinery (extract, load, reconcile, cutover) on something that cannot hurt the business if a cycle fails.
2. The finance close is dialect-heavy (proc-driven) and correctness-critical. It goes last with the largest test budget, because a wrong number in the close is the failure the whole program exists to prevent.
3. Conformed dimensions are a shared dependency: many domains join to them. They must migrate first (Wave 0), but with a compatibility bridge so source-side consumers still read consistent keys during the overlap — otherwise you split the truth.
4. The tree is deliberately shallow — four questions — so it is whiteboard-able. An interviewer can hand you any domain and you place it in under a minute, which is exactly the fluency the wave-planning question tests.
5. The ordering is not "smallest first" or "easiest first" in isolation; it is dependency-first, risk-last, with the pilot chosen to exercise the machinery safely. That framing is the senior signal.

Output.

Domain	Wave	Why
Conformed dims	Wave 0	shared dependency; bridge during overlap
Marketing mart	Wave 1 (pilot)	low risk; proves the pipeline
(standard domains)	Wave 2	normal risk
Finance close	Wave 3 (last)	critical + dialect-heavy; hardest validation

Rule of thumb. Order waves dependency-first and risk-last: shared dimensions go first behind a bridge, a low-risk domain pilots the machinery, and the correctness-critical, dialect-heavy domain goes last with the biggest validation budget. Never pilot on the finance close.

Senior interview question on migration strategy

A senior interviewer often opens with: "You inherit a Teradata EDW plus an Oracle finance warehouse feeding 300 dashboards and 40 downstream jobs. Leadership wants to be on Snowflake in three quarters. Walk me through how you'd sequence the program, where the risk actually lives, and how you'd prove — not assert — that Snowflake returns the same numbers before anyone cuts over."

Solution Using a phased program anchored on dual-run reconciliation and a decommission gate

Program plan — Teradata + Oracle → Snowflake (3 quarters)
=========================================================

Q1  Phase 1 Assess (all domains) + Phase 2 Translate (Wave 0/1)
    - Catalog inventory from DBC.* (Teradata) and ALL_OBJECTS (Oracle)
    - Parse 90 days of query logs → hot tables, expensive procs
    - Complexity score every object; draw waves 0..3
    - Auto-convert DDL/DML; hand-finish Wave 0 (conformed dims) + Wave 1

Q2  Phase 3 Move data + Phase 4 Dual-run (Wave 0/1/2)
    - COPY INTO bulk history; incremental catch-up jobs
    - Reconciliation harness online: count → aggregate → row-hash
    - Accumulate clean cycles per migrated table

Q3  Phase 4 Dual-run (Wave 3 finance) + Phase 5 Cutover (all waves)
    - Finance close dual-runs hardest; longest clean-cycle requirement
    - Cut over wave by wave as each hits its gate
    - Source stays authoritative + rollback-ready until decommission gate

-- The gate, expressed as data: a table cannot cut over until it has
-- accumulated N consecutive clean reconciliation cycles.
CREATE TABLE migration.reconcile_ledger (
    object_name    STRING      NOT NULL,
    cycle_date     DATE        NOT NULL,
    count_match    BOOLEAN     NOT NULL,
    aggregate_match BOOLEAN    NOT NULL,
    hash_match     BOOLEAN     NOT NULL,
    PRIMARY KEY (object_name, cycle_date)
);

-- Cutover-eligibility view: 5 consecutive clean cycles, no gaps
CREATE OR REPLACE VIEW migration.cutover_eligible AS
SELECT object_name
FROM (
    SELECT object_name,
           count(*) AS clean_cycles
    FROM   migration.reconcile_ledger
    WHERE  count_match AND aggregate_match AND hash_match
      AND  cycle_date > current_date - 7        -- last 7 daily cycles
    GROUP  BY object_name
)
WHERE clean_cycles >= 5;                         -- the gate

Step-by-step trace.

Quarter	Phases active	Exit evidence
Q1	assess (all) + translate (W0/W1)	every object scored; W0/W1 compiles + unit-tested
Q2	move + dual-run (W0/W1/W2)	history loaded; reconcile ledger filling
Q3	dual-run (W3) + cutover (all)	each wave hits ≥5 clean cycles → switches
gate	rollback retired per wave	source frozen only after decommission gate

After the program runs, no wave cuts over until migration.cutover_eligible lists it — five consecutive clean count/aggregate/hash cycles. Finance (Wave 3) accumulates the longest clean streak because its correctness bar is highest. The source stays authoritative and rollback-ready for every wave until that wave's decommission gate passes; only then is the legacy schema frozen and retired.

Output:

Metric	Big-bang (rejected)	Phased + dual-run (chosen)
Cutover risk	whole estate at once	one wave at a time
Correctness evidence	"it looked right"	5 clean count/agg/hash cycles per object
Rollback	none after flip	source authoritative until gate
Blast radius of a bug	all 300 dashboards	one wave's consumers
Decommission trigger	date on a slide	evidence-based gate

Why this works — concept by concept:

• Five-phase sequencing — assess → translate → move → dual-run → cutover makes the program legible and lets each phase have an exit criterion. The risk is front-loaded into assessment and centred on validation, not on Snowflake setup.
• Wave ordering (dependency-first, risk-last) — shared conformed dimensions migrate first behind a bridge; a low-risk mart pilots the machinery; the correctness-critical finance close goes last. Blast radius is bounded to one wave.
• Reconcile ledger as the gate — cutover eligibility is data, not a judgment call: a table cuts over only after N consecutive clean count/aggregate/hash cycles. The gate is queryable and auditable.
• Source stays authoritative until decommission — keeping the legacy system rollback-ready until the gate passes makes every wave reversible. Migrations fail when the source is retired on a calendar date instead of on evidence.
• Cost — the dual-run window doubles compute for the overlap (both systems live) and the reconciliation harness costs engineering time — but it buys evidence-based cutover with O(1)-per-wave blast radius instead of O(all) big-bang risk. The extra compute is a few weeks of overlap; the avoided cost is a finance-close incident and a full rollback.

SQL
Topic — sql
SQL migration and reconciliation query problems

Practice →

Design Topic — design Design problems on warehouse migration programs

Practice →

---

2. Migration assessment and inventory

migration assessment reads the catalog and the query logs — the schema tells you what exists, the workload tells you what will hurt

The mental model in one line: a migration assessment inventories every source object from the system catalog, measures data volumes, parses the actual query logs to find the hot and the dialect-heavy code, scores each object's complexity, and groups objects into dependency-ordered waves — because the schema alone tells you what exists while the workload tells you what will actually cost you, and a migration scoped without the query logs always underestimates the proc-heavy 5% that consumes most of the effort. Every senior data engineer who has run a migration has been burned once by a stored procedure that never showed up in the table list; the assessment exists to make that surprise impossible.

The four axes for assessment.

• Inventory completeness. Every object type, not just tables: views, stored procedures, Teradata macros/BTEQ, Oracle PL/SQL packages, sequences, triggers, materialized views, and the grants on all of them. The catalog (DBC.* on Teradata, ALL_OBJECTS / DBA_* on Oracle) is the source of truth; anything you don't inventory becomes a mid-project surprise.
• Volume and growth. Row counts and byte sizes per table, plus growth rate — this sizes the bulk-load window and the Snowflake warehouse. A 40 TB fact table and a 4 MB lookup are both "one table" in the schema and wildly different in the move plan.
• Workload reality. Parse the query logs (DBQL on Teradata, V$SQL / AWR on Oracle): which tables are hit most, which procedures run nightly, which SQL is dialect-heavy. This is what turns "1,200 tables" into "these 60 tables and 25 procedures carry the business."
• Complexity and dependency. Score each object (simple view vs. 800-line PL/SQL package with dynamic SQL) and build the dependency graph. The score drives the test budget; the graph drives the wave order. Objects with no dependents and low scores pilot; shared, high-score objects need bridges and extra care.

What a good complexity score captures.

• Line count and control flow. A flat CREATE VIEW is trivial; a package with loops, cursors, exceptions, and dynamic SQL is not. Weight procedural constructs heavily.
• Dialect-specific features. Teradata QUALIFY, SET tables, MULTISET, RESET WHEN, TOP, Oracle MERGE, sequences, CONNECT BY, (+) outer joins, ROWNUM, DECODE, autonomous transactions — each is a known translation cost. Count them.
• External touchpoints. Objects that call OS scripts, use UTL_FILE, or embed BTEQ export logic need re-platforming, not just translation.
• Fan-in / fan-out. How many objects depend on this one (fan-in) and how many it depends on (fan-out). High fan-in = migrate early behind a bridge; high fan-out = migrate after its inputs.

Common interview probes on assessment.

• "How do you inventory the source?" — from the system catalog (DBC.*, ALL_OBJECTS), never by hand.
• "Why read the query logs?" — the workload reveals the expensive 5% the schema hides.
• "How do you decide what moves first?" — dependency-ordered waves, low-risk pilot first.
• "How do you size the Snowflake warehouses?" — from volume + concurrency in the workload logs.

Worked example — building the object inventory from the source catalog

Detailed explanation. The canonical first artifact: a single inventory table joining object type, row/byte volume, and a workload hit-count, built from the source system catalog rather than by hand. Build it for Teradata and Oracle so the same downstream tooling consumes both.

• Teradata source. DBC.TablesV, DBC.ColumnsV, DBC.TableSizeV, DBC.DBQLogTbl for the workload.
• Oracle source. DBA_OBJECTS, DBA_SEGMENTS, DBA_TAB_STATISTICS, V$SQL for the workload.
• Output. One migration.inventory table: (source, object_name, object_type, row_count, bytes, hit_count_90d).

Question. Write the catalog queries that populate a unified inventory, including a workload hit-count per object.

Input.

Field	Teradata source	Oracle source
object list	DBC.TablesV	DBA_OBJECTS
size	DBC.TableSizeV	DBA_SEGMENTS
row count	COLLECT STATS / DBC	DBA_TAB_STATISTICS
workload	DBC.DBQLogTbl (DBQL)	V$SQL / AWR

Code.

-- TERADATA — object inventory + 90-day workload hit-count
-- 1. Object list with size (bytes)
SELECT t.DatabaseName,
       t.TableName                          AS object_name,
       t.TableKind                          AS object_type,   -- T=table, V=view, P=proc, M=macro
       SUM(s.CurrentPerm)                    AS bytes
FROM   DBC.TablesV t
LEFT JOIN DBC.TableSizeV s
       ON s.DatabaseName = t.DatabaseName
      AND s.TableName    = t.TableName
WHERE  t.DatabaseName = 'EDW'
GROUP  BY 1,2,3;

-- 2. Workload hit-count: how often each table appears in 90 days of DBQL
SELECT o.ObjectDatabaseName,
       o.ObjectTableName                     AS object_name,
       COUNT(*)                              AS hit_count_90d
FROM   DBC.DBQLObjTbl o
JOIN   DBC.DBQLogTbl  l ON l.QueryID = o.QueryID
WHERE  l.StartTime >= CURRENT_DATE - 90
GROUP  BY 1,2;

-- ORACLE — object inventory + workload hit-count
-- 1. Object list with size (bytes) and row counts
SELECT o.owner,
       o.object_name,
       o.object_type,                                -- TABLE, VIEW, PACKAGE, PROCEDURE, SEQUENCE
       NVL(s.bytes, 0)                     AS bytes,
       NVL(ts.num_rows, 0)                 AS row_count
FROM   dba_objects o
LEFT JOIN dba_segments s
       ON s.owner = o.owner AND s.segment_name = o.object_name
LEFT JOIN dba_tab_statistics ts
       ON ts.owner = o.owner AND ts.table_name = o.object_name
WHERE  o.owner = 'FINANCE'
  AND  o.object_type IN ('TABLE','VIEW','PACKAGE','PROCEDURE','SEQUENCE','MATERIALIZED VIEW');

-- 2. Workload hit-count from the shared SQL area (approx; AWR is more complete)
SELECT UPPER(REGEXP_SUBSTR(sql_text, '[A-Z_]+\.[A-Z_]+')) AS object_name,
       SUM(executions)                                    AS exec_count
FROM   v$sql
WHERE  parsing_schema_name = 'FINANCE'
GROUP  BY UPPER(REGEXP_SUBSTR(sql_text, '[A-Z_]+\.[A-Z_]+'));

Step-by-step explanation.

1. On Teradata, DBC.TablesV is the object catalog and TableKind distinguishes tables, views, procedures, and macros — so one query classifies the whole estate. Joining DBC.TableSizeV and summing CurrentPerm gives bytes per object, which sizes the load window.
2. The Teradata workload comes from Database Query Logging (DBQL): DBQLObjTbl records which objects each query touched, joined to DBQLogTbl for the timestamp. Counting appearances over 90 days ranks tables by how much the business actually uses them — the hit-count that turns 1,200 tables into a prioritized list.
3. On Oracle, DBA_OBJECTS lists every object and object_type separates tables from packages, procedures, and sequences — the object types that carry the dialect risk. DBA_SEGMENTS gives bytes; DBA_TAB_STATISTICS.num_rows gives row counts (assuming stats are fresh).
4. The Oracle workload approximation reads V$SQL for execution counts per referenced object; AWR (DBA_HIST_SQLSTAT) is the complete source when licensed. Either way, the goal is the same: rank objects by real usage, not by schema position.
5. Both sides land in one unified migration.inventory shape, so the complexity scorer and wave planner downstream never care whether an object came from Teradata or Oracle — the assessment normalizes the two dialects into one backlog.

Output.

source	object_name	object_type	bytes	hit_count_90d
teradata	EDW.SALES_FACT	TABLE	41 TB	128,400
teradata	EDW.LOAD_SALES	PROCEDURE	—	90 (nightly)
oracle	FINANCE.GL_BALANCES	TABLE	2.1 TB	44,900
oracle	FINANCE.CLOSE_PKG	PACKAGE	—	30 (monthly)

Rule of thumb. Build the inventory from the system catalog and join it to the query logs in the same pass. A table's schema size tells you the load window; its workload hit-count tells you its migration priority. Never scope from the schema alone — the logs are where the expensive procedures hide.

Worked example — scoring object complexity

Detailed explanation. Once inventoried, every object gets a complexity score that drives its translation effort and test budget. The score is a weighted count of the constructs known to cost translation time: procedural control flow and dialect-specific features. Build a scorer that turns raw DDL/source text into a tier.

• Inputs. Object source text (from SHOW PROCEDURE / DBMS_METADATA.GET_DDL).
• Signals. Line count, loops/cursors/exceptions, dialect features (QUALIFY, MERGE, sequences, CONNECT BY, dynamic SQL).
• Output. A tier: low (auto-convert, spot-check), medium (auto-convert + review), high (manual + full test).

Question. Write a scorer that classifies an object into low/medium/high from its source text.

Input.

Signal	Weight	Example
lines of code	1 per 50	400-line package
loop / cursor	3 each	FOR rec IN cur LOOP
exception handler	2 each	EXCEPTION WHEN …
dialect feature	3 each	QUALIFY, MERGE, CONNECT BY
dynamic SQL	5 each	EXECUTE IMMEDIATE

Code.

# Complexity scorer — source text -> tier
import re

DIALECT_FEATURES = [
    r"\bQUALIFY\b", r"\bMERGE\b", r"\bCONNECT\s+BY\b",
    r"\bRESET\s+WHEN\b", r"\bMULTISET\b", r"\.NEXTVAL\b", r"\(\+\)",
]

def complexity_score(src: str) -> tuple[int, str]:
    """Return (score, tier) for one object's source text."""
    lines   = src.count("\n") + 1
    loops   = len(re.findall(r"\b(LOOP|FOR\b.*\bIN\b|WHILE)\b", src, re.I))
    excepts = len(re.findall(r"\bEXCEPTION\b", src, re.I))
    dynamic = len(re.findall(r"\bEXECUTE\s+IMMEDIATE\b", src, re.I))
    dialect = sum(len(re.findall(p, src, re.I)) for p in DIALECT_FEATURES)

    score = (lines // 50) + 3 * loops + 2 * excepts + 5 * dynamic + 3 * dialect

    if score <= 5:
        tier = "low"        # auto-convert, spot-check
    elif score <= 20:
        tier = "medium"     # auto-convert + review
    else:
        tier = "high"       # manual + full unit test
    return score, tier


# Example: a gnarly Oracle package
pkg = open_source("FINANCE.CLOSE_PKG")     # 620 lines, 4 loops, 3 handlers,
                                           # 2 EXECUTE IMMEDIATE, 5 MERGE
print(complexity_score(pkg))
# → (49, 'high')

# Example: a flat reporting view
vw = open_source("EDW.V_SALES_DAILY")      # 40 lines, 1 QUALIFY
print(complexity_score(vw))
# → (3, 'low')

Step-by-step explanation.

1. The scorer counts the constructs that actually cost translation time, not just lines. A 620-line package is not 12× a 50-line one if the long one is flat SQL; the control flow and dialect features are what drive rework, so they carry the heavy weights.
2. EXECUTE IMMEDIATE (dynamic SQL) carries the highest per-instance weight (5) because dynamic SQL can't be validated by the converter — it constructs statements at runtime, so a human must reason about every code path it can generate.
3. Dialect features (QUALIFY, MERGE, CONNECT BY, MULTISET, .NEXTVAL, (+)) each add 3: they are mechanical to translate individually but each is a known semantic-difference risk, so their count predicts how much equivalence testing the object needs.
4. The thresholds bucket objects into three tiers that map directly to effort: low gets auto-conversion and a spot-check, medium gets auto-conversion plus human review, high gets manual translation and a full unit test. The tier, not the raw score, drives the plan.
5. Summed across the estate, the tiers turn 1,500 objects into a defensible budget: "1,100 low (fast), 300 medium (review), 100 high (manual)" — and the 100 high-tier objects are where the schedule risk lives, which is exactly what leadership needs to hear early.

Output.

Object	Score	Tier	Effort
FINANCE.CLOSE_PKG	49	high	manual + full unit test
EDW.LOAD_SALES	22	high	manual + review
EDW.V_SALES_DAILY	3	low	auto-convert + spot-check
FINANCE.DIM_CALENDAR	1	low	auto-convert

Rule of thumb. Score complexity by weighting procedural control flow and dialect features far above raw line count, and bucket into low/medium/high tiers that map to effort. The high-tier objects — dynamic SQL, deep PL/SQL, dialect-dense SQL — are a small fraction of the count and the large fraction of the risk; surface them in week one.

Worked example — dependency graph and wave planning

Detailed explanation. With objects inventoried and scored, the last assessment artifact is the dependency graph, which orders the waves. An object can migrate only after the objects it reads from, and shared objects (high fan-in) migrate first behind a bridge. Build the graph from the catalog's dependency views and derive a topological wave order.

• Teradata. DBC.Dependency / referenced-object metadata.
• Oracle. DBA_DEPENDENCIES (name, referenced_name).
• Output. A wave number per object from a topological sort, adjusted for risk.

Question. Derive wave numbers from the dependency graph so no object migrates before its inputs.

Input.

Object	Depends on	Fan-in (dependents)
DIM_CUSTOMER (conformed)	—	40
SALES_FACT	DIM_CUSTOMER, DIM_DATE	12
V_SALES_DAILY	SALES_FACT	3
RPT_EXEC_DASH	V_SALES_DAILY	0

Code.

-- ORACLE — extract the dependency edges from the catalog
SELECT d.name           AS object_name,
       d.referenced_name AS depends_on
FROM   dba_dependencies d
WHERE  d.owner = 'FINANCE'
  AND  d.referenced_owner = 'FINANCE'
  AND  d.type IN ('VIEW','PACKAGE BODY','PROCEDURE','MATERIALIZED VIEW');

# Topological wave assignment from dependency edges
from collections import defaultdict, deque

def assign_waves(edges: list[tuple[str, str]], nodes: set[str]) -> dict[str, int]:
    """edges: (object, depends_on). Wave = longest dependency depth."""
    deps = defaultdict(set)          # object -> {things it depends on}
    dependents = defaultdict(set)    # object -> {things that depend on it}
    for obj, dep in edges:
        deps[obj].add(dep)
        dependents[dep].add(obj)

    # Kahn-style longest-path layering
    indeg = {n: len(deps[n]) for n in nodes}
    wave  = {n: 0 for n in nodes}
    q = deque(n for n in nodes if indeg[n] == 0)   # objects with no deps
    while q:
        n = q.popleft()
        for child in dependents[n]:
            wave[child] = max(wave[child], wave[n] + 1)
            indeg[child] -= 1
            if indeg[child] == 0:
                q.append(child)
    return wave


nodes = {"DIM_CUSTOMER", "DIM_DATE", "SALES_FACT", "V_SALES_DAILY", "RPT_EXEC_DASH"}
edges = [("SALES_FACT","DIM_CUSTOMER"), ("SALES_FACT","DIM_DATE"),
         ("V_SALES_DAILY","SALES_FACT"), ("RPT_EXEC_DASH","V_SALES_DAILY")]
print(assign_waves(edges, nodes))
# → {'DIM_CUSTOMER': 0, 'DIM_DATE': 0, 'SALES_FACT': 1,
#    'V_SALES_DAILY': 2, 'RPT_EXEC_DASH': 3}

Step-by-step explanation.

1. The dependency edges come straight from the catalog (DBA_DEPENDENCIES on Oracle, the equivalent referenced-object metadata on Teradata) — never hand-maintained. Each edge says "object X reads from object Y," which is the constraint the wave order must respect.
2. The topological layering assigns each object a wave equal to its longest dependency depth: conformed dimensions with no dependencies land in wave 0, facts that read them in wave 1, views on facts in wave 2, and the exec dashboard in wave 3. Nothing migrates before its inputs.
3. High fan-in objects (DIM_CUSTOMER has 40 dependents) naturally sort to wave 0 — they must go first — but their fan-in also flags them for a compatibility bridge: while they live in both systems during the overlap, keys must stay consistent or downstream joins split.
4. The topological wave is a floor, not the final plan: risk adjusts it. A low-complexity wave-0 mart can pilot; a high-complexity, correctness-critical object may be held back to a later wave even if its dependencies are ready, trading dependency-earliness for validation budget.
5. The output is a defensible migration backlog: a wave number per object that respects dependencies, plus the fan-in flags that identify which objects need bridges. That backlog is the deliverable the assessment phase exists to produce.

Output.

Object	Topo wave	Risk adjust	Final wave
DIM_CUSTOMER	0	shared → bridge	Wave 0
SALES_FACT	1	standard	Wave 2
V_SALES_DAILY	2	low risk	Wave 2
RPT_EXEC_DASH	3	leaf, low risk	Wave 3

Rule of thumb. Derive wave order from a topological sort of the catalog dependency graph so nothing migrates before its inputs, then adjust for risk. High fan-in objects go first behind a compatibility bridge; leaf reports go last. The graph is data from the catalog — never a hand-drawn diagram that drifts.

Senior interview question on migration assessment

A senior interviewer might ask: "You're handed a Teradata warehouse with 1,500 objects and no documentation. Leadership wants a migration estimate in two weeks. Walk me through exactly how you'd produce a defensible inventory, complexity score, and wave plan — and how you'd avoid the classic trap of estimating from the schema and getting ambushed by the stored procedures."

Solution Using a catalog-driven inventory, weighted complexity score, and dependency-ordered waves

-- 1. Unified inventory: catalog objects + size + 90-day workload hit-count
CREATE TABLE migration.inventory AS
WITH objects AS (
    SELECT 'EDW' AS db, TableName AS object_name, TableKind AS object_type
    FROM   DBC.TablesV WHERE DatabaseName = 'EDW'
),
sizes AS (
    SELECT TableName AS object_name, SUM(CurrentPerm) AS bytes
    FROM   DBC.TableSizeV WHERE DatabaseName = 'EDW' GROUP BY 1
),
workload AS (
    SELECT o.ObjectTableName AS object_name, COUNT(*) AS hit_count_90d
    FROM   DBC.DBQLObjTbl o
    JOIN   DBC.DBQLogTbl  l ON l.QueryID = o.QueryID
    WHERE  l.StartTime >= CURRENT_DATE - 90
    GROUP  BY 1
)
SELECT ob.object_name, ob.object_type,
       COALESCE(sz.bytes, 0)         AS bytes,
       COALESCE(wl.hit_count_90d, 0) AS hit_count_90d
FROM   objects ob
LEFT JOIN sizes    sz ON sz.object_name = ob.object_name
LEFT JOIN workload wl ON wl.object_name = ob.object_name;

# 2. Score every procedural object; 3. derive waves from the dep graph
import re

def score_and_wave(inventory, source_texts, dep_edges):
    scored = {}
    for obj, src in source_texts.items():
        s = ((src.count("\n") // 50)
             + 3 * len(re.findall(r"\b(LOOP|WHILE)\b", src, re.I))
             + 2 * len(re.findall(r"\bEXCEPTION\b", src, re.I))
             + 5 * len(re.findall(r"\bEXECUTE\s+IMMEDIATE\b", src, re.I))
             + 3 * len(re.findall(r"\b(QUALIFY|MERGE|CONNECT\s+BY|MULTISET)\b", src, re.I)))
        scored[obj] = "high" if s > 20 else "medium" if s > 5 else "low"

    waves = assign_waves(dep_edges, set(inventory))   # topo sort from earlier
    return scored, waves


# 4. The estimate rolls up tier counts × per-tier effort
#    low: 0.5 day, medium: 2 days, high: 8 days (translate + test)
def estimate_days(scored):
    per = {"low": 0.5, "medium": 2, "high": 8}
    return sum(per[t] for t in scored.values())

-- 5. The deliverable leadership sees: risk concentrated in the high tier
SELECT tier,
       COUNT(*)                          AS objects,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_objects,
       SUM(effort_days)                  AS effort_days
FROM   migration.scored_objects
GROUP  BY tier
ORDER  BY effort_days DESC;

Step-by-step trace.

Step	Artifact	Guards against
1	catalog inventory + workload join	scoping from schema alone
2	weighted complexity score	under-costing the proc-heavy 5%
3	topological wave order	migrating an object before its inputs
4	tier × effort estimate	a single unbacked number on a slide
5	risk-concentration report	leadership surprise at the high tier

After the two weeks, leadership gets a defensible estimate: object counts and effort by tier, with the risk visibly concentrated in the ~7% high-tier objects (deep PL/SQL, dynamic SQL, dialect-dense procedures). The wave plan respects dependencies, and the workload hit-count has already surfaced the nightly procedures the schema-only view would have missed.

Output:

Tier	Objects	% of objects	Effort (days)
high	105	7%	840
medium	300	20%	600
low	1,095	73%	548
total	1,500	100%	1,988

Why this works — concept by concept:

• Catalog-driven inventory — the object list comes from DBC.* / ALL_OBJECTS, so it is complete by construction. Nothing is missed because nothing is hand-listed; every table, view, proc, macro, and sequence is accounted for.
• Workload join — folding the 90-day query logs into the inventory ranks objects by real usage and surfaces the nightly and monthly procedures the schema hides. This is the single step that prevents the "ambushed by stored procedures" trap.
• Weighted complexity score — weighting dynamic SQL, loops, and dialect features far above line count concentrates the estimate's risk where it actually lives, so the ~7% high tier is visible on day one instead of discovered mid-project.
• Topological waves — deriving wave order from the catalog dependency graph guarantees no object migrates before its inputs, and the fan-in flags identify which shared objects need bridges. The plan is data, not a diagram.
• Cost — the assessment is O(objects) catalog reads plus a one-pass parse of the source text and query logs — days of compute and two weeks of analyst time. The avoided cost is a mid-migration re-scope when the hidden procedures surface, which is measured in quarters. Cheap insurance against the most common migration failure.

Database
Topic — database
Database catalog, schema, and inventory problems

Practice →

Data validation Topic — data-validation Data validation and profiling problems

Practice →

---

3. SQL code translation — Teradata and Oracle to Snowflake

SQL code translation is a semantic-equivalence problem, not a find-and-replace — automate the 70–90% a converter handles, then hand-finish the dialect residue and prove it with tests

The mental model in one line: SQL code translation converts Teradata and Oracle DDL, DML, and procedural code into Snowflake SQL and Snowflake Scripting, and the senior discipline is to run an automated converter for the large fraction it handles mechanically, then hand-finish the dialect residue — Teradata QUALIFY/SET tables, Oracle MERGE, sequences, CONNECT BY, and PL/SQL — while treating every conversion as a semantic-equivalence obligation proven by a unit test, because SQL that compiles in Snowflake is not the same as SQL that returns the identical rows the business has trusted for a decade. Every migration has a converter; only the good ones have a test harness that catches the conversion that compiled and lied.

The four axes for code translation.

• Automated coverage. A converter (SnowConvert-style) mechanically translates most DDL, straightforward DML, and much procedural code — typically 70–90% by object count. The residue is the dialect-specific and dynamic constructs. Know what the tool covers and what it doesn't.
• Dialect residue. The 10–30% that needs a human: Teradata QUALIFY edge cases, SET/MULTISET table semantics, RESET WHEN, TOP n WITH TIES; Oracle MERGE with non-standard branches, sequences, CONNECT BY hierarchies, (+) outer joins, DECODE, ROWNUM, and PL/SQL packages with cursors, exceptions, and autonomous transactions.
• Semantic equivalence. The obligation is same input → same output, not "compiles." NUMBER/DECIMAL precision and rounding, NULL ordering, implicit type coercion, empty-string-vs-NULL (Oracle treats '' as NULL), and date arithmetic all differ subtly and silently. Each is a place a "successful" conversion returns different rows.
• Testability. Every converted object gets a unit test: run the source object and the Snowflake object on the same input, assert identical output. Without it, translation is a hope; with it, translation is a gate. The high-complexity objects get the full harness; the trivial ones get a spot-check.

Teradata → Snowflake — the residue you translate by hand.

• QUALIFY — Snowflake supports QUALIFY natively (a rare gift), but Teradata's interaction with SET tables and RESET WHEN needs care.
• SET vs MULTISET tables — Teradata SET tables silently reject duplicate rows; Snowflake has no such concept, so a naive load can introduce duplicates the source suppressed. Add an explicit dedupe.
• TOP n / SAMPLE — map to LIMIT / SAMPLE; watch tie semantics.
• BTEQ scripts — re-platform to COPY INTO + Snowflake Scripting or an orchestrator; there is no line-by-line equivalent.

Oracle → Snowflake — the residue you translate by hand.

• MERGE — Snowflake supports MERGE, but Oracle's DELETE clause and error-logging (LOG ERRORS) branches need rework.
• Sequences — Snowflake sequences exist but are not gap-free and behave differently under concurrency; validate any logic that assumed contiguous IDs.
• CONNECT BY — rewrite hierarchical queries as recursive CTEs (WITH RECURSIVE).
• (+) outer joins, DECODE, NVL, ROWNUM — map to ANSI LEFT JOIN, CASE/DECODE (Snowflake has DECODE), NVL/COALESCE, and ROW_NUMBER().
• PL/SQL — packages become Snowflake Scripting stored procedures; cursors → FOR loops over result sets; autonomous transactions have no direct equivalent and need redesign.

Common interview probes on translation.

• "How much can you automate?" — 70–90% by count; the residue is dialect + dynamic SQL.
• "What breaks silently?" — precision/rounding, NULL vs empty string, NULL ordering, sequence gaps.
• "How do you prove a translation is correct?" — a unit test asserting identical output on the same input.
• "How do you rewrite Oracle CONNECT BY?" — a recursive CTE.

Worked example — Teradata QUALIFY + SET-table dedupe

Detailed explanation. A Teradata pattern that translates almost cleanly and hides a duplicate-row trap: a QUALIFY ROW_NUMBER() dedupe writing into a SET table. Snowflake supports QUALIFY natively, so the query converts one-to-one — but Snowflake tables have no SET semantics, so any duplicate suppression the source relied on the table to enforce must become explicit. Walk through the faithful translation.

• Source. Teradata SET table latest_status; a QUALIFY keeps the latest row per key; the SET table silently drops any exact-duplicate that slips through.
• Target. Snowflake table (no SET concept) + an explicit QUALIFY dedupe on load so no duplicates appear.

Question. Translate the Teradata insert-select so Snowflake produces the identical de-duplicated result.

Input.

Concern	Teradata	Snowflake
QUALIFY	native	native (direct)
dup suppression	SET table (implicit)	explicit QUALIFY / DISTINCT
row-number reset	RESET WHEN (rare)	window frame rewrite
tie-break	deterministic ORDER	must add explicit tie-break

Code.

-- TERADATA source: SET table + QUALIFY latest-per-key
CREATE SET TABLE latest_status (           -- SET => duplicate rows rejected
    customer_id  BIGINT,
    status       VARCHAR(20),
    status_ts    TIMESTAMP
);

INSERT INTO latest_status
SELECT customer_id, status, status_ts
FROM   status_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id
                          ORDER BY status_ts DESC) = 1;

-- SNOWFLAKE translation: no SET table, so make dedupe explicit
CREATE TABLE latest_status (               -- Snowflake: no SET semantics
    customer_id  NUMBER,
    status       VARCHAR(20),
    status_ts    TIMESTAMP_NTZ
);

INSERT INTO latest_status
SELECT customer_id, status, status_ts
FROM   status_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id
                          ORDER BY status_ts DESC,
                                   status ASC          -- explicit tie-break:
                          ) = 1;                       -- SET table's implicit
                                                       -- dedupe is now explicit

Step-by-step explanation.

1. The QUALIFY clause converts directly — Snowflake is one of the few warehouses with native QUALIFY, so ROW_NUMBER() OVER (...) = 1 needs no rewrite. This is why the query looks fully translated and why the trap is easy to miss.
2. The trap is the SET table. On Teradata, CREATE SET TABLE rejects exact-duplicate rows at the storage layer, so even if the QUALIFY let two identical rows through, the table would keep one. Snowflake has no such behaviour — both rows would land.
3. The fix is to make the deduplication explicit and total in the query: a tie-break in the ORDER BY (status ASC) so ROW_NUMBER() = 1 is deterministic even when two rows share the same status_ts. Now the query, not the table, guarantees one row per key.
4. Type mappings matter too: Teradata TIMESTAMP becomes Snowflake TIMESTAMP_NTZ (or TIMESTAMP_TZ if the source carried a zone) and BIGINT becomes NUMBER. The converter does these, but the reviewer confirms the time-zone semantics didn't silently change.
5. The unit test loads a fixture with intentional duplicates and ties into both systems and asserts identical output. Without the explicit tie-break, the Snowflake result is non-deterministic on tied timestamps — a class of bug that passes on a small sample and fails in production.

Output.

status_events (input)	Teradata (SET + QUALIFY)	Snowflake (explicit QUALIFY)
(1, active, 09:00)	(1, active, 09:00) — kept? no	—
(1, churned, 10:00)	(1, churned, 10:00)	(1, churned, 10:00)
(2, active, 08:00) ×2 dup	(2, active, 08:00) once	(2, active, 08:00) once

Rule of thumb. When translating Teradata SET tables, never assume the table will suppress duplicates — Snowflake won't. Push the deduplication into an explicit QUALIFY/DISTINCT with a deterministic tie-break, and unit-test with a fixture that contains ties. QUALIFY translating cleanly is exactly why the SET-table trap slips past review.

Worked example — Oracle MERGE + sequence rewrite

Detailed explanation. An Oracle upsert built on a MERGE with a WHERE on the matched branch and a sequence-driven surrogate key. Snowflake supports MERGE, but two things need care: Oracle's per-branch WHERE and delete semantics, and the sequence's gap/concurrency behaviour. Walk through a faithful translation.

• Source. Oracle MERGE INTO dim_customer using a sequence dim_customer_seq.NEXTVAL for new surrogate keys, with a WHERE filter on the update branch.
• Target. Snowflake MERGE with a Snowflake sequence and an equivalent conditional update.

Question. Translate the Oracle MERGE so Snowflake upserts identically, including the surrogate-key assignment.

Input.

Concern	Oracle	Snowflake
MERGE	native	native
surrogate key	sequence.NEXTVAL	Snowflake SEQUENCE.NEXTVAL (not gap-free)
conditional update	UPDATE … WHERE	WHEN MATCHED AND
empty string	'' = NULL	'' is a real empty string

Code.

-- ORACLE source: MERGE with sequence surrogate key + conditional update
MERGE INTO dim_customer d
USING stg_customer s
ON (d.natural_key = s.natural_key)
WHEN MATCHED THEN
    UPDATE SET d.name = s.name, d.updated_at = SYSTIMESTAMP
    WHERE  d.name <> s.name                          -- only touch changed rows
WHEN NOT MATCHED THEN
    INSERT (d.customer_sk, d.natural_key, d.name, d.updated_at)
    VALUES (dim_customer_seq.NEXTVAL, s.natural_key, s.name, SYSTIMESTAMP);

-- SNOWFLAKE translation
-- 1. sequence (note: Snowflake sequences are NOT guaranteed gap-free)
CREATE SEQUENCE IF NOT EXISTS dim_customer_seq START = 1 INCREMENT = 1;

MERGE INTO dim_customer d
USING stg_customer s
ON  d.natural_key = s.natural_key
WHEN MATCHED AND d.name <> s.name THEN                -- Oracle's UPDATE…WHERE
    UPDATE SET d.name = s.name, d.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (customer_sk, natural_key, name, updated_at)
    VALUES (dim_customer_seq.NEXTVAL, s.natural_key, s.name, CURRENT_TIMESTAMP());

Step-by-step explanation.

1. Oracle's WHEN MATCHED THEN UPDATE … WHERE d.name <> s.name moves the filter onto the branch condition in Snowflake: WHEN MATCHED AND d.name <> s.name THEN UPDATE …. Same effect — only changed rows are touched — but the syntax placement differs, and a converter that drops the WHERE would silently rewrite every matched row's updated_at on every run.
2. SYSTIMESTAMP becomes CURRENT_TIMESTAMP(). These are close but not identical (time-zone handling differs), so the reviewer confirms whether downstream logic depends on the zone; if so, map to TIMESTAMP_TZ explicitly.
3. The sequence is the subtle risk. Oracle sequences and Snowflake sequences are both fast and not gap-free; but if any legacy logic assumed contiguous surrogate keys (some SCD reconciliation does), that assumption breaks on both — so the translation is an opportunity to confirm no code depends on contiguity.
4. The empty-string trap lurks here: Oracle treats '' as NULL, so d.name <> s.name behaves differently when one side is an empty string. In Snowflake '' is a real empty string that is not NULL, so a row with name = '' compares differently. The unit test must include an empty-string fixture.
5. The equivalence test runs the same staging batch through both MERGEs and asserts the target rows match on (natural_key, name, updated_at-was-touched-or-not) — specifically checking that unchanged rows did not get a new updated_at, the exact behaviour the branch WHERE protects.

Output.

stg_customer	dim_customer before	after (both systems)
(K1, "Acme")	(10, K1, "Acme", t0)	unchanged (name equal → no update)
(K2, "Globex Inc")	(11, K2, "Globex", t0)	(11, K2, "Globex Inc", t_now)
(K3, "NewCo")	—	(NEXTVAL, K3, "NewCo", t_now) insert

Rule of thumb. When translating Oracle MERGE, move the matched-branch WHERE into WHEN MATCHED AND <cond>, confirm no logic depends on gap-free sequences (neither Oracle nor Snowflake guarantees it), and always unit-test with an empty-string fixture because Oracle's '' = NULL semantics do not survive the move. The MERGE compiling is not the same as the MERGE upserting identically.

Worked example — PL/SQL procedure → Snowflake Scripting

Detailed explanation. The hardest residue is procedural code. An Oracle PL/SQL procedure with a cursor loop, an exception handler, and a sequence becomes a Snowflake Scripting stored procedure — cursors become FOR loops over a RESULTSET, EXCEPTION WHEN becomes an EXCEPTION block, and the whole thing is wrapped in Snowflake's BEGIN … END. Walk through a faithful re-platforming.

• Source. Oracle procedure process_batch: opens a cursor over pending rows, updates each, handles NO_DATA_FOUND, commits.
• Target. Snowflake Scripting procedure using a cursor FOR loop and an EXCEPTION block.

Question. Translate the PL/SQL procedure to Snowflake Scripting preserving the per-row logic and error handling.

Input.

PL/SQL construct	Snowflake Scripting equivalent
CURSOR c IS SELECT …	LET c CURSOR FOR SELECT …
FOR rec IN c LOOP	FOR rec IN c DO
EXCEPTION WHEN … THEN	EXCEPTION WHEN … THEN
seq.NEXTVAL	seq.NEXTVAL
implicit COMMIT	autocommit / explicit

Code.

-- ORACLE PL/SQL source
CREATE OR REPLACE PROCEDURE process_batch AS
    CURSOR c_pending IS
        SELECT id, amount FROM staging WHERE processed = 'N';
BEGIN
    FOR rec IN c_pending LOOP
        UPDATE ledger
        SET    total = total + rec.amount
        WHERE  account_id = rec.id;

        UPDATE staging SET processed = 'Y' WHERE id = rec.id;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, 'no pending rows');
END;

-- SNOWFLAKE Scripting translation
CREATE OR REPLACE PROCEDURE process_batch()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    c_pending CURSOR FOR
        SELECT id, amount FROM staging WHERE processed = 'N';
    no_rows EXCEPTION (-20001, 'no pending rows');
    n INT DEFAULT 0;
BEGIN
    FOR rec IN c_pending DO
        UPDATE ledger
        SET    total = total + rec.amount
        WHERE  account_id = rec.id;

        UPDATE staging SET processed = 'Y' WHERE id = rec.id;
        n := n + 1;
    END FOR;

    IF (n = 0) THEN
        RAISE no_rows;
    END IF;
    RETURN 'processed ' || n || ' rows';
EXCEPTION
    WHEN no_rows THEN
        RAISE;                          -- Snowflake auto-rolls back the txn
END;
$$;

Step-by-step explanation.

1. The PL/SQL CURSOR c_pending IS SELECT … becomes a Snowflake Scripting CURSOR FOR SELECT … inside a DECLARE block, and FOR rec IN c_pending LOOP … END LOOP becomes FOR rec IN c_pending DO … END FOR. The per-row body — the two UPDATEs — is unchanged, which is the whole point: the logic survives, only the wrapper changes.
2. Transaction semantics differ and must be reasoned about explicitly. Oracle's explicit COMMIT inside the procedure has no line-by-line equivalent; Snowflake stored procedures run in the caller's transaction context (or autocommit). Here the translation relies on Snowflake auto-rolling back on an unhandled exception, so the ROLLBACK is implicit — the reviewer must confirm this matches the source's atomicity intent.
3. Oracle's NO_DATA_FOUND doesn't map directly — a cursor FOR loop over zero rows simply doesn't iterate, it doesn't raise. So the translation makes the "no rows" condition explicit: count iterations (n) and RAISE a declared exception when n = 0. This preserves the source's behaviour (error on empty batch) that a naive translation would silently drop.
4. RAISE_APPLICATION_ERROR(-20001, …) becomes a declared EXCEPTION (-20001, …) and a RAISE. Snowflake Scripting exceptions carry a code and message, so the downstream error contract is preserved for any caller that inspected the Oracle error code.
5. The unit test drives both procedures against (a) a batch with pending rows — assert the ledger totals and processed flags match — and (b) an empty batch — assert both raise error -20001. The empty-batch case is exactly the behaviour the explicit n = 0 check restores; without it, the Snowflake version would succeed silently where Oracle failed.

Output.

Input batch	Oracle result	Snowflake result
3 pending rows	ledger += amounts; 3 flagged Y	ledger += amounts; 3 flagged Y; "processed 3 rows"
0 pending rows	ORA-20001 raised	error -20001 'no pending rows' raised

Rule of thumb. When re-platforming PL/SQL to Snowflake Scripting, preserve the per-row body verbatim but reason explicitly about transaction boundaries and error conditions — Oracle's implicit COMMIT/ROLLBACK and NO_DATA_FOUND have no line-by-line equivalent, so make "empty batch" and "atomicity" explicit and unit-test both the happy path and the error path. The loop body translating cleanly is never the hard part; the transaction and exception semantics are.

Senior interview question on SQL code translation

A senior interviewer might ask: "You've run a Teradata + Oracle estate through an automated converter and 85% converted cleanly. Walk me through how you handle the remaining 15%, how you decide a conversion is actually correct rather than just compiling, and give me a concrete example of a translation that compiles in Snowflake but returns different rows than the source."

Solution Using an automate-then-verify pipeline with per-object equivalence tests

Translation pipeline (per object)
=================================

  source DDL/DML/proc
        │
        ▼
  ┌───────────────┐   auto-converts 70-90% by count
  │  converter    │   (SnowConvert-style)
  └───────────────┘
        │
        ├── clean ──▶ lint (Snowflake compile) ──▶ equivalence test ──▶ merge
        │
        └── residue ─▶ manual rewrite (dialect/dynamic) ─▶ lint ─▶ equivalence test ─▶ review ─▶ merge

  Nothing merges without: (1) it compiles, AND (2) same input → same output.

-- Equivalence test harness: run source object and Snowflake object on the
-- same fixture, compare with a full row-hash (tier-3 reconcile, section 4).
-- Example: prove the translated view returns identical rows.
WITH src AS (   -- rows exported from the source engine for the fixture
    SELECT * FROM migration.fixture_expected_v_sales_daily
),
tgt AS (        -- rows from the Snowflake translation
    SELECT * FROM analytics.v_sales_daily
)
SELECT
    (SELECT COUNT(*) FROM src)                                   AS src_rows,
    (SELECT COUNT(*) FROM tgt)                                   AS tgt_rows,
    (SELECT COUNT(*) FROM src MINUS SELECT * FROM tgt)           AS in_src_not_tgt,
    (SELECT COUNT(*) FROM tgt MINUS SELECT * FROM src)           AS in_tgt_not_src;
-- PASS iff src_rows = tgt_rows AND both MINUS counts = 0

-- The classic "compiles but wrong": Oracle empty-string = NULL
-- Source (Oracle): '' is NULL, so this returns customers with no name
SELECT count(*) FROM customers WHERE name IS NULL;      -- counts '' too
-- Naive Snowflake translation returns FEWER rows because '' is NOT NULL here.
-- Correct translation normalises on load:
--   INSERT ... SELECT NULLIF(name, '') AS name ...     -- restore Oracle semantics

Step-by-step trace.

Stage	Check	Blocks merge on
convert	tool output produced	—
lint	compiles in Snowflake	syntax / unsupported feature
equivalence test	same input → same output	any row difference
review (residue)	human reads dialect rewrite	semantic doubt
merge	all above green	—

After the pipeline runs, the 85% clean conversions still pass through lint and an equivalence test — because "the converter said clean" is not evidence. The 15% residue is hand-rewritten (QUALIFY/SET traps, MERGE branches, PL/SQL, CONNECT BY), linted, equivalence-tested, and human-reviewed. The empty-string example is the concrete "compiles but wrong": Oracle's '' = NULL means a naive translation silently returns fewer rows on any IS NULL filter until the load normalises '' to NULL.

Output:

Conversion class	Volume	Gate applied	Outcome
auto-clean	85%	lint + equivalence test	most pass; some caught by test
dialect residue	~12%	manual + lint + test + review	rewritten, proven
dynamic SQL / autonomous txn	~3%	redesign + review	re-architected
"compiles but wrong"	any	equivalence test	blocked before merge

Why this works — concept by concept:

• Automate-first — the converter does the mechanical 70–90% so human effort concentrates on the residue that actually needs judgment. Spending senior time hand-porting flat views is the anti-pattern; the tool exists to prevent it.
• Equivalence test as the merge gate — every object, even the auto-clean ones, must prove same input → same output via a full row-hash comparison. "Compiles" is necessary but never sufficient; the test is what turns translation from a hope into a gate.
• Dialect-residue expertise — the QUALIFY/SET dedupe, the MERGE branch WHERE, the empty-string-vs-NULL, the sequence-gap, and the PL/SQL transaction semantics are the known silent-difference sites. Naming and testing each is the senior skill.
• Human review for the residue — dynamic SQL and autonomous transactions can't be validated by a fixture alone because their behaviour depends on runtime-constructed statements; those get redesign plus review, not just a test.
• Cost — the pipeline adds a lint + equivalence test per object (O(objects) cheap fixtures) plus manual effort proportional to the residue, not the whole estate. The avoided cost is a silent wrong-number bug reaching a dashboard — the single most expensive class of migration defect, because it erodes trust in the entire new platform.

SQL
Topic — sql
SQL dialect, window-function, and MERGE problems

Practice →

Data transformation Topic — data-transformation Data transformation and SQL rewrite problems

Practice →

---

4. Data migration and dual-run reconciliation

dual-run validation runs both systems live and proves equality in tiers — row count, then aggregates, then row-hash — so cutover rides on evidence, not on a green pipeline

The mental model in one line: data migration bulk-loads history into Snowflake via COPY INTO from a stage and then keeps an incremental catch-up running, while dual-run validation keeps both systems live and reconciles them in escalating tiers — cheap row counts first, then aggregate checksums (SUM/MIN/MAX/COUNT DISTINCT), then a full row-hash comparison — because a pipeline that ran green proves the job finished, not that the numbers match, and only a tiered reconciliation that agrees cycle after cycle earns the right to cut over. The load is the easy half; the reconciliation is the half that decides whether the migration is trustworthy.

The four axes for data migration + dual-run.

• Bulk vs incremental. History moves once in bulk (extract → compressed files in a stage → COPY INTO); ongoing changes move via an incremental catch-up (CDC, timestamp windows, or re-extract of changed partitions) so Snowflake tracks the source during the overlap.
• Reconciliation tiers. Cheap-to-expensive: (1) row count per table, (2) aggregate checksums per column, (3) full row-hash comparison. Run the cheap tiers every cycle and escalate to the expensive tier on mismatch or on a schedule — you cannot afford a full row-hash of a 40 TB table every hour.
• Tolerance and drill-down. Some differences are expected (in-flight rows during the window, floating-point summation order). Define tolerances per check; on a breach, drill down from "table X mismatched" to "these 12 rows differ on this column" so the fix is targeted, not a re-migration.
• Authority and cadence. The source stays authoritative during dual-run; Snowflake is shadow. Reconcile on a fixed cadence (usually daily, matching the batch), record every cycle's result in a ledger, and accumulate clean cycles toward the cutover gate.

The bulk-load recipe.

• Extract. Unload the source to delimited or Parquet files, compressed (gzip/zstd), split into ~100–250 MB chunks so COPY INTO parallelises.
• Stage. Land the files in an external stage (S3/ADLS/GCS) or a Snowflake internal stage; register a FILE FORMAT.
• COPY INTO. Load with ON_ERROR, VALIDATION_MODE for a dry run, and PURGE after success. One COPY command loads thousands of files in parallel across the warehouse.
• Verify the load. Immediately reconcile row count and a cheap aggregate against the source for that extract — catch a truncated file before it pollutes the dual-run.

The reconciliation ladder.

• Tier 1 — row count. SELECT COUNT(*) on both sides per table. Cheapest; catches gross load failures instantly.
• Tier 2 — aggregate checksum. Per numeric column SUM, MIN, MAX; per key column COUNT(DISTINCT); per text column a hash of concatenated sorted values. Catches value corruption a count misses.
• Tier 3 — row-hash. Hash every row (MD5/SHA of the concatenated, normalised columns), compare the set of hashes with MINUS both ways. Catches any per-row difference; expensive, so run on a schedule or on tier-2 breach.

Common interview probes on dual-run.

• "How do you load the history?" — extract → stage → COPY INTO, verify per extract.
• "How do you prove equality?" — tiered reconciliation: count → aggregate → row-hash.
• "Why not just row counts?" — counts miss value corruption; aggregates and hashes catch it.
• "What stays authoritative during the overlap?" — the source; Snowflake is shadow until the gate.

Worked example — bulk load with COPY INTO

Detailed explanation. The canonical bulk load: unload a Teradata/Oracle table to compressed files in a stage, define a file format, COPY INTO the Snowflake table, and immediately reconcile the load. Walk through it for a large fact table.

• Source. SALES_FACT, 41 TB, unloaded to zstd-compressed Parquet, ~200 MB per file.
• Stage. External S3 stage @sales_stage.
• Load. COPY INTO with error handling; verify count + a SUM immediately.

Question. Write the file format, the COPY INTO, and the immediate load-verification query.

Input.

Parameter	Value
Source table	SALES_FACT (41 TB)
File format	Parquet, zstd, ~200 MB/file
Stage	@sales_stage (S3)
Verify	row count + SUM(amount) vs source

Code.

-- 1. File format + stage
CREATE FILE FORMAT sales_parquet
    TYPE = PARQUET
    COMPRESSION = ZSTD;

CREATE STAGE sales_stage
    URL = 's3://migration-bucket/sales_fact/'
    FILE_FORMAT = sales_parquet;

-- 2. Optional dry run — validate without loading
COPY INTO analytics.sales_fact
FROM @sales_stage
FILE_FORMAT = (FORMAT_NAME = sales_parquet)
VALIDATION_MODE = RETURN_ERRORS;          -- reports bad rows, loads nothing

-- 3. The load — parallelises across the warehouse over thousands of files
COPY INTO analytics.sales_fact
FROM @sales_stage
FILE_FORMAT = (FORMAT_NAME = sales_parquet)
ON_ERROR = ABORT_STATEMENT                -- fail loud; don't half-load
PURGE = FALSE;                            -- keep files until reconciled

-- 4. Immediate load verification (cheap tiers) against the source extract
--    source_control values were captured at unload time from the source.
SELECT
    (SELECT COUNT(*)     FROM analytics.sales_fact)  AS tgt_rows,
    (SELECT COUNT(*)     FROM migration.source_control WHERE tbl='SALES_FACT') AS expected_rows,
    (SELECT SUM(amount)  FROM analytics.sales_fact)  AS tgt_sum,
    (SELECT sum_amount   FROM migration.source_control WHERE tbl='SALES_FACT') AS expected_sum;
-- PASS iff tgt_rows = expected_rows AND tgt_sum = expected_sum

Step-by-step explanation.

1. Unloading to many ~200 MB compressed files (not one giant file) is what lets COPY INTO parallelise — Snowflake assigns files to warehouse threads, so a well-sized fileset loads 41 TB in a fraction of the time a single file would. File sizing is the single biggest load-throughput lever.
2. VALIDATION_MODE = RETURN_ERRORS is a dry run: it parses every file and reports rows that would fail, loading nothing. Running it first on a new extract catches schema drift or bad encoding before a multi-hour load, not after.
3. ON_ERROR = ABORT_STATEMENT makes the load fail loudly on any bad row rather than silently skipping. During a migration you want a truncated or corrupt file to stop the load — a half-loaded fact table that then reconciles "close enough" is the exact failure dual-run exists to prevent.
4. PURGE = FALSE keeps the staged files until the load is reconciled. If verification fails you can re-COPY without re-extracting from the source; purge only after the cheap-tier check passes.
5. Step 4 reconciles immediately against control values captured at unload time — row count and a SUM. This is the "verify the load" gate: it catches a missing file (count low) or a truncated file (sum off) before that table ever enters the dual-run cadence, keeping bad data out of the reconciliation ledger.

Output.

Check	Source (control)	Snowflake (loaded)	Result
row count	8,412,900,110	8,412,900,110	match
SUM(amount)	1,203,884,221,540.55	1,203,884,221,540.55	match
files loaded	214,000	214,000	match
load errors	—	0 (ABORT on error)	clean

Rule of thumb. Unload to many right-sized compressed files so COPY INTO parallelises, dry-run with VALIDATION_MODE, load with ON_ERROR = ABORT_STATEMENT, keep files (PURGE = FALSE) until you have reconciled row count and one aggregate against source control values. Verify every extract at load time — a truncated file caught at load is a non-event; caught in dual-run it is a re-migration.

Worked example — tier-1 + tier-2 reconciliation (count + aggregate)

Detailed explanation. The daily dual-run check runs the two cheap tiers across every migrated table: row count and per-column aggregate checksums. It writes a pass/fail per table into the reconcile ledger. Build the generic check that works for any table.

• Tier 1. COUNT(*) both sides.
• Tier 2. SUM/MIN/MAX per numeric column, COUNT(DISTINCT) per key, a text-column hash.
• Output. A ledger row (object, cycle_date, count_match, aggregate_match).

Question. Write the count + aggregate reconciliation for sales_fact and record the result.

Input.

Check	Source expression	Snowflake expression
count	COUNT(*)	COUNT(*)
sum	SUM(amount)	SUM(amount)
distinct keys	COUNT(DISTINCT customer_id)	COUNT(DISTINCT customer_id)
bounds	MIN/MAX(sale_date)	MIN/MAX(sale_date)

Code.

-- Source-side aggregates are captured into migration.source_daily by an
-- extract job running the SAME expressions on Teradata/Oracle each cycle.

-- Snowflake-side aggregates for the same cycle
CREATE OR REPLACE TEMP TABLE tgt_daily AS
SELECT
    'sales_fact'                     AS object_name,
    CURRENT_DATE                     AS cycle_date,
    COUNT(*)                         AS row_count,
    SUM(amount)                      AS sum_amount,
    COUNT(DISTINCT customer_id)      AS distinct_customers,
    MIN(sale_date)                   AS min_date,
    MAX(sale_date)                   AS max_date
FROM analytics.sales_fact;

-- Compare against the source-side capture and write the ledger row
INSERT INTO migration.reconcile_ledger (object_name, cycle_date,
                                        count_match, aggregate_match, hash_match)
SELECT
    t.object_name,
    t.cycle_date,
    (t.row_count = s.row_count)                                   AS count_match,
    (t.sum_amount        = s.sum_amount
     AND t.distinct_customers = s.distinct_customers
     AND t.min_date      = s.min_date
     AND t.max_date      = s.max_date)                           AS aggregate_match,
    NULL                                                          AS hash_match  -- tier 3 runs separately
FROM tgt_daily t
JOIN migration.source_daily s
  ON s.object_name = t.object_name AND s.cycle_date = t.cycle_date;

Step-by-step explanation.

1. The source side runs the same aggregate expressions on Teradata/Oracle each cycle and lands them in migration.source_daily. Running identical expressions on both engines is the whole trick — the comparison is only valid if SUM(amount) means the same thing on both sides, which is why the translation phase already proved equivalence.
2. Tier 1 (row_count) catches gross failures: a missing incremental batch, a partition that didn't load, a filter that dropped rows. It is one cheap COUNT(*) per table and runs first because a count mismatch makes the aggregate comparison moot.
3. Tier 2 aggregates catch what counts miss: SUM(amount) catches value corruption (a scaling or rounding bug) even when the row count is identical; COUNT(DISTINCT customer_id) catches key duplication or loss; MIN/MAX(sale_date) catches a truncated date range. Together they're a strong, cheap proxy for "the values are right."
4. Each check resolves to a boolean and both booleans are written to reconcile_ledger for this cycle. The ledger is the accumulating evidence — the cutover gate (section 1) queries it for N consecutive clean cycles.
5. hash_match is left NULL here because tier 3 is expensive and runs on its own schedule (or on a tier-2 breach). The design deliberately runs cheap tiers every cycle and the expensive tier selectively — you reconcile a 40 TB table's full row-hash weekly or on-mismatch, not every day.

Output.

object	cycle_date	count_match	aggregate_match	hash_match
sales_fact	2026-08-16	true	true	(null)
sales_fact	2026-08-17	true	true	true (weekly)
dim_customer	2026-08-17	true	false	(null)

Rule of thumb. Run tier-1 (count) and tier-2 (aggregate checksums) every dual-run cycle on identical expressions both sides, and write a boolean-per-tier ledger row. Counts catch gross failures; aggregates catch value corruption a count misses. Reserve the expensive tier-3 row-hash for a schedule or a tier-2 breach — never every cycle on a huge table.

Worked example — tier-3 row-hash reconciliation and drill-down

Detailed explanation. When tier 2 flags a mismatch (or on the scheduled deep check), tier 3 computes a per-row hash on both sides and diffs the sets of hashes to find exactly which rows differ, then drills down to the differing columns. Walk through the row-hash and the drill-down for dim_customer, which failed tier 2 above.

• Row hash. MD5 of concatenated, normalised columns per row.
• Set diff. MINUS both directions to find rows present-but-different.
• Drill-down. Join the differing keys back to both tables and compare column by column.

Question. Write the row-hash comparison and the column-level drill-down that isolates the difference.

Input.

Step	Operation
normalise	COALESCE nulls, trim, cast to canonical types
hash	MD5(concat_ws('
diff	tgt MINUS src, src MINUS tgt on (key, hash)
drill	join differing keys; compare each column

Code.

-- 1. Per-row hash on the Snowflake side (source side runs the equivalent)
CREATE OR REPLACE TEMP TABLE tgt_hash AS
SELECT
    natural_key,
    MD5(CONCAT_WS('|',
        COALESCE(natural_key::STRING, '∅'),
        COALESCE(TRIM(name), '∅'),
        COALESCE(status, '∅'),
        COALESCE(TO_CHAR(updated_at, 'YYYY-MM-DD HH24:MI:SS'), '∅')
    )) AS row_hash
FROM analytics.dim_customer;

-- 2. Set diff: which keys differ between source and target
--    migration.src_hash was produced by the SAME normalise+hash on source
SELECT 'in_tgt_not_src' AS side, t.natural_key
FROM   tgt_hash t
LEFT JOIN migration.src_hash s
       ON s.natural_key = t.natural_key AND s.row_hash = t.row_hash
WHERE  s.natural_key IS NULL
UNION ALL
SELECT 'in_src_not_tgt' AS side, s.natural_key
FROM   migration.src_hash s
LEFT JOIN tgt_hash t
       ON t.natural_key = s.natural_key AND t.row_hash = s.row_hash
WHERE  t.natural_key IS NULL;

-- 3. Column-level drill-down for the differing keys (isolate the bug)
SELECT t.natural_key,
       t.name    AS tgt_name,    s.name    AS src_name,
       t.status  AS tgt_status,  s.status  AS src_status
FROM   analytics.dim_customer t
JOIN   migration.src_customer  s ON s.natural_key = t.natural_key
WHERE  t.natural_key IN (SELECT natural_key FROM diff_keys)
  AND (COALESCE(t.name,'∅')   <> COALESCE(s.name,'∅')
    OR COALESCE(t.status,'∅') <> COALESCE(s.status,'∅'));
-- Reveals e.g. tgt_name = 'ACME' vs src_name = 'ACME ' (trailing space) →
-- a normalise/trim gap, fixed by TRIM on load, not a re-migration.

Step-by-step explanation.

1. The row hash concatenates every column with a separator, after normalising: COALESCE nulls to a sentinel (∅), TRIM text, and format timestamps to a canonical string. Normalisation is essential — otherwise a trailing space or a null-vs-empty difference would make every row hash differ and hide the real signal.
2. The same normalise-and-hash runs on the source side into migration.src_hash. Because the hash inputs are identical, two rows with the same business content produce the same hash on both engines; any hash difference is a genuine per-row difference, not a formatting artifact.
3. The set diff uses MINUS-style anti-joins both directions on (natural_key, row_hash): rows in target with no matching source hash (changed or extra) and rows in source with no matching target hash (changed or missing). This pinpoints which keys differ out of billions — far more useful than "the table mismatched."
4. The drill-down joins the differing keys back to both tables and compares column by column, revealing the specific column that differs. In the example, tgt_name = 'ACME' vs src_name = 'ACME ' — a trailing-space normalisation gap in the load, not a data-loss bug.
5. The fix is now targeted and cheap: add TRIM on load (or to the normalise step) and re-reconcile, rather than re-migrating the whole table. This is why tier 3 pays for its cost — it turns "something's wrong with dim_customer" into "add TRIM to one column's load," which is a one-line fix and a re-run.

Output.

natural_key	tgt_name	src_name	diagnosis
K-4471	'ACME'	'ACME '	trailing space → add TRIM on load
K-9002	'Globex'	'Globex'	matches (hash equal; not in diff)
K-5510	NULL	''	Oracle '' = NULL → NULLIF on load

Rule of thumb. Run tier-3 row-hash on identical normalised inputs both sides, diff the hash sets both directions to find the differing keys, then drill down column by column to isolate the exact difference. Most tier-3 failures are normalisation gaps — trailing spaces, empty-string-vs-NULL, timestamp precision — that are fixed on load in one line, not by re-migrating. The hash finds the needle; the drill-down names it.

Senior interview question on dual-run reconciliation

A senior interviewer might ask: "You've loaded a 40 TB Teradata warehouse into Snowflake and both systems are now live. Design the dual-run reconciliation that will let you sign off cutover with confidence. Cover the tiers of checking, how you handle a 40 TB table you can't fully hash every day, how you deal with expected in-flight differences, and what accumulates toward the go/no-go decision."

Solution Using a tiered reconciliation harness with tolerances and a clean-cycle gate

-- 1. Every cycle: cheap tiers on ALL tables (count + aggregate)
--    Expensive tier (row-hash): rotate a subset daily so every table is
--    fully hashed at least weekly, plus on-demand on any aggregate breach.
MERGE INTO migration.reconcile_ledger tgt
USING (
    SELECT object_name, CURRENT_DATE AS cycle_date,
           (t.row_count = s.row_count)      AS count_match,
           (t.checksum  = s.checksum)       AS aggregate_match,
           NULL                             AS hash_match
    FROM   migration.tgt_daily t
    JOIN   migration.source_daily s USING (object_name, cycle_date)
) src
ON tgt.object_name = src.object_name AND tgt.cycle_date = src.cycle_date
WHEN NOT MATCHED THEN INSERT VALUES (src.object_name, src.cycle_date,
                                     src.count_match, src.aggregate_match, src.hash_match);

-- 2. Tolerance handling: exclude in-flight rows from the comparison window.
--    Reconcile only rows settled before the cutoff (source batch boundary),
--    so rows written during the dual-run window aren't counted as "diffs".
CREATE OR REPLACE VIEW analytics.sales_fact_settled AS
SELECT * FROM analytics.sales_fact
WHERE  load_ts < (SELECT batch_boundary FROM migration.cycle_control);

-- 3. The gate: 5 consecutive clean cycles (count+agg+hash) → cutover-eligible
CREATE OR REPLACE VIEW migration.cutover_eligible AS
SELECT object_name
FROM (
    SELECT object_name,
           MIN(count_match::INT * aggregate_match::INT
               * COALESCE(hash_match,TRUE)::INT) OVER (
               PARTITION BY object_name ORDER BY cycle_date
               ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS clean5
    FROM   migration.reconcile_ledger
)
WHERE clean5 = 1
GROUP BY object_name;

Step-by-step trace.

Concern	Mechanism	Reasoning
every-cycle checks	count + aggregate on all tables	cheap; catches gross + value corruption
40 TB table	rotate full row-hash (weekly) + on-breach	can't hash everything daily
in-flight rows	settled-view cutoff at batch boundary	exclude expected transient diffs
tolerances	per-check equality on settled data	no false alarms from the window
go/no-go	5 consecutive clean cycles in ledger	evidence, not opinion

After the harness runs, every table is reconciled cheaply every cycle and fully hashed at least weekly; a 40 TB fact table's full hash rotates through the schedule rather than running daily. In-flight rows are excluded via a settled-data cutoff at the source batch boundary, so the reconciliation compares only rows both systems have finished writing. A table becomes cutover_eligible only after five consecutive fully-clean cycles — the go/no-go is a query against evidence, not a meeting.

Output:

object	cheap tiers	full hash	clean streak	eligible?
sales_fact (40 TB)	daily	weekly + on-breach	5	yes
dim_customer	daily	daily (small)	5	yes
gl_balances	daily	weekly	2	no (needs 3 more)
rpt_exec_dash	daily	daily	5	yes

Why this works — concept by concept:

• Tiered reconciliation — cheap count + aggregate every cycle on all tables, expensive row-hash rotated/on-breach. This makes continuous validation affordable: you get daily evidence on everything and full-fidelity evidence on a sustainable schedule.
• Settled-data cutoff — comparing only rows written before the source batch boundary excludes in-flight differences that are expected during a live dual-run. Without it, the harness cries wolf on every transient row and the team learns to ignore alerts.
• Row-hash rotation — a 40 TB table can't be fully hashed daily, so the schedule rotates deep checks such that every table is fully hashed at least weekly, and any aggregate breach triggers an immediate targeted hash. Coverage without unaffordable compute.
• Clean-cycle gate as data — cutover eligibility is 5 consecutive clean cycles computed with a window function over the ledger, so go/no-go is auditable and objective. Nobody argues about whether a table is "ready"; the query answers it.
• Cost — the dual-run doubles compute for the overlap window and the deep hash is O(rows) on a rotation, but the cheap tiers are O(1) aggregates per table per cycle. The spend buys evidence-based sign-off; the avoided cost is cutting over on a green pipeline and discovering the mismatch after the source is gone.

Data validation
Topic — data-validation
Data validation and reconciliation problems

Practice →

ETL Topic — etl ETL problems on bulk load and incremental sync

Practice →

---

5. Cutover, rollback, and decommission

cutover is wave by wave with the source kept authoritative and rollback-ready — the legacy system is retired only behind a decommission gate, never on a calendar date

The mental model in one line: cutover switches consumers from the legacy warehouse to Snowflake one wave at a time, keeps the source authoritative and rollback-ready throughout, and retires the legacy system only behind a decommission gate — N consecutive clean reconcile cycles plus consumer sign-off — because a big-bang cutover has no rollback, a calendar-date decommission destroys your evidence trail, and the whole discipline of the migration is to make every switch reversible until it is proven safe. The migration is not won when Snowflake is live; it is won when the source is retired on evidence and nobody noticed the switch.

The four axes for cutover.

• Cutover unit. Wave, not estate. Each wave (from the assessment's dependency-ordered plan) cuts over independently once its tables hit the reconcile gate. Blast radius is one wave's consumers, never all 300 dashboards at once.
• Switch mechanism. How consumers repoint: a connection-string/DNS/alias switch, a semantic-layer redirection (BI tool points at Snowflake), or a routing flag in the orchestrator. The switch must be fast to flip and fast to flip back — that reversibility is the rollback.
• Rollback plan. Until a wave passes its decommission gate, the source stays authoritative and consumers can be repointed back in minutes. During the overlap you may dual-write (or keep the source's own loads running) so the source stays current enough to roll back to.
• Decommission gate. The one-way door. A wave's source objects are frozen and retired only after: N consecutive clean reconcile cycles, all consumers switched and signed off, a defined soak period with no incidents, and a final backup. After the gate, rollback is gone — so the gate must be strict.

Cutover strategies.

• Phased by wave (default). Cut over each wave as it qualifies. Lowest risk; longest overlap. This is the senior default.
• Blue/green. Snowflake (green) runs in parallel with the source (blue); flip traffic, keep blue warm for instant rollback, retire blue after soak. A wave-level blue/green is the reversible switch.
• Read-then-write. Switch read traffic (dashboards, reports) first — low risk, easy rollback — then switch write/load ownership once reads are proven.

The rollback triggers.

• Reconcile regression. A post-cutover reconcile cycle fails → automatic rollback candidate.
• Consumer-reported discrepancy. A dashboard shows a wrong number → repoint that consumer to source, investigate.
• Performance regression. A critical job misses SLA on Snowflake → roll back, right-size the warehouse, retry.
• The rule. Rollback is cheap and blameless before the gate; after the gate it is a disaster-recovery event. Keep the gate strict so you rarely need post-gate rollback.

Common interview probes on cutover.

• "Big-bang or phased?" — phased by wave; never big-bang.
• "How do you roll back?" — fast switch back to the still-authoritative source, before the gate.
• "When do you decommission?" — behind a gate: clean cycles + sign-off + soak + backup.
• "What's the risk of a calendar-date decommission?" — you destroy your rollback and your reconciliation baseline.

Worked example — the wave cutover runbook

Detailed explanation. The cutover of one wave is a scripted runbook, not an ad-hoc afternoon. It gates on reconcile eligibility, switches consumers, watches a soak window, and either confirms or rolls back. Walk through the runbook for the marketing-mart pilot wave.

• Pre-check. Wave's tables in cutover_eligible; consumers listed; rollback path tested.
• Switch. Repoint the wave's consumers (BI aliases, job configs) to Snowflake.
• Soak. Watch reconcile + consumer reports for a defined window.
• Confirm or roll back. Clean soak → confirm; any trigger → roll back.

Question. Write the cutover runbook steps with the exact gate and rollback conditions.

Input.

Phase	Gate / action
pre-check	all wave tables in cutover_eligible
switch	repoint consumers; keep source loading
soak	3 clean reconcile cycles post-switch
confirm	consumer sign-off → schedule decommission
rollback	any failed cycle/report → repoint to source

Code.

# Wave cutover runbook (orchestrated; each step logged + reversible)
def cutover_wave(wave: str) -> str:
    # 1. PRE-CHECK — every table in the wave must be reconcile-eligible
    pending = sql(f"""
        SELECT object_name FROM migration.wave_objects
        WHERE  wave = '{wave}'
          AND  object_name NOT IN (SELECT object_name FROM migration.cutover_eligible)
    """)
    if pending:
        return f"BLOCKED: {len(pending)} objects not yet eligible: {pending}"

    # 2. SWITCH — repoint this wave's consumers to Snowflake.
    #    Source keeps loading (rollback stays possible).
    for consumer in consumers_of(wave):
        repoint(consumer, target="snowflake")     # alias / conn-string / BI source
        log(f"switched {consumer} → snowflake")

    # 3. SOAK — require N clean reconcile cycles AFTER the switch
    for cycle in range(3):
        wait_for_next_cycle()
        if not reconcile_clean(wave):
            rollback_wave(wave)                    # step 5
            return f"ROLLED BACK: reconcile failed in soak cycle {cycle}"

    # 4. CONFIRM — consumer sign-off, then queue the decommission gate
    if consumer_signoff(wave):
        schedule_decommission(wave)                # gate handled separately
        return f"CUTOVER CONFIRMED for {wave}; decommission queued"
    return f"HELD: awaiting consumer sign-off for {wave}"


def rollback_wave(wave: str) -> None:
    for consumer in consumers_of(wave):
        repoint(consumer, target="source")         # back to authoritative source
        log(f"ROLLBACK {consumer} → source")

Step-by-step explanation.

1. The pre-check refuses to start unless every table in the wave is in cutover_eligible (the reconcile gate from section 4). A wave with one un-reconciled table is blocked — you never cut over a partially-validated wave, because the one un-validated table is exactly where the wrong number hides.
2. The switch repoints consumers via whatever indirection exists — a BI data-source alias, a connection string, an orchestrator flag — and keeps the source loading. Keeping the source current is what makes rollback real: if you stop loading the source at switch time, you can't roll back to it an hour later without a gap.
3. The soak requires N clean reconcile cycles after the switch, not before. Pre-switch cleanliness proves the data matched while Snowflake was shadow; post-switch cleanliness proves it still matches now that Snowflake is serving traffic. Both are needed.
4. Any failed soak cycle triggers an immediate, scripted rollback_wave — repoint every consumer back to the still-authoritative source. Because this path is code and was tested in the pre-check, rollback is minutes and blameless, not a heroic 3 AM scramble.
5. Only after clean soak and explicit consumer sign-off does the wave queue for the decommission gate. Sign-off is a human confirmation that the consumers (not just the reconcile harness) see correct numbers — the last check before the one-way door.

Output.

Runbook step	Pass path	Fail path
pre-check eligibility	proceed to switch	BLOCKED (not eligible)
switch consumers	source keeps loading	—
soak (3 cycles)	proceed to confirm	ROLLED BACK to source
consumer sign-off	queue decommission	HELD

Rule of thumb. Make cutover a scripted, reversible runbook gated on reconcile eligibility: pre-check every table, switch consumers while the source keeps loading, require clean post-switch soak cycles, and roll back automatically on any trigger. Rollback must be tested code, not a plan — a rollback you've never run is a rollback you don't have.

Worked example — dual-write overlap and rollback window

Detailed explanation. To keep rollback real during the overlap, some migrations dual-write: new data lands in both the source and Snowflake until the decommission gate, so either system can serve as authoritative and rollback has no gap. Walk through the dual-write pattern and its reconciliation implication.

• Pattern. The load layer writes each batch to both the source and Snowflake during the overlap.
• Benefit. Instant, gap-free rollback — both systems are current.
• Cost + risk. Double the load work; must reconcile that the dual-writes agree (or you've just created two truths).

Question. Design the dual-write overlap so rollback is gap-free and the two writes are themselves reconciled.

Input.

Aspect	Choice
write targets	source + Snowflake (both) during overlap
authority	source until gate; Snowflake shadow-then-primary
rollback	repoint reads; both systems current
reconcile	per-batch check that both writes match

Code.

# Dual-write during the overlap window: every batch → both systems
def load_batch(batch_id: int, rows: list[dict]) -> None:
    # 1. Write to the still-authoritative source (unchanged legacy path)
    src_count = source_load(batch_id, rows)

    # 2. Write the SAME batch to Snowflake
    sf_count = snowflake_load(batch_id, rows)

    # 3. Per-batch reconcile the two writes IMMEDIATELY (don't wait for the
    #    daily cycle — catch a divergent dual-write at the source)
    if src_count != sf_count:
        alert(f"DUAL-WRITE MISMATCH batch {batch_id}: src={src_count} sf={sf_count}")
        quarantine(batch_id)          # hold; investigate before it pollutes both
    else:
        log(f"dual-write ok batch {batch_id}: {sf_count} rows both sides")

-- Rollback during the overlap is a READ repoint only — no data movement,
-- because both systems already have every batch. Example: BI source flip.
ALTER VIEW bi.sales_current AS
    SELECT * FROM legacy.sales_fact;      -- rollback: point BI back at source
-- (forward cutover pointed it at analytics.sales_fact)

Step-by-step explanation.

1. During the overlap, every batch is written to both the source (the unchanged legacy load path) and Snowflake. This is the price of gap-free rollback: at any instant both systems have every committed batch, so repointing reads back to the source loses nothing.
2. Step 3 reconciles the two writes per batch, immediately — not at the daily cycle. Dual-write introduces a new failure mode: the two writes could diverge (a transform bug that only affects the Snowflake path). Catching it at write time, before it compounds over a day, is what keeps dual-write from creating two subtly different truths.
3. A mismatch quarantines the batch rather than letting it land divergently on both sides. This is stricter than the daily reconcile because a dual-write divergence is a code bug in the migration's own load, not a data-content difference — it must be fixed at the source, immediately.
4. Rollback during the overlap is a pure read repoint — flip the BI view (or connection alias) back to legacy.sales_fact. No data has to move because dual-write already put every batch on both sides. That's the entire benefit: rollback becomes an ALTER VIEW, not a restore.
5. Dual-write ends at the decommission gate: once a wave passes, you stop writing to the source, Snowflake becomes solely authoritative, and the source is frozen. Up to that moment, the overlap cost (double loads + per-batch reconcile) is buying gap-free reversibility.

Output.

Event	With dual-write	Without (bulk+incremental only)
rollback latency	read repoint (minutes)	possible gap since last sync
rollback data loss	none (both current)	rows written after last sync
overlap cost	2× load + per-batch reconcile	1× load
new failure mode	divergent dual-write (caught per batch)	none

Rule of thumb. Use dual-write when the rollback requirement is gap-free and the overlap is short — write every batch to both systems, reconcile the two writes per batch (not just daily), and quarantine divergences immediately. Dual-write makes rollback an ALTER VIEW, but it creates a new "two truths" risk that only per-batch reconciliation contains. End it at the decommission gate.

Worked example — the decommission gate checklist

Detailed explanation. Decommissioning the source is the one-way door: after it, rollback is a disaster-recovery event, not an ALTER VIEW. The gate is a strict, evidenced checklist. Walk through the gate for a wave that has cut over and soaked.

• Evidence required. N clean reconcile cycles post-cutover, all consumers signed off, a soak period with zero incidents, a final archived backup.
• Actions at the gate. Freeze source writes, archive a final backup, revoke source access, then (after a grace period) drop.

Question. Write the decommission gate as a checklist with the exact evidence each item requires.

Input.

Gate item	Evidence
reconcile	≥10 consecutive clean cycles post-cutover
consumers	100% switched + signed off
soak	≥2 weeks, zero correctness incidents
backup	final source backup archived + restore-tested
access	source set read-only, then revoked

Code.

-- Decommission-eligibility query: a wave passes ONLY if every item is green
WITH reconcile AS (
    SELECT object_name,
           COUNT(*) FILTER (WHERE count_match AND aggregate_match
                             AND COALESCE(hash_match, TRUE)) AS clean_cycles
    FROM   migration.reconcile_ledger
    WHERE  cycle_date > (SELECT cutover_date FROM migration.wave_status w
                         WHERE w.object_name = reconcile_ledger.object_name)
    GROUP  BY object_name
),
signoff AS (
    SELECT object_name FROM migration.consumer_signoff WHERE signed = TRUE
),
soak AS (
    SELECT object_name FROM migration.wave_status
    WHERE  incidents_since_cutover = 0
      AND  cutover_date <= current_date - 14        -- ≥ 2 weeks
)
SELECT w.object_name,
       (r.clean_cycles >= 10)          AS reconcile_ok,
       (s.object_name IS NOT NULL)     AS signoff_ok,
       (k.object_name IS NOT NULL)     AS soak_ok,
       w.backup_archived               AS backup_ok
FROM   migration.wave_status w
LEFT JOIN reconcile r ON r.object_name = w.object_name
LEFT JOIN signoff   s ON s.object_name = w.object_name
LEFT JOIN soak      k ON k.object_name = w.object_name;
-- DECOMMISSION only where reconcile_ok AND signoff_ok AND soak_ok AND backup_ok

-- On a fully-green wave: freeze, back up, revoke — then (after grace) drop.
REVOKE ALL ON legacy.sales_fact FROM ALL;            -- 1. freeze writes/reads
-- 2. final backup archived + restore-tested out of band
-- 3. after a grace period with the backup verified:
-- DROP TABLE legacy.sales_fact;                      -- the one-way door

Step-by-step explanation.

1. The gate query treats decommission as a conjunction of four independent evidences — reconcile, sign-off, soak, backup — each a boolean. A wave is decommission-eligible only when all four are green; any red holds the source alive. There is no "close enough" on the one-way door.
2. The reconcile evidence counts clean cycles since the cutover date (not since dual-run started) — post-cutover cleanliness is what matters, because it proves Snowflake is correct while serving production traffic, which is a stronger claim than shadow-mode cleanliness.
3. Sign-off is a per-consumer human confirmation recorded as data. This guards against the case where the harness is green but a consumer has a bespoke report the reconciliation didn't cover; the humans who own the numbers must assent.
4. The soak requires a defined incident-free window (≥2 weeks here). Some correctness issues only surface on a monthly close or a quarter-end run, so the soak must be long enough to have exercised the wave's real business cycle at least once where feasible.
5. Only on a fully-green wave do you freeze (revoke access), archive and restore-test a final backup, and — after a grace period — drop. The restore-test matters: an unverified backup is not a backup, and after the drop it's the only thing standing between you and unrecoverable data loss.

Output.

Gate item	Wave status	Green?
reconcile (≥10 clean post-cutover)	12 clean	yes
consumers signed off	100%	yes
soak (≥2 weeks, 0 incidents)	18 days, 0	yes
backup archived + restore-tested	verified	yes
→ decommission	eligible	freeze → drop

Rule of thumb. Treat decommission as a strict conjunction of evidence — post-cutover clean cycles, consumer sign-off, an incident-free soak long enough to cover the business cycle, and a restore-tested backup — and only then freeze, back up, revoke, and drop. The gate is the one-way door; make it strict, because after it your rollback is gone and your reconciliation baseline is gone with the source.

Senior interview question on cutover and rollback

A senior interviewer might ask: "Your Snowflake migration is validated and it's time to move 300 dashboards and 40 load jobs off Teradata. Design the cutover so there's never a big-bang risk, rollback is always possible until you're certain, and the source is decommissioned only when it's genuinely safe. Cover the switch mechanism, the rollback triggers, and the exact gate for retiring the source."

Solution Using wave-based cutover with tested rollback and an evidenced decommission gate

# End-to-end cutover controller: per wave, switch → soak → sign-off → gate
def run_cutover_program(waves: list[str]) -> None:
    for wave in ordered_by_dependency(waves):      # dependency-first (section 2)
        # 1. Gate on reconcile eligibility (section 4)
        if not all_eligible(wave):
            hold(wave, reason="reconcile gate not met")
            continue

        # 2. Read-then-write switch: dashboards first (low risk), loads second
        switch_reads(wave, target="snowflake")     # BI/report consumers
        if not soak_clean(wave, cycles=3):
            rollback(wave); continue               # tested, minutes, blameless
        switch_writes(wave, target="snowflake")    # load-ownership moves

        # 3. Source stays authoritative + loading until the gate
        if consumer_signoff(wave) and soak_clean(wave, cycles=10):
            if decommission_gate(wave):            # strict conjunction
                freeze_and_archive(wave)           # revoke + restore-tested backup
                retire_source(wave)                # one-way door

-- The rollback trigger: any post-cutover reconcile regression auto-flags
CREATE OR REPLACE VIEW migration.rollback_candidates AS
SELECT object_name, cycle_date
FROM   migration.reconcile_ledger
WHERE  cycle_date > (SELECT cutover_date FROM migration.wave_status w
                     WHERE w.object_name = reconcile_ledger.object_name)
  AND  NOT (count_match AND aggregate_match AND COALESCE(hash_match, TRUE));
-- Any row here → repoint that object's consumers back to the authoritative source.

Step-by-step trace.

Stage	Action	Reversible?
eligibility gate	require reconcile-clean per table	n/a (pre-switch)
switch reads	dashboards → Snowflake	yes (repoint back)
soak reads	3 clean cycles	rollback on fail
switch writes	load ownership → Snowflake	yes (source still loads)
soak + sign-off	10 clean cycles + consumer assent	rollback on fail
decommission gate	strict conjunction + backup	NO (one-way)

After the program runs, each wave switches only after its reconcile gate, and switches reads before writes so the lowest-risk consumers move first with trivial rollback. The source stays authoritative and loading until a wave clears the decommission gate, so rollback_candidates — any post-cutover reconcile regression — can repoint consumers back in minutes. The source is retired only behind the strict, evidenced gate; every step before it is reversible, and the one irreversible step is the most heavily gated.

Output:

Property	Big-bang (rejected)	Wave + read-then-write (chosen)
blast radius	300 dashboards at once	one wave's consumers
rollback before gate	none	read/write repoint, minutes
first movers	everything	low-risk reads
decommission trigger	date	evidenced conjunction
irreversibility	at flip	only at the gate

Why this works — concept by concept:

• Wave-based, dependency-ordered switch — cutting over one wave at a time in dependency order bounds blast radius to a single wave's consumers and respects the graph so no consumer reads a half-migrated dependency. The estate is never all-at-risk.
• Read-then-write — switching low-risk read consumers first, soaking, then moving write/load ownership means the earliest, most reversible moves happen first and the riskier write switch happens only after reads are proven. Risk is sequenced, not taken all at once.
• Source-authoritative overlap — keeping the source live and loading until the gate makes rollback_candidates actionable: any post-cutover regression repoints to a still-current source in minutes. Rollback is real because the fallback is real.
• Strict decommission gate — the one irreversible action is guarded by a conjunction of evidence (post-cutover clean cycles + sign-off + soak + restore-tested backup). The most dangerous step is the most gated, which is exactly the right risk allocation.
• Cost — the overlap costs double loading and a longer dual-run, and the runbook/rollback automation costs engineering time — but it buys O(1)-per-wave blast radius and reversibility right up to a strict gate. The avoided cost is a big-bang cutover with no rollback discovering a finance-close error after the source is gone: unrecoverable, and the reason migrations get post-mortems.

ETL
Topic — etl
ETL problems on cutover and pipeline orchestration

Practice →

Design
Topic — design
Design problems on zero-downtime cutover and rollback

Practice →

---

Cheat sheet — Snowflake migration recipes

• The five phases (memorise). Assess → translate → move data → dual-run validate → cutover. The hard phase is validation, not standing up Snowflake. Keep the source authoritative from phase 1 to the decommission gate in phase 5. Never call a migration "done" at data-load; call it done at "N clean reconcile cycles + consumers switched + source retired behind a gate."
• Assessment inventory query. Build one migration.inventory from the system catalog joined to the query logs: Teradata DBC.TablesV + DBC.TableSizeV + DBC.DBQLObjTbl/DBQLogTbl; Oracle DBA_OBJECTS + DBA_SEGMENTS + V$SQL/AWR. Score complexity by weighting dynamic SQL (5), loops/dialect features (3), exceptions (2) far above line count (1 per 50). Bucket low/medium/high; the ~7% high tier is your schedule risk. Derive wave order from a topological sort of the catalog dependency graph — shared dims first behind a bridge, finance close last.
• Dialect translation cheat map. Teradata: QUALIFY → native (but watch SET-table dedupe → make explicit QUALIFY/DISTINCT with tie-break), SET/MULTISET → no equivalent (add dedupe), RESET WHEN/TOP n → window/ LIMIT, BTEQ → COPY INTO + Scripting. Oracle: MERGE → native (move matched WHERE into WHEN MATCHED AND …), sequences → not gap-free (validate contiguity assumptions), CONNECT BY → WITH RECURSIVE, (+) → LEFT JOIN, '' = NULL → NULLIF(col,'') on load, PL/SQL → Snowflake Scripting (cursor FOR … DO, explicit exceptions). Prove every conversion with a same-input-same-output unit test — "compiles" is not "correct".
• COPY INTO bulk-load recipe. Unload to many ~100–250 MB compressed files (Parquet/zstd), land in a stage, CREATE FILE FORMAT, dry-run with VALIDATION_MODE = RETURN_ERRORS, load with ON_ERROR = ABORT_STATEMENT and PURGE = FALSE, then reconcile row count + one aggregate against source control values before the table enters dual-run. Right-sized files are the biggest throughput lever.
• Tiered reconciliation template. Tier 1 COUNT(*) both sides (gross failures). Tier 2 SUM/MIN/MAX/COUNT(DISTINCT) + text hash (value corruption). Tier 3 per-row MD5(CONCAT_WS('|', normalised cols)), diff hash sets both directions with MINUS, drill down column-by-column (per-row differences). Run cheap tiers every cycle on all tables; rotate the expensive tier so every table is fully hashed at least weekly, plus on any tier-2 breach. Normalise inputs (COALESCE, TRIM, canonical timestamps) or you'll chase formatting ghosts.
• Dual-run tolerance + gate. Compare only settled rows (written before the source batch boundary) so in-flight rows aren't false diffs. Write a boolean-per-tier row into migration.reconcile_ledger every cycle. A table is cutover_eligible after N (e.g. 5) consecutive clean count+aggregate+hash cycles — the go/no-go is a query, not a meeting.
• Cutover + rollback decision matrix. Unit = wave (never estate). Strategy = phased/blue-green/read-then-write; default phased, read-before-write. Switch = fast-to-flip indirection (BI alias, conn-string, orchestrator flag). Rollback = repoint to the still-authoritative, still-loading source; triggers = failed reconcile cycle, consumer discrepancy, SLA miss. Rollback is cheap+blameless before the gate, a DR event after. Test the rollback path — an untested rollback is not a rollback.
• Dual-write overlap. For gap-free rollback, write every batch to both source and Snowflake during the overlap and reconcile the two writes per batch (quarantine divergences immediately). Rollback becomes an ALTER VIEW. End dual-write at the decommission gate. The risk it adds — two truths — is only contained by per-batch reconciliation.
• Decommission gate checklist. Strict conjunction: ≥N clean reconcile cycles post-cutover, 100% consumers switched + signed off, an incident-free soak long enough to cover the business cycle (≥2 weeks / a monthly close), and a restore-tested final backup. Then freeze (revoke), archive, grace period, drop. The gate is the one-way door — after it, rollback and your reconciliation baseline are both gone.
• Wave ordering rule. Dependency-first, risk-last. Shared conformed dimensions → Wave 0 behind a compatibility bridge. Low-risk, low-complexity domain → Wave 1 pilot (proves the machinery). Correctness-critical + dialect-heavy (finance close) → last wave, biggest validation budget. Never pilot on the finance close.
• What interviewers score. Names five phases with validation at the centre; reads query logs (not just schema) in assessment; automates translation then proves equivalence with tests; treats tiered reconciliation (count→aggregate→hash) as the cutover gate; refuses big-bang and keeps the source rollback-ready to a strict decommission gate. Every one of these is a senior signal.

Frequently asked questions

What are the phases of a Snowflake migration?

A Snowflake migration off Teradata or Oracle is a five-phase program: (1) assess — inventory every object from the system catalog and the query logs, score complexity, and plan dependency-ordered waves; (2) translate — convert DDL/DML/procedural SQL into Snowflake SQL and Snowflake Scripting, automating the 70–90% a converter handles and hand-finishing the dialect residue; (3) move data — bulk-load history via COPY INTO from a stage and run an incremental catch-up; (4) dual-run validate — keep both systems live and reconcile them in tiers (row count → aggregates → row-hash) every cycle; (5) cut over — switch consumers wave by wave, keeping the source authoritative and rollback-ready until a decommission gate. The load is the easy half; the validation is the half that decides whether the migration is trusted. The single most common failure is treating "the data is loaded" as "the migration is done" — it isn't until a tiered reconciliation has proven equality cycle after cycle.

Teradata vs Oracle migration — what differs when moving to Snowflake?

Both are relational warehouses moving to Snowflake, so the program (assess → translate → move → dual-run → cutover) is identical; the dialect residue differs. A Teradata migration's hard translation cases are SET/MULTISET table semantics (Snowflake has no duplicate-suppressing tables, so you add explicit dedupe), QUALIFY interactions with RESET WHEN, TOP n WITH TIES, and re-platforming BTEQ scripts to COPY INTO plus Snowflake Scripting. An Oracle migration's hard cases are PL/SQL packages (cursors, exceptions, autonomous transactions → Snowflake Scripting), MERGE branch semantics, sequences (not gap-free), CONNECT BY hierarchies (→ recursive CTEs), (+) outer joins, and the empty-string-equals-NULL rule that silently changes IS NULL results (fix with NULLIF(col,'') on load). Snowflake natively supports QUALIFY, MERGE, and DECODE, which helps both. The assessment and reconciliation phases are dialect-agnostic; only the translation phase branches on source engine.

How much SQL code translation can be automated?

Typically 70–90% of objects by count convert cleanly through an automated converter (SnowConvert-style tools) — most DDL, straightforward DML, and much procedural code. The remaining 10–30% is the dialect and dynamic-SQL residue that needs a human: Teradata SET-table dedupe and edge-case QUALIFY, Oracle MERGE branches, sequences, CONNECT BY, and PL/SQL with cursors, exceptions, and autonomous transactions. But the automation percentage is a trap if you stop there: a conversion that compiles in Snowflake is not a conversion that returns the same rows. Precision/rounding on NUMBER, NULL ordering, empty-string-vs-NULL, and date arithmetic all differ silently. The senior discipline is automate-then-verify: run the converter, then put every object — even the auto-clean ones — through a SQL code translation unit test that asserts identical output on the same input. The test, not the converter's "clean" report, is the merge gate.

What is dual-run validation and reconciliation?

dual-run validation is running the legacy warehouse and Snowflake live in parallel on the same workloads and proving they return the same numbers before anyone cuts over. The proof is a tiered reconciliation: tier 1 compares row counts (catches gross load failures), tier 2 compares aggregate checksums — SUM/MIN/MAX/COUNT(DISTINCT) per column (catches value corruption a count misses), and tier 3 compares a per-row hash of every row, diffing the hash sets both directions to find exactly which rows differ (catches any per-row difference). You run the cheap tiers every cycle on every table and rotate the expensive row-hash so each table is fully hashed at least weekly, plus on any tier-2 breach. Every cycle's result is recorded in a ledger, and a table becomes cutover-eligible only after N consecutive fully-clean cycles. This turns data validation from "we spot-checked a dashboard" into an auditable, evidence-based gate — the difference between a migration you can sign off and one you're hoping is right.

How do you cut over without a big-bang risk?

You never flip the whole estate at once. cutover happens wave by wave — each dependency-ordered wave from the assessment cuts over independently once its tables pass the reconcile gate, so the blast radius is one wave's consumers, not all 300 dashboards. Within a wave, switch reads before writes: repoint low-risk report/BI consumers first (trivial rollback), soak for N clean cycles, then move write/load ownership. Throughout, the source stays authoritative and keeps loading, and the switch is a fast indirection (BI alias, connection string, orchestrator flag) that flips back in minutes — that reversibility is the rollback. Rollback triggers are explicit: a failed post-cutover reconcile cycle, a consumer-reported discrepancy, or an SLA miss. Some teams also dual-write (every batch to both systems) during the overlap so rollback is gap-free. Rollback stays cheap and blameless right up until the decommission gate; big-bang cutover is rejected precisely because it has no rollback.

When is it safe to decommission the legacy source?

Only behind a strict decommission gate — the one-way door of the migration. A wave qualifies to retire its source objects only when all of these are true: (1) N consecutive clean reconcile cycles after cutover (proving Snowflake is correct while serving production traffic, not just in shadow mode); (2) 100% of consumers switched and explicitly signed off; (3) an incident-free soak period long enough to have exercised the wave's real business cycle (≥2 weeks, ideally covering a monthly close); and (4) a final backup that has been restore-tested, because an unverified backup is not a backup. Only then do you freeze the source (revoke access), archive, wait out a grace period, and drop. The reason the gate is strict is that after it, both your rollback and your reconciliation baseline disappear with the source — so decommissioning on a calendar date instead of on evidence is how a migration turns a recoverable mismatch into an unrecoverable incident.

Practice on PipeCode

• Drill the SQL practice library → for the dialect-translation, window-function, MERGE, and reconciliation-query problems a Snowflake migration interview loves.
• Rehearse on the data transformation practice library → for the Teradata/Oracle → Snowflake SQL rewrites and the empty-string / precision / NULL-ordering traps.
• Sharpen the validation axis on the data validation practice library → for tiered reconciliation, row-hash checksums, and dual-run drill-down scenarios.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the five-phase migration map against real graded inputs — assessment, translation, dual-run, and cutover.

Lock in migration muscle memory

Docs explain Snowflake features. PipeCode drills explain the decision — when to read the query logs and not just the schema, when a translation compiles but returns the wrong rows, when a row-count reconcile is hiding a value-corruption bug, and when a wave has earned its cutover gate. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice data validation problems →