Teradata to Snowflake Migration: What Converts Cleanly and What Needs Rework

Last updated: October 2026

Until recently, the first decision in a Teradata migration was which conversion tool to buy. That decision has mostly gone away. Snowflake made SnowConvert free in January 2025, renamed it SnowConvert AI in June 2025, and in June 2026 brought it into Snowflake AIM, a migration platform that also covers data movement and validation.

The tooling does not settle the behavioural differences between the two platforms. Teradata and Snowflake treat duplicate rows, physical design, string comparison and script error handling differently, and code that converts without a single warning can still return different results. This guide covers the tooling as it stands in October 2026, the differences that cause real defects, and a validation approach that finds them before cutover.

What Snowflake's migration tooling covers in 2026

Snowflake AIM brings together SnowConvert AI, the Snowpark Migration Accelerator and technology from Datometry, which Snowflake agreed to acquire in November 2025. For a warehouse migration, Snowflake recommends the AIM agent, which runs inside Snowflake CoCo (the new name for Cortex Code), or the scai command-line tool. The SnowConvert Desktop application still exists, but Snowflake's documentation describes it as handling code conversion only, without the newer migration features.

FastLoad and TPump scripts are also accepted as input. Every conversion produces an assessment report and a list of issue codes: EWI codes mark code that needs a manual fix, and FDM codes mark converted code whose behaviour may differ from the original. On a large estate the FDM list deserves as much attention as the EWI list, because FDM code usually deploys and runs, and any difference shows up only in the results.

One point is worth checking before you plan around it. SnowConvert AI's release notes record AI-assisted conversion being enabled for Teradata in version 2.18.0 on 30 March 2026, but the AIM support matrix still lists AI conversion for Teradata as planned. We recommend confirming the current status in the support matrix for the version you install, and treating deterministic conversion as the baseline.

Datometry for Snowflake, in public preview since April 2026, takes a different approach. It virtualises Teradata SQL behaviour on Snowflake so that existing applications can run without their SQL being rewritten. For applications that are expensive to change, such as a vendor tool you cannot modify, it is worth evaluating, with the preview status taken into account. Snowflake also runs LiftOff, a migration programme that can partly fund migration activities and requires AIM for its workshops.

Start from what is actually used

Migrations finish sooner when the scope is based on what people actually use. In a telecom migration we delivered, the Teradata system had accumulated more than 300 BTEQ scripts. Only 47 of them had been run in the previous 90 days, and another 80 had not run in over a year. We asked each department head to sign off on the reports they relied on, migrated those, and left the rest behind. The details are in the telecom CDR case study.

Teradata's query logging (DBQL), where it is enabled, is the most reliable record of what runs and who runs it. Pair it with the SnowConvert assessment report, which counts objects and conversion issues, and you have a scope based on use, which is far easier to estimate than one based on inventory.

SET tables and primary indexes

A Teradata SET table does not allow duplicate rows. Snowflake has no equivalent, so SnowConvert AI drops the SET keyword and adds the note SSC-FDM-TD0024, "TABLE MIGHT HAVE DUPLICATE ROWS". Any load that relied on the table to discard duplicates will now keep them, and the first sign is usually a total that is slightly too high.

For each former SET table, decide whether duplicates are possible in the source feed. If they are, enforce uniqueness in the load. A MERGE on the business key is the simplest way to do that:

-- Teradata relied on a SET table to reject duplicates.
-- In Snowflake, de-duplicate the batch and merge on the key.
MERGE INTO sales.orders AS t
USING (
    SELECT order_id, line_no, customer_id, order_date, amount
    FROM staging.orders_load
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY order_id, line_no ORDER BY order_date DESC) = 1
) AS s
  ON t.order_id = s.order_id AND t.line_no = s.line_no
WHEN MATCHED THEN UPDATE SET
    customer_id = s.customer_id,
    order_date  = s.order_date,
    amount      = s.amount
WHEN NOT MATCHED THEN INSERT (order_id, line_no, customer_id, order_date, amount)
    VALUES (s.order_id, s.line_no, s.customer_id, s.order_date, s.amount);

-- After the first full load, confirm nothing slipped through.
SELECT order_id, line_no, COUNT(*) AS copies
FROM sales.orders
GROUP BY order_id, line_no
HAVING COUNT(*) > 1;

Primary indexes need a different kind of decision. In Teradata the primary index controls how rows are distributed, and a unique primary index also enforces uniqueness. Snowflake manages micro-partitions automatically, so SnowConvert AI turns a unique primary index into a UNIQUE constraint and comments out PARTITION BY clauses with the note SSC-FDM-0038. Snowflake does not enforce UNIQUE, PRIMARY KEY or FOREIGN KEY constraints on standard tables (NOT NULL is enforced, and hybrid tables do enforce them), so uniqueness that the database used to guarantee becomes something your load or your tests have to check.

