Teradata earned its place. For two decades it was the gold standard of enterprise data warehousing — and if you’re reading this, you probably have a Teradata estate that still runs critical parts of your business. But the economics have flipped. Licensing and hardware costs keep climbing, the talent pool keeps shrinking, and every AI and analytics initiative in your roadmap assumes a cloud-native platform.
BigQuery is a potential destination for a Teradata exit: serverless, zero infrastructure management, and a consumption model that scales with actual usage instead of peak-provisioned hardware. But Teradata-to-BigQuery is also one of the most technically demanding warehouse migrations, because Teradata estates carry decades of accumulated complexity — BTEQ scripts, stored procedures, MULTISET semantics, and workloads tuned to Teradata’s unique architecture.
This playbook walks through the full migration: what makes Teradata different, how to sequence the work, and the technical traps that break these projects.
Why Teradata Migrations Are Uniquely Hard
Every warehouse migration involves schema, SQL, data, and orchestration. Teradata adds four layers of difficulty:
1. Procedural logic everywhere. Teradata estates accumulate enormous volumes of BTEQ scripts and SQL stored procedures — often thousands of them — encoding business logic, ETL orchestration, and error handling in procedural code that predates your current team’s tenure.
2. Dialect depth. Teradata SQL has deep, idiosyncratic features: QUALIFY, SAMPLE, MULTISET vs. SET table semantics, the PI (primary index) distribution model, Teradata-specific date arithmetic, and VOLATILE tables in session-based workflows.
3. Architecture mismatch. Teradata’s shared-nothing, AMP-based parallelism doesn’t map to BigQuery’s slot-based serverless model. Physical design decisions — indexes, partitioning, statistics — must be rethought, not translated.
4. Volume of downstream dependencies. Decades-old warehouses tend to be load-bearing: hundreds of reports, extracts, and downstream feeds depend on them, and every one is a cutover touchpoint.
The Playbook: Six Phases
Phase 1: Assessment — Know What You Have
The single most important phase, and the one teams most often skip. A real assessment produces:
- Complete object inventory: tables, views, macros, stored procedures, BTEQ scripts, TPT/FastLoad/MultiLoad jobs, and the schedules that orchestrate them
- Complexity scoring: which objects convert cleanly, which need manual work, and which should be redesigned
- Dependency mapping: what reads from and writes to every object — essential for wave planning
- Usage analysis: which objects are actually queried. In typical estates, unused objects may be candidates for retirement after owner review
- Automation coverage estimate: what percentage of your SQL can be machine-converted, which drives your effort model and timeline
Without this, any timeline you commit to is a guess. With it, you can sequence waves rationally and price the migration credibly.
Phase 2: Architecture and Schema Design
Resist the urge to 1:1 port your Teradata physical design. Key translation decisions:
- Primary index → partitioning and clustering. Teradata’s PI is a data distribution mechanism; BigQuery’s partitioning (by date/timestamp or integer range) and clustering are query-pruning mechanisms. Design them around how BigQuery will actually be queried, not how Teradata stored the data.
- SET and MULTISET tables. BigQuery allows duplicate rows and has no SET/MULTISET table declaration. Preserve required duplicate-handling semantics explicitly; do not silently remove duplicates or assume the target enforces source behavior.
- Data types. Most map cleanly (
DECIMAL→NUMERIC/BIGNUMERIC,VARCHAR→STRING), but watch Teradata’sTIMESTAMP WITH TIME ZONEbehavior andPERIODtypes, which need explicit redesign. - VOLATILE and derived tables. Session-scoped tables in BTEQ workflows become temp tables, CTEs, or intermediate tables in scripted BigQuery sessions — each a small design decision.
Phase 3: Data Migration
Two workstreams run in parallel:
Historical load. Bulk-extract Teradata data (TPT export is the workhorse) to GCS, then load to BigQuery. For large estates, this is a bandwidth-and-compression engineering exercise, well-understood but requiring planning for transfer time.
Incremental sync. Plan the period during which both systems must stay synchronized. Establish a CDC-style feed — via Teradata’s change data capture mechanisms, incremental extracts keyed on load timestamps, or replication tooling — to keep BigQuery current until cutover.
Phase 4: SQL and Workload Conversion
This is where the migration succeeds or stalls. The work splits into three tiers:
Tier 1 — Mechanical conversion. Standard SELECT/INSERT/UPDATE queries, views, and DDL convert well with automated tooling. QUALIFY has a direct BigQuery equivalent; most functions map 1:1 or with simple rewrites.
Tier 2 — Guided conversion. Queries using Teradata-isms that need judgment: SAMPLE, complex OLAP window patterns, NORMALIZE, recursive queries with Teradata-specific syntax, and session-temporal features.
Tier 3 — Redesign. BTEQ scripts and stored procedures. BTEQ is not SQL — it’s a scripting environment with control flow, error branching (IF ERRORCODE), and OS interaction. Each script needs a disposition: convert to BigQuery scripting, rewrite as a DAG in your orchestrator (Composer/Airflow, dbt), or fold into a modern ELT pattern. Stored procedures follow the same triage. Measure this tier separately: control flow and external dependencies can require substantial redesign.
Phase 5: Validation — Prove Equivalence
“It compiles” is not success. Converted SQL that returns wrong numbers is worse than SQL that fails loudly. Validation must be built into every wave:
- Table-level reconciliation: row counts, column-level aggregates (sums, min/max, null counts, distinct counts), and checksums between Teradata and BigQuery after every sync
- Query-level validation: run source queries on Teradata and converted queries on BigQuery against synchronized data and diff outputs, with defined tolerances for floating point and timestamp precision
- Known edge traps: Teradata and BigQuery differ on division semantics, implicit type coercion,
NULLhandling in aggregates and joins, date overflow behavior, and case sensitivity in comparisons. These pass compilation silently — reconciliation is what catches them.
Phase 6: Cutover and Decommission
Migrate in business-domain waves, not technical layers. Per wave: freeze Teradata-side changes, final sync, final reconciliation sign-off, repoint consumers (BI tools, downstream extracts, scheduled jobs), monitor for a stabilization window. Decommissioning comes last — and plan it as its own workstream: data archival for compliance, audit retention, and contract termination timing (Teradata contracts rarely align neatly with your migration end date; negotiate early).
The Traps That Blow Up Timelines
- Underestimating BTEQ and stored procedures. If your plan says “convert the SQL” and treats procedural logic as a footnote, your timeline is fiction.
- Migrating dead objects. Without usage analysis, you’ll spend months converting tables nobody has queried since 2019.
- 1:1 physical design ports. Copying PI-based design into BigQuery produces slow, expensive queries. Redesign for the target.
- Validation as an afterthought. Discovering semantic drift in week 14 of a 16-week plan is how migrations lose executive confidence.
- Ignoring the coexistence period. Dual-running is expensive and operationally risky; plan the sync architecture and the exit criteria for it explicitly.
Realistic Timelines
Build the estimate from your assessment and a representative pilot. Measure procedural conversion, data transfer, reconciliation, and downstream cutover effort. Record assumptions and revise the plan when undocumented dependencies are discovered.
Start with the Assessment
Start the Teradata exit with an objective, automated inventory of what’s actually in the estate. It’s the difference between a migration plan and a migration hope.
Discuss a Teradata assessment with SmartMigrate to establish source coverage, procedural exceptions, and validation requirements before setting a migration timeline.