Workload

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.

Book assessment Explore workloads

Quick answer

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.

Back to pair page

Context

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.

Approach

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.

Coverage

Supported constructs

Representative Teradata SQL constructs we commonly convert to BigQuery Standard SQL (exact coverage depends on your estate).

SourceTargetNotes
QUALIFY + windowed filtersQUALIFY (BigQuery) with deterministic ORDER BYTie-breakers enforced to prevent nondeterministic drift.
TOP n / SAMPLELIMIT / TABLESAMPLE (where applicable)ORDER BY made explicit for business-critical top-N queries.
Teradata date/time arithmeticDATE_ADD/DATE_DIFF/TIMESTAMP_* functionsUnits 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 idiomsTemporary tables / CTEs / staged materializationsConverted to BigQuery-safe staging patterns.
String/regex functionsBigQuery string/regex equivalentsCollation/case expectations validated on golden cohorts.

Compare

How workload changes

TopicTeradataBigQueryNotes
Optimizer assumptionsPI/AMP locality + collected stats influence plansPruning and query shape dominate executionTranslation must include pruning-aware rewrites for top queries.
Top-N and orderingTOP patterns sometimes rely on platform orderingORDER BY must be explicit for deterministic resultsWe enforce tie-breakers where results are business-critical.
Date/time behaviorTeradata date casting/truncation idiomsExplicit DATE vs TIMESTAMP handlingTimezone boundaries are validated where reporting depends on local time.

Examples

Examples

Representative Teradata -> BigQuery rewrites for QUALIFY/top-N and date/time logic. Adjust identifiers and types to your schema.

01_qualify_teradata.sql
-- 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;
01_qualify_bigquery.sql
-- BigQuery: enforce deterministic tie-breaker
SELECT
  customer_id,
  order_id,
  order_ts
FROM `proj.ds.orders`
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_ts DESC, order_id DESC
) = 1;
02_top_teradata.sql
-- Teradata: TOP with explicit ordering
SELECT TOP 100
  product_id,
  SUM(revenue) AS rev
FROM sales
WHERE txn_dt BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'
GROUP BY 1
ORDER BY rev DESC;
02_limit_bigquery.sql
-- BigQuery: LIMIT
SELECT
  product_id,
  SUM(revenue) AS rev
FROM `proj.ds.sales`
WHERE txn_dt BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'
GROUP BY product_id
ORDER BY rev DESC
LIMIT 100;
03_date_arithmetic_teradata.sql
-- Teradata: date arithmetic
SELECT
  order_dt,
  order_dt + INTERVAL '7' DAY AS plus_7
FROM t;
03_date_arithmetic_bigquery.sql
-- BigQuery: DATE_ADD
SELECT
  order_dt,
  DATE_ADD(order_dt, INTERVAL 7 DAY) AS plus_7
FROM `proj.ds.t`;

Workload Assessment

Translate Teradata SQL with parity gates

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.

Book assessment

Avoid

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.

Proof

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.

Execution

Migration steps

A sequence that keeps correctness measurable and prevents semantic drift.

  1. 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.

  2. 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.

  3. 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).

  4. 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).

  5. 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.

FAQ

Frequently asked questions

Does BigQuery support QUALIFY like Teradata? +

Yes, BigQuery supports QUALIFY for filtering window function results. The key risk is semantic drift from incomplete ordering-so we enforce deterministic tie-breakers where results matter.

What about Teradata-specific functions and date logic? +

We map functions to BigQuery equivalents and normalize date/time intent explicitly (DATE vs TIMESTAMP, units, boundary days). High-risk patterns get golden-query parity checks.

Will performance be the same after translation? +

Not automatically. Teradata PI/AMP-era shapes often need pruning-aware rewrites and layout alignment in BigQuery. We baseline and tune the top queries as part of the workload.

How do you prove the translated queries are correct? +

We validate with compilation gates, catalog/type checks, golden queries, KPI aggregates, and targeted diffs on edge cohorts (ties, null-heavy segments, boundary dates).

Migration Acceleration

Ship BigQuery queries with proof-backed sign-off

Get a conversion plan, review markers, and validation artifacts so query cutover is gated by evidence and rollback-ready criteria.

Book assessment
Book assessment