BigQuery to Snowflake: How to Move Your Data

Here is a failure mode worth knowing before you build this pipeline. You replicate a BigQuery table of financial transactions into Snowflake, the sync succeeds, the row counts match, and six weeks later finance reports that a total is off by a few cents against the source. Nothing failed. The numbers were converted through a floating point type and came out approximate.

This is entirely avoidable, but only if you make a decision during setup that the default does not make for you.

Why do the numbers come out slightly wrong?

The Snowflake destination has a setting called Decimal Data Type that governs which Snowflake type is used for columns Airbyte types as a number. The two options are FLOAT and NUMBER(38,9).

FLOAT is the default. It is an approximate binary floating point type with roughly fifteen digits of precision, well suited to scientific values, model scores, and probabilities, where range matters more than exactness and small rounding differences are acceptable.

NUMBER(38,9) is exact fixed-point, supporting up to 38 total digits with up to 29 before the decimal point and 9 after. Airbyte's documentation recommends it, and it is what you want for money.

BigQuery's NUMERIC type is exact decimal. It maps to Airbyte's number type, which then lands as FLOAT unless you change the setting. Exact goes in, approximate comes out, and nothing warns you.

Change this before your first sync. Changing it afterwards is genuinely hazardous: Snowflake converts the column in place, switching to NUMBER(38,9) sets stored FLOAT values with more than 29 integer digits to NULL without adding a metadata entry recording the change, and precision already lost during the original FLOAT load cannot be recovered. Only a full refresh restores the original values, so run one after changing this setting on an existing connection.

What else changes shape in transit?

The second surprise sits in the BigQuery source type mapping. DATE, DATETIME, TIMESTAMP, and TIME all map to String in ISO8601 format, as does GEOGRAPHY. NUMERIC and BIGNUMERIC both map to Number.

Airbyte's typed-string handling means these generally recover as proper Snowflake temporal types, with timestamp-with-timezone becoming TIMESTAMP_TZ and timestamp-without becoming TIMESTAMP_NTZ. Verify it on a sample rather than assuming, particularly for DATETIME columns where timezone semantics are ambiguous on both sides.

Snowflake also enforces size limits worth knowing: VARCHAR caps at 16 MB and the VARIANT type used for objects and arrays caps at 128 MB. Values beyond those limits are nulled out and the change is recorded in the row's metadata column.

What does extraction cost?

The BigQuery source supports full refresh and cursor-based incremental. It does not support change data capture, because BigQuery has no change log, which means rows deleted in BigQuery will not be removed from Snowflake.

Every sync issues a real BigQuery query and is billed accordingly, so sync frequency is a spending decision. Two things reduce the bill. Choose a cursor field aligned with your table's partitioning so BigQuery can prune partitions rather than scan. And prefer TIMESTAMP or INT64 cursors, which the documentation ranks highest for performance, over STRING, which is slower on large datasets.

Watch for null cursor values. Records with a null cursor are skipped rather than failing the sync, so a nullable updated timestamp silently loses rows with no error to investigate.

Setting up Snowflake

Create dedicated Snowflake entities rather than reusing existing ones: a role, user, warehouse, database, and schema, with the role holding OWNERSHIP on the database. Airbyte publishes a setup script for exactly this. The connection user needs CREATE SCHEMA on the target database, or OWNERSHIP on schemas you create by hand.

On sizing, Snowflake bills compute per second and the warehouse resumes on every load. Airbyte's guidance is an X-Small warehouse dedicated to syncs with a sixty-second auto-suspend, which the setup script configures. A larger shared warehouse costs more for no benefit on a load-shaped workload.

Authentication is username and password or key pair. For key pair, generate a PKCS#8 private key with openssl, derive the public key, and attach it to the Snowflake user with ALTER USER. Encrypted private keys are supported.

Two other settings deserve a moment. Data Retention Period sets Snowflake Time Travel days on Airbyte's tables, and a nonzero value adds storage cost. And by default Airbyte creates permanent tables, so if you would rather avoid Fail-safe storage charges, create a dedicated transient database, accepting that transient tables cannot be recovered after an operational failure.

Frequently asked questions

Why are my numbers slightly wrong in Snowflake?

The Decimal Data Type setting defaults to FLOAT, which is approximate. Switch to NUMBER(38,9) and run a full refresh, since in-place conversion cannot restore precision that was never stored.

Can I change the decimal setting on a live connection?

You can, but follow it with a full refresh. Values above 29 integer digits are nulled during conversion without a metadata entry, and previously truncated values stay truncated.

Does the BigQuery source support CDC?

No. BigQuery has no change log, so replication is cursor-based and rows deleted in BigQuery will not be removed downstream.

What warehouse size should I use?

X-Small with a one-minute auto-suspend, dedicated to Airbyte. Snowflake bills per second and resumes the warehouse on every sync, so idle time is the thing worth minimising.

Get your BigQuery data into Snowflake

Set the decimal type before the first sync, align your cursor with the table's partitioning, and give Airbyte its own X-Small warehouse. For other routes into the same warehouse see our guide to ClickHouse to Snowflake, or for the reverse direction out of BigQuery, BigQuery to PostgreSQL.

Start syncing now →