BigQuery to AWS: A Complete Migration Assessment Playbook

BigQuery migrations are happening at scale. Post-2023 pricing changes led to 25–265% cost increases for many customers, GCP incentive packages are expiring, and enterprises are re-evaluating their data platform strategy.

I’ve been involved in enough of these — from the initial “should we leave?” conversation to the final cutover — to know that “just move to Redshift” is not a strategy. A proper migration starts with a rigorous assessment. Without it, you’ll underestimate complexity and over-promise timelines every time.

Here’s the playbook.

Why Customers Leave BigQuery

The top three drivers I see repeatedly:

1. Unpredictable cost model and price increases. BigQuery’s pay-per-query pricing becomes unpredictable at scale. On-demand bills surprise budget owners quarterly.
2. Federation and data sharing limitations. Restricted cross-platform data sharing and federated query options compared to Redshift/Athena.
3. Governance gaps. Simpler IAM model that lacks enterprise-grade column/row-level security granularity.

Additional pain points: support responsiveness, contract renegotiation friction, proprietary SQL extensions creating vendor lock-in, and a thinner partner ecosystem for data engineering.

Key insight: A business case for migration is 5% about cost. The real motivations are innovation velocity, operational maturity, security posture, and ecosystem breadth.

The Assessment Framework

1. Data Warehouse Metrics

Collect these first — they drive every subsequent decision:

• Total data volume (TB/PB)
• Number of datasets and tables
• Table types (standard, partitioned, clustered, external, views, materialized views)
• Data growth rate (monthly)
• Concurrent users and queries at peak
• Query complexity distribution

2. Query Patterns & Workloads

3. Cost & Performance

• Monthly BigQuery costs (on-demand vs editions/flat-rate)
• Slot utilization patterns
• Query performance SLAs
• Peak usage hours
• Cost allocation by department/project

4. Integrated GCP Services

Map every service the customer uses — this defines migration scope:

5. Advanced Features Usage

Check every one of these — they determine conversion complexity:

• Nested/repeated fields (STRUCT/ARRAY)
• User-defined functions (SQL and JavaScript)
• Scheduled queries
• Wildcard tables
• Time travel
• BI Engine acceleration
• Search indexes
• Remote functions
• Stored procedures

Target Architecture Options

Option 1: Amazon Redshift (Primary recommendation)

Best for: Traditional DW, complex SQL, BI/reporting, predictable patterns, enterprise governance.

Key advantages: Up to 4.9x better price-performance on industry benchmarks. Decoupled compute/storage (RA3 nodes). Native data sharing across accounts. Zero-ETL from Aurora, RDS, DynamoDB. Streaming ingestion from Kinesis.

Option 2: Amazon Athena (Data Lake)

Best for: Ad-hoc queries, log analysis, variable workloads, pay-per-query simplicity.

Architecture: S3 (Parquet/ORC) → Glue Catalog → Athena → QuickSight

At $5/TB scanned with columnar formats reducing scan by 90%+, effective cost can be under $0.50/TB.

Option 3: Hybrid (Redshift + Athena)

Best for: Mixed workloads, cost optimization, hot/cold data separation.

Hot data  → Redshift (frequent queries, BI dashboards)
Cold data → S3 + Athena (historical analysis, ad-hoc)
Redshift Spectrum bridges both layers

Option 4: Lakehouse (S3 Tables + Apache Iceberg)

Best for: Modern data architectures, multi-engine access, no vendor lock-in.

• S3 Tables with Apache Iceberg format
• Query with Athena, Redshift Spectrum, EMR Spark
• Open format — portable across engines
• Zero-ETL from Redshift Serverless

Sizing Guidance

Migration Technical Playbook

Phase 1: Schema Conversion

Primary tool: AWS Schema Conversion Tool (SCT)

• Converts BigQuery schemas to Redshift DDL
• Handles data type mapping
• Flags incompatible constructs
• Generates assessment report with conversion percentage

Phase 2: Data Migration

Four methods, depending on scale and requirements:

