Migrating from Amazon Redshift to Google BigQuery: An Engineering Framework

Migrating a production data warehouse from Amazon Redshift to Google BigQuery is an architectural transformation, not a simple file transfer between Amazon S3 and Google Cloud Storage (GCS). Moving raw Parquet files usually accounts for less than 15% of the project effort. The remaining 85% involves redesigning table storage structures, refactoring procedural SQL, rebuilding Change Data Capture (CDC) pipelines, reconfiguring orchestration and BI tools, and implementing FinOps controls for a serverless execution model.

1. Business Rationale and Decision Matrix: When to Migrate vs. Alternatives

A database migration carries operational risk, dual-running infrastructure costs, and AWS data egress fees. Before starting a migration, evaluate whether moving to BigQuery solves your core problem or if an in-place upgrade is more cost-effective.

When Migrating to BigQuery Makes Sense

• Hardware and Queue Contention: Your engineering team spends significant time tuning Redshift Workload Management (WLM) queues, managing concurrency scaling credits, fixing disk-full errors during large joins, or resizing clusters.
• Unpredictable or Spiky Workloads: You run heavy ELT batches alongside ad-hoc analyst queries and BI dashboards. BigQuery allows you to isolate these workloads into separate slot reservations that autoscale independently in seconds without copying underlying data.
• Ecosystem Consolidation: Your organization relies on Google Marketing Platform (Google Analytics 4, Google Ads, Campaign Manager 360), Firebase, or Vertex AI, where zero-copy data sharing and native ML integration eliminate custom ingestion pipelines.
• High Maintenance Overhead for Semi-Structured Data: Your pipelines process massive volumes of nested JSON event data (telemetry, clickstreams), and maintaining SUPER parsing or flattened relational bridge tables in Redshift slows down feature delivery.

When NOT to Migrate (and Practical Alternatives)

Scenario	Why Full Migration Fails	Recommended Alternative
Legacy Redshift Hardware Bottlenecks	Teams on older DC2 or DS2 nodes blame Redshift for slow performance and local storage limits.	Upgrade to Redshift RA3 or Redshift Serverless. Decoupling storage and compute inside AWS takes hours instead of months and automates background VACUUM for most workloads.
High-Frequency Point Lookups & Sub-Second Serving	BigQuery has a base scheduling overhead (~100–300 ms per query). It is not designed to serve thousands of sub-second single-row lookups per second for customer-facing web apps.	Keep operational serving in AWS (RDS/Aurora/DynamoDB) or pair BigQuery with Cloud Bigtable / AlloyDB / Cloud SQL or BigQuery BI Engine for cached dashboard queries.
Data Must Stay in AWS S3 (Compliance or Egress Cost)	Moving petabytes out of AWS S3 incurs ~USD 0.08–0.09 per GB in AWS egress fees, or security policies forbid moving raw data out of AWS regions.	Use BigQuery Omni. Run the BigQuery compute engine directly inside AWS (aws-us-east-1 or aws-ap-northeast-1) to query Parquet/Iceberg tables on S3 in place. (Note: transferring query results back to a standard GCP region still incurs AWS egress fees for the transferred result size).
Heavy Reliance on Custom C/Python UDFs or Postgres Locks	Tightly coupled procedural workflows that depend on Postgres-specific transaction locks or synchronous Python libraries.	Refactor into dbt or Apache Spark on AWS first, or migrate tables to Apache Iceberg on S3 before evaluating a warehouse engine switch.

The Migration TCO Equation

To calculate the return on investment (ROI), account for one-time migration costs:

Migration Cost = AWS Egress Fees + Dual-Run Cloud Spend (1–3 Months) + Engineering Refactoring Hours

• AWS Data Egress Warning: Extracting data from Amazon S3 or Redshift to the public internet or Google Cloud costs roughly USD 80,000 to 90,000 per Petabyte in AWS data transfer out fees. Always export data as Snappy-compressed Parquet (which reduces raw volume by 60–75% while preserving types), and ensure Redshift writes to S3 via a free S3 Gateway VPC Endpoint rather than a NAT Gateway (which adds an extra USD 0.045 per GB processing charge).

