Teradata SQL queries to BigQuery
Translate Teradata-specific constructs-QUALIFY, TOP, date/time and analytic patterns, and PI-era assumptions-into BigQuery Standard SQL with validation gates that prevent semantic drift.
- Input
- Teradata SQL / query migration logic
- Output
- BigQuery equivalent (validated)
- Common pitfalls
- QUALIFY drift: top-N/window filters without deterministic tie-breakers produce nondeterministic results.
- TOP without ORDER BY: Teradata queries sometimes rely on platform ordering; BigQuery requires explicit ordering.
- Type coercion surprises: CASE/COALESCE branches return mixed types; BigQuery needs explicit casts to preserve intent.
Why this breaks
Teradata SQL estates often run on more than syntax: physical design assumptions (PI/AMP locality), optimizer expectations, and long-standing idioms like QUALIFY, TOP, and volatile/derived tables. BigQuery will compile many translated queries-but outputs and performance can drift when implicit assumptions aren’t made explicit.
Common symptoms after cutover:
- KPI drift from QUALIFY/top-N logic with incomplete ordering or ties
- Date/time edge cases change (casting, truncation, timezone boundaries)
- Teradata-specific functions map syntactically but shift NULL/type behavior
- “Convenience” features (TOP, QUALIFY, positional GROUP BY) translate but change intent
- Performance collapses because PI/AMP-era query shapes don’t prune well in BigQuery
SQL migration must preserve both meaning and execution posture for BigQuery.
How conversion works
- Inventory & prioritize the SQL corpus (BI extracts, stored queries, views, dbt/ELT models). Rank by business impact, frequency, and complexity.
- Normalize Teradata dialect: identifiers/quoting, date literals, volatile table patterns, macro-like idioms, and join syntax normalization.
- Rewrite with rule-anchored mappings: function equivalents, analytic/window rewrites, explicit cast strategy, and deterministic ordering in QUALIFY/top-N.
- Pattern libraries for high-risk constructs: QUALIFY, TOP + ORDER BY ties, interval/date arithmetic, regex/string behavior, and null-safe comparisons.
- Validate with gates: compile/run checks, catalog/type alignment, and golden-query parity with edge-window sampling diffs.
- Performance-safe refactors: prune-aware filters, join strategy adjustments, and pre-aggregation guidance for the most expensive BI queries.
Supported constructs
Representative Teradata SQL constructs we commonly convert to BigQuery Standard SQL (exact coverage depends on your estate).
| Source | Target | Notes |
|---|---|---|
| QUALIFY + windowed filters | QUALIFY (BigQuery) with deterministic ORDER BY | Tie-breakers enforced to prevent nondeterministic drift. |
| TOP n / SAMPLE | LIMIT / TABLESAMPLE (where applicable) | ORDER BY made explicit for business-critical top-N queries. |
| Teradata date/time arithmetic | DATE_ADD/DATE_DIFF/TIMESTAMP_* functions | Units and DATE vs TIMESTAMP intent normalized explicitly. |
| Analytic functions (ROW_NUMBER, RANK, SUM OVER) | Window functions (BigQuery) | Ordering and framing made explicit to preserve results. |
| Volatile / derived table idioms | Temporary tables / CTEs / staged materializations | Converted to BigQuery-safe staging patterns. |
| String/regex functions | BigQuery string/regex equivalents | Collation/case expectations validated on golden cohorts. |
How workload changes
| Topic | Teradata | BigQuery |
|---|---|---|
| Optimizer assumptions | PI/AMP locality + collected stats influence plans | Pruning and query shape dominate execution |
| Top-N and ordering | TOP patterns sometimes rely on platform ordering | ORDER BY must be explicit for deterministic results |
| Date/time behavior | Teradata date casting/truncation idioms | Explicit DATE vs TIMESTAMP handling |
Examples
Representative Teradata -> BigQuery rewrites for QUALIFY/top-N and date/time logic. Adjust identifiers and types to your schema.
-- Teradata: QUALIFY pattern
SELECT
customer_id,
order_id,
order_ts
FROM orders
QUALIFY ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_ts DESC
) = 1;Common pitfalls
- QUALIFY drift: top-N/window filters without deterministic tie-breakers produce nondeterministic results.
- TOP without ORDER BY: Teradata queries sometimes rely on platform ordering; BigQuery requires explicit ordering.
- Type coercion surprises: CASE/COALESCE branches return mixed types; BigQuery needs explicit casts to preserve intent.
- Null-safe comparisons: equality behavior differs; join keys can mis-match when NULLs are involved.
- Date math differences: interval units and truncation semantics differ (DATE vs TIMESTAMP).
- Positional GROUP BY/ORDER BY: shorthand can hide intent; rewrite explicitly.
- Performance regressions: missing partition filters and pruning-aware rewrites cause unbounded scans.
Validation approach
- Compilation gates: converted queries compile under BigQuery Standard SQL.
- Catalog/type checks: referenced tables/columns exist; implicit casts are surfaced and made explicit.
- Golden-query parity: critical dashboards and queries match on outputs or agreed tolerances.
- KPI aggregates: compare aggregates by key dimensions (date, region, product, customer cohorts).
- Edge-window diffs: validate boundary dates, tie situations, and null-heavy cohorts to catch subtle drift.
- Performance baseline: capture runtime/bytes scanned/slot time for top queries; set regression thresholds.
Migration steps
- 01
Collect and prioritize the query estate
Export BI SQL, view definitions, stored query libraries, and ETL SQL. Rank by business impact, frequency, and complexity/risk patterns.
- 02
Define semantic contracts for risky patterns
Make tie-breakers, NULL handling, casting strategy, and date/time intent explicit-especially for QUALIFY/top-N and reporting boundary days.
- 03
Convert with rule-anchored mappings
Apply deterministic rewrites for common constructs and flag ambiguous intent with review markers (implicit casts, ordering ambiguity, regex/collation expectations).
- 04
Validate with golden queries and edge cohorts
Compile and run in BigQuery, compare KPI aggregates, and run targeted diffs on edge windows (ties, null-heavy cohorts, boundary dates).
- 05
Optimize top queries for BigQuery
Add pruning-aware filters, rewrite join patterns where needed, and recommend pre-aggregations/materializations for the heaviest BI workloads.
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.
Get a conversion plan, review markers, and validation artifacts so query cutover is gated by evidence and rollback-ready criteria.