Phase 3: SQL & Query Migration

Key syntax differences to handle:

Expect 90–95% automated conversion, 5–10% manual tuning. Never promise 100% SQL compatibility.

Phase 4: Validation

• Row count validation
• Schema comparison
• Data sampling + checksums
• Query result comparison (top N queries by frequency and cost)
• Performance baseline vs. target

Migration Waves

Cost Positioning

BigQuery Pricing

• On-demand: $6.25/TB scanned (first 1TB free/month)
• Editions (flat-rate): Slots reserved — predictable but expensive at scale
• Storage: $0.02/GB active, $0.01/GB long-term
• Streaming inserts: $0.05/GB

AWS Advantages

• Redshift Serverless: Pay per RPU-hour + storage. More predictable at scale.
• Redshift Provisioned (RA3): Reserved instances = up to 75% savings. Concurrency scaling (free 1hr/day per cluster).
• Athena: $5/TB with columnar optimization reducing effective cost to < $0.50/TB.

The free tier attracts initial adoption, but costs become unpredictable at scale. Organizations consistently report budget overruns when usage grows beyond initial projections.

Common Pitfalls to Avoid

1. Don’t lead with cost if BigQuery is genuinely cheaper for their pattern. Small ad-hoc workloads (<1TB/month) ARE cheaper on BigQuery. Focus on governance, ecosystem, innovation speed instead.

2. Don’t promise 100% SQL compatibility. JavaScript UDFs, wildcard tables, and SAFE_ functions need rework. Set realistic expectations.

3. Don’t ignore the Looker dependency. Looker works on Redshift too. Position it as complement, not replacement.

4. Don’t underestimate streaming ingestion migration. Pub/Sub → BigQuery is a one-liner in GCP. On AWS: Kinesis → Redshift streaming is powerful but architecturally different.

5. Don’t try to migrate everything at once. Start with 1–2 representative workloads. Prove value, then expand.

6. Don’t present only Redshift as the answer. Some workloads belong on Athena (ad-hoc, logs). Some need open lakehouse (Iceberg on S3). Let the workload profile drive the choice.

The Roadmap

Phase 1: Assess (2–4 weeks)
├── Run assessment (bq-assess CLI or manual framework)
├── Collect billing export + architecture diagram
├── Identify top 5 workloads by cost/complexity
├── Map BigQuery features used → AWS equivalents
└── Build business case

Phase 2: Prove (4–6 weeks)
├── PoC on 1–2 workloads (Redshift Serverless)
├── Demonstrate performance, cost, governance improvements
├── Validate SQL conversion rate
└── Refine target architecture

Phase 3: Migrate (6–12+ weeks)
├── Wave 1: Simple schemas, standard SQL
├── Wave 2: Moderate complexity, UDFs, partitioning
├── Wave 3: Complex (nested fields, streaming, ML)
├── Cutover and validation per wave
└── Decommission BigQuery

Phase 4: Optimize (4–8 weeks)
├── Performance tuning
├── Cost optimization (Reserved Instances, compression)
├── Documentation and knowledge transfer
└── Hypercare support

Key Takeaways

• BigQuery migrations are driven by cost unpredictability, governance gaps, and ecosystem limitations — not just price
• Assessment is non-negotiable — understand features used, query patterns, and integrated services before proposing a target
• Not every workload belongs on Redshift — Athena for ad-hoc, Lakehouse for open formats, hybrid for mixed
• Expect 90–95% automated SQL conversion; budget time and expertise for the remaining 5–10%
• Migrate in waves — simple workloads first, complex (nested fields, streaming, ML) last
• Start with a PoC on 1–2 representative workloads — prove value before committing to full migration
• The biggest trap is treating this as a pure infrastructure swap — it’s an architecture decision

References

• Migrating BigQuery to Redshift with DMS
• AWS SCT for BigQuery
• BigQuery → Redshift with SCT (Blog)
• BigQuery → Redshift with Glue (Blog)
• BladeBridge SQL Migration (Blog)

Any opinions in this article are my own.