2. End-to-End Migration Lifecycle (6 Phases)

A reliable migration follows a phased domain rollout rather than a single “big bang” switchover.

1. Phase 0: Pre-Migration Discovery and Log Persistence (Weeks 1–4)Critical prerequisite: Redshift STL_ system views (STL_QUERY, STL_SCAN) only retain 2 to 5 days of history on disk, and Redshift Serverless SYS_QUERY_HISTORY retains up to 7 days. Before running an assessment, enable Redshift Audit Logging to S3 or schedule daily unloads of STL_/SYS_ views for at least 30 days. Use these persisted logs to build table-to-dashboard lineage, identify unused “zombie” tables, and group active workloads into isolated migration waves.
2. Phase 1: Cloud Foundation, Security Mapping, and FinOps Setup (Weeks 3–4)Provision Google Cloud projects (separating Storage/Data projects from Compute/Billing projects), configure cross-cloud network connectivity and IAM federation, map Redshift users/groups to Google Cloud IAM and BigQuery Row-Level Security, and configure slot reservations and cost quotas.
3. Phase 2: Schema Redesign and Historical Backfill (Weeks 4–6)Convert Redshift DDLs to BigQuery DDLs, replacing DISTKEY/SORTKEY with Partitioning and Clustering, and preserving informational PRIMARY KEY / FOREIGN KEY NOT ENFORCED constraints. Unload historical tables from Redshift to S3 as Parquet, transfer them to GCS, and load them into BigQuery.
4. Phase 3: Pipeline, SQL, CDC, and Orchestration Refactoring (Weeks 6–10)Translate SQL views, stored procedures, Airflow DAGs, and dbt models (dbt-redshift to dbt-bigquery). Rebuild incremental ingestion pipelines using the BigQuery Storage Write API (native CDC) or partition-pruned batch MERGE statements.
5. Phase 4: Automated Validation and Shadow Running (Weeks 10–12)Run Redshift and BigQuery pipelines in parallel. Execute automated row-count, schema, column-aggregation (SUM, MIN, MAX), and row-hash validations across both systems until discrepancy rates reach zero.
6. Phase 5: BI Repointing, Cutover, Rollback Window, and Decommissioning (Weeks 13–14)Switch BI tools (Looker, Tableau, Power BI) and downstream consumers to BigQuery per domain wave. Keep Redshift in read-only standby (or maintain dual-ingest) for a 7-to-14-day rollback window before taking a final S3 archival snapshot and shutting down Redshift clusters.

3. Architectural Shift: Redshift vs. BigQuery Under the Hood

Even with RA3 nodes and Redshift Serverless, Redshift relies on cluster-based data distribution. BigQuery separates compute (Dremel execution engine), storage (Colossus distributed file system), and shuffle memory (Jupiter petabit network) across a multi-tenant infrastructure.

Architectural Layer	Amazon Redshift (RA3 / Serverless)	Google BigQuery	Required Engineering Change
Compute Unit	Node slices (RA3) or Redshift Processing Units (RPUs)	Slots (virtual CPUs with dedicated RAM and network throughput)	Stop tuning queues for fixed hardware; manage slot reservations or scan quotas instead.
Data Distribution	DISTKEY (KEY, EVEN, ALL, AUTO)	Automatic block distribution on Colossus; optional Clustering (up to 4 columns)	Drop DISTKEY logic. Use clustering on high-cardinality filter and join keys.
Physical Ordering	SORTKEY (Compound or Interleaved)	Partitioning (1 column, up to 10,000 partitions per table) + Clustering	Convert SORTKEY into one date/timestamp/integer partition column and up to 4 cluster columns.
Table Constraints	Informational PRIMARY KEY, FOREIGN KEY, UNIQUE (not enforced on load, used by planner)	Informational PRIMARY KEY NOT ENFORCED and FOREIGN KEY NOT ENFORCED	Keep constraints in DDL adding NOT ENFORCED—BigQuery requires PKs for native CDC and uses PK/FK for join elimination.
Column Compression	Manual or automatic ENCODE (AZ64, LZO, ZSTD)	Automatic columnar compression in Capacitor format	Remove all ENCODE clauses from DDL scripts.
Storage Maintenance	Background Auto-Vacuum or manual VACUUM and ANALYZE	Automatic background storage optimization and metadata collection	Remove VACUUM and ANALYZE tasks from Airflow/orchestration DAGs.
Concurrency Control	Workload Management (WLM) queues	Fair scheduler across reservations; isolated slot pools per workload (ELT, BI, Ad-hoc)	Separate heavy ELT jobs and BI dashboards into distinct GCP projects or slot reservations.

