BigQuery to Snowflake migration: how to move a warehouse without freezing reporting

How do you migrate from BigQuery to Snowflake?

You migrate in four phases: translate the schema and types into Snowflake, backfill history in chunks through Google Cloud Storage, run both warehouses in parallel loading from the same upstream sources, and cut reports over per table once counts and totals reconcile. Rewrite BigQuery-specific SQL during the parallel window, never during the cutover itself.

Sequence matters more than tooling. Start by inventorying what you actually have: tables, views, materialized views, scheduled queries, routines, and the IAM grants that gate them. A large share of objects in most projects are stale, and migrating a table nobody queries costs the same as migrating the one behind the executive dashboard. Pull usage from INFORMATION_SCHEMA.JOBS and rank tables by real access over the last 90 days. Everything below the line becomes an archive export instead of a migration target.

Then translate the DDL. BigQuery's schema JSON and INFORMATION_SCHEMA.COLUMNS give you a starting point, but the output is not valid Snowflake, so treat it as input to a mapping script rather than something to paste. Generate Snowflake DDL, decide where to keep nested RECORD fields as VARIANT versus flattening them, and add clustering keys where a table is large enough to need them. If your ingestion is already managed, adding a second destination is a configuration change: the same source can feed a BigQuery to Snowflake migration path while the original loads keep running. Teams going the other way should read the reverse Snowflake to BigQuery guide, which covers the mirror-image traps.

Backfill history next, oldest partitions first, so a failure costs you a re-run of one month rather than four years. The extraction method decides how much of this you hand-build.

Method	What it is	When it fits and what it costs you
EXPORT DATA to GCS, then COPY INTO	BigQuery writes Parquet or Avro to a bucket; Snowflake reads it from an external stage, optionally auto-ingested with Snowpipe	The workhorse for bulk backfill. Parquet and Avro keep types far better than CSV. You schedule, chunk, monitor, and retry the export yourself
BigQuery Storage Read API	A gRPC API that streams table data in Arrow or Avro directly, in parallel, without a query job	Fast for large extracts and avoids export file management. Needs code to consume the streams and land them into Snowflake staging
Scheduled query plus incremental watermark	A scheduled query selects rows since the last updated_at into a staging table you export and load	Simple steady-state incremental sync. You own the watermark bookkeeping and the export step between the query and Snowflake
CDC via table snapshots or change history	BigQuery time-travel snapshots, or CHANGES and the _CHANGE_TYPE pseudo-column, expose inserts, updates, and deletes	Captures deletes that a watermark alone never sees. More moving parts, and change history has a limited retention window you must load within
Managed sync tool	A hosted connector that handles export, staging, COPY, watermarks, MERGE, and schema drift	When you would rather not own retries and chunking. Less control over the exact SQL issued; you accept the tool's semantics

Most migrations combine two: a bulk EXPORT DATA or Storage Read backfill for history, then an incremental watermark load for the steady state. Load into Snowflake staging tables first, then MERGE into the targets on the primary key, so a re-pulled window updates rows in place instead of doubling them. That idempotency matters far more during a parallel run than in normal operation, because you will re-pull the same window repeatedly while chasing a discrepancy.

How do BigQuery data types map to Snowflake?

Most BigQuery types have a direct Snowflake equivalent: STRING to VARCHAR, BOOL to BOOLEAN, DATE to DATE. The two that need judgment are the numeric family, where BIGNUMERIC can exceed what Snowflake's NUMBER can hold, and the timestamp family, where TIMESTAMP and DATETIME mean different things and the wrong choice shifts hours without an error.

