SyncSpur | Surviving the Initial Sync: Backfilling Billion-Row Tables with Fivetran

Steady-state Fivetran is boring in the best way: a few million changed rows a day, incremental syncs that finish in minutes, nobody thinking about it. The initial sync is a different animal. It is the one run that reads everything, and on a source with a couple of billion rows it can run for days, hold a replication slot open the whole time, saturate a database nobody sized for a full table scan, and put a month's worth of MAR on the board before a single dashboard exists.

This tutorial is the planning playbook we use before turning on a large connector: what to decide up front, how to sequence the backfill, and what to watch while it runs.

Size the job before you start it

Before the connector is created, get four numbers per table from the source:

Row count - approximate is fine (reltuples in Postgres, information_schema.tables in MySQL, sys.dm_db_partition_stats in SQL Server).
On-disk size, which predicts the read load better than row count does.
Average row width, which predicts how much of that turns into destination storage and MAR-relevant rows.
Whether history is actually wanted. This is the one that saves money.

A quick Postgres pass:

SELECT c.relname AS table_name,
       to_char(c.reltuples, 'FM999,999,999,999') AS est_rows,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 25;

In nearly every source we profile, the top five tables are 80-95% of the volume, and at least one of them is an append-only event or audit log that nobody in the analytics team has asked for. The whole backfill plan is usually decided by what you do with those five tables.

Decide what not to sync

Every row you exclude is read load you do not create, initial sync time you do not spend, and monthly active rows you do not pay for. Fivetran charges on distinct primary keys touched per month, so the initial sync counts every row you bring across once. Pruning is the cheapest lever you have, and it is much easier to add a table later than to remove one after analysts have built on it.

Things worth cutting before the first run:

Tables with no consumer. Sync the ones with a named question behind them. Fivetran will happily add the rest later with a targeted resync of just that table.
Wide columns you will never query - serialised blobs, base64 payloads, raw request bodies. Column exclusion does not reduce MAR, but it reduces bytes read and destination storage substantially.
Soft-deleted or archive partitions where the application already keeps a *_archive table.
PII you have no lawful basis to land. Handle it at the connector with column blocking or hashing rather than cleaning it up downstream - see our walkthrough on blocking and hashing PII in Fivetran.

Write the exclusion list down with a one-line reason for each entry. Six months later someone will ask why events_raw is not in the warehouse, and "nobody asked for 1.4 billion rows" is a much better answer than a shrug.

Protect the source: replica, not primary

An initial sync is a sustained sequential read of the biggest tables you own. Point it at a read replica whenever one exists. For Postgres logical replication this needs care - a replication slot has to live somewhere valid for your version and topology, so confirm where the slot is created and that the replica is configured to support it before you promise the DBA anything.

Whatever the topology, three settings matter more than the rest:

WAL / binlog / transaction log retention. The log must be retained for longer than the entire initial sync takes, plus a safety margin. If the slot falls behind and the log is recycled, the connector cannot resume incrementally and you are re-syncing from zero - twice the time, twice the MAR.
Disk headroom on the source. A replication slot that is not being consumed fast enough causes WAL to accumulate. Alert on slot lag and on free disk, not just on connector status.
Statement timeouts and idle-session killers. A statement_timeout or an aggressive connection reaper will kill long-running reads mid-table. Grant the Fivetran user an exemption rather than turning the protection off globally.

For SQL Server, confirm CDC or change-tracking retention the same way. For Oracle and SAP under HVR the equivalent question is archive log retention, covered in replicating high-volume ERP data.

Sequence the backfill instead of firing it all at once

The default instinct is to select every table, save, and walk away. On a large source, a staged sequence gets usable data sooner and keeps the blast radius small:

Stage 1 - a thin slice. Enable three or four small dimension tables. Confirm the destination schema, naming, types and permissions are right. Getting a NUMERIC precision or a timezone convention wrong is cheap to fix now and expensive after two billion rows have landed.

Stage 2 - the business core. The transactional tables analysts are actually waiting for, minus the giants. This is usually where the project can start delivering models.

