Everyone budgets Fivetran by MAR. Almost nobody budgets what Fivetran does on the other side of the pipe. Then the Snowflake invoice arrives, a FIVETRAN_WH line item is sitting at 30-40% of total credit spend, and the pipeline that was supposed to be "fully managed" has quietly become the largest consumer in the account.
This tutorial is about the destination side: what Fivetran's loader actually executes in Snowflake, which knobs change the credit burn, and how to measure the cost of a single connection so you can defend or cut it. It assumes you already understand connection-level MAR pricing - that is the vendor bill. This is the other one.
What the loader actually runs
Fivetran does not stream rows into Snowflake one at a time. For each table in a sync it follows roughly the same shape:
- Batch the changed rows into files and
PUTthem into an internal stage. COPY INTOa transient staging table.MERGE(orINSERTfor append-only / history mode) the staging table into the target table on the primary key.- Drop the staging table and commit sync progress.
Three consequences fall out of that, and they explain nearly every surprising Snowflake bill we audit:
- Cost tracks the number of sync operations, not just the number of rows. Steps 1-4 have a fixed floor per table per sync. Fifty small tables synced every five minutes is 14,400 merge cycles a day whether or not anything changed.
- The MERGE is the expensive part. A merge must locate the target rows. On a large unclustered table with a random-ish primary key, that means scanning far more micro-partitions than the change set justifies.
- The warehouse is billed in 60-second minimums. A sync that does four seconds of real work still bills a minute. Frequent syncs against an always-warm warehouse are the classic silent leak.
Step 1: measure before you tune
Give Fivetran its own warehouse. If it shares one with dbt, BI or ad-hoc users, you cannot attribute anything and every number below is noise.
create warehouse fivetran_wh
warehouse_size = xsmall
auto_suspend = 60
auto_resume = true
initially_suspended = true;
Then attribute credits per warehouse over the last month:
select warehouse_name,
round(sum(credits_used), 1) as credits,
round(sum(credits_used) * 3.00, 0) as approx_usd -- your effective rate
from snowflake.account_usage.warehouse_metering_history
where start_time >= dateadd(day, -30, current_timestamp())
group by 1
order by credits desc;
To get per-connection cost, use the fact that Fivetran names its schemas after connections and its queries land in one warehouse:
select regexp_substr(query_text, '"?([A-Z0-9_]+)"?\\.', 1, 1, 'e') as target_schema,
count(*) as statements,
round(sum(credits_attributed_compute), 2) as credits,
round(sum(total_elapsed_time)/1000/60, 1) as minutes
from snowflake.account_usage.query_attribution_history
where warehouse_name = 'FIVETRAN_WH'
and start_time >= dateadd(day, -7, current_timestamp())
group by 1
order by credits desc
limit 25;
Rank connections by credits. In every audit we have run, the top three connections account for the majority of the spend, and at least one of them is syncing something nobody reads. Cross-check the winners against fivetran_platform.incremental_mar from the Platform Connector: a connection that is expensive in Snowflake but low in MAR is a frequency and merge problem, not a volume problem.
Step 2: fix sync frequency first
Frequency is the single biggest destination-side lever, and unlike most tuning it costs nothing to change.
Work through every connection and put it in one of three tiers:
| Tier | Frequency | Typical sources |
|---|---|---|
| Operational | 5-15 min | Order/event tables feeding reverse ETL or ops dashboards |
| Analytical | 1-6 hours | Product DBs, CRM, support, billing |
| Reference | Daily | HR, finance systems of record, ad platforms, spreadsheets |
Most connections that arrive at 5 minutes were never a decision - it is just the default that got clicked. Ask the consuming team what decision changes if the data is four hours old. If the honest answer is "none", move it.
The arithmetic is blunt. An XS warehouse running 24/7 is about 24 credits a day. The same warehouse woken 24 times a day for ninety seconds of work is under one credit a day. Two connections on 5-minute schedules are usually enough to keep a warehouse permanently warm all by themselves; drop them to hourly and the warehouse actually suspends.
A related trick: stagger schedules. If twelve connections all sync on the hour, they contend for the same warehouse, queue, and stretch the billed window. Fivetran lets you set the sync start offset - spread them across the hour.
Step 3: size the warehouse correctly (usually down)
The instinct when loads are slow is to scale up. For Fivetran loads that is usually wrong. Snowflake bills credits per second at a rate that doubles with each size, so scaling up only saves money if the job gets proportionally faster - and merge jobs on small change sets do not, because they are bound by partition lookup and file handling, not raw parallelism.
Practical guidance:
- XS or S for almost everything. Start at XS. Move to S only if you see spillover.
- Scale up only for initial syncs, then scale back down. A billion-row initial sync genuinely benefits from an L warehouse for a day.
- Use a second warehouse for the whales. Put the two or three high-volume connections on
FIVETRAN_BULK_WHat S/M, and leave everything else on an XS. Fivetran sets the warehouse per destination, so this means a second destination group pointed at the same Snowflake account and database. - Set
auto_suspend = 60. The Snowflake default of 600 seconds means every sync pays ten idle minutes. For a load-only warehouse there is no warm cache worth protecting.
Check for spillover before you resize anything:
select query_type,
count(*) as queries,
round(avg(bytes_spilled_to_local_storage)/1024/1024, 1) as avg_local_spill_mb,
round(avg(bytes_spilled_to_remote_storage)/1024/1024, 1) as avg_remote_spill_mb,
round(avg(queued_overload_time)/1000, 1) as avg_queue_s
from snowflake.account_usage.query_history
where warehouse_name = 'FIVETRAN_WH'
and start_time >= dateadd(day, -7, current_timestamp())
group by 1;
Remote spill means size up. Queue time means either stagger schedules or enable a multi-cluster warehouse (min 1, max 2-3) rather than jumping a size.
Step 4: make the MERGE cheap
Once frequency and sizing are settled, what remains is the merge itself. Find the worst offenders:
select query_id,
left(query_text, 120) as stmt,
round(total_elapsed_time/1000, 1) as seconds,
partitions_scanned,
partitions_total,
round(100 * partitions_scanned / nullif(partitions_total, 0), 1) as pct_scanned,
rows_produced
from snowflake.account_usage.query_history
where warehouse_name = 'FIVETRAN_WH'
and query_type = 'MERGE'
and start_time >= dateadd(day, -3, current_timestamp())
order by total_elapsed_time desc
limit 20;
A merge scanning 90% of partitions to update 4,000 rows is the pattern to hunt. Fixes, in order of payoff:
Cluster the big tables on the column the merge prunes by. Fivetran merges on the primary key, but Snowflake prunes on physical layout. If the table is naturally time-ordered (events, orders, telemetry), cluster on a date column - the change set is nearly always recent, so recent-partition pruning does most of the work:
alter table raw.shopify.orders cluster by (date_trunc('day', updated_at));
Only do this on tables above roughly a terabyte or with clearly bad pruning. Automatic clustering is itself billed, and on a hot table it can cost more than the merges it saves. Measure with system$clustering_information before and watch automatic_clustering_history after.
Reduce the number of merged tables. Every deselected table is a merge cycle that never runs. Table and column deselection cuts MAR and destination compute - the only lever that hits both bills. Trace tables to consumers; deselect what nothing reads.
Consider append-only for very large, very hot tables. History mode and soft-delete handling change the write pattern from merge-heavy to insert-heavy, which is cheaper to write but shifts cost onto the readers who now have to deduplicate. That is a good trade for immutable event streams and a bad one for a dimension table queried by every dashboard.
Do not transform in the landing zone. The raw schema Fivetran owns should stay raw. Adding views, masking policies or row access policies on top of landing tables can force the merge into slower paths. Do governance one layer up - or, for PII that must never land, at the column-blocking and hashing stage before it reaches Snowflake.
Step 5: stop paying for storage you forgot about
Compute dominates, but storage and Time Travel on raw landing tables add up, and they are easy wins:
alter table raw.salesforce.opportunity set data_retention_time_in_days = 1;
Raw landed data is reproducible by re-syncing; seven days of Time Travel plus seven days of fail-safe on multi-terabyte landing tables is insurance you are paying for twice. Set retention to 0-1 days on the raw database (not on curated marts). And check for orphaned schemas from connections that were deleted in Fivetran but whose tables still sit in Snowflake - deleting a connection does not drop the data.
Step 6: put a guardrail on it
Tuning decays. Someone adds a connection, someone bumps a schedule, and six months later you are back where you started. Two things keep it honest:
Resource monitors on the Fivetran warehouse, set to notify - not suspend - at 75% and 100% of the monthly credit budget. Suspending a loader mid-sync creates a backlog problem worse than the bill.
A weekly credits-per-connection report built from the attribution query above, posted where the data team will see it, showing week-over-week change. A new connection or a schedule change shows up as a step function within days.
select date_trunc('week', start_time) as wk,
round(sum(credits_used), 1) as credits
from snowflake.account_usage.warehouse_metering_history
where warehouse_name = 'FIVETRAN_WH'
and start_time >= dateadd(day, -90, current_timestamp())
group by 1 order by 1;
If you manage schedules and warehouse assignment in code, both of these belong next to your Terraform-managed connector definitions so a schedule change is a reviewed pull request rather than a click.
A realistic order of operations
- Isolate Fivetran on its own warehouse, XS,
auto_suspend = 60. - Rank connections by attributed credits over 7 days.
- Re-tier sync frequency; stagger start times.
- Deselect unread tables and columns.
- Check spillover and queuing; resize only if the data says so.
- Cluster the two or three tables whose merges scan the whole table.
- Cut Time Travel on the raw database; drop orphaned schemas.
- Add a resource monitor and a weekly report.
Steps 1-4 are free, take an afternoon, and in our experience account for most of the savings. Steps 5-7 are where a deeper audit pays off.
If your Snowflake bill has grown faster than your data, our Fivetran cost optimization and performance tuning teams do exactly this exercise end to end - source-side MAR and destination-side credits together, since cutting one usually cuts the other. Get in touch with a month of warehouse_metering_history and we can tell you quickly where the money is going.