4. Native Google Cloud Migration Toolchain (2026 Stack)

Before writing custom migration frameworks, use the native tools included in the BigQuery Migration Service (BQMS). The BQMS tools are free to use (you only pay for standard GCS and BigQuery storage and compute).

• BigQuery Migration Assessment: Extracts metadata and persisted query logs from Redshift (SVV_TABLE_INFO, STL_QUERY, STL_SCAN, SYS_QUERY_HISTORY, or S3 Audit Logs). It generates a report showing table sizes, query complexity, tables that would exceed BigQuery’s 10,000-partition limit, and an estimate of required BigQuery slots and storage costs.
• BigQuery Migration Lineage: Parses Redshift execution logs to build a directed graph of table dependencies. Use this to identify unused tables and isolate self-contained pipeline groups for phased migration waves.
• Batch and Interactive SQL Translator (with MCP Server Support): Converts Redshift SQL, DDL, DML, and PL/pgSQL stored procedures into GoogleSQL. It supports YAML configuration macros to map schema names and replace custom patterns automatically. In 2026, BQMS also provides a Model Context Protocol (MCP) server to integrate SQL translation directly into AI-assisted IDE and CI/CD workflows.
• BigQuery Data Transfer Service (DTS) for Redshift: Automates initial and incremental batch transfers. DTS connects to Redshift via JDBC (over public IP or private VPC Network Attachment), runs UNLOAD to an S3 staging bucket, transfers files to GCS via Storage Transfer Service, and loads them into BigQuery.
• Storage Transfer Service (STS): Moves Parquet files from Amazon S3 to GCS on a schedule or via event-driven AWS SQS notifications. It supports AWS IAM role federation (AssumeRoleWithWebIdentity), avoiding static AWS keys in Google Cloud.
• BigQuery Permission Mapper: Reads Redshift users, groups, and GRANT rules and generates Terraform or JSON mapping templates for Google Cloud IAM roles and BigQuery dataset access controls.
• Google PSO Data Validation Tool (google-pso-data-validator): An open-source Python CLI tool built by Google Professional Services that connects to Redshift and BigQuery simultaneously to validate schemas, row counts, column aggregations, and SHA-256 row hashes.

5. Schema Redesign and Data Type Mapping

Data Type Mapping and Edge Cases

Redshift Data Type	BigQuery Data Type	Migration Nuance & Required Action
SMALLINT, INTEGER, BIGINT	INT64	Direct mapping. All integer types occupy 8 bytes logically in BigQuery (compressed physically).
DECIMAL(p,s) / NUMERIC(p,s)	NUMERIC or BIGNUMERIC	Redshift DECIMAL supports up to 38 digits of precision. BigQuery NUMERIC supports 38 digits (scale up to 9). If Redshift scale exceeds 9 digits, map to BIGNUMERIC (76 digits precision, scale up to 38) to prevent rounding errors.
REAL, DOUBLE PRECISION	FLOAT64	Direct mapping. Do not use FLOAT64 for financial amounts; convert to NUMERIC.
VARCHAR(n), CHAR(n), TEXT	STRING	BigQuery STRING supports up to 10 MB per cell (and 100 MB maximum row size), compared to Redshift’s 65,535-byte VARCHAR limit. CHAR(n) trailing spaces are stripped on export unless explicitly preserved.
TIMESTAMP (without timezone)	DATETIME	Critical trap: Do not map timezone-naive TIMESTAMP to BigQuery TIMESTAMP unless the source data is strictly UTC. Map local or naive timestamps to DATETIME.
TIMESTAMPTZ	TIMESTAMP	Maps directly to an absolute point in time stored in UTC.
SUPER	JSON or repeated STRUCT	Redshift SUPER holds up to 16 MB of semi-structured data. If schema is dynamic, map to native BigQuery JSON. If schema is fixed and queried frequently, parse into repeated STRUCT arrays for columnar pruning.
GEOMETRY, GEOGRAPHY	GEOGRAPHY	Redshift GEOMETRY uses planar coordinates; BigQuery GEOGRAPHY uses WGS84 spherical coordinates. Convert SRIDs to WGS84 (SRID 4326) before export.

