SFTP

SFTP provides a secure and encrypted method for transferring files between a client and a server over a network. SFTP uses SSH (Secure Shell) for authentication and data encryption, ensuring that file transfers are protected from unauthorized access and interception. It offers strong security features such as public key authentication, password encryption, and data integrity checks. SFTP operates on a client-server model similar to FTP, but with the added benefit of secure data transmission. It is commonly used in scenarios where data security is a top priority, such as transferring sensitive files, backup operations, and remote file management. SFTP has become a popular choice for secure file transfers due to its ease of use, platform compatibility, and robust security measures.

πŸ”§ Setup Guide

Step 1 - Begin Configuration

  1. After selecting Data Sources in the side menu, click + New on the top right.
  2. Find the SFTP connector from the list and click on it.
  3. In the connector setup form, enter the connection name of your choice.
  4. Fill in the required fields:
  • Host - the IP address or domain name of the SFTP server.
  • User - the username used to authenticate with the SFTP server.
  • Password (optional) - the password used to authenticate with the SFTP server (can be left empty if you are using SSH private key authentication).
  • Port - the port number used for the SFTP connection (default is 22).

You can also select Use SSH Private Key Authentication if you prefer to use a private key for authentication instead of a password, and I want to provide my own private key instead if you prefer pasting your own private key (starting with -----BEGIN *** PRIVATE KEY-----).

Using a private key is more secure than using a password.

Step 2 - Connection Settings

Fill in the following fields:

  • Table name - the name of the table you want to create in the destination schema.
  • Folder path - the path to the folder on the SFTP server where the files are located.
  • File Regex pattern - a regular expression pattern to match the files you want to load.
    • Make sure all the files have the same schema (same columns and same data types).
    • An example regex that finds all CSV files with a product prefix is product.*csv. This will return files such as product_123.csv, productABC.csv, etc.
  • File type - the file type of the files you want to load (currently only supports CSV).
    • Delimiter - the delimiter used in the files (only for CSV files).
    • Skip number of header lines - the number of header lines to skip in the files (only for CSV files).

Step 3 - Data to sync

  1. Select the data you want to sync from the SFTP server to the destination schema.
  2. You can view the schema, remove columns, and setup the type of sync:
  • Full sync - sync all data from the source to the destination.
  • Incremental sync - sync only new or updated data from the source to the destination. This requires setup of Primary Key column(s) and Cursor.

Weld fields:

We automatically add the following fields to the schema:

  • _file: the file name
  • _modified: the timestamp when the file was last modified
  • _line: the line number of the record in the CSV file

By default you can use the _file and _line field as the primary keys for incremental sync, as these will be unique for each record, or you can choose your own primary key.
The _modified field can be used as the cursor for incremental sync, as it will track the last modified time of the file. Otherwise, you can use any timestamp or incremental field in your data.

Step 4 - Configure sync

  1. Select how often you would like the data to sync.
  2. Provide a unique destination schema name.

Weld will take over from here and commence syncing data from your SFTP server.

Was this page helpful?