BigQuery type	Snowflake type	Notes
STRING	VARCHAR	BigQuery STRING is unbounded UTF-8. Snowflake VARCHAR defaults to the max length, so you rarely need to declare one
INT64	NUMBER(38,0) (alias INTEGER)	Snowflake INTEGER is NUMBER(38,0) underneath, wider than a 64-bit int, so every INT64 value fits with room to spare
FLOAT64	FLOAT	Both are 64-bit IEEE floats. Never use for currency on either side
NUMERIC	NUMBER(38,9)	BigQuery NUMERIC is fixed at precision 38, scale 9. Map it exactly so money keeps its scale
BIGNUMERIC	NUMBER at declared precision	The trap. BIGNUMERIC allows up to precision 76, scale 38, which exceeds Snowflake NUMBER's ceiling of precision 38, scale 37. Values beyond 38 significant digits cannot be represented and must be rescaled or stored as text
BOOL	BOOLEAN	NULL stays NULL. Watch loaders that coerce empty strings to false
BYTES	BINARY	Export as base64 so bytes survive a text staging format, then Snowflake decodes on load
DATE	DATE	Direct match
DATETIME	TIMESTAMP_NTZ	DATETIME is wall-clock with no zone, which matches NTZ (no time zone). Do not send it to a zoned type, or you invent an offset that was never there
TIMESTAMP	TIMESTAMP_TZ or TIMESTAMP_LTZ	BigQuery TIMESTAMP is an absolute UTC instant. Map to a zoned Snowflake type. LTZ renders in the session zone; TZ retains an offset. Mapping it to NTZ drops the UTC anchor and shifts every row by the reader's offset
TIME	TIME	Both store time of day. Match the fractional-second precision so nothing truncates
GEOGRAPHY	GEOGRAPHY	Both use WGS84 spherical coordinates. Move through WKT or GeoJSON in staging
JSON	VARIANT (or OBJECT)	VARIANT keeps flexible semi-structured access with dot-path syntax. Use OBJECT when the shape is a known key-value map
STRUCT / RECORD	OBJECT (or VARIANT)	A single RECORD becomes an OBJECT. Store as VARIANT if the shape drifts, or flatten into typed columns if it is stable
ARRAY	ARRAY	Both support arrays. Snowflake arrays are semi-structured VARIANT elements rather than typed, so a homogeneous BigQuery array loses its element type unless you cast on read
Nested + repeated RECORD	Flatten to child table, or store as VARIANT	A repeated RECORD (an array of structs) has no native columnar twin. Either explode it into a child table keyed to the parent id, or keep the whole thing in one VARIANT column

Identifiers are the other gotcha, and it surfaces late. BigQuery treats column and field names as case-sensitive and stores them exactly as declared, so customerId and customerid are two different columns. Snowflake folds unquoted identifiers to uppercase, so customerId becomes CUSTOMERID unless you wrap it in double quotes, and quoted names then have to be quoted forever. Pick one convention (lowercase snake_case is the common choice), apply it in your DDL generator, and do not let the export tool decide for you. Otherwise you get customer_id in some tables and "customerId" in others, and every query written against the wrong spelling fails or joins nothing.

How do you move nested and repeated fields from BigQuery to Snowflake?

You either flatten them into child tables during the load or store each nested column as a single VARIANT. BigQuery models one-to-many relationships as repeated RECORD fields that you read with UNNEST. Snowflake has no repeated column type, so the query pattern changes: you expand arrays with LATERAL FLATTEN instead.

The decision is about how the data gets queried, not just how it is stored. If analysts filter and join on the nested values (order line items, event parameters, tags), flatten a repeated RECORD into its own table keyed to the parent id during the load, so it becomes ordinary SQL that Snowflake clusters and prunes efficiently. If the nested blob is read whole or rarely, keep it as a VARIANT and expand on demand. A BigQuery query that reads SELECT o.id, i.sku FROM orders o, UNNEST(o.items) AS i becomes, against a VARIANT column in Snowflake, SELECT o.id, f.value:sku::string FROM orders o, LATERAL FLATTEN(input => o.items) AS f. The shape is the same, the syntax is not, and mixing them up is one of the most common rewrite errors.

SQL that breaks moving from BigQuery to Snowflake

Casting breaks first. BigQuery's SAFE_CAST, which returns NULL instead of erroring on a bad value, becomes TRY_CAST in Snowflake. Approximate aggregates differ in name and signature: APPROX_QUANTILES(x, 100)[OFFSET(50)] becomes APPROX_PERCENTILE(x, 0.5), and APPROX_COUNT_DISTINCT maps to APPROX_COUNT_DISTINCT with the same intent but its own tuning. ARRAY_AGG exists on both sides, though Snowflake needs WITHIN GROUP (ORDER BY ...) for ordered aggregation. BigQuery's SELECT * EXCEPT(col) and SELECT * REPLACE(expr AS col) have no Snowflake equivalent, so those column lists get written out. Quoting flips too: BigQuery wraps identifiers in backticks, Snowflake uses double quotes, and string literals are single-quoted on both.

