Best practices for a BigQuery to Amazon Redshift migration proof of concept (POC)
A practical guide to running a BigQuery to Amazon Redshift proof of concept and avoiding the pitfalls that most often make the results misleading.
Many organizations evaluating a move from Google BigQuery to AWS begin with a proof of concept (POC): a small, time-boxed exercise that converts a representative slice of the estate, runs it on Amazon Redshift, and compares correctness, performance, and cost against the current BigQuery baseline. A POC done well de-risks the full migration and produces an evidence-based total cost of ownership (TCO) that an engine decision can rest on. A POC done poorly, measuring Amazon Redshift on an architecture you would never run in production, or pricing it with the wrong billing model can point a customer in the wrong direction.
In this post, I show you how to run a credible BigQuery to Amazon Redshift migration POC. I will cover how to scope the work, how to convert schemas, SQL, and pipelines to Amazon Redshift equivalents, how to benchmark fairly given that BigQuery and Amazon Redshift use different pricing models, and a set of field-tested best practices drawn from an actual customer engagement. I will close with a checklist of the pitfalls that most often invalidate a POC and how to avoid them.
Overview
The POC follows a convert-and-validate approach on a deliberately small slice of the estate. You pick a handful of representative tables and the dashboards that read those tables, convert the BigQuery SQL, stored procedures, and data pipelines to Amazon Redshift equivalents, run the converted workloads, and validate the results against BigQuery.
The following diagram illustrates the end-to-end flow: Apache Iceberg tables on Amazon Simple Storage Service (Amazon S3) act as the source of truth, Amazon Redshift serves the hot query layer from Redshift Managed Storage
The engine mapping follows a simple rule. A BigQuery dataset becomes an Amazon Redshift schema, and the BigQuery project maps to your AWS account and an Amazon Redshift database.
To keep the POC focused and achievable, we recommend the following:
- Scope it to one to two weeks, with a few tables and a few queries only
- Choose use cases that exercise distinct patterns for example, an incremental load, a full rebuild, and a concurrent dashboard query mix
- Pin the data window and dataset size so every run is measured on identical inputs
- Agree the success criteria up front: correctness tolerances, performance targets (SLAs), and the TCO basis (for example, one month of total cost against the current BigQuery bill)
- Engage stakeholders early, including the data engineering, BI, and finance owners who will sign off on the results, so there is agreement on scope and success criteria before POC starts
Prerequisites
Before you begin, make sure you have the following:
- A Google Cloud service account with permissions to read the BigQuery datasets in scope.
- An AWS account with permissions to use Amazon Redshift, AWS Glue, and Amazon S3.
- An Amazon Redshift Serverless workgroup (or a provisioned cluster – Use Redshift Graviton Instance ) and an S3 bucket ideally an S3 table bucket if you keep an Iceberg source-of-truth layer.
- The representative slice identified: the tables, the dashboard queries, and queries
- A documented BigQuery baseline: current monthly cost and per-query run times for the queries in scope.
Convert and validate
Convert the proof-of-concept slice end to end, then validate the converted workloads against the original.
Schema conversion
A BigQuery dataset becomes an Amazon Redshift schema, and the BigQuery project maps to your AWS account and an Amazon Redshift database. Use AWS Schema Conversion tool for performing schema conversion.
SQL/code conversion
Convert the BigQuery SQL dialect to ANSI SQL or Amazon Redshift-specific syntax. Common conversions include the following:
- Nested and semi-structured types. We recommend building a gold layer optimized for reporting, where data is flattened for high-performance queries. When nested data must be stored in Amazon Redshift, use the SUPER data type : BigQuery JSON, STRUCT AND ARRAY maps to SUPER and queried with PartiQL syntax
- MERGE statements. Convert BigQuery MERGE to Amazon Redshift MERGE INTO
- User-defined functions. Convert BigQuery JavaScript UDFs to a SQL expression where possible; otherwise, use a AWS Lambda-backed UDF on Amazon Redshift
- Scheduled queries. Convert BigQuery scheduled queries to Amazon Redshift scheduled queries in Query Editor v2 , Amazon EventBridge Scheduler. Chains of schedules become AWS Step Functions workflows
AWS Schema Conversion tool is also used for performing code conversion from Google BigQuery to Amazon Redshift. More information here: Migrate Google BigQuery to Amazon Redshift using AWS Schema Conversion tool (SCT) . Note: Stored procedures needs to be manually converted into Redshift stored procedure.
Validation
Run the converted workloads against the slice and verify correctness against BigQuery. Compare row counts and checksums per table, compare query results for the dashboards on the slice, and compare run times against the agreed target.
Benchmarking and TCO on Amazon Redshift
BigQuery offers two pricing models for running queries.
- On-demand pricing offers scan-based pricing model charging $6.25/1 terabyte data scanned
- Capacity based pricing offers compute-based pricing model where customers are charged for compute capacity used to run queries
When you benchmark Amazon Redshift against BigQuery, keep one thing in mind: BigQuery on-demand pricing is not equivalent to Redshift pricing (redshift offers compute based pricing). If you are using BigQuery in on-demand manner and because these models are not directly comparable, benchmark a representative mix of queries on Amazon Redshift rather than a single query type.
A good practice is to run three tiers of queries and compare total cost and performance against your current BigQuery baseline:
- Lightweight - simple lookups and aggregations.
- Medium - moderate joins and transformations.
- Heavyweight - complex joins, window functions, and large scans.
To build a workload-representative TCO, multiply each use case by how often it runs, then add storage, to produce a monthly figure you can compare directly to the current monthly BigQuery bill.
Field-tested best practices
The following practices come from BigQuery to Amazon Redshift POC. Each one addresses a specific way an otherwise well-intentioned benchmark produced misleading results.
Make the comparison apple-to-apple
The single biggest source of error is storage asymmetry. If BigQuery runs every workload on its own native storage (Capacitor) while for Amazon Redshift runs you use Redshift only as an external Iceberg reader and writer where tables staying in an S3 table bucket, you are not measuring apples to apples. For equivalent comparison in this case, keep the hot serving data in native Redshift Managed Storage (RMS) for best price and performance.
Follow Redshift Table Design Best Practices for the POC
Apply the following table design best practices to get representative performance from your POC:
- Load the hot serving tables into Amazon Redshift with proper sort key and distribution keys defined. Although Redshift supports Automatic Table Optimization (ATO) but ATO kick-off when significant number of queries have been executed on Redshift. Initially all tables with ATO are created as dist style EVEN which makes the join operation on distribution key non performant. Recommendation is to assign optimal sort and distribution key for the tables in POC Phase.
- For small dimension tables(≤5M), choose dist style ALL
- Choose the dist key for big Fact table based on distribution key of biggest dimension table. Use dist style EVEN for denormalized tables.
- For Sort keys: Choose column which are used in filter / where conditions as SORT key, candidates are date/timestamps columns. For multi column sort key, use Max 4 columns, select based on low to high cardinality and don't compress sort key
- Create a materialized view for the heaviest dashboard queries, because that is how those dashboards are served in production
- If you keep an Iceberg source-of-truth layer on Amazon S3, treat the Redshift Managed Storage copy as the serving layer for queries requiring very high performance
- Compute table statistics on every table before any measurement using Analyze command.
Right-size Redshift compute
BigQuery measures compute in slots. A BigQuery slot is a virtual compute unit that BigQuery uses to execute SQL queries, Python code, and other job types. During a query, BigQuery automatically decides how many slots to use, based on the volume of data processed, the complexity of the query, and the number of slots available.
So the practical question is: given a customer's BigQuery slot usage, how much Redshift compute do they need?
Predicting data warehouse performance from a resource formula alone is difficult, because performance depends on many interacting factors: CPU, memory, disk I/O, network I/O, data model, query pattern, and concurrency. A slot count does not capture any of this cleanly, and the slot itself is not publicly defined. A slot is best understood as a thread of execution on a query worker shard. Google does not publish a fixed CPU and memory value per slot.
A two-step approach: resource-based sizing, then performance-based sizing
The approach we recommend is to move from resource-based sizing (on paper) to performance-based sizing (in the POC).
Step 1: Resource-based sizing (on paper). Use the customer's BigQuery slot count to derive a reasonable starting configuration for Redshift. Because memory is the key performance factor in a data warehouse workload, a workable rule of thumb is to assume 1 BigQuery slot is approximately 1 GiB of memory(this is based on assumption and working with customers in POC phases), then map that memory target to a Redshift Serverless RPU setting or a provisioned RG configuration. Treat this exercise as the opening estimate for the POC, not as a final recommendation, because they rest on assumptions. A good practice is to start with Redshift Serverless to gauge the complexity of the query workload and then transition to a provisioned cluster once the workload is well understood.
Step 2: Performance-based sizing (in the POC). Resource-based sizing gets you to a credible starting size. The POC then confirms it against real workloads. Measure at several RPU sizes (for example, 16, 32, and 64 RPU), and pick the cheapest size that meets the target.
Measure cost correctly
If you choose, Amazon Redshift Serverless for POC, it bills per second with a 60-second minimum, not in whole minutes. Don't price a run as minutes × RPU × rate. Take the charged seconds from SYS_SERVERLESS_USAGE, scope the sum to the benchmark statements only, and exclude verification, row-count, and monitoring queries that happen to land in the same minutes. Show the arithmetic with the regional RPU rate. More information on Billing for Redshift Serverless . Please note, this will provide you on demand pricing. In final pricing estimate, you can include Redshift reservations based on 1 year to 3 years commitment. More information on Redshift serverless reservation
Common pitfalls and how to avoid them
The following table summarizes the anti-patterns that most often invalidate a BigQuery to Amazon Redshift POC, and the correction for each.
| Pitfall | Correction |
|---|---|
| Amazon Redshift measured as an external Iceberg reader/writer while BigQuery uses native storage | Measure Redshift on Redshift Managed Storage for apple to apple comparison |
| No column statistics on tables | Compute statistics on every table before measuring |
| Tables default to DISTSTYLE EVEN with no sort keys | Set distribution style (KEY/ALL) and sort keys explicitly based on query pattern |
| Every run forced to the largest size to match BigQuery | Define an SLA, measure multiple RPU sizes, pick the cheapest that meets it |
| Cost computed as minutes × RPU × rate | Use charged seconds from SYS_SERVERLESS_USAGE, scoped to benchmark statements |
| Verification and monitoring queries counted in benchmark cost | Run them separately and exclude them from the measured window |
| A single query type used to judge cost | Benchmark lightweight, medium, and heavyweight tiers for TCO |
Conclusion
In this post, I showed you how to run a BigQuery to Amazon Redshift POC you can trust: scope a small but representative slice, convert your schemas, SQL, and pipelines, validate the results against BigQuery, and benchmark across a realistic mix of queries rather than a single one. The guiding idea throughout is simple: measure Amazon Redshift the way you would run it in production.
I invite you to try these practices on your own migration and to leave your feedback and questions in the comments.
About the Authors
Saman Irfan - Saman is a Senior GTM Specialist Solutions Architect at Amazon Web Services, based in Berlin, Germany. A Data & AI strategist with 10+ years of experience, she drives the technical GTM motion for Data, Analytics, and AI at AWS across EMEA, helping enterprise customers modernize their data architectures and navigate large-scale migrations to unlock the full potential of their data.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article