Most organizations approach a Teradata-to-Snowflake migration as a translation exercise: convert the SQL, move the data, and validate the results. The technical migration succeeds. Then the first quarterly bill arrives at double the projected budget, dashboards queue for 20 minutes during batch windows, and data-loading pipelines that ran in 45 minutes on Teradata now take 5 hours.
The problem is not Snowflake. The problem is that Teradata professionals carry assumptions into Snowflake that are not just incomplete, but actively counterproductive. Decades of Teradata expertise have created instincts for data distribution, workload management, query optimization, and operational patterns that simply do not apply in Snowflake’s architecture. Worse, following those instincts often produces the opposite of the intended result.
Here are five things that reliably break when experienced Teradata teams migrate to Snowflake, and what to do about each one.
1. Your Cost Model
From predictable licenses to consumption billing, where every query is a spending decision.
In Teradata’s traditional on-premises model, capacity is purchased upfront through fixed-term licenses. Once the system is installed, the marginal cost of running an additional query is zero. No meter ticks, no credits burn, no invoice line grows. Budget planning is predictable because the annual cost is the same regardless of utilization, whether it’s 20% or 95%.
Snowflake inverts this entirely. Every second of compute is billed. A warehouse that sits idle but running burns credits at its full rate. Serverless features consume credits in the background without requiring any user action. Storage costs multiply quietly through Time Travel and Fail-safe retention. Cross-region data transfer fees appear on the bill with no visible equivalent in Teradata.
The most common migration failure is projecting Snowflake costs the way you would estimate a Teradata license: as a single line item. In reality, Snowflake bills across multiple cost dimensions, each with its own pricing model and its own hidden multipliers: virtual warehouse compute, serverless compute, cloud services, storage, and data transfer.
What to do: Build your cost model across all five billing dimensions before writing a single line of migration code. Set auto-suspend to 60 seconds (not the default 5 or 10 minutes) as your starting point. Monitor costs weekly, not annually. In Teradata, the cost of inefficiency is measured in performance. In Snowflake, it is measured in money.
2. Your Query Optimization Instincts
No Primary Index, no secondary indexes, no statistics collection. Everything you relied on is gone.
In Teradata, query performance revolves around data distribution. The Primary Index determines where each row physically resides by hashing column values to specific AMPs. Single-AMP access when PI columns appear in equality conditions. All-AMP scans when they do not. Data skew when PI values are unevenly distributed. Expensive redistribution when joining tables with different PIs. Every table design decision flows from this deterministic distribution.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookSnowflake has no Primary Index and no equivalent concept. Data is stored in arrival order as immutable micro-partitions, with comprehensive metadata (min/max values, null counts, distinct value counts) automatically recorded for each column. The optimizer uses this metadata for partition pruning, skipping partitions that cannot contain relevant data. There is no hash distribution to optimize, no permanent assignment of rows to specific processors.
Secondary indexes (USI, NUSI), join indexes, and COLLECT STATISTICS routines also have no direct Snowflake equivalents. Snowflake’s Search Optimization Service offers similar point-lookup acceleration for specific columns, but it is an supported account configuration feature with serverless costs. Micro-partition pruning and automatic metadata replace the need for traditional statistics collection entirely.
The instinct to restructure joins to avoid cross-AMP redistributions, replace MERGE statements with explicit DELETE-then-INSERT logic, or add clustering keys on every join column (mirroring secondary indexes) is counterproductive. These optimizations are based on Teradata’s hash-distributed, AMP-local processing model. In Snowflake, the fastest query version is often the simplest.
What to do: Start with the simplest possible query. Do not restructure joins to avoid redistributions, because Snowflake handles data movement differently. Do not recreate every secondary index as a clustering key. Remove all COLLECT STATISTICS routines from your migration plan. Use the Query Profile (Snowflake’s visual execution plan) to diagnose actual bottlenecks before applying any optimization.
3. Your Workload Management Strategy
TASM’s priority scheduling disappears. Workload isolation replaces contention management.
In Teradata, all workloads share resources among the same AMPs. The Teradata Active System Management (TASM) and its lighter counterpart TIWM classify queries, assign priorities, and manage resource allocation within this shared system. The Priority Scheduler implements a six-tier hierarchy: from the critical TDAT system tier down through virtual partitions, tactical workload, Service Level Goal tiers, and finally the timeshare tier. Resources flow downward: higher-priority workloads consume what they need, and whatever remains cascades to lower tiers.
Separate Snowflake warehouses isolate much compute contention, but shared storage, metadata, locks and service quotas remain possible bottlenecks. A runaway load can still affect shared dependencies. Teradata Lake also offers independent compute options.
Running all workloads on a single warehouse, as Teradata does, recreates the contention problems that TASM was designed to manage, but without TASM’s sophisticated priority scheduling. ETL batch jobs, dashboards, ad-hoc queries, and data science notebooks all compete for the same compute, leading to queuing, oversizing, and cost escalation that isolation would have prevented.
What to do: Stop thinking about priority and start thinking about isolation. Create separate warehouses for separate workload types: batch processing on a Large warehouse that runs only during ETL windows, dashboards on an X-Small with auto-suspend balanced against cold-start latency, analytics on a Medium that scales as needed. Each warehouse suspends independently when idle, so you pay only for the compute you use. Isolation is cheaper than contention management when you are paying per second.
4. Your Tactical Workload
Teradata tactical workload management can support low-latency queries but does not guarantee subsecond response under every load, lock state or failure. Snowflake standard and hybrid tables have different access paths; benchmark the chosen table and compute configuration.
Teradata tactical rules can prioritize work and reserve AWT capacity. They do not guarantee immediate preemption of every request or freedom from locks and other bottlenecks. Limits and exception behavior depend on configuration and release.
Limited-AMP access and suitable join plans can help tactical workloads. Eligibility and priority derive from configured workload rules and release capabilities, not an unconditional list of only two join algorithms.
Warehouse concurrency settings affect queuing and throughput; inspect the supported parameter ranges for the account and warehouse type. Dedicated compute does not by itself guarantee latency or priority for every request.
The Snowflake approach to tactical-style workloads is architectural rather than scheduling-based. You create a dedicated small warehouse exclusively for low-latency queries. Because this warehouse serves only tactical queries, there is no contention from batch jobs or analytical workloads. The warehouse can be X-Small (minimizing cost per query) and configured with auto-suspend balanced against cold-start latency. If concurrency demand increases, multi-cluster scaling (supported account configuration) can automatically add clusters.
This approach works differently from Teradata’s tactical tier. Teradata guarantees priority within a shared system. Snowflake guarantees isolation through a dedicated system. The result is similar (low-latency responses for critical queries), but the mechanism and its limitations differ. In Teradata, a well-designed tactical query leverages single-AMP access and ROWHASH-level locking for maximum concurrency. In Snowflake, the same query relies on micro-partition pruning and the absence of competing workloads on its dedicated warehouse.
5. Your Data Loading Pipelines
FastLoad, MultiLoad, TPump, and BTEQ collapse into COPY INTO and staged files.
Teradata offers a rich ecosystem of specialized loading utilities: FastLoad for bulk loading empty tables, MultiLoad for updates and merges on populated tables, TPump for continuous micro-batch ingestion, and BTEQ for interactive and transactional loads. The Teradata Parallel Transporter (TPT) unifies these under a common scripting framework. Each utility has distinct requirements, trade-offs, and operational patterns that Teradata professionals have internalized over years of production experience.
Snowflake replaces this entire ecosystem with COPY INTO commands operating on staged files, supplemented by Snowpipe for automated continuous loading and Snowpipe Streaming for low-latency ingestion. This simplification is elegant, but it creates a trap: Teradata professionals often apply familiar loading patterns to Snowflake, resulting in inefficient pipelines, wasted compute, and unnecessary costs.
Load parallelism depends on format, compression and loader options. Many moderate files can help, but within-file parallel scanning exists for eligible inputs; one file is not universally one thread. Benchmark the chosen format and file size.
Batch loading often reduces per-statement overhead, but no controlled evidence supports a universal 50–100x cost ratio here. BTEQ can batch requests too. Compare identical data, transaction boundaries, format, concurrency and billable runtime.
What to do: Split large exports into 100 to 250 megabyte compressed files (Parquet format loads significantly faster than CSV). Stage data in the same cloud region as your Snowflake account to avoid egress charges. Convert all BTEQ row-by-row INSERT patterns to batch COPY INTO before migration. For continuous loading needs (replacing TPump), evaluate Snowpipe (serverless, event-driven) or Snowpipe Streaming (low-latency, API-based) based on your latency and cost requirements.
The Common Thread
Each of these five breakdowns has the same root cause: applying Teradata’s architectural assumptions to a platform that works on fundamentally different principles. Teradata’s shared-nothing, fixed-capacity architecture rewards a specific set of instincts: optimize data distribution, manage contention through priority scheduling, collect statistics, and design for single-AMP access. Snowflake’s shared-data, consumption-based architecture rewards different instincts: optimize for cost efficiency, isolate workloads, let the platform handle data organization, and continuously monitor spending.
Your Teradata expertise is valuable. You understand data warehousing, query optimization, workload management, and data modeling at a deep level. The challenge is not unlearning what you know, but channelling that expertise into Snowflake’s different mechanisms for achieving the same goals. The principles are often similar. The implementations are different.
The traps are avoidable. With awareness and the right practices, you can build a Snowflake environment that delivers the performance your organization needs at costs that make sense. The key is recognizing where your instincts will serve you well and where they will lead you astray.
Roland Wenzlofsky has over 20 years of experience with Teradata, working with major finance and telecommunications companies across Europe. He is the author of ‘Teradata SQL Tuning’ and the upcoming ‘Teradata to Snowflake: Avoiding the Traps’ (with Tibor Kovacs).
Planning a Teradata-to-Snowflake migration?
Review the assessment, conversion, reconciliation and cutover work needed for a controlled transition.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book