PowderInsights | Teradata to Snowflake Migration: A Phase-by-Phase Playbook with SnowConvert

Teradata is still the system of record for a surprising number of large enterprises, and most of those teams now have a board-level mandate to get off it. The migration is very doable, but it fails in predictable ways: teams treat it as a SQL translation exercise, discover six months in that nobody agreed what "done" means, and end up running both platforms for two extra years of double licence cost.

This tutorial is the playbook we use on Teradata-to-Snowflake programmes: how to inventory the estate, what SnowConvert does and does not do, how the Teradata dialect maps to Snowflake SQL, how to reproduce workload management without TASM, and how to prove equivalence before you switch consumers over.

Phase 0: inventory before you translate a single line

Almost every failed migration skipped this. Pull the facts out of Teradata's dictionary views first:

-- Teradata: what actually gets used?
SELECT DatabaseName, TableName, SUM(CurrentPerm)/1e9 AS gb
FROM DBC.TableSizeV
GROUP BY 1,2
ORDER BY gb DESC;

-- Query workload by object, last 90 days
SELECT ObjectDatabaseName, ObjectTableName, COUNT(*) AS refs
FROM DBC.QryLogObjectsV
WHERE CollectTimeStamp > CURRENT_DATE - 90
GROUP BY 1,2
ORDER BY refs DESC;

-- Heaviest queries, for the "must be no slower" list
SELECT QueryID, UserName, TotalIOCount, AMPCPUTime, StartTime
FROM DBC.QryLogV
WHERE StartTime > CURRENT_DATE - 30
ORDER BY AMPCPUTime DESC;

Two numbers decide your scope. The first: what fraction of tables saw zero references in 90 days — typically 40–60% of a mature Teradata estate. Those are archive candidates, not migration candidates. The second: your top 200 queries by CPU, which will become the acceptance test suite.

Deliverables from phase 0: a table inventory tagged migrate / archive / retire, a ranked list of consuming applications and BI reports, and a written definition of done per workload.

Phase 1: schema and code conversion with SnowConvert

Snowflake's SnowConvert is a free code conversion tool with a Teradata source. Point it at extracted DDL, BTEQ scripts, stored procedures, macros, and FastLoad/MultiLoad scripts; it emits Snowflake SQL plus an assessment report.

What it handles well:

DDL: data types, PRIMARY INDEX removal, SET/MULTISET semantics flagged, partitioning translated or dropped
BTEQ scripts, into SQL or Python driver code
Most stored procedure and macro bodies, into Snowflake Scripting
An object inventory and effort estimate, which is what you take to the steering committee

What it will not do for you:

Redesign anything. A table that was skewed on Teradata stops being a skew problem on Snowflake, but a model that was wrong stays wrong.
Resolve UPDATE ... FROM and MERGE semantics differences that need a human decision
Translate TASM workload rules, BTEQ error-handling conventions, or third-party ETL tool internals
Guarantee identical results for anything touching collation, implicit casts, or NULL ordering

Treat SnowConvert output as a first draft with a built-in effort report. Typically 70–90% of objects convert untouched, and the remaining tail is where the whole schedule lives.

Phase 2: dialect differences that actually bite

Data types. NUMBER maps cleanly, but Teradata's default DECIMAL(5,0) for untyped numerics can silently encode assumptions you do not want to inherit. Teradata CHAR is blank-padded and comparisons ignore trailing spaces; Snowflake does not pad. Wrap comparisons in RTRIM() or clean the data during load rather than discovering it in a reconciliation report.

Indexes and distribution. Drop PRIMARY INDEX, UNIQUE PRIMARY INDEX, SECONDARY INDEX, JOIN INDEX, and PARTITION BY clauses. Snowflake has no distribution key. Add a clustering key only where a large table is consistently filtered on a low-cardinality column, and only after you have query history to justify it:

-- Not a translation of PRIMARY INDEX. A deliberate, measured choice.
ALTER TABLE fact_sales CLUSTER BY (sale_date);

Identity and surrogate keys. Teradata GENERATED BY DEFAULT AS IDENTITY becomes a Snowflake IDENTITY or sequence, but Snowflake sequence values are not gap-free. If a downstream system assumes contiguous keys, fix the assumption now.

QUALIFY is the good news: it exists in Snowflake with the same semantics, so window-filter logic ports directly.

-- Works on both platforms
SELECT customer_id, order_date, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

Date and time functions. ADD_MONTHS and CURRENT_DATE carry over. Teradata INTERVAL arithmetic, TD_SYSFNLIB functions, CAST(x AS DATE FORMAT 'YYYYMMDD'), and SEL ... SAMPLE need rewriting to DATEADD, TO_DATE(x,'YYYYMMDD'), and SAMPLE/TABLESAMPLE.

