FTP to BigQuery: How to Move Your Data

Moving files from an FTP server into BigQuery turns a folder somebody drops files into every night into rows your analysts can query. Partner feeds and vendor extracts arrive this way constantly, and they stay invisible to everybody until something loads them.

One thing to settle before you start, because it decides whether this pipeline is possible at all. There is no plain FTP connector. The connector you want is SFTP Bulk, and SFTP is a different protocol that runs over SSH, so what your server actually supports matters more than what people call it.

FTP to BigQuery at a glance:

Why move data from FTP to BigQuery?

Two situations account for most of these pipelines.

The first is automating a manual step. A partner drops files onto a server every night and somebody currently downloads them, opens them to check nothing looks odd, and uploads them somewhere else. That person is a pipeline nobody has written yet, and they are doing it on holiday too.

The second is bridging an old protocol to a modern warehouse. File transfer is how a great deal of business data still moves between organisations, and BigQuery has no way of reaching a server directly, so a pipeline is the only route rather than one option among several.

What do you need before you start?

The first item is the one that can stop the project, so confirm it before anything else:

A server that genuinely speaks SFTP. Ask whoever runs it whether SSH-based transfer is enabled, usually on port 22, rather than assuming from the word people use. The SFTP Bulk source documentation covers the connector, and the section below covers what to do if the answer is no.

Credentials, and a private key as a file if you use one. A username with either a password or a key pair. If you authenticate with a key, Airbyte requires the key to be uploaded as a file rather than pasted into the field, which catches people who are used to pasting secrets.

A folder path and a glob pattern. The pattern selects which files belong to the stream, and being specific matters, because a loose pattern that catches two differently shaped feeds produces one confused table.

A BigQuery service account and a decided dataset location. With the BigQuery User and BigQuery Data Editor roles. Queries can only reference datasets in the same physical location and that cannot be changed after creation, so match it to whatever you intend to join against.

Note also that SFTP Bulk is a Marketplace connector at an early release stage, so alert on sync failures rather than assuming silence means success.

How do you build an FTP to BigQuery pipeline in Airbyte?

Step 1: Confirm the protocol, then the delivery method

Establish that the server supports SFTP before planning anything else. Then choose the delivery method: replicate records parses your files into rows, which is what a warehouse wants, while copy raw files transfers them without parsing and is for archival cases.

Step 2: Configure the SFTP Bulk source

Click Sources in the left navigation, then New Source, and select SFTP Bulk, following adding a source. Supply the host, port and credentials, uploading the private key as a file if you use one, then set the folder path, file format and glob pattern.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key. Batched standard inserts is the default and is ample for most file feeds, with Cloud Storage staging worth considering for a large initial backlog.

Step 4: Create the connection and alert on failures

Click Connections, then New connection, select your stream and an incremental sync mode so new files are picked up without re-reading the ones already processed. Set the frequency against how often files actually arrive rather than how fresh anyone would like them.

Check the resulting table after the first sync, particularly with CSV, since that format carries no type information and the connector has to infer what each column contains.

What if your server only speaks plain FTP?

Then this connector will not reach it, and no configuration changes that. FTP and SFTP are unrelated protocols that happen to share three letters: FTP is the older unencrypted protocol on port 21, while SFTP runs inside an SSH session on port 22. A client for one cannot talk to the other.

In practice most servers people call FTP have supported SFTP for years, because sending business data unencrypted is no longer acceptable to anybody's security team. So the first move is simply to ask, and the answer is usually yes even when the habit of saying FTP persists.

Where it genuinely is plain FTP only, ask whoever runs the server to enable SFTP, or have the files landed in Cloud Storage instead and use the GCS source. That second route is particularly tidy here, since the files end up in the same cloud as your warehouse and the pipeline gets shorter rather than longer.

How should you select and consolidate files?

The connector consolidates multiple files into a single stream, which is what you want. A folder holding a year of daily extracts with the same columns becomes one table rather than 365, and that is the difference between a dataset somebody can query and a directory listing.

The glob pattern is how you control that, and precision matters because a pattern that accidentally matches two differently shaped feeds will consolidate them into one confused table. Include the folder when files sit in a subdirectory, and use a double asterisk to match recursively.

Incremental sync works from file creation or modification time, which has a consequence worth asking your partner about. A file rewritten in place gets a new timestamp and its rows arrive twice, while a file edited without changing its timestamp is never revisited and nothing reports it. Which of those happens decides what you build.

Frequently asked questions

Is there an FTP connector in Airbyte?

Not for plain FTP. The connector is SFTP Bulk, which speaks SFTP over SSH. Confirm your server supports that before planning the pipeline.

Should I use SFTP or SFTP Bulk?

SFTP Bulk. It offers features the older SFTP connector does not, including delivery method options and better file consolidation.

My private key is rejected. What is wrong?

Airbyte requires the key to be uploaded as a file rather than pasted into the field as text. Save it and upload it.

Can I change the dataset location later?

No. It is fixed at creation, and queries can only reference datasets in the same physical location, so match it to whatever you intend to join against.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You may want BigQuery views casting CSV columns, since that format carries no type information.

Get your FTP data into BigQuery

Confirm the server speaks SFTP before anything else, because that decides whether the pipeline is possible. Then create the dataset in the right location since that is permanent, upload your private key as a file, write a specific glob pattern, and ask whether files are ever rewritten in place.

Airbyte's connector catalog includes 700+ pre-built connectors, so partner files can reach a warehouse alongside your own systems. For the same source in another warehouse, see FTP to Snowflake, and for files in object storage instead, Amazon S3 to BigQuery.

Start syncing now →