When migrating analytical workloads from Teradata to Snowflake, one subtle but important performance factor often gets overlooked: how the two systems handle GROUP BY operations on huge tables.
The SQL looks the same, but the execution engines behave differently. If you’ve relied on Teradata for years, you may be surprised by Snowflake’s behavior.
GROUP BY in Database Systems
Most databases implement GROUP BY with either:
- Hash aggregation: rows are placed into an in-memory hash table keyed by the grouping columns.
- Sort aggregation: rows are sorted, then consecutive keys are aggregated.
We assume both Teradata and Snowflake use hash aggregation as their main strategy, since this is standard in large-scale systems, though Snowflake does not disclose full internals.
Teradata’s Approach
- Low cardinality: AMPs can aggregate locally, sending only a small set of partial results across the BYNET.
- High cardinality: partial aggregation may reduce little data, so redistribution and memory/spool costs can dominate; inspect the chosen plan.
- Primary Index advantage: if grouping is on the PI, no redistribution is needed — aggregation is local, which is the fastest case.
- Skew sensitivity: uneven group distribution can overload a single AMP, leading to slow queries or spool failures.
Snowflake’s Approach
A Snowflake plan may use these stages; inspect the actual profile rather than assuming every aggregation requires all of them:
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- Local partial aggregation per node.
- Redistribution by group key.
- Final aggregation.
Differences to note:
- No PI equivalent: redistribution depends on the plan and data distribution.
- Memory and spilling: intermediate results spill to local SSD if they don’t fit in memory; SSD is finite, so extreme cases can fail.
- Scalability: warehouses can be resized to add memory and SSD capacity ; Teradata scaling options also depend on deployment, including independent compute options.
- Skew handling: uneven groups still slow queries, but available adaptive behavior depends on supported features; this article does not establish a 2024 introduction date or a universal skew remedy.
Disk Spilling
Neither system has infinite memory:
- Teradata spills to spool; if quotas are exceeded, queries fail.
- Snowflake spills to SSD; usually queries just run slower, though disk exhaustion can cause further spilling to object storage.
Migration Implications
- Low-cardinality GROUP BY or PI-based queries that ran extremely fast in Teradata may lose their edge in Snowflake, since redistribution is part of the plan.
- Neither platform is universally more predictable for skewed high-cardinality aggregation. Establish comparative behavior with representative data, concurrency and resource limits; no controlled comparative benchmark is supplied here.
- Scaling in Snowflake gives you a way out of memory pressure, at the cost of higher credits; Teradata flexibility depends on the deployment and supported scaling options.
Final Thought
Cardinality, skew, and memory limits strongly influence aggregation performance.
Teradata and Snowflake can choose staged aggregation according to cardinality, placement and resources. Redistribution and partial aggregation are plan-dependent; neither platform’s performance is guaranteed by this high-level description.
For migration teams, the lesson is simple: test your GROUP BY queries. What ran best in Teradata may behave differently in Snowflake, especially at the extremes of very low or very high cardinality.
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