Migrating to BigQuery: A Practical Playbook

Copying tables from one warehouse to another is the easy part of a migration. The hard parts are everything that surrounds the tables: thousands of lines of SQL written in another dialect, pipelines that load on a schedule, reports that people trust, and the need to prove that after the move every number is still right. A plan for migrating to BigQuery is mostly a plan for those four things.

What Google provides

The BigQuery Migration Service bundles the tooling (Google Cloud: BigQuery migration):

• Assessment. You extract metadata and query logs from the existing warehouse, and it produces a report on storage cost, optimization opportunities and the likely time and effort (Google Cloud: migration assessment).
• SQL translation. Batch translation for scripts in bulk and interactive translation for single queries, converting other dialects to GoogleSQL (Google Cloud: batch SQL translator).
• Data transfer, through the BigQuery Data Transfer Service.

Supported sources include Amazon Redshift, Snowflake, Teradata, Oracle, IBM Netezza and Hadoop. The Migration API itself is free; you pay for the storage its files use. Google also publishes open-source helpers, notably a data validation tool.

The method: one use case at a time

Google's guidance is to migrate in stages, by use case, not all at once (Google Cloud: schema and data transfer). A use case is a set of tables together with what feeds them and what reads them: the sales dashboards, the finance close, the marketing attribution.

Each use case passes through three states:

1. Existing. Everything still runs on the old warehouse.
2. Offloaded. The old warehouse is still loaded as before, the data is copied to BigQuery, and the reports now read from BigQuery.
3. Fully migrated. The pipelines write to BigQuery directly and the old warehouse is no longer involved.

The offloaded state is what makes the method safe. Reports move first, while the old pipelines keep running, so a problem can be reversed by pointing a report back.

Two rules from the same guidance are worth adopting literally:

• Move tables as they are first. Do not redesign the schema during the move. Get the use case running, then optimize.
• Optimize afterwards, when you can measure. Partitioning, clustering and denormalization belong to a later pass.

The playbook

1. Assess. Inventory tables, queries, pipelines and reports. Find what is unused; a migration is the cheapest moment to retire it.
2. Pick the first use case. Choose one that matters, has few dependencies and has an owner who will test it.
3. Transfer the schema and data, unchanged.
4. Translate the SQL and the pipelines.
5. Validate that source and target agree.
6. Run both in parallel for a period, then cut over.
7. Repeat with the next use case, and decommission at the end.

Steps 4 and 5 are where projects go wrong, so they are reproduced below.

Translation and validation, reproduced

The script does both locally. For translation it uses sqlglot, an open-source SQL translator, not Google's service. For validation it copies three tables of the Chinook sample database (Chinook on GitHub) from SQLite to DuckDB, with two defects introduced on purpose, and compares them at three levels.

# Two steps of a warehouse migration, reproduced locally. Step 1 translates source-dialect SQL with
# the open-source sqlglot library; it is not Google's translation service. Step 2 validates a copy
# of three tables, with SQLite as the source and DuckDB as the target, after two deliberate defects.
import hashlib
import sqlite3
import duckdb
import pandas as pd
import sqlglot
from sqlglot.errors import ErrorLevel, UnsupportedError

print("1. Translation to GoogleSQL")
statements = [
    ("redshift", "SELECT NVL(discount, 0) AS discount, GETDATE() AS loaded_at FROM sales"),
    ("redshift", "SELECT TOP 10 order_id::varchar AS order_key FROM sales ORDER BY total DESC"),
    ("redshift", "SELECT customer_id, LISTAGG(product, ', ') WITHIN GROUP (ORDER BY product) FROM sales GROUP BY customer_id"),
    ("snowflake", "SELECT IFF(total > 100, 'large', 'small'), TO_VARCHAR(order_date, 'YYYY-MM-DD') FROM sales"),
]
for dialect, sql in statements:
    try:
        result = sqlglot.transpile(sql, read=dialect, write="bigquery", unsupported_level=ErrorLevel.RAISE)[0]
    except UnsupportedError as error:
        result = f"NOT TRANSLATED: {error}"
    print(f"  {dialect}: {sql}\n    -> {result}")

print("\n2. Validation of the copied tables")
source = sqlite3.connect("../../sqlds/Chinook_Sqlite.sqlite")
target = duckdb.connect()
KEYS = {"Customer": "CustomerId", "Invoice": "InvoiceId", "InvoiceLine": "InvoiceLineId"}
MEASURE = {"Customer": "SupportRepId", "Invoice": "Total", "InvoiceLine": "UnitPrice * Quantity"}

for table, key in KEYS.items():
    frame = pd.read_sql_query(f"SELECT * FROM {table} ORDER BY {key}", source)
    if table == "InvoiceLine":
        frame = frame.iloc[:-40]                               # defect: the last file was never loaded
    if table == "Customer":                                    # defect: a wrong character encoding
        text = frame.select_dtypes(include=["object", "string"]).columns
        frame[text] = frame[text].apply(lambda column: column.map(
            lambda value: value.encode("ascii", "replace").decode() if isinstance(value, str) else value))
    target.execute(f"CREATE TABLE {table} AS SELECT * FROM frame")


def fingerprints(connection, table, key):
    rows = connection.execute(f"SELECT * FROM {table} ORDER BY {key}").fetchall()
    return {row[0]: hashlib.md5("|".join(map(str, row)).encode()).hexdigest() for row in rows}


print(f"  {'table':12} {'row count':22} {'sum of a measure':26} rows whose hash differs")
for table, key in KEYS.items():
    checks = []
    for sql in (f"SELECT COUNT(*) FROM {table}", f"SELECT ROUND(SUM({MEASURE[table]}), 2) FROM {table}"):
        before, after = source.execute(sql).fetchone()[0], target.execute(sql).fetchone()[0]
        checks.append(f"{before:g} = {after:g}" if before == after else f"{before:g} vs {after:g} MISMATCH")
    old, new = fingerprints(source, table, key), fingerprints(target, table, key)
    differing = sum(1 for k in old if old[k] != new.get(k))
    print(f"  {table:12} {checks[0]:22} {checks[1]:26} {differing}")