Translating DISTKEY, SORTKEY, and Constraints

1. Partitioning (Max 1 Column): Choose the primary date, timestamp, or integer column used in WHERE filters (typically the first column in a Redshift Compound SORTKEY).
   • Hard Limit: A BigQuery table supports a maximum of 10,000 partitions. If you partition by day, 10,000 partitions cover ~27 years. If a Redshift table has 15 years of hourly data (over 130,000 hours), change the partitioning granularity from HOUR to DAY or MONTH.
2. Clustering (Up to 4 Columns): Combine the former Redshift DISTKEY and remaining SORTKEY columns into the CLUSTER BY clause, ordered from most frequently filtered/aggregated to least frequently used (for example, CLUSTER BY tenant_id, event_type, user_id).
3. Unenforced Primary and Foreign Keys: Preserve Redshift relational metadata in BigQuery DDL:
   SQLCREATE TABLE my_dataset.orders ( order_id INT64 NOT NULL, customer_id INT64 NOT NULL, order_date DATE, total_amount NUMERIC, PRIMARY KEY (order_id) NOT ENFORCED, FOREIGN KEY (customer_id) REFERENCES my_dataset.customers(customer_id) NOT ENFORCED ) PARTITION BY order_date CLUSTER BY customer_id; Declaring NOT ENFORCED keys costs nothing at write time, enables the BigQuery query optimizer to skip unnecessary joins on views, and is mandatory if you enable Storage Write API CDC.
4. Denormalization (Nested and Repeated STRUCT Columns): In Redshift, joining a 10-billion-row orders table with a 50-billion-row order_items table works fast only if both tables share the exact same DISTKEY (order_id). In BigQuery, shuffling 50 billion rows across the network during a join consumes substantial slot capacity. For core fact tables, pre-nest line items as a repeated STRUCT array column (ARRAY<STRUCT<...>>) inside the parent orders table to eliminate the runtime join completely.

6. Common Migration Problems and Engineering Solutions

Problem 1: High-Frequency CDC and Small DML Updates

• Cause: Teams migrate pipelines that run INSERT, UPDATE, or DELETE statements every 1–5 minutes. In BigQuery On-Demand mode, a MERGE or UPDATE scans the target table or partition, causing cost spikes and hitting concurrent mutating DML limits (max 20 queued mutating DML statements per table).
• Solution (Two Mutually Exclusive Patterns):
  • Pattern A: Real-Time / Near-Real-Time Native CDC (Storage Write API):
    1. Define a PRIMARY KEY (...) NOT ENFORCED on the target BigQuery table (supports composite keys up to 16 columns).
    2. Stream row changes via the BigQuery Storage Write API (gRPC default stream) in Protobuf format (Apache Arrow is not supported for CDC), passing pseudocolumns _CHANGE_TYPE (UPSERT or DELETE) and _CHANGE_SEQUENCE_NUMBER.
    3. Set the table’s max_staleness option (for example, OPTIONS(max_staleness = INTERVAL 15 MINUTE)). BigQuery applies delta changes in the background while queries read fresh data within your SLA.
    4. Crucial Limitation: While a table has active Storage Write API CDC, BigQuery blocks all SQL mutating DML statements (MERGE, UPDATE, DELETE) on that table.
  • Pattern B: Batch Micro-Batching via SQL MERGE:If you require SQL MERGE flexibility, batch updates into 15–60 minute intervals, stage incoming deltas into an append-only table, and ensure the MERGE ... ON clause explicitly filters the target table by constant partition boundaries to trigger partition pruning.

