Transfer data at scale from your warehouse to your apps through SFTP
To get started, you will need your hostname, port, credentials. This 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 need to allowlist Hightouch's IP addresses to let our systems contact your SFTP server. Reference our networking docs to determine which IPs you need to allowlist.
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:
Provide the full private key for the user, including the header and footer.
-----BEGIN OPENSSH PRIVATE KEY----- <private key body> <private key body> <private key body> -----END OPENSSH PRIVATE KEY-----
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-----
- All - All Mode creates a CSV file with all the rows in the query results, every time the sync is run.
- Insert - Insert mode creates a CSV file with the rows that were added since the last sync.
The file name field will allow you to specify which directory and the name of the file that you want to name your results. The parent directory for the file must exist. In the screenshot below, we've specified that we want to put the file in the upload directory and the file name is results.csv.
You can also timestamp variables in the file name, surrounding each with
We support these timestamp variables: YYYY, MM, DD, HH, mm, ss, and ms. All dates and times will be UTC.
You can also set a File Name Timestamp Offset. By default, the date/time used to generate the file name (above) is the date/time that the sync takes place; you can enter a number of seconds to offset this (offset can be positive or negative).
Ex. To set the date in the file name to 24 hours before the sync takes place, input
-86400. The default offset is
For this destination, we give you the ability to export all columns as they are represented in your model.
If you need remap the fields that you're exporting, maybe because you don't want to alter your model, you can manually map fields.
Only the fields that you map will be exported in this instance. In this example, we're just exporting
location. These fields
are mapped to the new fields in the CSV as
user_location respectfully. All other columns from your results are ignored.
We provide you the option to include a CSV header with your exported results.
We provide you the option to prepend a byte order mark (BOM) to the exported
file. The BOM is
By default, the file will be saved in ASCII encoding. If there are any non-ASCII characters in your results, the file will be saved with utf-8 encoding. If the BOM option is enabled, the file will be saved with utf-8 encoding with BOM.
This destination will not respect any sorting that you have in your model. It will export results file sorted by ID. You will need to ensure that the file that you transmit is removed from the folder before the next sync runs. This ensures that you don't encounter an error when Hightouch detects that the file already exists.
During an SFTP sync, Hightouch writes the data to a temporary file 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.
If you require further assistance, please reach out to us. We're here to help.
Hightouch can alert you of any sync issues via Slack, PagerDuty, SMS, and/or email. For details, please visit our article on alerting.