Sftp Setup Guide

Prerequisites

• SFTP server: You will need the host address, username and one of:
  • password
  • private key
  • private key and passphrase

Setup Guide

1. Choose authentication method.
2. Enter Hostname, Port.
3. Depending on the chosen authentication method, enter one of:

• Username + Password
• Username + Add Matia's SFTP Server Public Key to your server’s list of authorized keys
• Username + Private Key
• Username + Private Key + Passphrase

ℹ️ Note:: If using SSH, please provide full private key, including header and footer. For example:

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

4. Enter a Name for the connector.
5. (Optional) Enter a Description for the connector.
6. Select the Owner of the connector.
7. (Optional) Verify that your Sftp account is successfully connected by clicking on Test Connection.
8. Click Connect.

Choose a Sync Mode

Sync Mode	Type	Description
Magic Folder Mode	Full refresh only, does not support incremental or nested folder structure.	Sync Each file in the chosen folder into a unique table.
Merge Mode	Supports incremental syncs (only updates changed or new data).	Sync All files (including those in subfolders) are combined into a single table in your destination.

Set a File Pattern (Optional)

This filters which files to sync from the folder. Please, test your regex here.

Leave it blank if you want to sync all files in the folder.

Error Handling Mode

When encountering an error, choose either:

• Fail: The sync job stops if any file throws an error.
• Skip: The sync skips over bad files and continues processing the rest.

Merge Mode – Additional Configuration:

Select File Type

All files will be treated as the selected type. Use File pattern to include only the extensions you want.

• CSV
• JSON

Select JSON Delivery Mode

Choose how you wish to structure your data in the destination:

• Packed: All data goes into one column as a JSON blob.
• Unpacked: JSON is flattened into individual columns (recommended if you want to query fields easily).

Primary Key for Upsert

When you select Merge Mode, you’ll see an option called "Primary Key for Upsert." This determines how the system updates or inserts new rows into the destination table during sync.

1. File Name and Line Number

• Use this when your files are uniquely named and only contain new, fresh data and you just want to insert new data each time or overwrite old lines in the same file.
• The system uses: _file : the filename, _line : the line number in the file
• This pair ensures each line in each file is treated as unique.

2. File Name, Line Number, and Modified

• Use this when files may contain both old and updated data, such as partial refreshes or late-arriving data. You want to maintain a more complete or versioned history.
• The system uses: _file, _line, and _modified (last modified timestamp)
• This combo allows the sync to track changes over time more intelligently.