UPDATE ... FROM and updatable views. Teradata's UPDATE t FROM ... idiom becomes a MERGE or an UPDATE ... FROM with explicit join predicates. Note that Snowflake raises an error on non-deterministic multi-match MERGE unless ERROR_ON_NONDETERMINISTIC_MERGE = FALSE; prefer fixing the duplicate rows over setting the flag.

Stored procedures and BTEQ. Snowflake Scripting covers DECLARE, cursors, and exception handlers, but long procedural BTEQ chains are usually better rewritten as set-based SQL orchestrated by Tasks or your existing scheduler. Migration is the cheapest moment you will ever get to delete procedural code.

Phase 3: data movement

Do not stream 40 TB through a JDBC connector. Export to cloud storage and bulk load:

CREATE OR REPLACE FILE FORMAT ff_pipe
  TYPE = CSV FIELD_DELIMITER = '|' NULL_IF = ('', 'NULL', '?')
  EMPTY_FIELD_AS_NULL = TRUE COMPRESSION = GZIP;

CREATE OR REPLACE STAGE stg_td
  URL = 's3://acme-td-migration/fact_sales/'
  STORAGE_INTEGRATION = s3_int
  FILE_FORMAT = ff_pipe;

COPY INTO fact_sales FROM @stg_td
  ON_ERROR = 'ABORT_STATEMENT';

SELECT * FROM TABLE(VALIDATE(fact_sales, JOB_ID => '_last'));

On the Teradata side, TPT (Teradata Parallel Transporter) export is the workhorse. Aim for 100–250 MB compressed files, one prefix per table, and a manifest table recording file and row counts per extract so reconciliation is mechanical. For the cutover window, plan a final incremental extract keyed on a reliable change column or a journal-based delta.

Phase 4: workload management without TASM

TASM throttles, workload classes, and priority tiers do not translate — and they do not need to. On Snowflake you separate workloads physically instead:

CREATE WAREHOUSE wh_etl   WAREHOUSE_SIZE = LARGE  AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

CREATE WAREHOUSE wh_bi    WAREHOUSE_SIZE = MEDIUM AUTO_SUSPEND = 60
  MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 4 SCALING_POLICY = 'STANDARD';

CREATE WAREHOUSE wh_adhoc WAREHOUSE_SIZE = SMALL  AUTO_SUSPEND = 60
  STATEMENT_TIMEOUT_IN_SECONDS = 1800;

-- Guardrails replace throttling
CREATE RESOURCE MONITOR rm_adhoc WITH CREDIT_QUOTA = 200
  TRIGGERS ON 90 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

ALTER WAREHOUSE wh_adhoc SET RESOURCE_MONITOR = rm_adhoc;

The mental shift to socialise early: on Teradata, concurrency was a scarce resource you rationed. On Snowflake, concurrency is cheap and credits are the scarce resource. Ad-hoc users no longer queue behind the nightly batch — but somebody has to own the budget.

Phase 5: proving equivalence

Reconciliation is where trust is won. Three levels, in order:

-- 1. Row counts and column fingerprints per table
SELECT COUNT(*) AS rows,
       SUM(HASH(order_id, customer_id, order_date, amount)) AS fingerprint
FROM fact_sales;

-- 2. Aggregate agreement on business measures
SELECT DATE_TRUNC('month', sale_date) AS mth, SUM(amount)
FROM fact_sales GROUP BY 1 ORDER BY 1;

-- 3. Query level: run the top-200 list on both platforms and diff results

Run level 1 on every migrated table, level 2 on every fact table against a Teradata-produced control file, and level 3 as the formal acceptance gate. Store results in a table so the programme can report a percent-complete number that means something.

For performance acceptance, do not promise "faster" query by query. Promise workload SLAs — batch finishes by 05:00, p95 dashboard query under three seconds — then tune with the query profile, clustering, and warehouse sizing rather than by translating old index strategies.

Phase 6: parallel run and decommission

Run both platforms for one to two full reporting cycles, not longer; dual running is where migration budgets die
Cut consumers over in dependency order: ingestion first, semantic layer and BI last
Freeze Teradata to read-only on cutover day so nobody writes to the dead system
Book the decommission date in the plan before you start, and hold a written sign-off per workload against the phase 0 definition of done

Common failure modes

Lift-and-shift of a 15-year-old model, including the 300 unused tables. Archive first.
No owner for cost. Add resource monitors, budgets, and warehouse tagging in week one, not after the first surprise invoice.
Procedural BTEQ ported literally, producing thousands of lines of Snowflake Scripting nobody wants to maintain.
Reconciliation invented at the end. Build the harness in phase 1 and run it continuously.
Blank padding and collation differences found by a business user rather than by a test.

A typical enterprise estate of a few hundred actively used tables and a few thousand code objects runs six to twelve months with a focused team — and materially longer if phase 0 is skipped.

PowderInsights runs Teradata and legacy EDW migrations onto Snowflake end to end: assessment, SnowConvert-assisted conversion, reconciliation harnesses, and workload cutover. Get in touch with your estate size and target date and we will sketch a realistic plan.