SyncSpur | Ingesting Files with Fivetran: S3, SFTP and Blob Storage, Schema Inference and Folder Patterns

Every modern data stack has a file problem. The database connectors are elegant, the SaaS connectors authenticate themselves, and then a payments processor emails a nightly CSV, a logistics partner drops a gzipped extract on SFTP at an unpredictable hour, and finance exports a spreadsheet that changes column order whenever somebody edits the template.

Fivetran's file connectors handle all of that, but they behave differently from a CDC database connector in ways that catch teams out. A database connector reads a log and knows exactly what changed. A file connector reads a bucket and has to infer what changed, what the schema is, and which row wins. Most file-pipeline incidents come from that difference being misunderstood at setup time.

This tutorial covers the structured file connectors - Amazon S3, Azure Blob Storage, Google Cloud Storage, SFTP/FTP, Google Drive and Sheets - and how to configure them so they survive contact with real vendors. If your files are PDFs, contracts or images rather than tabular data, that is a different pipeline shape entirely and we covered it in building a RAG-ready document pipeline.

How a file connector actually decides what to sync

The mental model matters more than any single setting:

On each sync, the connector lists objects under the configured path and filters them against your file pattern.
It compares each matching object against what it has already ingested, using the object's last-modified timestamp and name.
New or modified objects are parsed, the schema is inferred or applied, and rows are written to one destination table per configured file group.
Every row from a re-processed file is written again - as an insert if you have no primary key, as an upsert if you do.

Three consequences fall straight out of that list, and they are the source of nearly every file-connector ticket:

A file that gets re-uploaded is a file that gets re-synced. Vendors who overwrite daily_orders.csv in place instead of writing daily_orders_2026-03-14.csv will re-deliver the whole file on every correction.
Listing cost grows with the bucket, not with the data. A bucket holding four years of hourly drops in one flat prefix will spend most of each sync enumerating objects it has already seen.
Without a primary key you get append-only history, including duplicates. That is sometimes exactly what you want. It is never what you want by accident.

Design the path layout before you touch the connector UI

If you have any influence over how files land - and on an internal export you usually do - specify this first. A date-partitioned, immutable, one-schema-per-prefix layout makes every later decision easy:

s3://acme-data-inbound/
  orders/
    year=2026/month=03/day=14/orders_20260314_001.csv.gz
    year=2026/month=03/day=14/orders_20260314_002.csv.gz
  refunds/
    year=2026/month=03/day=14/refunds_20260314.csv.gz

What this buys you:

One prefix per destination table. Mixing two schemas under one prefix forces the connector to reconcile columns that were never meant to be reconciled, and you will end up with a wide table full of nulls.
Immutable filenames. A corrected extract arrives as orders_20260314_002.csv.gz, never as a rewrite of _001. Corrections become new rows you can resolve downstream instead of silent re-syncs.
Date partitions you can scope. You can point the connector at a prefix and archive everything older than it without disturbing the sync.
Cheap listing. Object stores enumerate by prefix; a narrow prefix is a fast sync.

When you have no influence - a partner SFTP you do not control - do not fight it. Land their drop verbatim in a staging bucket with a small copy job that renames into the layout above, and point Fivetran at your bucket. One tiny Lambda or scheduled task is far cheaper than a year of ambiguous re-syncs.

File patterns: the setting people get wrong

Fivetran's file pattern is a regular expression matched against the path below your configured folder, not a shell glob. *.csv does not do what you think. Some patterns that do:

Intent	Pattern
All CSVs anywhere under the prefix	`.*\.csv(\.gz)?`
Only the orders feed	`orders_\d{8}(_\d{3})?\.csv\.gz`
Only 2026 partitions	`year=2026/.*\.parquet`
Everything except the vendor's checksum files	`(?!.\.md5$).\.csv`

Two rules from experience. First, anchor on the feed name, not the extension - a pattern of .*\.csv will happily swallow the _manifest.csv and README.csv a vendor adds six months in, and the resulting schema merge is unpleasant. Second, test the pattern against a listing of real object keys before the first sync, because the connector will not tell you it matched nothing; it will just report a successful sync of zero rows, and nobody looks at a green connector.

CSV, Parquet and the schema inference you are relying on

Fivetran samples the beginning of each file to infer the schema. That sampling is the weakest link in a CSV pipeline.

Parquet and Avro should be your default whenever you have a choice. The schema is in the file. Types are unambiguous, nested structures unpack predictably, and columnar compression usually cuts transfer size several-fold. Ask the vendor. Roughly half of them already produce Parquet internally and export CSV only because nobody asked.

When it has to be CSV, control these explicitly rather than trusting inference:

Headers. Every file must carry a header row, or none must and you supply the column list. A vendor who sends headers on Monday and omits them on Tuesday will silently load a header row as data.
Delimiter, quoting and escape characters. Free-text fields with embedded commas and un-escaped quotes are the classic cause of a row that parses into the wrong columns. Insist on RFC 4180 quoting.
Null representation. NULL, \N, -, "" and an empty field are five different things to a parser. Pick one and put it in the interface contract.
Type pinning for identifiers. Order numbers with leading zeros, ZIP codes, and long account IDs must be strings. Inference will read them as integers, drop the leading zero, and you will find out during a reconciliation meeting. Where the connector allows an explicit column type or a downstream cast, pin it.
Dates and time zones. 03/04/2026 is ambiguous between two continents. Require ISO-8601 with an offset.
Compression. .gz, .zip and .bz2 are handled; keep the extension accurate because the connector keys off it. Many small gzipped files sync faster than one enormous uncompressed file, but see the next section on how small is too small.

