BQBrains | Migrating to BigQuery: Assessment, SQL Translation, and Data Validation End to End

Most BigQuery migrations do not fail on the load step. They fail on the thousands of lines of legacy SQL nobody wants to read, and on the awkward meeting where someone asks whether the new numbers actually match the old ones. Google's BigQuery Migration Service exists for exactly those two problems, and it is free to use — you pay only for the BigQuery resources the migration consumes.

This tutorial walks the whole path: assess, translate, move, validate, cut over. Examples assume a Snowflake source, but the same tooling covers Amazon Redshift, Teradata, Netezza, Oracle, Hive, Vertica, SQL Server, and several others.

Step 0: Scope the estate before you touch anything

Before any tool runs, get three inventories out of the source system:

Objects — tables, views, materialized views, stored procedures, UDFs, and their sizes.
Workloads — which queries actually run, how often, and by whom. In Snowflake this is SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY; in Redshift, SYS_QUERY_HISTORY.
Consumers — BI tools, reverse-ETL jobs, notebooks, cron scripts, and the one Excel workbook with an ODBC connection that somebody in finance depends on.

A rule of thumb from real projects: 20–30% of the objects in a mature warehouse have not been queried in twelve months. Migrating them is pure cost. Decide early what gets retired rather than translated.

Step 1: Run a migration assessment

The migration assessment takes metadata and query logs extracted from the source and produces a Looker Studio report: object counts, SQL complexity, unsupported constructs, storage and compute profiles, and a rough BigQuery sizing.

Extract metadata with the dwh-migration-dumper tool (a Google-published open-source extractor):

# Snowflake metadata + query history
dwh-migration-dumper \
  --connector snowflake \
  --host <account>.snowflakecomputing.com \
  --database SNOWFLAKE \
  --user MIGRATION_SVC \
  --password "$SNOWFLAKE_PASSWORD" \
  --assessment \
  --output ./snowflake-metadata.zip

Upload the archive to Cloud Storage and start the assessment from the BigQuery console under Migration → Assessment, pointing it at the bucket. It writes results into a BigQuery dataset and gives you a report link.

What to actually read in the output:

SQL complexity distribution. The count of statements flagged as complex is the single best predictor of migration effort.
Unsupported or partially supported features. Recursive CTEs, proprietary semi-structured functions, session variables, and stored-procedure-heavy code all surface here.
Storage vs. compute split. This tells you whether your BigQuery bill will be dominated by scanned bytes or by transformation slots, which in turn drives the on-demand vs. Editions decision.

Step 2: Translate the SQL

The Migration Service offers three translation surfaces. Use all three, for different jobs.

Batch SQL translation

Best for the bulk of the codebase. Drop .sql files in a Cloud Storage bucket and run:

bq mk --transfer_config \
  --project_id=my-project \
  --data_source=dts_migration \
  --display_name="snowflake-batch-translation" \
  --params='{
    "source_dialect": "snowflake",
    "gcs_source_path": "gs://mig-bucket/source-sql/",
    "gcs_target_path": "gs://mig-bucket/translated-sql/",
    "default_database": "ANALYTICS",
    "schema_search_path": "PUBLIC"
  }'

Every output file carries inline comments where the translator had to make a judgement call or could not translate a construct. Grep those first:

gsutil cat gs://mig-bucket/translated-sql/**/*.sql | grep -n "Translation issue" | sort | uniq -c | sort -rn

A handful of repeated issues usually account for most of the noise — and each one is a candidate for a config-driven fix rather than manual editing.

Name mapping and macros

Rather than hand-editing translated files, feed the translator a name mapping file so ANALYTICS.PUBLIC.ORDERS lands as prod_analytics.core.orders:

{
  "name_map": [
    {
      "source": { "type": "RELATION", "database": "ANALYTICS", "schema": "PUBLIC", "relation": "ORDERS" },
      "target": { "database": "my-project", "schema": "prod_analytics_core", "relation": "orders" }
    }
  ]
}

This is the difference between a migration you can re-run nightly and one you can only do once. Keep mapping files in version control alongside the source SQL.

Interactive and Gemini-assisted translation

For the awkward remainder — stored procedures, dynamic SQL, cursor loops — use the interactive translator in the BigQuery editor (paste dialect-specific SQL, get GoogleSQL back), and Gemini-based translation for the pieces the rule-based engine flags. Treat AI output the way you would treat a junior engineer's pull request: useful, fast, and reviewed line by line before it goes anywhere near production.

