SFTP

Transfer data at scale from your warehouse to your apps through SFTP

Supported syncing

Object Type	Description	Supported Sync Modes
Any data set	Sync data from a source to SFTP as CSV, JSON, NDJSON, XML, or Parquet files	Insert, All, Diff

All: All mode creates one file with all the rows in the query results, every time the sync runs.
Insert: Insert mode creates one file with the rows that were added since the last sync.
Diff: Diff mode creates three files, one for rows added, one for rows changed, and another for rows removed since the last sync.

For more information about sync modes, refer to the sync modes docs.

Go to the Destinations overview page and click the Add destination button. Select SFTP and click Continue. You can then authenticate Hightouch to SFTP by entering the following required fields into Hightouch:

Hostname
Port
Credentials (see the supported authentication methods below)

The user needs to have access to the target directory and file with write privileges. A permission denied error message during a sync indicates the user may not have write permissions for both the directory and the file.

You may need to allowlist Hightouch's IP addresses to let our systems connect to your SFTP server. Reference our networking docs to determine which IP addresses you need to allowlist.

Supported authentication methods

We support three authentication methods:

Username + password
Username + SSH private key
Username + SSH private key + passphrase

We support two formats for the SSH private key:

OpenSSH
RSA

Provide the full private key for the user, including the header and footer.

For example:

-----BEGIN OPENSSH PRIVATE KEY-----
<private key body>
<private key body>
<private key body>
-----END OPENSSH PRIVATE KEY-----

PGP encryption

The SFTP destination supports PGP encryption. Given a PGP Public Key, all files transferred to the destination will be encrypted.

To use PGP encryption, provide your public key in the PGP Public Key text area including the header and footer in the following format:

-----BEGIN PGP PUBLIC KEY BLOCK-----

<PGP public key body>
<PGP public key body>
<PGP public key body>
-----END PGP PUBLIC KEY BLOCK-----

The PGP Private Key is required to decrypt the files synced to your destination. You don't need to insert this value in Hightouch.

Sync configuration

Once you've set up your SFTP destination and have a model to pull data from, you can set up your sync configuration to begin syncing data. Go to the Syncs overview page and click the Add sync button to begin. Then, select the relevant model and the SFTP destination you want to sync to.

Select file format

Hightouch supports syncing CSV, JSON, NDJSON, XML, and Parquet files via SFTP.

Enter filename

The filename field lets you specify the parent directory and the name of the file you want to use for your results. You can include timestamp variables in the filename, surrounding each with {}. Hightouch supports these timestamp variables:

YYYY: Represents the full year in four digits.
YY: The last two digits of the year.
MM: Two-digit month format (01-12).
DD: Two-digit day format (01-31).
HH: Two-digit hour format in 24-hour clock (00-23).
mm: Two-digit minute format (00-59).
ss: Two-digit second format (00-59).
ms: Three-digit millisecond format.
X: Unix timestamp in seconds.
x: Unix timestamp in milliseconds.

All dates and times are UTC.

For example, you could enter upload/{YYYY}-{MM}-{DD}-{HH}-{mm}-result.json to dynamically include the year, month, date, hour, and minute in each uploaded file. Hightouch would insert each file in the upload directory, which would need to already exist in your bucket.

You can also use other variable values to include sync metadata in the filename:

{model.id}
{model.name}
{sync.id}
{sync.run.id}

If a file already exists at the path you entered at the time of a sync, Hightouch overwrites it.

If you are using an audience and would like to include the audience name, you will still use {model.name}.

Set filename offset

By default, Hightouch uses the timestamp of the sync run to fill in timestamp variables. You can optionally include an offset in seconds. For example, if you want the filename's date to be 24 hours before the sync takes place, enter -86400 (24 hours * 60 minutes * 60 seconds). If you want the filename's data to be one hour after the sync takes place, you would enter 3600 (60 minutes * 60 seconds).

CSV options

If you're syncing to a CSV file, you have additional configuration options:

Delimiter: Your options are comma (,), semicolon (;), pipe (|), tilde (~), and tab
(Optional) Whether to include a CSV header row in the exported files
(Optional) Whether to include a byte order mark (BOM) in the exported files, the BOM is <U+FEFF>

Columns to sync

You can export all columns exactly as your model returns them or choose to export specific ones.

If you need to rename or transform any column values you're syncing, you can use the advanced mapper to do so. If you choose this option, Hightouch only syncs the fields you explicitly map.

The preceding example shows how to selectively export the id, email and location. These columns are mapped to new fields in the destination file as user_id, user_email and user_location. Hightouch exports these fields to the new fields in the file and ignores all other columns from your results.

Boolean values that appear as True and False in your model appear as 1 and blank respectively in the file created during the sync. If you'd like for these values to appear in another format in your downstream file, you can use template mapping to achieve your desired format.

Batch size

By default, and depending on your sync mode, Hightouch sends one file for each export. (In diff mode, Hightouch sends three files.)

By enabling batching, you allow Hightouch to send multiple files if your model query results are large. This can help avoid file size errors.

Empty file results

You can select how Hightouch should handle empty results files. Empty result files can occur if your model's query results haven't changed since the last sync.

You can select whether to skip empty files. If you skip empty files, it means Hightouch won't export any files if your model's query results haven't changed since your last sync.

Encoding

By default, Hightouch saves the file in ASCII encoding. If there are any non-ASCII characters in your results, the file will be saved with utf-8 encoding. If you've enabled including a BOM, the file will be saved with utf-8 encoding with BOM.

Tips and troubleshooting

The Test connection button located on the destination configuration page tries to connect to your configured server, but it can only detect if your credentials are completely invalid. To fully test user permissions, you can create and run a one-row test sync to verify that your data is successfully synced to your SFTP server.

Common errors

If you encounter an error or question not listed below and need assistance, don't hesitate to . We're here to help.

Renaming temporary file to target file failed

During an SFTP sync, Hightouch writes the data to a temporary file in your server and then renames it to the final filename as specified by your sync configuration. This is an important step to avoid partial data writes and consumption by your downstream processes that can happen due to network issues.

Failure at this step indicates that the temporary file has been consumed and/or deleted by a downstream process that exists in the SFTP server before the sync has concluded.

Our suggestion is to adjust directory listeners to ignore temporary files, which are prefixed with a period ., or provide a different temporary file directory in your sync configuration.

Otherwise, this error can also occur if the credentials you're using don't have the necessary permissions to rename the temporary file or modify its directory.

Sync alerts

Hightouch can alert you of sync issues via Slack, PagerDuty, SMS, or email. For details, please visit our article on alerting.