SFTP to Snowflake: How to Move Your Data

Loading SFTP to Snowflake takes files dropped on a server by a partner, a vendor, or a legacy internal system and turns them into warehouse tables you can query. It is one of the most common pipelines in enterprise data work, because file drops remain how a great deal of business data actually moves. Airbyte handles it with no code.

The first decision is one people often make by accident. Airbyte offers two SFTP source connectors, and they are not equivalent. Choosing the wrong one caps your file formats and costs you incremental loading.

Which SFTP connector should you use?

The plain SFTP source supports CSV and JSON only, with more formats such as Avro noted as future additions. It provides a single stream per file with a dynamic schema, and it takes a folder path and a comma-separated list of file types. Airbyte Cloud users may need to enable the Alpha checkbox before it appears in the connector list.

The SFTP Bulk source is the better choice for almost every real workload. It supports Avro, CSV, JSONL, Parquet, and document file formats. It bulk ingests files matching a pattern into a single stream rather than producing one stream per file. And it loads incrementally based on when files were added or modified, which is the behaviour you actually want from a folder that receives a new drop every night.

That incremental behaviour is the deciding factor. With the plain connector, a folder accumulating daily files means re-reading everything on each run. With SFTP Bulk, only new or changed files are picked up. The rest of this guide assumes SFTP Bulk.

Matching the right files

You select files with a glob pattern, which works across all supported formats. If your files sit in a subfolder, include the folder in the pattern, as in a prefix followed by a wildcard and an extension. Use a double asterisk to match recursively through subdirectories.

This is worth getting precise rather than approximately right. A pattern that is too broad picks up archive folders, partner test files, and anything else that lands in the same tree, and each of those becomes rows in your Snowflake table. A pattern that is too narrow silently misses a file whose name convention changed. Confirm against a real directory listing before the first sync.

You also choose a delivery method. Replicating records parses file contents into rows, which is what you want for Snowflake. Copying raw files transfers them without parsing, and in that mode individual files are capped at 1.5 GB. There is a Preserve Sub-Directories option that carries the folder structure through to the destination alongside file names, which matters when the same file name recurs in different date folders.

Authentication is by password or private key. For key pair authentication, generate the keys with ssh-keygen, and note that the private key must be in PEM format as a plain text file including the BEGIN and END lines. Airbyte requires a file upload here rather than pasting the key into the field, which trips people up on first setup.

The schema problem with file sources

Files from an external party are the least stable schema you will deal with. A vendor adds a column, changes a date format, or starts quoting a field differently, and nobody tells you. CSV makes this worse because it carries no type information at all, so everything is inferred.

Two mitigations are worth building in from the start. Prefer Parquet over CSV wherever the sending party can produce it, because Parquet carries explicit types and removes an entire class of inference problem. And model over the landed tables rather than pointing dashboards straight at them, so a change in the incoming file breaks one definition you own instead of every downstream consumer at once.

Loading into Snowflake

The Snowflake destination is an Airbyte-supported connector with a high reported sync success rate, using direct-load architecture since version 4.0.0 so data goes straight to final tables.

Set the Decimal Data Type option before your first sync. It defaults to FLOAT, which is approximate binary floating point, and the alternative is NUMBER(38,9), which is exact and what Airbyte's documentation recommends. File drops frequently carry invoice totals, quantities, and prices, so this matters here. Changing it later converts the column in place, nulls stored values with more than 29 integer digits, and cannot recover precision that was never stored, so only a full refresh restores the original numbers.

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 this. Use an X-Small warehouse with a sixty-second auto-suspend dedicated to syncs, since Snowflake bills per second and resumes the warehouse on every load. Authentication is username and password or key pair, with encrypted private keys supported.

Frequently asked questions

Which SFTP connector supports Parquet?

SFTP Bulk. It handles Avro, CSV, JSONL, Parquet, and document formats. The plain SFTP source supports only CSV and JSON.

How do I avoid re-reading every file on each sync?

Use SFTP Bulk, which loads incrementally based on when files were added or modified. The plain SFTP connector has no equivalent behaviour.

My private key is rejected.

The key must be in PEM format, a plain text file containing the key between the BEGIN and END lines, and Airbyte requires it as a file upload rather than pasted into the field.

Is there a file size limit?

With the Copy Raw Files delivery method, individual files are limited to 1.5 GB. That limit applies to raw transfer, where files are copied without parsing their contents.

Get your SFTP data into Snowflake

Choose SFTP Bulk, write a precise glob pattern, prefer Parquet over CSV where you can influence it, and set the decimal type before the first load. For the same source into a lakehouse, see our guide to SFTP to Databricks. For a relational source into the same warehouse, see PostgreSQL to Snowflake.

Start syncing now →