When a vendor adds a column mid-quarter, the new column appears in the destination according to your schema change policy - exactly the mechanics described in the schema drift playbook. Files make column reordering a live risk too, which is another argument for headers and for Parquet.

Primary keys, deduplication and the append-only trap

By default many file connectors load append-only: every row of every matching file becomes a new row in the destination, with _file and _line metadata columns telling you where it came from.

Three workable strategies:

Append-only plus a dedup view. Keep the raw load faithful and resolve in dbt: window by the business key, order by _file or an ingestion timestamp, keep the latest. Preferred when files are corrections-heavy or you need audit lineage back to the exact file.
Declared primary key. If the feed has a genuine stable key, declare it and let the connector upsert. Re-delivered files then update in place rather than duplicating. Only do this when the key is genuinely unique across files - a per-file row number is not a key.
Composite key including the partition date. For snapshot feeds where a key repeats every day and you want one row per key per day.

Whatever you pick, keep _file in the destination and keep it in your models. When finance asks why a number moved, being able to answer "the vendor re-delivered orders_20260314_002.csv.gz at 02:14" ends the conversation in a minute.

Sync scheduling and the small-files problem

File connectors have a fixed per-sync overhead: list the prefix, diff against state, then parse. Two failure shapes sit on either side of it.

Too many tiny files. Ten thousand 4 KB files per day means the connector spends its life on object listing and handshakes. Batch upstream to a target of roughly 100 MB - 1 GB per object where you can.

One enormous file. A single 80 GB uncompressed CSV must be parsed in one pass and is all-or-nothing on failure. Split it.

On cadence: match the sync frequency to when files genuinely arrive. A connector polling a bucket every five minutes for a file that lands once at 04:00 is burning sync slots for nothing. Better, have the drop trigger the sync - hit POST /v1/connections/{id}/sync from the Lambda or job that finishes the upload, and leave the scheduled sync as a slow safety net. That pattern and its orchestration siblings are covered in orchestrating Fivetran syncs with Airflow and Dagster.

What files cost you in MAR

This is where file pipelines quietly become expensive. Fivetran bills on monthly active rows - distinct primary keys touched in the month. File connectors interact with that badly in two specific ways:

Append-only loads count every row as active. A daily full-snapshot extract of a 5-million-row table is 150 million active rows a month, for data that barely changed. This single pattern accounts for most of the shock file-connector bills we are asked to look at.
Re-delivered files re-activate their rows. A vendor who restates 90 days of history every night has multiplied your MAR by ninety.

Mitigations, in order of impact:

Get incremental extracts instead of full snapshots. Ask the vendor for changed rows only, or for a last_modified column you can filter on. This is a commercial conversation, not a technical one, and it is usually winnable.
Declare a real primary key so re-deliveries upsert the same keys instead of inflating row counts.
Scope the file pattern to recent partitions rather than re-scanning the entire archive on a re-sync.
Model the feed before you commit. Rows per file times files per day times 30 is a MAR estimate you can do in a spreadsheet during scoping, and the arithmetic is the same one we work through in connection-level MAR pricing and in Fivetran cost optimization.

SFTP, Drive and Sheets: the awkward corners

SFTP/FTP. Prefer key-based authentication over passwords, and whitelist by Fivetran's regional egress IPs; if the server sits in a private network the options are the same ones in connecting Fivetran to databases in a private VPC. Watch for the partner whose retention policy deletes files after seven days - if a connector is paused for a week, that data is gone and no re-sync brings it back. Archive raw drops to your own object storage on arrival. Always.

Google Drive and Sheets. Genuinely useful for the finance mapping table or the manually maintained reference list, and genuinely fragile: a human can rename a column, insert a row, or change a cell to text at any time. Protect the sheet range, keep it to one tab with one header row, and treat it as reference data, never as a transactional feed. Add a dbt test asserting expected columns and row-count sanity so a broken sheet fails loudly in the model rather than quietly in a dashboard.

A pre-flight checklist

Before the first production sync:

Sample files from the real vendor - not the sanitised one they sent for the demo - parse correctly, including a file containing free-text with commas and quotes.
The file pattern matches exactly the intended objects, verified against a real listing.
Compression, delimiter, quoting, header and null conventions are documented in an interface contract both sides have signed off.
Identifier columns are typed as strings; dates are ISO-8601 with offsets.
Primary key strategy is chosen deliberately, and the append-only case has a dedup model behind it.
Raw drops are archived somewhere the connector does not control.
Freshness monitoring exists for file absence, not just sync failure. A vendor that sends nothing produces a perfectly green connector - the alerting approach in the pipeline observability guide covers how to catch that.
MAR estimate calculated and sanity-checked against the plan.

The part that is not a Fivetran problem

File pipelines fail at the interface, not in the connector. The vendor changes an export template, someone renames a folder, a retention policy trims the archive, and the sync that had run perfectly for eight months starts loading nonsense. The durable fix is a written interface contract - layout, naming, schema, compression, delivery window, correction policy - plus tests that assert it on every load.

SyncSpur builds these feeds as part of data pipeline development and connector configuration and management, including the vendor-side conversations that turn a daily full snapshot into an incremental one. If you have a file feed that is expensive, unreliable, or both, get in touch and we will review the layout and the MAR arithmetic with you.