Skip to main content

SFTP

Prerequisites​

  1. Access to an SFTP server with appropriate permissions
  2. Either password or SSH private key for authentication
  3. Write permissions to the target directory on the SFTP server

Setup Guide​

Step 1 - Configure Your SFTP Server​

Ensure your SFTP server is configured and accessible:

  1. Verify SFTP Service: Ensure the SFTP service is running on your server (typically on port 22)

  2. Create User Account: Set up a dedicated user account for Extract with appropriate permissions

  3. Directory Permissions: Ensure the user has write permissions to the target directory

  4. Network Access: If your SFTP server has firewall rules, whitelist Extract's server IPs:

    • 3.134.124.160
    • 3.150.64.207
    • 44.232.26.19
    • 54.214.149.234

Step 2 - Choose Your Authentication Method​

Extract supports two authentication methods for SFTP:

Option A: Password Authentication

  • Simple username/password authentication
  • Easier to set up
  • Suitable for internal or trusted networks

Option B: SSH Key Pair Authentication (Recommended)

  • More secure with public key cryptography
  • No password transmission over the network
  • Recommended for production environments

Step 3A - Set Up Password Authentication​

If using password authentication:

  1. Create or use an existing user account on your SFTP server
  2. Set a strong password for the account
  3. Note the username and password for configuration in Extract

Step 3B - Set Up SSH Key Authentication​

If using SSH key authentication:

  1. Extract generates the SSH key pair for you:

    • When you navigate to the SFTP destination setup in Extract, an SSH key pair is automatically generated
    • Extract will display the public key (e.g., ssh-ed25519 AAAAC3NzaC1lZDI1NTE5... extract)
    • Copy this public key

  2. Add the public key to your SFTP server:

    • SSH into your SFTP server as the designated user
    • Add the public key to the authorized keys file:
    echo "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5... extract" >> ~/.ssh/authorized_keys
    • Ensure proper permissions:
    chmod 700 ~/.ssh
    chmod 600 ~/.ssh/authorized_keys

  3. Complete the setup in Extract:

    • The private key is securely managed by Extract
    • You only need to configure the host, port, username, and remote path
    • No need to handle or provide any private key

Step 4 - Configure the Connector in Extract​

Navigate to the Destinations tab in Extract and add a new SFTP destination with the following parameters:

  • Host - The hostname or IP address of your SFTP server
  • Port - The SFTP port (default: 22)
  • Username - The username for SFTP authentication
  • Authentication Method - Choose between:
    • Password - Enter the password for the user account
    • SSH Key Pair - Use the automatically generated SSH key pair (public key shown in setup)
  • Remote Path - The path template for uploaded files (see Path Templating section)
  • Output Format - Choose between CSV or Parquet

Note: For SSH key authentication, Extract automatically generates and manages the key pair. You only need to add the provided public key to your SFTP server.

Hit Save and verify the connection is successful.

Configuration Parameters​

  • Host - The hostname or IP address of your SFTP server (e.g., sftp.example.com or 192.168.1.100)

  • Port - The port number for SFTP connection. Defaults to 22 if not specified.

  • Username - The username for authenticating with the SFTP server

  • Password - Password for authentication. Used when selecting password authentication method. Either Password or SSH Key Pair is required.

  • SSH Key Pair - When selected, Extract automatically generates an SSH key pair. The public key is displayed during setup for you to add to your SFTP server's authorized_keys file. Either SSH Key Pair or Password is required.

  • Remote Path - Template for the file path on the SFTP server. Supports dynamic variables for flexible file organization. If the path does not end with the appropriate file extension, Extract appends it automatically (for example, .csv, .parquet, or .csv.gz when Compress is enabled for CSV).

  • Output Format - The format for data files:

    • csv - Comma-separated values, ideal for tabular data and compatibility with spreadsheet applications
    • parquet - Columnar format, optimal for analytics workloads with better compression

  • Compress - When enabled, Extract compresses CSV output files using gzip (.gz). Defaults to false.

  • Compat Mode - Enables compatibility mode for SFTP uploads. Defaults to false.

Path Templating​

The remote path template allows you to organize your data using dynamic variables. This helps create a logical directory structure on your SFTP server.

Available Template Variables​

File Format:

  • {extension} - Automatically adds the file extension based on output format (.csv or .parquet)