Problem 2: Correlated Subqueries and Procedural Loops

• Cause: Redshift optimizers handle certain correlated EXISTS / IN subqueries and multi-statement temp-table pipelines on local node disks. BigQuery restricts complex correlated subqueries that cannot be flattened into standard joins, throwing the error: Correlated subqueries that reference other tables are not supported unless they can be de-correlated.
• Solution:
  1. Rewrite correlated subqueries using LEFT JOIN or INNER JOIN combined with QUALIFY ROW_NUMBER() OVER (...) = 1.
  2. Replace cursor-based FOR loops inside PL/pgSQL stored procedures with set-based array expressions (ARRAY_AGG, UNNEST) or single-pass window functions.

Problem 3: SQL Dialect Traps and Implicit Casting

• Cause: Subtle behavior differences between Postgres-derived Redshift SQL and GoogleSQL break views or silently alter numbers.
• Solution Reference Table:

Feature	Redshift Syntax / Behavior	BigQuery GoogleSQL Solution
Integer Division	5 / 2 returns 2 (truncates to integer)	5 / 2 returns 2.5 (FLOAT64). Use DIV(5, 2) or CAST(TRUNC(5 / 2) AS INT64) to preserve integer division.
String Aggregation	LISTAGG(col, ',') WITHIN GROUP (ORDER BY id)	STRING_AGG(col, ',' ORDER BY id)
Null Handling	NVL(a, b)	IFNULL(a, b) or COALESCE(a, b)
Date Arithmetic	DATEADD(day, 7, my_date)	DATE_ADD(my_date, INTERVAL 7 DAY)
Current Timestamp	SYSDATE or GETDATE()	CURRENT_TIMESTAMP() or CURRENT_DATETIME()
Safe Type Casting	TRY_CAST(col AS INT)	SAFE_CAST(col AS INT64)
Late-Binding Views	CREATE VIEW ... WITH NO SCHEMA BINDING	Remove clause. BigQuery views validate underlying schemas dynamically on read.

Problem 4: UNLOAD Performance Starvation and Missing Partition Columns

• Cause: Running massive UNLOAD queries to export historical data competes with live production queries in Redshift, causing WLM queue timeouts. Additionally, two common UNLOAD mistakes break downstream loads:
  1. Specifying ZSTD or GZIP alongside FORMAT AS PARQUET throws a syntax error because Redshift Parquet unloads only use native SNAPPY compression.
  2. Using PARTITION BY (event_date) without the INCLUDE keyword drops event_date from the Parquet file schema and writes it only to the S3 folder path.
• Solution:
  1. Run UNLOAD from an isolated WLM queue with low concurrency or a separate Redshift Serverless consumer workgroup via Redshift Data Sharing.
  2. Use valid Parquet syntax and include partition columns inside the file payload if partitioning the S3 export:
     SQLUNLOAD ('SELECT * FROM schema.events') TO 's3://my-migration-bucket/events/' IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftExportRole' FORMAT AS PARQUET MAXFILESIZE 512 MB PARTITION BY (event_date) INCLUDE;
  3. Serialize inconsistent SUPER columns using JSON_SERIALIZE(super_col) during UNLOAD and ingest them into a BigQuery JSON column.

Problem 5: Security and Access Control Translation

• Cause: Redshift manages permissions inside the database (CREATE USER, ALTER GROUP, GRANT SELECT ON TABLE). BigQuery manages permissions at the Google Cloud IAM level (Project, Dataset, Table), where giving a user roles/bigquery.dataViewer at the project level exposes every dataset in that project.
• Solution:
  1. Group tables by security boundary into separate BigQuery Datasets (datasets are the primary unit of access control in BigQuery).
  2. Use Authorized Views or Authorized Routines when users need to query aggregated views without having direct SELECT access to the underlying raw PII tables.
  3. Implement BigQuery Row-Level Security (CREATE ROW ACCESS POLICY) and Column-Level Security (Data Catalog Policy Tags) to replicate Redshift RLS and column grants.

7. Refactoring Orchestration (Airflow / dbt) and BI Layers

Engineers executing migrations frequently struggle with downstream tooling after the tables are already in BigQuery.