Stage 3 - the giants, one at a time. Add the largest tables individually, each after the previous one has completed and been validated. Running them serially rather than in parallel keeps source read load predictable and makes it obvious which table caused a slowdown.

Stage 4 - optional extras. Anything from the maybe list, with a consumer attached.

Where Fivetran offers a priority-first sync for a connector type, use it on stage 2 and 3 tables: it brings the most recent data across first so downstream teams can build and validate against real recent rows while the deep history continues loading behind them. It does not make the total job faster; it makes the wait far more tolerable, which is usually the actual problem.

Pause scheduled syncs on other connectors sharing the same destination warehouse during the heaviest stage if your destination is small. The bottleneck on a big backfill is often destination write throughput, not the source.

When the connector is the wrong tool for history

Sometimes the right answer is to not backfill through the connector at all. If a table has ten years of immutable history and only the last ninety days ever change, consider:

Bulk export the historical partition once - a COPY, a database export, or a snapshot to object storage.
Load it into the destination as a separate historical table.
Let Fivetran sync only the recent, active partition going forward, with a source-level filter or a dedicated view as the connector's source object.
Union the two in your transformation layer behind a single model name.

This trades a little modelling complexity for a dramatically shorter initial sync and a much smaller ongoing footprint. It is particularly worth it for event logs and immutable ledger tables. It is not worth it for tables with widespread updates, where you would be rebuilding CDC by hand.

Understand the MAR shape of month one

A backfill counts. Every distinct primary key the initial sync touches is an active row for that month, so month one typically looks like a large one-off spike followed by a much lower steady state. That is expected - the failure mode is not knowing it was coming, or repeating it.

Things that quietly repeat a backfill and should be treated as incidents:

A re-sync triggered by an unhandled schema change or a broken replication slot.
Changing a connector setting that forces a full table re-sync.
Re-creating a connection rather than fixing the existing one.
Someone "testing" by cloning the connector into a second destination.

Budget the spike in advance with a rough estimate - rows to backfill plus expected monthly changed rows - and check it against the connection-level figures once the sync completes. Our notes on connection-level MAR pricing and on cost-aware schema drift handling go deeper on both.

Monitor the run properly

A multi-day sync needs more than an occasional glance at the dashboard. Put three signals somewhere visible:

Progress, from the connector's sync status and the row counts landing in the destination. A simple daily SELECT COUNT(*) per target table against the known source count gives you a completion percentage and an ETA.
Source pressure - replication slot lag, log disk usage, replica lag, long-running query alerts.
Failures and restarts. The Fivetran Platform Connector and webhooks give you sync start, end and failure events you can alert on rather than watching a UI; see pipeline observability from the Platform Connector.

And resist the urge to intervene. Pausing and resuming a long initial sync to "try something" is how a 40-hour job becomes a restarted 40-hour job. Change one thing, wait a full cycle, measure.

Validate before you declare it done

When the sync completes, reconcile before anyone builds on it:

Row counts per table, source versus destination, accepting a small delta for rows changed during the run.
A checksum or sum of a numeric column for the largest tables - counts alone will not catch a truncation or a type coercion problem.
Spot-check the widest and the oddest types: high-precision decimals, timezone-aware timestamps, JSON columns, and anything the source stores as an unsigned integer.
Confirm deleted rows behave as expected before history matters - our post on handling deletes and _fivetran_deleted covers the traps.

Then write down the completion date, the final row counts and the MAR the backfill consumed. That record is what turns next quarter's "can we add the other source?" into a ten-minute estimate instead of a guess.

The short version

Profile the source, cut what nobody asked for, read from a replica with generous log retention, stage the backfill smallest-to-largest with priority-first sync where it exists, consider a separate bulk load for immutable history, expect the month-one MAR spike and make sure it never happens twice.

SyncSpur plans and runs these backfills for teams whose source databases are too big to experiment on. If you are staring at a billion-row table and a DBA with strong opinions, get in touch - or read how we approach migration to the Fivetran platform and performance tuning in Fivetran.