Time-based Variables (Current Sync Time):

  • {date_time} or {timestamp} - Format: YYYY_MM_DD_HH_MM_SS
  • {iso_date_time} - ISO 8601 format (e.g., 2024-01-15T14:30:00Z)
  • {date} - Format: YYYY_MM_DD
  • {dashed_date} or {iso_date} - Format: YYYY-MM-DD
  • {job_timestamp} - Format: YYYY-MM-DD HH:MM:SS
  • {job_date} - Format: YYYY-MM-DD
  • {year} - 4-digit year
  • {month} - 2-digit month (01-12)
  • {day} - 2-digit day (01-31)
  • {hour} - 2-digit hour (00-23)
  • {minute} - 2-digit minute (00-59)
  • {second} - 2-digit second (00-59)

Stream and Connection Variables:

  • {stream_id} - Unique identifier for the stream
  • {stream_name} - Name of the source stream
  • {connection_id} - Unique identifier for the connection
  • {connection_run_id} - Unique identifier for the current connection run
  • {stream_run_id} - Unique identifier for the current stream run

Cursor Variables (for incremental syncs):

  • {cursor_year} - Year from cursor datetime
  • {cursor_month} - Month from cursor datetime
  • {cursor_day} - Day from cursor datetime
  • {cursor_date_time} - Cursor datetime in YYYY_MM_DD_HH_MM_SS format
  • {cursor_iso_date_time} - Cursor datetime in ISO 8601 format
  • {cursor_singular_date_time} - Cursor datetime in YYYY-MM-DD-HH-MM-SS format

Path Template Examples​

Daily partitioning by stream:

/data/{stream_name}/{year}/{month}/{day}/extract_{timestamp}

Result: /data/customers/2024/01/15/extract_2024_01_15_14_30_00.csv

Simple stream-based organization:

/extract/{stream_name}_{date}

Result: /extract/orders_2024_01_15.parquet

Hourly partitioning with cursor:

/warehouse/{stream_name}/{cursor_year}-{cursor_month}/{cursor_day}/hour_{hour}

Result: /warehouse/events/2024-01/15/hour_14.csv

With automatic extension:

/exports/{connection_id}/{stream_name}_{timestamp}.{extension}

Result: /exports/12345/products_2024_01_15_14_30_00.parquet

Note: The file extension (.csv or .parquet) is automatically appended based on your output format if not already present in the path template.

Notes​

  • The destination supports output_format values csv and parquet.

  • You can optionally enable gzip compression by setting compress: true. When enabled, the connector writes gzipped output for CSV (for example, *.csv.gz). Compression does not change Parquet output.

  • remote_path is treated as a template. If the rendered value does not already end with the expected file extension, the connector appends it automatically (.csv, .parquet, or .csv.gz when compress: true with CSV).

  • compat_mode is available for compatibility with legacy SFTP destination behavior. Leave this disabled unless you have a specific need to match an older file layout or naming convention.

Troubleshooting​

  • remote_path is required

    • If you see an error like remote_path parameter is required, set the remote_path configuration parameter.
    • The destination will append the file extension automatically if it’s missing (for example, .csv, .parquet, or .csv.gz when compress: true with CSV).

  • Unexpected filename or extension

    • The final extension is determined by:
      • output_format: csv → .csv (or .csv.gz if compress: true)
      • output_format: parquet → .parquet (compression does not change the extension)
    • If your remote_path already ends with a different extension, update it to match the configured output_format/compress settings.

  • Path templating issues

    • If you use placeholders in remote_path, verify they resolve to a valid SFTP path for your server (including required directories).
    • If the upload succeeds but the file is not where you expect, log/inspect the resolved path by temporarily setting remote_path to a simple fixed value (for example, exports/output) to confirm connectivity and permissions, then reintroduce templating.

  • Authentication failures

    • SFTP authentication requires either password or ssh_key_pair (with private_key).
    • If you get an authentication error, confirm:
      • The username is correct.
      • You provided exactly one valid auth method (password or key).
      • The private key is in the expected format for your SFTP server.

  • Permission or “No such file” errors

    • Ensure the target directory exists on the SFTP server and the user has write permissions.
    • If your server does not allow creating directories, pre-create the directory structure referenced by remote_path.