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

How do you migrate from Snowflake to BigQuery?

Migrate in four phases: translate the schema and types into BigQuery, backfill historical data in chunks through 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 Snowflake-specific SQL during the parallel window, never during the cutover itself.

The order matters more than the tooling. Start by inventorying what you actually have: tables, views, materialized views, tasks, streams, stored procedures, and the roles that grant access to them. In most warehouses a large share of objects are stale. Migrating a table nobody queries costs the same as migrating one that runs the executive dashboard, so pull query history from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY and rank tables by real access over the last 90 days. Everything below the line becomes an archive export rather than a migration target.

Then translate the DDL. Snowflake's GET_DDL gives you a starting point, but the output is not valid BigQuery, so treat it as a source for a mapping script rather than something to paste. Generate BigQuery DDL that adds partitioning and clustering, which Snowflake never made you declare.

Backfill history next, oldest partitions first, so a failure costs you a re-run of one month rather than four years. Once history is in place, point your existing ingestion at both warehouses. If those pipelines are already managed, adding a second destination is a configuration change: the same source can feed a Snowflake to BigQuery migration path while the original loads keep running. For sources loading straight into the new warehouse, the mechanics look identical to any Postgres to BigQuery load, and the patterns are covered in more depth in our guide to loading Postgres into BigQuery.

How long does a Snowflake to BigQuery migration take?