Physical design changes shape. BigQuery partitions a table by a DATE or TIMESTAMP column or by ingestion time, and clusters on up to four columns. Snowflake partitions automatically into micro-partitions and exposes clustering keys as an optional tuning layer you rarely need to set until a table is large. So the explicit partitioning you declared in BigQuery has no direct translation: you drop it and let Snowflake micro-partition, adding a clustering key only where query pruning demands it.

How long does a BigQuery to Snowflake migration take?

For a mid-sized project (a few hundred tables and a handful of BI tools) plan on six to twelve weeks of calendar time, most of it spent in parallel validation rather than data movement. Copying bytes is the fast part. Rewriting SQL, reconciling numbers, and getting analysts to trust the new tables is what consumes the schedule.

A rough allocation that holds up: one to two weeks on inventory and schema translation, one to two weeks on backfill and load engineering, three to six weeks running both warehouses side by side while transformations and dashboards get ported, and a final week for cutover and decommissioning. Projects with heavy scheduled-query logic, JavaScript UDFs, or hundreds of hand-written dbt models sit at the long end.

The variable that actually moves the timeline is how much business logic lives inside BigQuery versus in a transformation layer you control. A dbt project ports quickly, because the models are text you can search, adapt, and test. Logic buried in scheduled queries, stored routines, or a pile of ad hoc views ports slowly, because someone has to rediscover what each one does before it can be replaced. A big-bang export tempts teams who want to skip the parallel run, but it freezes reporting for the duration and defers every type bug until a wrong number shows up in a board deck.

How do you validate a BigQuery to Snowflake migration?

You validate by running the same aggregates on both warehouses and comparing them mechanically, not by eyeballing a few rows. For every migrated table, check four things on a schedule: row count, the sum of each money or quantity column, the min and max of the primary timestamp, and the count of distinct primary keys. Only cut a table over once it reconciles cleanly for several consecutive days.

Run each aggregate over a fixed closed date range (all of last month, say) so late-arriving data does not raise false alarms, and store the results in a small reconciliation table with the run date. A column sum catches type problems that row counts miss entirely: if a BIGNUMERIC got rescaled into a narrower NUMBER, the counts match perfectly and the total is off by amounts that grow with volume. Spot-check a hash of a few key columns per primary key to catch value-level drift a sum would net out. During the parallel run it also pays to trace every table's lineage across both warehouses, so a mismatch points at the exact upstream transform that diverged instead of sending you hunting.

Then check real dashboards, not just tables. Pick five reports people actually open, point a copy at Snowflake, and compare the rendered numbers for the same date filters. This is where timezone mistakes surface, because a daily revenue chart drawn from a BigQuery DATETIME that was mapped to a zoned Snowflake type looks fine in total but shifts each day's bucket by a few hours, moving revenue across day boundaries. Keep both warehouses loading from the same sources during this window so the comparison is apples to apples, and leave the BigQuery loads running for two to four weeks after cutover as a rollback path: if a report is wrong, you repoint it back and lose nothing. Suspend the BigQuery-side jobs only once reconciliation has been quiet for a full cycle.

Is Snowflake cheaper than BigQuery?

Not inherently. The two bill on different axes: BigQuery on-demand charges for the bytes each query scans (or for reserved slot capacity), while Snowflake charges credits per second of warehouse uptime scaled by warehouse size. Whether you save money depends on your query shape, not on a rate card comparison.

The distinction has a practical consequence. In BigQuery on-demand, wall-clock time barely matters and bytes read are everything, so an unpartitioned table turns a cheap-looking dashboard into a full-history scan every refresh. In Snowflake, a query that scans a huge table costs the same as a small one if both finish in the same time on the same warehouse; your levers are warehouse sizing, auto-suspend, and concurrency. A workload tuned for BigQuery's bytes-scanned model is usually not tuned for Snowflake's per-second credit model, so plan to re-tune rather than copy: right-size warehouses, set aggressive auto-suspend, and let idle compute stop. Budget honestly for the overlap too, since running both warehouses through the validation window means paying for both until you switch the old one off. Set a decommissioning date before you start.

To map the pair for your own tables, set up the the BigQuery to Snowflake connector from the data integration platform. Our flat pricing is by plan rather than per row, so a heavy backfill month does not spike the bill, and you can book a walkthrough to see both warehouses loading in parallel before you commit to a cutover date.