Migration

Teradata → BigQuery migration

Move Teradata workloads (SQL/BTEQ scripts, macros, stored procedures, volatile tables, and WLM-shaped concurrency) to BigQuery with predictable conversion and verified parity. SmartMigrate makes semantic and performance differences explicit, produces reconciliation evidence you can sign off on, and gates cutover with rollback-ready criteria—so production outcomes are backed by proof, not optimism.

Book Assessment

Quick answer

Fit

Is this migration right for you?

Ideal if

You have a mature Teradata estate with hundreds/thousands of queries and BI dependencies
You rely on macros, procedures, and BTEQ/TPT orchestration in production
Performance and concurrency predictability matter for SLAs
You require provable parity and reconciliation evidence before cutover
You want cost and performance governance in BigQuery from day one

Not a fit if

You’re doing a simple one-time table copy with no semantic dependency on existing queries
You don’t need reconciliation evidence or rollback guarantees
There are no critical consumers depending on strict output parity

Risk map

What breaks in a Teradata → BigQuery migration

These are the common “gotchas” that cause silent result drift, operational breaks, or cost/performance surprises if not handled explicitly.

Breakage	Mitigation
Teradata-specific SQL semantics (QUALIFY, TOP, set ops, date logic)Teradata patterns look familiar but behave differently in BigQuery around filtering, ranking, NULL handling, and date/timestamp behavior—often surfacing as “same query, different KPI.”	Mitigation: Assess, rewrite where needed, then validate with parity checks.
Primary Index, AMP distribution, and stats assumptionsTeradata performance depends on PI choices, AMP distribution, and collected statistics. BigQuery performance is driven by partitioning/clustering, join strategy, and scan economics. If you port queries without a physical design plan, performance can swing wildly.	Mitigation: Assess, rewrite where needed, then validate with parity checks.
Volatile tables and session-based workflowsMany Teradata pipelines rely on volatile tables and session logic. These need deliberate redesign (staging datasets, temp tables, materialization strategy) or workloads break.	Mitigation: Assess, rewrite where needed, then validate with parity checks.
Macros, stored procedures, and operational SQL (BTEQ/TPT)Teradata estates are rarely “just SQL.” Macros, stored procedures, BTEQ scripts, FastLoad/MultiLoad/TPT jobs, and scheduler glue must be migrated with an execution plan—or production orchestration fails post-cutover.	Mitigation: Assess, rewrite where needed, then validate with parity checks.
Concurrency and workload management mismatchTeradata workload management patterns don’t translate 1:1. Without a plan for BigQuery concurrency (on-demand vs reservations, slot contention, BI bursts), SLAs become unpredictable.	Mitigation: Assess, rewrite where needed, then validate with parity checks.
Hidden BI coupling and “business meaning” driftDashboards often depend on undocumented behavior (rounding, casting, ordering, time semantics). If you don’t lock a “parity contract,” correctness becomes a debate at cutover.	Mitigation: Assess, rewrite where needed, then validate with parity checks.

Flow

Migration Flow

Extract → Plan → Convert → Reconcile → Cutover to BigQuery, with exception handling, validation gates, and a rollback path

Flow diagram showing SmartMigrate’s end-to-end migration pipeline

Conversion

SQL & Workload Conversion Overview

Teradata → BigQuery migration is not just “SQL translation.” The objective is to preserve business meaning while aligning to BigQuery’s execution model and cost structure. SmartMigrate converts what is deterministic, flags ambiguity, and structures the remaining work so engineering teams can resolve exceptions quickly. What we automate vs. what we flag:

Automated: Common Teradata SQL patterns into BigQuery SQL, routine DDL mapping, straightforward join/aggregation conversions, safe function rewrites, and baseline view conversion.
Flagged as “review required”: QUALIFY + window edge cases, implicit casts, NULL-sensitive logic, volatile table workflows, macros/procedures, and performance-sensitive query shapes.
Manual by design: Final partitioning/clustering strategy, hot-query rewrites, macro/procedure execution decisions, and concurrency/slot strategy.

Failure modes

Common failure modes

QUALIFY and ranking drift

Analytic filters and window logic translate syntactically but change edge-case outputs.

PI/AMP assumptions carried over

Teradata physical tuning is treated as “schema,” and BigQuery performance collapses.

Volatile table workflow breaks

Session-based pipelines lose their staging semantics and fail mid-stream.

Macro/procedure gaps

Macros and stored procedures are deferred, and operational workloads break after cutover.

Load tool mismatch

FastLoad/MultiLoad/TPT patterns aren’t re-homed cleanly, causing slow loads and fragile backfills.

Implicit CAST behavior

Differences in numeric/timestamp casting silently change aggregates and join matches.

Stats-driven plan surprises

Teradata stats expectations don’t carry; BigQuery needs partitioning/clustering/materialization choices.

Cost model whiplash

Queries tuned for Teradata become expensive in BigQuery due to scan patterns and missing pruning.

Controls

Validation and reconciliation you can sign off on