1. Translation to GoogleSQL
  redshift: SELECT NVL(discount, 0) AS discount, GETDATE() AS loaded_at FROM sales
    -> SELECT COALESCE(discount, 0) AS discount, CURRENT_TIMESTAMP() AS loaded_at FROM sales
  redshift: SELECT TOP 10 order_id::varchar AS order_key FROM sales ORDER BY total DESC
    -> SELECT CAST(order_id AS STRING) AS order_key FROM sales ORDER BY total DESC NULLS FIRST LIMIT 10
  redshift: SELECT customer_id, LISTAGG(product, ', ') WITHIN GROUP (ORDER BY product) FROM sales GROUP BY customer_id
    -> SELECT customer_id, STRING_AGG(product, ', ') WITHIN GROUP (ORDER BY product NULLS LAST) FROM sales GROUP BY customer_id
  snowflake: SELECT IFF(total > 100, 'large', 'small'), TO_VARCHAR(order_date, 'YYYY-MM-DD') FROM sales
    -> NOT TRANSLATED: Argument 'format' is not supported for expression 'ToChar' when targeting BigQuery.

2. Validation of the copied tables
  table        row count              sum of a measure           rows whose hash differs
  Customer     59 = 59                233 = 233                  23
  Invoice      412 = 412              2328.6 = 2328.6            0
  InvoiceLine  2240 vs 2200 MISMATCH  2328.6 vs 2288 MISMATCH    40

What the translation shows

• The first two statements translate cleanly. NVL becomes COALESCE, GETDATE() becomes CURRENT_TIMESTAMP(), the :: cast becomes CAST, and TOP becomes LIMIT.
• The second also gains a clause. The translator added NULLS FIRST. It is correct to do so: Redshift sorts NULL first in descending order by default (AWS: ORDER BY clause), and GoogleSQL sorts it last (Google Cloud: query syntax). Without the clause the query would run and return rows in a different order. Differences in defaults are the dangerous ones, because nothing fails.
• The third is wrong. The output keeps a WITHIN GROUP clause, which GoogleSQL's STRING_AGG does not have; the ordering belongs inside the parentheses (Google Cloud: aggregate functions). The tool reported no problem.
• The fourth is refused. The tool states that it cannot carry the date format across, which is the better kind of failure.

No translator is complete, whichever one you use. Treat translated SQL as a draft: run every statement, and compare its results with the original's.

What the validation shows

The copy was checked at three levels, the same ones Google's Data Validation Tool offers: counts, column aggregates and row comparison (Data Validation Tool on GitHub).

• Invoice passes all three. 412 rows, the same total, no differing rows.
• InvoiceLine fails all three. Forty rows are missing, as if one file had not been loaded. A row count alone catches this.
• Customer passes two and fails the third. The count matches and the summed column matches, yet 23 rows differ. Their text was damaged by a wrong character encoding: names with accents no longer match. A migration signed off on row counts would have shipped it.

The lesson is to validate in layers. Counts catch missing data. Sums catch numeric damage. Only a row-level comparison catches damaged text, shifted timestamps and swapped columns.

What changes besides the syntax

Google's Redshift guide makes the point that the aim is not to map features one to one (Google Cloud: Redshift migration overview). Some habits do not carry over:

• No cluster to size. Capacity planning becomes a choice between paying per query and reserving slots.
• No distribution or sort keys. Their role is played by partitioning and clustering. See partitioning and clustering.
• Bulk changes, not single-row updates. Pipelines written as row-by-row updates need rewriting as set-based merges.
• Cost follows the query. A SELECT * that cost nothing extra on a fixed cluster is billed in full. See BigQuery cost optimization.
• Access is regranted. Users, groups and roles are rebuilt in IAM, which is also a chance to tidy them.

Cutting over

• Run both systems in parallel for at least one full business cycle that includes a month end.
• Compare daily. Automate the validation queries and alert on any difference.
• Freeze changes to the migrating use case in the old system while both run.
• Agree the rollback before the switch: who decides, and how reports are pointed back.
• Decommission deliberately. Keep the old warehouse read-only for an agreed period, then remove it. A warehouse left running "just in case" costs money for years.

Mistakes that recur

• Migrating everything at once. One failure then blocks all of it.
• Redesigning while moving. When numbers differ, you cannot tell whether the move or the redesign caused it.
• Trusting translated SQL because it runs.
• Validating with row counts only.
• Forgetting the consumers. Dashboards, exports and spreadsheets that query the old system are part of the migration.

For loading the data, see loading data into BigQuery. For how the three main warehouses differ, see BigQuery vs Snowflake vs Redshift.

Warehouse design and pipeline migration are covered in the Data Engineering course.

Sources

• Introduction to BigQuery migration (Google Cloud Documentation) — Google Cloud
• Schema and data transfer overview (Google Cloud Documentation) — Google Cloud
• Migration assessment (Google Cloud Documentation) — Google Cloud
• Migrate code with the batch SQL translator (Google Cloud Documentation) — Google Cloud
• Amazon Redshift to BigQuery migration: Overview (Google Cloud Documentation) — Google Cloud
• Data Validation Tool (GitHub) — GitHub
• Query syntax (Google Cloud Documentation) — Google Cloud
• ORDER BY clause (Amazon Redshift Database Developer Guide) — AWS Documentation
• Aggregate functions (Google Cloud Documentation) — Google Cloud
• Chinook sample database (GitHub) — GitHub