It is tempting to turn every partitioned primary index into a clustering key. We recommend against it. Snowflake's own documentation says clustering keys are not intended or recommended for all tables, and automatic clustering has an ongoing cost. Load the data without clustering keys, look at pruning on the largest tables under real queries, and add CLUSTER BY only where queries consistently filter on the same columns and pruning is poor.

Case sensitivity converts cleanly and still breaks reports

In Teradata's default TERA session mode, character columns are NOT CASESPECIFIC, so 'Tango' = 'TANGO' is true. In Snowflake, comparisons are case-sensitive by default and trailing spaces are not trimmed. A filter, join or GROUP BY on a text column can therefore return fewer rows, or split one group into two, without any error.

SnowConvert AI handles this in one of two ways. By default it adds COLLATE to emulate NOT CASESPECIFIC; if you disable that setting, it translates comparisons to RTRIM(UPPER(expression)). Both preserve the results, and both have costs. Snowflake's documentation notes that collation can slow comparisons and reduce micro-partition pruning, and collation is not supported with UDFs.

-- Option 1: declare the behaviour on the column.
-- 'ci' ignores case, 'rtrim' ignores trailing spaces.
CREATE OR REPLACE TABLE ref.customer (
    customer_code VARCHAR(20) COLLATE 'en-ci-rtrim',
    customer_name VARCHAR(200)
);

-- Option 1b: a default for tables and columns created later.
ALTER SCHEMA ref SET DEFAULT_DDL_COLLATION = 'en-ci-rtrim';

-- Option 2: normalise once at load time, compare plainly.
INSERT INTO ref.customer (customer_code, customer_name)
SELECT UPPER(TRIM(customer_code)), customer_name
FROM staging.customer_load;

Our preference is to decide column by column. For codes and join keys, normalising the value once at load time keeps comparisons plain and pruning intact. For descriptive text that users search, collation on the column is usually the safer choice, because it does not depend on every query remembering to apply UPPER.

BTEQ, macros and procedures

BTEQ scripts convert to Python by default. If you choose Snowflake Scripting instead, the conversion follows a consistent pattern: the script body is wrapped in EXECUTE IMMEDIATE $$ ... $$, .IF ERRORCODE checks become checks on STATUS_OBJECT['SQLCODE'], .IF ACTIVITYCOUNT reads the row count through RESULT_SCAN(LAST_QUERY_ID()), and .GOTO and .LABEL become nested procedures. .EXPORT and .IMPORT become COPY INTO with PUT or GET through the Snowflake CLI.

The ERRORCODE conversion carries the note SSC-FDM-TD0013 because Snowflake's error codes are not Teradata's. Any script that branches on a specific Teradata error number, for example to skip a DROP when a table does not exist, needs to be read and tested by hand. We run each converted job once with a deliberately forced failure to confirm that the error path still does what the original did.

Snowflake now describes SnowSQL as a legacy client and recommends the Snowflake CLI, although SnowSQL 1.5.x remains supported until 16 April 2028. New scheduling and wrapper scripts should be written against the Snowflake CLI.

Macros become Snowflake Scripting procedures that return a table through a RESULTSET. A macro that returned several result sets writes them to temporary tables instead (SSC-FDM-0020). Callers change from EXEC to CALL, which matters for BI tools and applications that invoke macros directly.

A handful of function and statement differences account for most of the remaining issues:

• Functions with direct equivalents. ZEROIFNULL and NULLIFZERO exist in Snowflake. OREPLACE becomes REPLACE, OTRANSLATE becomes TRANSLATE, INDEX becomes CHARINDEX and TRYCAST becomes TRY_CAST.
• Functions with no equivalent. HASHROW, HASHAMP and HASHBUCKET describe Teradata's physical distribution and are flagged as unsupported (SSC-EWI-0031). Code that used them for sampling or bucketing has to be redesigned.
• RANK ordering. SnowConvert AI's function reference notes that Teradata's RANK defaults to descending order and Snowflake's to ascending. Check any ranking that does not state its order explicitly.
• UPDATE with a join. When an UPDATE ... FROM matches a target row more than once, Snowflake picks one source row without raising an error, because ERROR_ON_NONDETERMINISTIC_UPDATE defaults to FALSE. MERGE raises an error in the same situation by default. Setting the parameter to TRUE in test environments surfaces these cases early.
• Statistics. COLLECT, HELP and DROP STATISTICS are commented out, because Snowflake maintains its own metadata.