In a Teradata → BigQuery migration, success must be measurable. We validate correctness in layers: first ensuring translated workloads compile and execute reliably, then proving that outputs match expected business meaning via reconciliation. Validation is driven by pre-agreed thresholds and a defined set of golden queries and datasets. This makes sign-off objective: when reconciliation passes, cutover is controlled; when it fails, you get a precise delta report that identifies where semantics, type mapping, or query logic needs adjustment. Checks included (typical set): - Row counts by table and key partitions where applicable

Null distribution + basic profiling (min/max, distinct counts where appropriate) - Checksums/hashes for stable subsets where feasible - Aggregate comparisons by key dimensions (day, region, customer/product keys)
Sampling diffs: top-N, edge partitions, known corner cases - Query result parity for golden queries (reports and KPI queries) - Post-cutover SLA monitoring plan (latency, bytes scanned, failure rates, concurrency/slots)

Optimization

Performance optimization in BigQuery

Partitioning strategy (ingestion-time and query-time): Align partition keys to dominant filters to improve pruning and reduce bytes scanned.
Clustering on high-selectivity columns: Improve performance for common predicates and joins when partitions alone are not sufficient.
Materialized views and summary tables: Stabilize BI workloads and reduce repeated full-scan aggregations.
Concurrency and slot planning: Decide on on-demand vs reservations based on workload mix and required predictability.
Cost hygiene via selectivity and pruning: Encourage explicit column selection, predicate placement, and partition filters.
Query plan observability: Use query logs and INFORMATION_SCHEMA to detect regressions and validate tuning outcomes.
Join strategy tuning: Reduce large intermediate results by managing join cardinality and filter ordering.

Do the work

Teradata → BigQuery migration checklist

Parity contract exists

Do you have signed-off golden queries/reports + thresholds (including QUALIFY/window edge cases and casting/time behavior) before conversion starts?
Operational SQL is in scope (not “later”)

Do you have a plan for macros, procedures, BTEQ scripts, and load jobs (TPT/FastLoad/MultiLoad) with operational parity?
Volatile-table workflows are accounted for

Have you identified session-based staging patterns and decided the BigQuery equivalents (temp tables, staging datasets, materialization)?
Performance strategy replaces PI/AMP assumptions

Have you defined how partitioning/clustering/materialization will replace Teradata physical tuning for hot workloads?
Cutover is rollback-safe under real concurrency and cost

Parallel run + canary gates + rollback criteria + BigQuery guardrails (bytes scanned, slots, latency) are ready.

Workloads

Frequently asked questions

What are the biggest differences between Teradata and BigQuery SQL? +

Both support analytics SQL, but practical differences show up in Teradata-specific constructs (e.g., QUALIFY patterns), type casting and date/time behavior, and execution/performance models. Teradata relies on PI/AMP distribution and statistics; BigQuery relies on partitioning/clustering, scan economics, and slot-based concurrency. Reliable migration requires handling semantics—not just syntax.

How do you handle QUALIFY and window function differences? +

We translate patterns, then validate with targeted edge-case datasets and golden queries where ranking/sessionization/report logic is sensitive. We explicitly enforce ordering and frames where needed so outputs are stable and auditable.

What happens to macros, procedures, and BTEQ/TPT scripts? +

We inventory and classify operational SQL and orchestration dependencies, translate what is straightforward, and re-home execution into appropriate patterns (e.g., Dataform/dbt + Composer/Workflows) so schedules and operational behavior remain intact.

How do you validate results are correct after SQL translation? +

We use layered validation: compiler/execution checks, then reconciliation checks (row counts, profiling, aggregates by key dimensions, sampling diffs) and golden query parity. Thresholds are defined up front so correctness is measurable and auditable.

How do you estimate BigQuery cost after migration? +

Cost is driven by bytes scanned, concurrency, and repeated query patterns. We analyze query shapes and scan patterns, then recommend partitioning/clustering/materialization and slot strategy. Post-migration, we monitor bytes scanned and regressions.

Can we migrate with minimal downtime? +

Often yes—via a parallel run window with controlled cutover gates and a rollback-ready plan. The approach depends on batch vs CDC movement, SLA requirements, and downstream consumer behavior. Cutover is gated by reconciliation thresholds and operational readiness.

Migration Acceleration

Book a Teradata → BigQuery migration assessment

Get a migration plan you can execute—with validation built in. We’ll inventory your Teradata estate (including macros, procedures, BTEQ/TPT jobs, and volatile-table workflows), convert representative workloads, surface risks in SQL translation and type mapping, and define a validation and reconciliation approach tied to your SLAs. You’ll also receive a cutover plan with rollback criteria and performance optimization guidance for BigQuery.

Book Assessment

Teradata → BigQuery migration

Ideal if

Not a fit if

⚠ QUALIFY and ranking drift

⚠ PI/AMP assumptions carried over

⚠ Volatile table workflow breaks

⚠ Macro/procedure gaps

⚠ Load tool mismatch

⚠ Implicit CAST behavior

⚠ Stats-driven plan surprises

⚠ Cost model whiplash