TL;DR — Direct Answer
A production Redshift-to-BigQuery migration takes 8–16 weeks for a 1–5TB estate, follows seven phases (assess → translate → move → rebuild transformations in dbt → repoint BI → parallel-run → decommission), and is justified when Redshift cluster costs are dominated by idle capacity. Our documented production migration of a 2TB+ estate finished in 14 weeks and delivered a 62% total-cost-of-ownership reduction — $125K saved annually — with 10x query scalability. The single biggest success factor: parallel-run reconciliation before cutover.
Why teams migrate off Redshift
Amazon Redshift is a capable warehouse, and staying on it is the right call for steady, predictable workloads on committed AWS spend. Migrations happen when three pressures stack up: idle-capacity cost (paying for a provisioned cluster around the clock while analysts query eight hours a day), concurrency ceilings (dashboards queuing behind nightly loads), and maintenance drag (vacuum, distribution keys, WLM tuning — engineering hours that BigQuery's serverless model eliminates).
In the engagement this playbook is based on, the client's 2TB+ Redshift estate had unpredictable costs and manual scaling. The finance case was simple: BigQuery's on-demand pricing meant paying for queries actually run, and flat-rate reservations remained available as a hedge if usage grew predictable.
The 7-phase playbook
Phase 1 — Assessment (week 1–2)
Inventory everything: tables and their actual query frequency (most estates have 40%+ dead tables), all ETL jobs and their dependencies, every BI dashboard and its connection. Profile Redshift system tables (STL_QUERY, SVV_TABLE_INFO) for usage truth. Output: a migration scope document that explicitly lists what will not be migrated.
Phase 2 — Schema translation (week 2–3)
Redshift DDL does not map one-to-one. Distribution and sort keys become BigQuery partitioning and clustering; interleaved sort keys usually become clustering on the two highest-cardinality filter columns. Data types need a mapping table (Redshift SUPER → BigQuery JSON; VARCHAR length limits disappear). Resist the urge to "improve" the model mid-flight — translate first, refactor after cutover.
Phase 3 — Data movement (week 3–5)
The reliable path is Redshift UNLOAD to S3 (Parquet), transfer via Storage Transfer Service to GCS, then load to BigQuery. Parquet preserves types and compresses well. For continuous sync during the migration window, schedule incremental UNLOADs keyed on update timestamps. Expect to move history once and increments daily.
Phase 4 — Rebuild transformations in dbt (week 4–9, overlaps)
This is where most of the value is created. Legacy Redshift estates typically hide transformation logic in stored procedures and scheduled SQL scripts. Rebuilding them as dbt models gives version control, tests, lineage, and documentation in one move. In our engagement the redesigned dbt layer is what unlocked the cost win — intelligent partitioning plus incremental models meant queries scanned megabytes instead of full tables.
Phase 5 — Repoint BI (week 8–11)
Dashboards move last-but-one, one workspace at a time. Build a connection-inventory spreadsheet in Phase 1 and burn it down. Watch for SQL dialect issues hiding inside BI tools — Looker derived tables and Tableau custom SQL carry Redshift syntax that must be translated.
Phase 6 — Parallel-run validation (week 10–13)
Run both warehouses simultaneously and reconcile daily: row counts per table, column checksums on critical fields, and — most importantly — business-metric parity: the revenue number on the old dashboard must equal the new one, to the cent, for two consecutive weekly cycles. This phase is why our migration reported zero data-integrity incidents.
Phase 7 — Cutover and decommission (week 13–14)
Freeze legacy writes, final increment, flip DNS/connections, monitor for one billing cycle, then actually decommission the cluster. Teams that keep Redshift "just in case" for six months pay double and erode the business case.
What it actually cost and saved
Migration engineering cost is typically recovered in 6–14 months of savings; in this case under seven months. Full details are in the Cloud FinOps & Modernization case study.
The five pitfalls that sink Redshift migrations
- Migrating dead tables. Moving 100% of a warehouse where 40% is unqueried wastes weeks. Profile first; migrate what's used.
- Lift-and-shift SQL without partitioning design. BigQuery bills by bytes scanned; unpartitioned tables turn the cost model against you.
- Ignoring the transformation layer. Copying stored-procedure spaghetti preserves your problems at a new address. Rebuild in dbt.
- Skipping parallel-run. Cutting over on row counts alone misses logic divergence; reconcile business metrics.
- No FinOps from day one. Without per-team cost attribution and quotas, BigQuery on-demand can surprise you the other way.