Constructs that always need human judgement

Source pattern	What to do in BigQuery
Snowflake `VARIANT` / `OBJECT` columns	`JSON` type, or `STRUCT` if the shape is stable
`MERGE` inside high-frequency micro-batches	Batch into larger windows; DML quotas and slot cost both reward it
Cursor loops in stored procedures	Rewrite set-based; use scripting only where genuinely sequential
Clustering keys / sort keys / dist keys	Partition + cluster; distribution keys have no BigQuery equivalent
Session variables	`DECLARE`/`SET` in scripts, or query parameters
Time-travel-dependent logic	BigQuery time travel is 2–7 days, configurable; snapshots for anything longer

Step 3: Move the data

For Redshift and Teradata, the BigQuery Data Transfer Service has managed connectors that stage through Cloud Storage or S3. For Snowflake, the common pattern is COPY INTO an external stage in Parquet, then load:

-- In Snowflake
COPY INTO @gcs_stage/orders/
FROM ANALYTICS.PUBLIC.ORDERS
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE
MAX_FILE_SIZE = 268435456;

# In BigQuery
bq load --source_format=PARQUET \
  --time_partitioning_field=order_date \
  --clustering_fields=customer_id \
  prod_analytics_core.orders \
  "gs://mig-bucket/orders/*.parquet"

Three practical notes:

Target roughly 256 MB–1 GB Parquet files. Thousands of tiny files make loads slow and expensive.
Set partitioning and clustering at load time. Retrofitting means rewriting the table.
For very large historical tables, load history once, then run incremental loads on a watermark column until cutover.

Step 4: Prove the numbers match

This is the step teams skip and then regret. The open-source Data Validation Tool (DVT) compares source and target at three levels — row counts, column aggregates, and row-level hashes.

pip install google-pso-data-validator

data-validation validate column \
  --source-conn snowflake_conn \
  --target-conn bq_conn \
  --tables-list ANALYTICS.PUBLIC.ORDERS=prod_analytics_core.orders \
  --grouped-columns order_date \
  --count '*' --sum amount --min amount --max amount \
  --bq-result-handler my-project.migration_ops.validation_results

Run validate row with --hash '*' on a sampled subset for the tables that carry money or regulatory meaning. Write results to a BigQuery table and chart the failure count over time — a burn-down of validation failures is the most honest migration status report you can give a steering committee.

Expect a predictable set of benign mismatches: floating-point rounding, timestamp precision and time-zone handling, NULL vs. empty string, and collation differences in sort order. Document each one as accepted rather than silently ignoring it.

Step 5: Run in parallel, then cut over

For two to four weeks, run both warehouses on the same daily inputs and diff the outputs of the reports that matter most. Cut over consumer by consumer, not all at once, and keep the source system readable (not writable) for a defined window afterwards.

A workable cutover order:

Internal dashboards and exploratory analytics.
Scheduled internal reporting.
Reverse-ETL and operational integrations.
Customer-facing and regulatory reporting — last, with the longest parallel run.

A realistic timeline

For a mid-sized estate — a few hundred tables, 50–100 TB, a few thousand SQL objects — a typical shape is: 2 weeks assessment and scoping, 4–8 weeks translation and remediation, 2–4 weeks bulk data movement (overlapping), 3–4 weeks validation and parallel run. The variance is almost entirely in stored-procedure complexity and in how many consumers need coordinating.

Common mistakes worth avoiding

Lift-and-shift with no retirement pass. You inherit a decade of dead objects and pay to keep them.
Recreating warehouse sizing as reservations one-to-one. BigQuery autoscaling makes the old sizing math wrong; measure real slot usage first.
Translating first, mapping names later. Do name mapping up front so translation is repeatable.
Skipping row-level validation because column aggregates looked fine. Aggregates hide offsetting errors.
No rollback plan. Keep the source warehouse readable and the old pipelines resumable until the last consumer signs off.

Where to get help

BQBrains runs BigQuery migrations end to end — assessment, SQL translation and remediation, pipeline rebuild, validation, and parallel-run support — or embeds senior BigQuery engineers alongside your team for the phases you would rather not staff internally. If you have a Snowflake, Redshift, or Teradata estate with a migration date attached to it, get in touch and we will start with the assessment.