For a mid-sized warehouse (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 in practice: 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. Warehouses with heavy stored procedure logic, dynamic SQL, 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 the warehouse 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 Snowflake tasks, JavaScript UDFs, or a decade of stored procedures ports slowly, because someone has to rediscover what each one does before it can be replaced.

Is BigQuery cheaper than Snowflake?

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

The distinction has a practical consequence. In Snowflake, a query that scans a huge table costs the same as a small one if both finish in the same wall-clock time on the same warehouse; your lever is warehouse sizing, auto-suspend, and concurrency. In BigQuery on-demand, wall-clock time is nearly irrelevant and bytes read are everything, so an unpartitioned table turns a cheap-looking dashboard refresh into a full-history scan every time it runs. A workload tuned for Snowflake is usually not tuned for BigQuery.

That is why lift-and-shift migrations sometimes produce a bill shock in month one. The fix is structural: partition fact tables by the date column analysts filter on, cluster on the high-cardinality columns that show up in WHERE and JOIN, and replace SELECT * in BI extracts with explicit column lists, since BigQuery charges by column bytes read. If your query pattern is steady and predictable, evaluate slot reservations, which convert a variable per-query bill into fixed capacity.

Budget honestly for the overlap too. Running both warehouses through a validation window means paying for both, and the second one is not free just because it is new. Teams typically keep a close watch on cloud compute spend during the parallel run so the temporary double cost stays temporary and does not drift into a permanent line item because nobody switched Snowflake off. Set a date for decommissioning before you start, and put it in the plan.

How do you map Snowflake data types to BigQuery?

Most Snowflake types have a direct BigQuery equivalent: FLOAT to FLOAT64, VARCHAR to STRING, BOOLEAN to BOOL. The two that need judgment are NUMBER(p,s), which may exceed BigQuery's NUMERIC range and require BIGNUMERIC, and the timestamp family, where choosing DATETIME instead of TIMESTAMP silently shifts hours.

Snowflake type	BigQuery type	Notes
NUMBER(p,0), INT, BIGINT	INT64	Snowflake's integer aliases are all NUMBER(38,0) underneath. Safe as INT64 only if values fit in 64 bits; otherwise use NUMERIC
NUMBER(p,s)	NUMERIC or BIGNUMERIC	NUMERIC covers precision up to 38 with scale up to 9. More scale, or precision beyond what NUMERIC allows, needs BIGNUMERIC
FLOAT, DOUBLE, REAL	FLOAT64	All are the same 64-bit float in Snowflake. Never use for currency
VARCHAR, STRING, TEXT, CHAR	STRING	Snowflake length limits are not carried over. BigQuery STRING is unbounded unless you declare a parameterized length
BINARY, VARBINARY	BYTES	Export as base64 to survive a text-based staging format
BOOLEAN	BOOL	NULL stays NULL. Watch loaders that coerce empty strings to false
DATE	DATE	Direct match
TIME	TIME	Snowflake TIME supports fractional seconds to nanoseconds; BigQuery TIME stores microseconds, so nanosecond precision is truncated
TIMESTAMP_NTZ	DATETIME (usually) or TIMESTAMP	The trap. NTZ carries no zone, which matches DATETIME. Map it to TIMESTAMP only if you know the values are UTC, because TIMESTAMP is an absolute UTC-anchored instant and mislabeling local time as UTC shifts every row by your offset
TIMESTAMP_TZ	TIMESTAMP	Absolute instant on both sides. BigQuery does not retain the original offset, so store it separately if you need it
TIMESTAMP_LTZ	TIMESTAMP	Stored as an instant but rendered in the session timezone, so the same row looks different to two Snowflake users. Compare against BigQuery in UTC or the diff will look like a bug
VARIANT	JSON (or STRUCT)	JSON keeps flexibility and supports dot-path access. Use a typed STRUCT when the shape is known and stable, since it is cheaper to scan
OBJECT	JSON or STRUCT	Same tradeoff. STRUCT gives you column pruning; JSON tolerates drift
ARRAY	JSON or REPEATED field	A REPEATED column is the better fit for homogeneous arrays. BigQuery arrays cannot contain NULL elements, so filter during load
GEOGRAPHY	GEOGRAPHY	Both use WGS84. Move through WKT or GeoJSON in staging
GEOMETRY	GEOGRAPHY or STRING	Snowflake GEOMETRY is planar. There is no planar equivalent, so project it or keep the WKT text

Identifiers are the other gotcha, and it surfaces late. Snowflake resolves unquoted identifiers by uppercasing them and comparing case-insensitively, so a table created as customers is really CUSTOMERS and both spellings work. BigQuery column and field names are case-sensitive, and dataset and table names are case-sensitive too. Migrate with one convention (lowercase snake_case is the common choice), apply it consistently in your DDL generator, and do not let the export tool decide for you. Otherwise you get CUSTOMER_ID in some tables and customer_id in others, and every query written against the wrong one fails or, worse, joins nothing.

Can you run Snowflake and BigQuery at the same time?

Yes, and you should. Running both in parallel for a few weeks is what turns a risky cutover into a routine one. Both warehouses load from the same upstream sources, you compare them continuously, and reporting keeps working the entire time because nothing is switched off until its replacement is proven.

There are two ways to keep them in sync. Dual-load is the cleaner one: each source pipeline writes to both destinations independently, so BigQuery is not downstream of Snowflake and a Snowflake outage does not stall your validation. The alternative is chaining, where you unload from Snowflake into BigQuery on a schedule. Chaining is faster to set up and is appropriate for tables built by transformations you have not ported yet, but it inherits every Snowflake bug and gives you nothing to compare against, since both sides came from the same place.

Either way, load incrementally. Keep a per-table watermark on updated_at, query the window since the last successful run with a small overlap so rows committed out of clock order are not skipped, and MERGE the staging batch into the target on the primary key. Dedupe the staging set first with ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) keeping rank 1, because a batch can hold several versions of the same row. That combination makes the load idempotent, which matters far more during a parallel run than in normal operation: you will re-pull the same window repeatedly while chasing a discrepancy, and a non-idempotent load duplicates rows every time you do, manufacturing the very mismatch you were investigating.

Here is how the extraction options compare.

Method	When it fits	Tradeoffs
COPY INTO cloud storage, then BigQuery load jobs	Bulk historical backfill of large tables	Fast and cheap for volume, and Parquet preserves types better than CSV. But it is a batch job you have to schedule, chunk, monitor, and retry yourself
BigQuery Data Transfer Service	Recurring scheduled loads from cloud storage or supported SaaS sources	Managed scheduling and retries inside Google Cloud. It does not read Snowflake directly, so you still need the unload step in front of it
Managed sync tool	Ongoing dual-load during and after the parallel run	Handles watermarks, MERGE, schema drift, and per-run logging. Less control over the exact SQL issued; you accept the tool's semantics
Hand-written scripts	A handful of tables with unusual logic, or one-off archive exports	Total control and no vendor. You own retries, backfill chunking, type edge cases, alerting, and the person who wrote it leaving
Federated or external query	Spot-checking during validation	Useful for reading staged files in place without loading. Not a substitute for materialized tables in production reporting

