Fivetran to BigQuery: Destination Setup, Partitioning and Keeping the Bill Predictable

Fivetran's destination docs make BigQuery look like a five-minute job: pick a project, grant a service account, pick a dataset location, done. The syncs will indeed start. The surprise arrives two or three weeks later, when someone in finance asks why the BigQuery bill grew faster than the warehouse did, and the honest answer is that nobody configured the destination — they accepted it.

This tutorial covers the parts of a Fivetran-to-BigQuery deployment that actually decide your cost and query performance: the service account grants, dataset location and naming, how Fivetran writes (merge vs. upsert behaviour and the load jobs behind it), partitioning and clustering on tables you do not own the DDL for, storage billing mode, and the reservation choices that keep the compute side predictable. It assumes you already know what you are replicating; if the source side is still open, start with Postgres CDC to Snowflake — the CDC mechanics are destination-agnostic.

1. Choose the project layout before you create the connection

Fivetran writes into one BigQuery project per destination. The three layouts we see, and what each costs you:

Layout	What it looks like	Good for	Watch out for
Single raw project	acme-raw holds every Fivetran dataset; transformations write to acme-analytics	Most teams	Needs discipline on dataset naming
Per-domain projects	acme-raw-finance, acme-raw-product	Strong domain ownership, chargeback by project	Multiple Fivetran destinations, multiple credentials to rotate
Shared project with analytics	Fivetran datasets sit beside modelled marts	Small teams	Raw and modelled data get confused; hard to grant "analysts cannot read raw PII"

Pick the single raw project unless you have a concrete reason not to. Keeping ingestion output in its own project makes the IAM story trivial: analysts get roles/bigquery.dataViewer on the analytics project only, and raw tables with unmasked source columns stay behind a boundary. If some of those columns should never land at all, handle it at the connector, not in BigQuery — see blocking and hashing PII in Fivetran.

2. Grants: the minimum that works

Create a dedicated service account, fivetran-loader@<project>.iam.gserviceaccount.com, and do not reuse it for anything else. Fivetran needs, at the project level:

• roles/bigquery.user — to run load and query jobs
• roles/bigquery.dataEditor — to create and write the datasets it manages

If you would rather not grant dataEditor project-wide, pre-create the datasets yourself and grant dataEditor on each dataset instead, plus roles/bigquery.jobUser at project level so jobs can run. That is the version that passes most security reviews, and it has a useful side effect: you own the dataset DDL, so you control location and default table expiry.

If you are using a Google Cloud Storage staging bucket rather than Fivetran-managed staging, the same service account needs roles/storage.objectAdmin on that bucket. Use your own bucket when data residency rules require the intermediate files to stay in your project, and set a lifecycle rule to delete objects after a day — nobody needs month-old staging files, and they bill as standard storage until you remove them.

Rotate the key on the same schedule as your other warehouse credentials, and stage the rotation with the Terraform provider if you have adopted it (pipelines as code) so the change is reviewable rather than a console click at 6pm.

3. Dataset location is permanent — decide it once

A BigQuery dataset's location cannot be changed after creation. If you land data in US and later discover the analytics estate lives in europe-west4, your options are a full re-load or a managed cross-region copy, both of which cost money and downtime. Two rules:

1. Every Fivetran dataset must live in the same location as the datasets that will join against it. Cross-location joins are simply not allowed in BigQuery.
2. Prefer a specific region (europe-west4, us-east4) over the multi-regions US and EU when you have any residency commitment to a customer, because multi-region means the data may be stored in any region within that geography.

Fivetran names the destination dataset after the connector schema — salesforce, postgres_prod_public, and so on. Set the schema prefix at connector creation, because renaming later triggers a re-sync of everything under it. A prefix convention such as raw_salesforce, raw_pg_orders reads better in the console and keeps Fivetran-owned datasets sorted together, away from anything a human created.

4. How Fivetran writes, and why that shows up on the bill

For each sync, Fivetran writes changed rows to staging, then runs a MERGE against the target table keyed on the primary key. Two consequences matter:

The merge scans the target. BigQuery bills on-demand queries by bytes scanned, and a MERGE against an unpartitioned 800 GB table can scan a large slice of it every sync. Sync a 15-minute frequency connector into that table and you are paying for the scan 96 times a day, regardless of whether ten rows changed.

Frequency multiplies everything. Fivetran's own MAR is counted per changed row, not per sync (the 2026 connection-level MAR rules are here), but BigQuery cost scales with sync count. A connector nobody looks at before 9am does not need a 5-minute schedule. This is the single largest lever on a BigQuery destination bill and it takes ninety seconds to change.

5. Partitioning and clustering on Fivetran-managed tables

You do not write the DDL, but you are not powerless. Fivetran creates BigQuery tables unpartitioned by default. For any table above roughly 100 GB, or any table where the merge is visibly slow, add partitioning and clustering yourself:

-- One-time restructure of a large Fivetran-managed table.
-- Do this in a maintenance window with the connector paused.
CREATE OR REPLACE TABLE `acme-raw.raw_pg_orders.orders`
PARTITION BY DATE(_fivetran_synced)
CLUSTER BY customer_id, status
AS SELECT * FROM `acme-raw.raw_pg_orders.orders`;

