Teradata to Snowflake: A Source-Connected Migration Blueprint
Teradata to Snowflake sits at the top of the modernization queue for a specific reason. License renewals are landing in a range most CFOs did not budget for, senior BTEQ specialists are moving on, and the workloads Teradata used to be uniquely good at now have credible homes on Snowflake. The decision itself is often the easy part. What separates a migration that ships from one that stalls is whether the plan is grounded in the estate that actually exists.
The pattern that works for a Teradata to Snowflake move looks the same across banking, insurance, and healthcare payers. It starts with reading the source directly rather than reconstructing it from interviews.
1. Source-connected discovery for Teradata
Start by connecting to Teradata under read-only access and extracting metadata directly. This means the DBC catalog for tables and views, the collection of BTEQ scripts stored in the estate's job scheduler, and the PE and AMP topology that determines how tables are distributed. You cannot plan a Snowflake target without knowing how the source is physically laid out today, because primary index choices and distribution patterns carry into cluster key decisions.
The BTEQ inventory is usually where discovery uncovers scope surprises. Teradata estates typically carry a large volume of BTEQ scripts written over 15 to 20 years, and much of it lives outside the database in shell wrappers and scheduler entries. A source-connected read reaches into both.
2. Complexity scoring for stored procedures and BTEQ scripts
A flat object count is misleading. Score each object on a consistent one to five rubric based on dependency depth, dialect-specific constructs, and business logic density.
For Teradata stored procedures, the drivers of complexity are cursor logic, dynamic SQL, error handler blocks, transaction control, and volatile table usage. Procedures that hold state across nested calls are typically the four and five scores.
For BTEQ scripts, complexity lives in control flow (.IF ERRORCODE, .GOTO, .LABEL), dynamic script assembly, and export or import blocks that interact with the operating system. A BTEQ script that does nothing more than run a SELECT is a one. A script that assembles a query from parameters, handles error branching, and coordinates with a downstream job is a four or five.
The complexity distribution across the estate is what drives the effort estimate. Applying a flat average across 12,000 stored procedures and 600 BTEQ scripts is how programs miss object counts by 40 to 60 percent.
3. Snowflake target architecture
The Snowflake side of the plan is not just about picking an edition. It is about deciding the multi-database topology, the warehouse sizing per workload, and the clustering strategy per large table.
Multi-database topology. Separating raw, integration, and consumption layers into distinct databases keeps access control simple and lets you promote through environments cleanly. In regulated estates, add a governance database for masking policies and row-access policies.
Warehouse sizing. Different consumer patterns get different warehouses. BI workloads on a size that matches concurrency. ETL and pipeline workloads on a warehouse that can be scaled independently. Ad-hoc analyst workloads separated so they do not compete with production loads.
Clustering. Large tables that are queried by date or region typically benefit from a cluster key. Small tables should not have one. The decision is per table, and the discovery phase is where you gather the information to make it.
4. BTEQ conversion patterns
BTEQ does not have a single clean equivalent in Snowflake. The conversion is a pattern-matching exercise across several categories.
Control flow. .IF ERRORCODE and .GOTO patterns convert to Snowflake Scripting with IF blocks and structured exception handling. The mechanical translation is straightforward. The semantic decision is whether to preserve the original control flow or refactor into a cleaner procedure.
Error handling. BTEQ writes error codes to session state. Snowflake Scripting uses EXCEPTION blocks. Preserve the intent, not the syntax.
Dynamic execution. BTEQ scripts that assemble SQL as strings and execute it map to EXECUTE IMMEDIATE in Snowflake Scripting. This is often where legacy scripts hide business rules that were never documented.
Export and import blocks. BTEQ .EXPORT and .IMPORT typically go away in the Snowflake world, replaced by COPY commands against stages. Rethink the pattern rather than translate it directly.
At scale, this is where accelerator-driven bulk conversion pays for itself. A specialized code conversion engine processes 1,000 plus objects per run against codified BTEQ-to-Snowflake dialect rules, with senior architect review on the outputs. Hand conversion of 12,000 stored procedures and 600 BTEQ scripts is a multi-year effort. Accelerator-driven conversion with expert oversight compresses the same work to weeks.
5. Wave planning by dependency
The wave plan is where source-connected discovery earns its keep. Dependency mapping across tables, views, procedures, BTEQ scripts, and downstream reports lets you sequence the work so each wave respects the objects it depends on.
The pattern that works. Split by domain, then by complexity tier within domain. Wave one is usually the least dependency-heavy domain at complexity tiers one to three. Wave two adds tier four objects and starts the harder domains. The complex, dependency-heavy objects (tier five, cross-domain, business-critical) live in the final waves after the team has built confidence and pattern coverage.
Waves that ignore dependency look tidy on a slide and fail during cutover. The moment a report depends on a procedure that has not yet moved, the wave has stalled.
6. Validation approach
Validation is not a single step at the end. It runs alongside conversion.
Source-to-target reconciliation. Every converted procedure gets an automated reconciliation pass that compares source and target output on a defined sample set. Row counts, aggregate checksums, and result set diffs on representative queries. Semantic drift is caught at the object level, not at the report level.
Sample-set testing. Regulated estates cannot always ship production data into lower environments. Production-grade synthetic data lets you validate at volume without triggering HIPAA, PCI, or GDPR obligations on the test environment.
Business logic sign-off. Reconciliation catches most divergence. It does not catch business logic drift that no one owns. Assign an owner per domain for logic sign-off before that domain goes to production.
Where to start
The starting point for any Teradata to Snowflake move worth taking seriously is a source-connected assessment. An 8 business day Modernization Canvas produces the inventory, complexity scoring, target architecture, wave plan, and sample converted code that a real execution phase needs. It replaces the six to eighteen week discovery cycle that most SIs default to, and it produces a plan the CDO can defend at the next steering committee.
If the Teradata renewal decision is on your calendar this fiscal year, the discovery method is what determines whether the estimate you present is defensible. Read the estate. Score it. Design the target. Then execute.
Conclusion
A successful Teradata-to-Snowflake migration starts with understanding the estate before converting it. Source-connected discovery, complexity scoring, dependency mapping, target architecture, pattern-based conversion, and continuous validation provide a more defensible path from assessment through execution.