-- In test environments, make multi-match UPDATEs fail loudly
-- instead of silently choosing one source row.
ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE;

Data types mostly map without trouble. Teradata NUMBER without precision becomes NUMBER(38,18), CLOB becomes VARCHAR, BLOB becomes BINARY, JSON becomes VARIANT, and PERIOD becomes VARCHAR. INTERVAL becomes VARCHAR(20) unless you opt into Snowflake's interval data types, which were still in preview in October 2026. Snowflake now allows VARCHAR values up to 128 MB, although the default size is still 16 MB.

Replacing FastLoad, MultiLoad and TPT

Teradata Parallel Transporter (TPT) wraps the older utilities as operators: Load for FastLoad, Update for MultiLoad, Stream for TPump and Export for FastExport. The mapping below is ours, based on how each utility is typically used.

Two pricing changes since 2024 affect this choice. Since 8 December 2025, Snowpipe on Standard and Enterprise editions has been billed at 0.0037 credits per GB, with no per-file charge. Snowpipe Streaming has a new high-performance architecture, generally available since September 2025 on AWS and November 2025 on Azure, billed by uncompressed volume, with latency that Snowflake documents as low as 5 seconds. For bulk loads, Snowflake recommends files of roughly 100 to 250 MB compressed.

Workload management works differently as well. Teradata's TASM uses workload definitions, throttles and priorities within one system. In Snowflake the usual pattern is a separate warehouse per workload, multi-cluster warehouses (Enterprise Edition) for concurrency, and resource monitors for spend. Resource monitors cover warehouses only, so serverless features need budgets. Note also that STATEMENT_TIMEOUT_IN_SECONDS defaults to two days, so a runaway query can run for that long unless each warehouse sets a lower limit.

CREATE WAREHOUSE IF NOT EXISTS etl_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  STATEMENT_TIMEOUT_IN_SECONDS = 7200;

CREATE WAREHOUSE IF NOT EXISTS bi_wh
  WAREHOUSE_SIZE = 'SMALL'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3        -- multi-cluster needs Enterprise Edition
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  STATEMENT_TIMEOUT_IN_SECONDS = 900
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;

CREATE RESOURCE MONITOR IF NOT EXISTS etl_monthly
  WITH CREDIT_QUOTA = 500
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = etl_monthly;

Moving the history

Since SnowConvert AI 2.29.0 (28 May 2026), the scai CLI can move Teradata data as well as code. The feature is labelled Preview. It runs an orchestrator with workers, supports pause, resume and retry, and offers three extraction strategies:

• regular (the default) runs partitioned SELECT statements through the teradatasql driver or ODBC and writes Parquet to an internal stage.
• tpt generates a TPT EXPORT job and runs it with tbuild.
• write_nos has Teradata write Parquet directly to S3, Azure Blob Storage or Google Cloud Storage, which Snowflake reads through an external stage. Snowflake recommends it when its prerequisites are met.

Incremental runs are supported only with watermark or checksum synchronisation, so plan the final catch-up load before cutover rather than after. There is no Openflow connector for Teradata. If you prefer to run the extract yourself, the manual path is to export to files in cloud storage and load them with COPY INTO.

Validating before cutover

SnowConvert AI's data validation, also labelled Preview, compares Teradata with Snowflake at three levels. Level 1 checks the schema and row counts. Level 2 compares metrics per column, including minimum, maximum, sum, average, null count and distinct count. Level 3 compares rows using primary-key columns and an MD5 hash per partition. It is off by default, and on Teradata it needs the HASH_MD5 UDF installed on the source. Results land in SNOWCONVERT_AI.DATA_VALIDATION with a Streamlit dashboard on top.

scai data validate generate-config
scai data validate start
scai data validate status
scai data validate revalidate

Matching tables do not prove that the converted logic behaves the same way. We add a short list of tests aimed at the differences described above:

• A duplicate check on every table that was a SET table in Teradata, after each load.
• Mixed-case and trailing-space inputs for every converted filter and join on a text column.
• One forced failure for each converted BTEQ job, to confirm the error path.
• ERROR_ON_NONDETERMINISTIC_UPDATE set to TRUE in every test environment.
• A full comparison of report outputs for one closed period, such as the last month-end, run on both systems.

In the telecom migration mentioned earlier, every rebuilt report had to match Teradata on row counts, with metric totals within 0.01% to allow for floating-point differences, before any user group moved. Teradata was then decommissioned in three phases over four weeks, one department at a time. A phased cutover like that costs a few weeks of running both systems, and a problem found late then affects one department at a time.