1. Migrating dbt Projects (dbt-redshift to dbt-bigquery)

• Model Configurations: Replace Redshift-specific dist, sort, and sort_type parameters in dbt_project.yml and model .sql headers with partition_by and cluster_by:
  SQL-- Before (dbt-redshift) {{ config(materialized='incremental', dist='user_id', sort=['event_date', 'event_type']) }} -- After (dbt-bigquery) {{ config( materialized='incremental', incremental_strategy='merge', unique_key='event_id', partition_by={'field': 'event_date', 'data_type': 'date', 'granularity': 'day'}, cluster_by=['user_id', 'event_type'], require_partition_filter=true ) }}
• Incremental Strategies: In dbt-redshift, the default incremental strategy is often delete+insert. In dbt-bigquery, use merge (with explicit partition predicates via incremental_predicates) or insert_overwrite (which replaces entire partitions atomically without scanning unrelated partitions).

2. Migrating Apache Airflow / Cloud Composer DAGs

• Replace S3ToRedshiftOperator and RedshiftDataOperator / PostgresOperator with GCSToBigQueryOperator and BigQueryInsertJobOperator from apache-airflow-providers-google.
• Job Location and Priority: Always pass location and configuration={'query': {'priority': 'BATCH'}} or assign the Airflow Service Account to a dedicated ELT slot reservation so background DAGs do not compete with interactive users.

3. Migrating BI Tools (Looker, Tableau, Power BI)

• Enable the BigQuery Storage Read API: Standard JDBC/ODBC pagination bottlenecks when pulling more than 100,000 rows into Tableau Extracts or Power BI Import mode. Ensure the BI connector has the roles/bigquery.readSessionUser IAM role so it streams results in parallel over gRPC via the Storage Read API.
• Convert Live Unfiltered Extracts: If Tableau or Power BI refreshes a full-table extract every hour without a partition filter, move that refresh job to a slot-reserved Compute Project or point the dashboard at a pre-aggregated BigQuery Materialized View.
• Looker (LookML) Dialect Switch: Change the connection dialect from redshift to bigquery_standard_sql. Run the LookML Validator to catch raw SQL blocks inside sql: definitions (specifically DATEADD, DATEDIFF, and LISTAGG).

8. FinOps Framework: Controlling Costs in BigQuery

Migrating from a fixed-cost Redshift cluster to BigQuery without cost guardrails is the primary cause of budget overruns. Set up the following FinOps architecture before migrating users.

1. Choosing the Right Compute Pricing Model (2026 Pricing)

BigQuery offers two compute billing models that you can mix and match across different GCP projects linked to the same organization:

Workload Type	Recommended Billing Model	Why It Fits
Ad-hoc Exploration & Low-Volume Dev/Test	On-Demand (USD 6.25 per TiB scanned, first 1 TiB/month free)	Zero idle cost; you pay only when queries execute. Must be paired with strict per-query scan limits.
Predictable Production ELT & Heavy BI	BigQuery Editions (Standard, Enterprise, Enterprise Plus)	Uses Autoscaling Slots (billed per slot-hour in 1-second increments, min 60 seconds). Prevents runaway scan bills when analysts query multi-terabyte tables repeatedly.

• Architectural Best Practice: Store all tables in a central Storage Project, and create separate Compute Projects for ELT (elt-compute-proj), BI Dashboards (bi-compute-proj), and Ad-Hoc Analysts (adhoc-compute-proj). Assign dedicated slot reservations to ELT and BI so an unoptimized analyst query never delays a production pipeline or slows down executive dashboards.

2. Switching to Physical (Compressed) Storage Billing

By default, BigQuery datasets use Logical Storage Billing (charging ~USD 0.02/GiB/month for active uncompressed bytes and ~USD 0.01/GiB/month for long-term untouched data). Because migrated data warehouses typically achieve a 4:1 to 8:1 compression ratio in BigQuery’s Capacitor format, Logical billing often costs 2x to 3x more than necessary.