Validation is the point of the parallel window, so make it mechanical. For every migrated table, compare four things on a schedule: row count, the sum of each meaningful numeric column (revenue, quantity, amount), the min and max of the primary timestamp, and the count of distinct primary keys. Run the same aggregate on both sides for a fixed closed date range (say, all of last month) so late-arriving data does not create 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 NUMBER(38,12) got truncated into NUMERIC, the counts match perfectly and the total is off by cents that grow with volume.

Then check real dashboards, not just tables. Pick five reports people actually open, point a copy at BigQuery, and compare the rendered numbers for the same date filters. This is where timezone mistakes surface, because a daily revenue chart drawn from a mislabeled TIMESTAMP_NTZ looks fine in total but shifts each day's bucket by a few hours, moving revenue across day boundaries. Only cut a table over after it reconciles cleanly for several consecutive days.

What breaks when you move SQL from Snowflake to BigQuery?

Semi-structured access breaks hardest: LATERAL FLATTEN has no BigQuery equivalent and becomes UNNEST. Beyond that, IFF becomes IF, NVL becomes IFNULL, and the date functions differ in signature rather than name, which is worse because they often still run and return the wrong answer.

The date functions deserve care. Snowflake's DATEADD(part, value, date) takes the unit first; BigQuery's DATE_ADD(date, INTERVAL value part) takes the date first with an INTERVAL literal. Snowflake's DATEDIFF(part, start, end) maps to BigQuery's DATE_DIFF(end, start, part), with the arguments in the opposite order, so a careless port flips the sign of every duration. BigQuery also has separate DATETIME_ADD, TIMESTAMP_ADD, and TIMESTAMP_DIFF variants for the different temporal types, where Snowflake uses one function for all of them.

Some things port more easily than teams expect. QUALIFY exists in BigQuery and filters on window functions the same way, so those queries usually move unchanged. LISTAGG becomes STRING_AGG. ARRAY_AGG exists on both sides. Common table expressions, window functions, and standard joins behave the same.

For VARIANT access, Snowflake's col:field::string becomes JSON_VALUE(col, '$.field') in BigQuery, and LATERAL FLATTEN(input => col:items) becomes a cross join to UNNEST(JSON_QUERY_ARRAY(col, '$.items')). The rewrite is mechanical but tedious, which argues for converting stable JSON shapes into typed STRUCT and REPEATED columns during the load rather than carrying raw JSON forward and reparsing it in every query.

Physical design also changes shape. Snowflake micro-partitions data automatically and clustering keys are an optional tuning layer; BigQuery makes partitioning an explicit property of the table, set at creation. Partition fact tables by the date column your reports filter on, or by ingestion time when there is no natural candidate, and cluster on up to four high-cardinality filter columns such as tenant ID, customer ID, or status. Because on-demand billing follows bytes scanned, partition pruning is a direct cost lever rather than just a latency optimization.

Cutover itself should be dull by the time you reach it. Keep both warehouses loading, switch BI connections table group by table group starting with the lowest-stakes reports, and leave the Snowflake loads running for two to four weeks afterward as a rollback path: if something is wrong, you repoint the report back and lose nothing. A short freeze (a few hours over a weekend) is only needed for the final tables where in-flight writes could straddle the switch. When every report has moved and reconciliation has been quiet for a full cycle, suspend the Snowflake warehouses before you drop anything, so the rollback stays available for one more week at near-zero compute cost.

If you are rebuilding transformations along the way, the tradeoffs between transforming before or after the load are covered in ETL vs ELT, and the broader pipeline approach lives on the ETL solutions page. To see the mapping for your own tables, set up the Snowflake to BigQuery connector from the data integration platform, or book a demo.