How to Migrate from Redshift to BigQuery in PostgreSQL

Why migrate from Redshift to BigQuery?

Lower maintenance, serverless scalability, and native BI integration often justify the switch. BigQuery’s pay-as-you-go model eliminates cluster tuning and vacuuming tasks.

What are the high-level steps?

1) Extract data from Redshift to Amazon S3, 2) Transfer files to Google Cloud Storage (GCS), 3) Load data into BigQuery, 4) Validate and cut over.

How do I export Redshift tables quickly?

Use UNLOAD to write compressed, partitioned Parquet files to S3.Parquet preserves types and speeds BigQuery loading.

UNLOAD ('SELECT * FROM customers')
TO 's3://my-bucket/migration/customers_'
FORMAT AS PARQUET
PARALLEL ON
ALLOWOVERWRITE;

How do I transfer files from S3 to GCS?

gsutil rsync copies entire folders while preserving file names and compression. Ensure IAM roles allow cross-cloud access or download and re-upload locally.

gsutil rsync -r s3://my-bucket/migration gs://my-gcs-bucket/migration

What is the BigQuery load command?

bq load ingests Parquet files into native tables.Use wildcard URIs for partitioned exports.

bq load --source_format=PARQUET ecommerce.customers \
gs://my-gcs-bucket/migration/customers_*

How do I preserve primary keys and constraints?

BigQuery lacks traditional PK constraints. Emulate uniqueness with scheduled queries that raise alerts when COUNT(id) ≠ COUNT(DISTINCT id).

How is incremental sync handled?

Add an updated_at column to each table and schedule nightly UNLOADs filtering on last export timestamp.Use BigQuery MERGE to upsert.

bq query """
MERGE ecommerce.customers T
USING staging.customers S
ON T.id = S.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT ROW"""

When should I cut over?

After full reload, run dual-writes for 24 hours, compare row counts, and validate critical queries. Switch applications once parity is confirmed.

Best practices

• Export in Parquet for type fidelity. • Use parallel UNLOAD to speed extraction. • Load into staging tables first, then promote. • Automate validation with COUNT(*) and MIN/MAX timestamps.

.