Notes from doing this in anger:

• Pause the connector first, let the in-flight sync finish, then restructure, then resume. A merge landing mid-rebuild will fail the sync, and depending on the connector it may schedule a re-sync.
• Partition on a column the merge actually filters on where you can. _fivetran_synced is the safe universal choice; a natural created_date is better if your merge predicate touches it.
• Cluster on the primary key columns used in the merge condition. This is what gives you pruning on the target scan.
• Fivetran preserves partitioning and clustering across normal syncs, but a re-sync recreates the table and you lose it. Keep the restructure statements in version control next to your dbt project and re-run them after any re-sync. Our schema drift and re-sync playbook has the wider checklist.
• If a table is genuinely append-only and enormous, ask whether it belongs in BigQuery at all, or in a managed data lake landing into Iceberg with BigLake external tables over the top.

6. Storage billing mode: the five-minute saving nobody takes

BigQuery datasets can bill storage as logical bytes (uncompressed) or physical bytes (compressed on disk, including time travel and fail-safe). Physical billing has a higher per-GB rate but is usually the cheaper option for Fivetran raw data, which compresses well — replicated relational tables with repetitive values often compress 4:1 or better.

Check before you switch:

SELECT
  table_schema,
  ROUND(SUM(total_logical_bytes)/POW(1024,4), 2)  AS logical_tib,
  ROUND(SUM(total_physical_bytes)/POW(1024,4), 2) AS physical_tib,
  ROUND(SAFE_DIVIDE(SUM(total_logical_bytes), SUM(total_physical_bytes)), 2) AS compression_ratio
FROM `region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_schema LIKE 'raw_%'
GROUP BY table_schema
ORDER BY logical_tib DESC;

If the compression ratio comfortably beats the price ratio between the two modes in your region, switch the dataset to physical billing. Two caveats: the change is limited to a small number of switches per dataset per period, and physical billing charges for time travel bytes, so a dataset with a seven-day window and heavy churn is less of a win. Drop the time travel window to two days on raw datasets — you have the source system, you do not need a week of undo on ingested data.

7. On-demand or a reservation?

On-demand pricing bills per TiB scanned and needs no setup. A capacity reservation bills per slot-hour with autoscaling, and becomes the better deal once your scan volume is steady and high.

A practical approach: run on-demand for the first month, then read INFORMATION_SCHEMA.JOBS for actual slot-ms consumed by the Fivetran service account, and compare. Give the loader its own reservation assignment if you do buy capacity — the point is that a batch of merges at 03:00 cannot starve the morning dashboards, and vice versa.

SELECT
  DATE(creation_time) AS day,
  COUNT(*) AS jobs,
  ROUND(SUM(total_bytes_billed)/POW(1024,4), 2) AS tib_billed,
  ROUND(SUM(total_slot_ms)/1000/3600, 1) AS slot_hours
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND user_email = 'fivetran-loader@acme-raw.iam.gserviceaccount.com'
GROUP BY day
ORDER BY day;

That query is also your early warning system. A connector that quietly moved from 40 GB scanned a day to 900 GB is almost always a schema change, a re-sync, or someone bumping the sync frequency.

8. Type mapping details that bite

• NUMERIC vs BIGNUMERIC. Source decimals with precision above 38 digits map to BIGNUMERIC, which is wider and slower to aggregate. Check the source column definitions rather than assuming.
• TIMESTAMP vs DATETIME. Fivetran maps timezone-aware source timestamps to TIMESTAMP (UTC) and naive ones to DATETIME. Mixing them in a join produces a type error, not a wrong answer, which is at least merciful — but standardise in your staging models.
• JSON columns. Postgres jsonb lands as a JSON or string column depending on connector version. If your dbt models use JSON_VALUE, pin the expectation with a test; a connector upgrade that switches the mapping will break silently otherwise.
• Metadata columns. _fivetran_synced, _fivetran_deleted, and _fivetran_id for tables without a primary key. Never filter on _fivetran_synced for business logic — it reflects when the row landed, not when anything happened. Deletes deserve their own treatment; see handling deletes in Fivetran.

9. A go-live checklist

• Dedicated service account, least-privilege grants, key rotation scheduled
• Dataset location matches the analytics estate and any residency commitment
• Schema prefix convention agreed before the first connector is created
• Sync frequency set per connector against a real freshness requirement, not the default
• Tables above ~100 GB partitioned and clustered, statements in version control
• Storage billing mode evaluated with the compression query; time travel trimmed on raw datasets
• Staging bucket lifecycle rule set, if using your own bucket
• Cost and freshness alerting wired up (platform connector dashboard)
• A named owner for the destination, not just for the connectors

Most BigQuery cost problems on Fivetran deployments are not pricing problems — they are three unpartitioned tables merged every five minutes for no reason. Fix those first, then argue about reservations.

If you want the setup reviewed before it becomes a bill, SyncSpur does exactly this work: destination architecture, partitioning strategy, and Fivetran cost optimization. Get in touch.