• Action: Switch production datasets with a compression ratio greater than 2.5:1 to Physical Storage Billing:
  SQLALTER SCHEMA my_dataset SET OPTIONS(storage_billing_model = 'PHYSICAL'); (Note: Changing a dataset’s storage billing model takes 24 hours to take effect and locks the setting for 14 days).
• Isolate High-Churn Staging Datasets for Time Travel Optimization: Under Physical billing, Time Travel (2 to 7 days) and Fail-Safe (7 days) bytes are billed separately at active physical rates. Because max_time_travel_hours is a dataset-level setting (not table-level), place high-churn staging or CDC tables into a dedicated dataset (raw_staging) and reduce its Time Travel window to 48 hours:
  SQLALTER SCHEMA raw_staging SET OPTIONS(max_time_travel_hours = 48); This avoids paying for 7 days of overwritten staging blocks while preserving the full 168-hour (7-day) recovery window on your core analytical dataset.

3. Mandatory Technical Cost Guardrails

1. Enforce Partition Filters on Large Tables: Run ALTER TABLE my_dataset.events SET OPTIONS (require_partition_filter = true) on every partitioned table over 10 GB. Any query without a WHERE clause on the partition column fails immediately at validation time with zero bytes billed.
2. Set Maximum Bytes Billed: Configure a default maximum_bytes_billed quota (for example, 100 GB per query) in dbt profiles, BI JDBC/ODBC connections, and user settings for On-Demand projects.
3. Monitor Slot and Scan Consumption via INFORMATION_SCHEMA: Deploy automated alerts on region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT to flag queries scanning more than 1 TB or consuming excessive slot-hours.

9. Cutover and Rollback Strategy

Never decommission Redshift on the day of cutover. Use a structured cutover and rollback protocol per domain wave:

1. Dual-Run Freeze Window (T-7 Days to T-0): Both Redshift and BigQuery ingest and transform data in parallel. Automated validation (google-pso-data-validator) runs daily.
2. Cutover Execution (T-0):
   • Pause orchestration schedules in Redshift for the target domain wave.
   • Revoke INSERT/UPDATE/DELETE permissions on the migrated Redshift tables (REVOKE WRITE) so no shadow writes occur in Redshift, leaving tables in READ ONLY mode.
   • Repoint BI connections and downstream reverse-ETL consumers to BigQuery.
3. Rollback Window (T+1 to T+14 Days):
   • Keep the raw ingestion files landing in Amazon S3 (or mirror GCS back to S3 via STS) during the first 14 days after cutover.
   • Rollback Trigger: If a critical financial discrepancy or performance blocker occurs in BigQuery that cannot be fixed within your incident SLA, re-enable the paused Redshift DAGs to catch up from the S3 staging bucket and switch the BI connection string back to Redshift in minutes.
4. Decommission (T+15 Days): Once the 14-day window passes with zero rollbacks, export a final cluster snapshot to Amazon S3 Glacier and terminate the Redshift cluster or Serverless workgroup.

10. Migration Anti-Patterns (What Not to Do)

• Anti-Pattern 1: The 1:1 “Lift-and-Shift” Schema CopyMoving 15 normalized dimension and fact tables as-is without adding Partitioning, Clustering, NOT ENFORCED key constraints, or denormalizing high-volume 1-to-many relationships into repeated STRUCT arrays. This turns fast Redshift co-located DISTKEY joins into expensive distributed network shuffles in BigQuery.
• Anti-Pattern 2: Using SELECT * LIMIT 10 on Unclustered Tables to Preview DataIn Redshift, SELECT * FROM table LIMIT 10 stops reading fast. According to BigQuery documentation, applying a LIMIT clause to a SELECT * query on a non-clustered table does not reduce the bytes billed in On-Demand mode—you are billed for reading all columns across the table or partition. Use the free Table Preview tab in the Google Cloud Console or TABLESAMPLE SYSTEM (1 PERCENT).
• Anti-Pattern 3: Translating Redshift System Views (PG_TABLE_DEF, SVV_COLUMNS)Attempting to recreate Postgres system catalogs in BigQuery. Instead, refactor metadata scripts to query BigQuery’s INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.COLUMNS, and INFORMATION_SCHEMA.PARTITIONS.
• Anti-Pattern 4: Running Validation Only on Row CountsValidating a migration by checking SELECT COUNT(*) alone misses silent data corruption such as truncated precision in NUMERIC fields, shifted timezones (TIMESTAMP vs DATETIME), and NULL vs empty string ('') differences. Always validate column sums and row-level hashes.
• Anti-Pattern 5: Dropping Redshift Tables Based on a 5-Day STL_QUERY DumpAssuming STL_QUERY contains full historical usage. Because STL_ tables only hold 2 to 5 days of logs, relying on a single snapshot deletes tables used by monthly closing pipelines.

