Teradata SQL queries to Snowflake
Translate Teradata SQL—analytic/QUALIFY patterns, date/time semantics, and platform-specific idioms—into Snowflake SQL with validation gates that prevent semantic drift and credit spikes.
- Input
- Teradata SQL / query migration logic
- Output
- Snowflake equivalent (validated)
- Common pitfalls
- QUALIFY drift: analytic filters translate but change winners if ORDER BY isn’t complete.
- Implicit cast drift: Teradata coercion differs; Snowflake needs explicit casts for stable outputs.
- NULL semantics in joins: equality behavior differs; joins can drop or duplicate rows when NULLs exist.
Why this breaks
Teradata query estates often encode business logic in Teradata-specific semantics: QUALIFY-heavy analytic filters, casting behavior, and date/time functions. Many queries will translate syntactically to Snowflake, but drift and cost spikes occur when implicit casts, NULL behavior, and analytic ordering aren’t made explicit—and when large scans aren’t bounded.
Common symptoms after cutover:
- KPI drift from analytic logic when ordering/ties aren’t deterministic
- Type coercion and NULL semantics differences in CASE/COALESCE and join keys
- Date/time edge cases shift (DATE vs TIMESTAMP intent, timezone boundaries)
- Teradata-specific idioms map syntactically but change edge-case outputs
- Credit spikes because predicates and query shapes don’t prune well or are unbounded
SQL migration must preserve both meaning and a Snowflake-native execution posture.
How conversion works
- Inventory & prioritize the SQL corpus (BI extracts, views, ETL SQL, app SQL). Rank by business impact and risk patterns (QUALIFY/windows, casts, time, top-N).
- Normalize Teradata dialect: identifiers/quoting, CTE normalization, and common function idioms.
- Rewrite with rule-anchored mappings: Teradata → Snowflake function equivalents, explicit cast strategy, and deterministic ordering for window/QUALIFY patterns.
- Replace high-risk constructs: QUALIFY + analytics with explicit tie-breakers; validate edge windows.
- Validate with gates: compile/type gates, golden-query parity, and edge-cohort diffs.
- Credit-safe refactors: pruning-friendly predicates, bounded apply windows, and optional clustering guidance where beneficial.
Supported constructs
Representative Teradata SQL constructs we commonly convert to Snowflake SQL (exact coverage depends on your estate).
| Source | Target | Notes |
|---|---|---|
| QUALIFY + window filters | Snowflake window functions + QUALIFY | Deterministic ORDER BY and tie-breakers enforced. |
| Top-N / paging patterns | LIMIT/OFFSET with explicit ORDER BY | Ordering made explicit for deterministic results. |
| Date/time arithmetic | Snowflake date/time equivalents | DATE vs TIMESTAMP intent normalized explicitly (NTZ/LTZ/TZ as needed). |
| NULL/type coercion idioms | Explicit casts + null-safe comparisons | Prevents join drift and filter selectivity changes. |
| String/regex functions | Snowflake string/regex equivalents | Edge-case behavior validated via golden cohorts. |
| Teradata optimizer-tuned query shapes | Pruning-aware rewrites + optional clustering guidance | Replace physical tuning assumptions with Snowflake-native posture. |
How workload changes
| Topic | Teradata | Snowflake |
|---|---|---|
| Analytic filtering | QUALIFY used heavily; ordering assumptions often implicit | QUALIFY supported but ordering must be deterministic |
| Type behavior | Implicit casts often tolerated | Explicit casts required for stable outputs |
| Cost predictability | Platform capacity and spool behavior | Warehouse credits + bounded scans |
Examples
Representative Teradata → Snowflake rewrites for QUALIFY, date arithmetic, and deterministic windowed filters. Adjust identifiers and types to your schema.
-- Teradata: QUALIFY
SELECT *
FROM events
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_ts DESC) = 1;Common pitfalls
- QUALIFY drift: analytic filters translate but change winners if ORDER BY isn’t complete.
- Implicit cast drift: Teradata coercion differs; Snowflake needs explicit casts for stable outputs.
- NULL semantics in joins: equality behavior differs; joins can drop or duplicate rows when NULLs exist.
- Date/time semantics: Teradata date functions differ; Snowflake needs explicit timestamp intent (NTZ/LTZ/TZ) and conversions.
- Top-N ambiguity: LIMIT without deterministic ordering yields nondeterministic results.
- Unbounded scans: predicates don’t prune effectively; scans become large and credit burn spikes.
Validation approach
- Compilation gates: converted queries compile and execute in Snowflake reliably under representative parameters.
- Catalog/type checks: referenced objects exist; implicit casts surfaced and made explicit.
- Golden-query parity: critical dashboards and queries match outputs or agreed tolerances.
- KPI aggregates: compare aggregates by key dimensions and windows.
- Edge-cohort diffs: validate ties, null-heavy segments, boundary dates, and timezone transitions.
- Cost baseline: capture runtime and credit/scan behavior for top queries; set regression thresholds to prevent credit spikes.
Migration steps
- 01
Collect and prioritize the query estate
Export BI SQL, view definitions, ETL SQL, and app queries. Rank by business impact, frequency, and risk patterns (QUALIFY/windows, time logic, implicit casts, top-N).
- 02
Define semantic contracts for risky patterns
Make tie-breakers, NULL handling, casting strategy, and timestamp intent explicit—especially for QUALIFY/top-N and analytic logic.
- 03
Convert with rule-anchored mappings
Apply deterministic rewrites for Teradata constructs and flag ambiguous intent with review markers (implicit casts, ordering ambiguity, timezone assumptions).
- 04
Validate with golden queries and edge cohorts
Compile and run in Snowflake, compare KPI aggregates, and run targeted diffs on edge cohorts (ties, null-heavy segments, boundary dates).
- 05
Tune top queries for Snowflake
Ensure pruning-friendly predicates, optionally apply clustering where justified, and baseline credit/runtime so high-cost regressions are caught early.
We inventory your SQL estate, convert a representative slice, and deliver parity evidence on golden queries—plus a risk register for constructs that carry business meaning and cost risk.
Get a conversion plan, review markers, and validation artifacts so query cutover is gated by evidence and rollback-ready criteria—without credit spikes.