11. Production Migration Checklist

Phase 0 & 1: Assessment, Foundation, and FinOps

• [ ] Enable Redshift Audit Logging to S3 or persist STL_QUERY / SYS_QUERY_HISTORY logs for at least 30 days, then run BigQuery Migration Assessment and Lineage.
• [ ] Archive or drop unused Redshift tables that have zero read queries across 90 days of persisted S3 audit logs.
• [ ] Set up separated GCP Storage, ELT Compute, BI Compute, and Ad-hoc Compute projects with IAM groups mapped via BigQuery Permission Mapper.
• [ ] Create a dedicated raw_staging dataset (max_time_travel_hours = 48) separated from the core analytical dataset (max_time_travel_hours = 168).
• [ ] Configure an Amazon S3 Gateway VPC Endpoint in AWS to avoid NAT Gateway fees during UNLOAD.
• [ ] Choose compute pricing (On-Demand with maximum_bytes_billed quotas vs. BigQuery Editions with Autoscaling Slots) and set storage_billing_model = 'PHYSICAL' on datasets where compression exceeds 2.5:1.

Phase 2: Schema Redesign and Historical Backfill

• [ ] Map Redshift TIMESTAMP (without timezone) to DATETIME and TIMESTAMPTZ to TIMESTAMP.
• [ ] Verify that no target table exceeds 10,000 partitions; adjust partition granularity (DAY vs MONTH) if needed.
• [ ] Define PARTITION BY (1 column), CLUSTER BY (up to 4 columns), and PRIMARY KEY / FOREIGN KEY (...) NOT ENFORCED on target tables.
• [ ] Enable require_partition_filter = true on all partitioned tables larger than 10 GB.
• [ ] Export historical Redshift data to S3 in Snappy-compressed Parquet format (FORMAT AS PARQUET MAXFILESIZE 512 MB, adding INCLUDE if using PARTITION BY) and transfer to GCS via Storage Transfer Service or BigQuery DTS.

Phase 3: Pipelines, SQL, Orchestration, and CDC

• [ ] Run Redshift DDL/DML and PL/pgSQL scripts through the BQMS Batch SQL Translator with custom YAML macro rules.
• [ ] Refactor integer division (/ to DIV()), correlated subqueries, and cursor loops into set-based GoogleSQL.
• [ ] Migrate dbt models (dbt-redshift to dbt-bigquery), replacing dist/sort configs with partition_by and cluster_by blocks, and update Airflow operators to apache-airflow-providers-google.
• [ ] For real-time tables, implement BigQuery Storage Write API (gRPC default stream, Protobuf format, _CHANGE_TYPE) with PRIMARY KEY (...) NOT ENFORCED and max_staleness, ensuring no SQL MERGE/UPDATE/DELETE jobs target those CDC tables.

Phase 4 & 5: Validation, BI Cutover, and Decommission

• [ ] Execute google-pso-data-validator across all migrated tables to verify row counts, numeric aggregations (SUM, AVG, MIN, MAX), and sampled SHA-256 row hashes.
• [ ] Run parallel shadow pipelines for 7–14 days and compare daily query outputs and slot consumption against assessment estimates.
• [ ] Repoint BI tools (Looker, Tableau, Power BI) to the dedicated BigQuery BI Compute project with roles/bigquery.readSessionUser enabled for the BigQuery Storage Read API.
• [ ] Set Redshift tables to read-only during the 14-day rollback window, keep raw S3 ingestion active, take a final archival snapshot to Amazon S3 Glacier, and terminate Redshift clusters/workgroups.