Already have an account? Sign in

SFTP

SFTP feature snapshot

A high-level look at Stitch's SFTP (v1) integration, including release status, useful links, and the features supported in Stitch.

Connecting SFTP

SFTP setup requirements

To set up SFTP in Stitch, you need:

• Files that adhere to Stitch’s file requirements:

  First-row header (CSV files only)	Every file must have a first-row header containing column names. Stitch assumes that the first row in any file is a header row, and will present these values as columns available for selection. Note: If you are a Stitch Advanced or Premium customer, have a signed BAA with Stitch, and are replicating data subject to HIPAA,header rows must not contain any PHI. Files with the same first-row header values, if including multiple files in a table. Stitch’s SFTP integration allows you to map several files to a single destination table. Header row values are used to determine a table’s schema. For the best results, each file should have the same header row values. Note: This is not the same as configuring multiple tables. See theSearch pattern section for examples.
  File types	CSV (.csv) Text (.txt)
  Compression types	These files must be correctly compressed or errors will surface during Extraction. gzip compressed files (.gz) zip compressed archive (.zip)
  Delimiters (CSV files only)	Comma (,) Tab (/t) Pipe (|) Semicolon (;)
  Character encoding	The default encoding is UTF-8, but it can be changed to any encoding format supported by Python 3.9. For more information, see thePython documentation.

Step 1: Whitelist Stitch's IP addresses

For the connection to be successful, you’ll need to configure your firewall to allow access from our IP addresses.

The IP addresses you’ll whitelist depend on theData pipeline region your account is in.

1. Sign into your Stitch account, if you haven’t already.
2. ClickUser menu (your icon) > Edit User Settings and locate theData pipeline region section to verify your account’s region.

3. Locate the list of IP addresses for your region:

   • North America region IP addresses

   • Europe region IP addresses

4. Whitelist the appropriate IP addresses.

Step 2: Configure SSH for the SFTP server

To use SFTP, you’ll need to configure SSH via key-pair authentication. This allows Stitch to use an SSH tunnel to securely connect to your SFTP SFTP server.

If key-pair authentication isn’t configured, Stitch can still connect using username/password auth.Note: This step is required if key-pairs are required to log into the SFTP server. Alternatively, you can provide a password to use username/password authentication.

Step 2.1: Retrieve your Stitch public key

1. On the Stitch Dashboard page, click theAdd Integration button.
2. Click theSFTP icon.
3. The Stitch public key will be at the top of the page that opens.

Keep this page handy - you’ll need it in the next step.

Step 2.2: Add the public key to authorized_keys

Next, you’ll add the public key to the connecting SFTP user’sauthorized_keys file on your server. This will allow Stitch to authenticate via a public key and connect to the server.

1. Open a new terminal window and log into your SSH server.

2. Create the.ssh directory , if it doesn’t already exist for the user you used to log in:

   mkdir ~/.ssh

3. Usingchmod, alter the permissions of the directory to allow access:

   chmod700 ~/.ssh

4. Navigate to the.ssh directory:

   cd ~/.ssh

5. Usingtouch, create theauthorized_keys file:

   touchauthorized_keys

6. Set the permissions forauthorized_keys:

   chmod600 authorized_keys

7. Import theStitch public key intoauthorized_keys, replacing<PUBLIC KEY> with the public key. Verify after replacing the key that it is on one line and doesn’t contain line breaks:

   echo"<PUBLIC KEY>">> ~/.ssh/authorized_keys

Step 3: Add SFTP as a Stitch data source

1. If you aren’t signed into your Stitch account,sign in now.

2. On the Stitch Dashboard page, click theAdd Integration button.

3. Locate and click theSFTP icon.

4. Fill in the fields as follows:

   • Integration Name: Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

     For example, the name “Stitch SFTP” would create a schema calledstitch_sftp in the destination.Note: The schema name cannot be changed after the integration is saved.

   • Host (Endpoint): Enter the host address (endpoint) used by the SFTP server. For example: This could be a network address such as192.68.0.1, or a server endpoint likeserver.hosting-provider.com.

   • Port: Enter the port used by the SFTP server. The default is22.

   • Username: Enter the username of the SFTP user. This should be the same user that added the public key toauthorized_keys inStep 2.2.

   • Password:Note: If youconfigured SSH for the SFTP server, you don’t need to enter a password.

     Otherwise, enter the password of the SFTP user.

Step 4: Select an encoding format

The default encoding format isutf-8, but you can update it by entering the name of the format in theEncoding Format field.

Note: The selected format will be used for all files selected. If a file is encoded in a different format, an error may occur. If you need to replicate data from files with different encoding formats, you should consider creating a different connection for each format.

Step 5: Configure tables

Next, you’ll indicate which file(s) you want to include for replication. You can include a single file, or map several files to a table. Refer to theSetup requirements section for info about what Stitch supports for SFTP files.

In the following sections, we’ll walk you through how to configure a table in Stitch:

• Step 5.1: Define the table’s search settings

• Step 5.2: Define the table’s name

• Step 5.3: Define the table’s Primary Key

• Step 5.4: Specify datetime fields

• Step 5.5: Configure additional tables

Step 5.1: Define the table's search settings

In this step, you’ll tell Stitch which files on your SFTP server you want to replicate data from. To do this, you’ll use theSearch Pattern andDirectory fields.

Step 5.1.1: Define the Search Pattern

TheSearch Pattern field defines the search criteria Stitch should use for selecting and replicating files. This field accepts regular expressions, which can be used to include a single file or multiple files.

When creating a search pattern, keep the following in mind:

• If including multiple files for a single table, each file should have the same header row values.
• Special characters such as periods (.) have special meaning in regular expressions. To match exactly, they’ll need to be escaped. For example:.\
• Stitch uses Python for regular expressions, which may vary in syntax from other varieties. Try usingPyRegex to test your expressions before saving the integration in Stitch.
• Search patterns should account for how data in files is updated. Consider these examples:

customers\.csv

(customers-).*\.csv

Step 5.1.2: Limit file search to a specific directory

TheDirectory field limits the location of the file search Stitch performs during replication jobs. When defined, Stitch will only search for files in this location and select those that match thesearch pattern.Note: This field is not a regular expression.

To define a specific location in the SFTP server, enter the directory path into theDirectory field. For example:data-exports/lists/ will exactly matchdata-exports/lists/.

Step 5.2: Define the table's name

In theTable Name field, enter a name for the table. Keep in mind that each destination has its own rules for how tables can be named. For example: Amazon Redshift table names can’t exceed 127 characters.

If the table name exceeds the destination’s character limit, thedestination will reject the table entirely. Refer to thedocumentation for your destination for more info about table naming rules.

Step 5.3: Define the table's Primary Key

In thePrimary Key field, enter one or more header fields (separated by commas) Stitch can use to identify unique rows. For example:

account_id,date

Step 5.4: Specify datetime fields

In theSpecify datetime fields field, enter one or more header fields (separated by commas) that should appear in the destination table asdatetime fields instead of strings. For example:

created_at,updated_at

Step 5.5: Configure additional tables

If you want to add another table, click theConfigure another table? link below theSpecify datetime fields field. Otherwise, move onto theSync historical data section.

Stitch doesn’t enforce a limit on the number of tables that you can configure for a single integration.

Step 6: Define the historical sync

For example: You’ve added acustomers.*\csv search pattern and set the integration’s historicalStart Date to 1 year.

• During the initial replication job, Stitch will fully replicate the contents of all files that match the search pattern that have been modified in the past year.

• During subsequent replication jobs, Stitch will only replicate the files that have been modified since the last job ran.

As files included in a replication job are replicated in full during each job, how data is added to updated files can impact your row count. Refer to theData replication section for more info on initial and subsequent replication jobs.

Step 7: Create a replication schedule

In theReplication Frequency section, you’ll create the integration’sreplication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

SFTP integrations support the following replication scheduling methods:

• Replication Frequency

• Anchor Scheduling

• Advanced Scheduling using Cron (Advanced or Premium plans only)

To keep your row usage low, consider setting the integration to replicate less frequently. See theUnderstanding and Reducing Your Row Usage guide for tips on reducing your usage.

Step 8: Select data to replicate

The last step is to select the tables and columns you want to replicate.

Note: If a replication job is currently in progress, new selections won’t be used until the next job starts.

• Selection options
• Individual tables and columns
• All tables and columns

Initial and historical replication jobs

After you finish setting up SFTP, itsSync Status may show asPending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, aPending status indicates that Stitch is in the process of scheduling the initial replication job for the integration.This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.

SFTP replication

In this section:

• Details about object and data type discovery and selecting data for replication

• Details about how data replicated from SFTP is loaded into a destination

Extraction

For every table set to replicate, Stitch will perform the following during Extraction:

• Discover table schemas and type discovered columns

• Select records (files) for replication

Discovery

During Discovery, Stitch will:

• Determine table schemas

• Type the data in discovered columns

Determining table schemas

At the start of each replication job, Stitch will analyze the header rows in the five most recently modified files returned by the table’ssearch pattern. The header rows in these files are used to determine the table’s schema.

For this reason, the structure of files replicated using SFTP should be the same for every file included in a table’s configuration. If the header row in an included file changes after the fifth most recently modified file, Stitch will not detect the difference.

For example: Based on the files in the table below, the table created from these files would haveid,name, andactive columns. Thehas_magic column in thecustomers-001.csv file will not be detected, as it’s not in the five most recently modified files.

Data typing

To determine data types, Stitch will analyze the first 1,000 rows in thefiles included in object discovery.

If a column has been specified as adatetime column, Stitch will attempt to parse the value as a date. If this fails, the column will be loaded as a nullableSTRING.

For all other columns, Stitch will perform the following to determine the column’s data type:

1. Attempt to parse the value as anINTEGER
2. If that fails, attempt to parse the value as aFLOAT
3. If that fails, type the column as aSTRING.Note: If a column contains entirely null values, it will be created as an empty column in the destination with a type ofSTRING.

Data replication

After discovery is completed, Stitch will move onto extracting data.

While data from SFTP integrations is replicated usingKey-based Incremental Replication, the behavior for this integration differs subtly from other integrations.

The table below compares Key-based Incremental Replication andReplication Key behavior for SFTP to that of other integrations.

To reduce row usage, only include updated records in new files that match a table’ssearch pattern. This will ensure that only updated records are replicated and counted towards your usage.

Loading

How data replicated from an SFTP integration is loaded into your destination depends on two factors:

1. If Primary Keys were specified for the table during integration setup. If Primary Keys aren’t specified during setup, Stitch will load data in an Append-Only manner. This means that new records and updates to existing records are appended to the end of the table as new rows.

2. If your destination supports upserts, or updating existing rows. For destinations that support upserts, Stitch uses Primary Keys to de-dupe data during loading. Primary Keys are used to identify unique rows within a table and ensure that only the most recently updated version of that record appears in your destination.

Note: For Append-Only destinations, data will be loaded in an Append-Only manner regardless of whether a Primary Key is specified during setup.

Loading with defined Primary Keys

If the destination supports upserts and Primary Keys are defined during setup, Stitch will use the Primary Keys to de-dupe records during loading.

This means that existing rows will be overwritten with the most recent version of the row. A record can only have a single unique Primary Key value, ensuring that only one version of the record exists in the destination at a time.

For example: The following rows are replicated during the initial replication job:

Before the next job, the file containing these rows is modified. This means that Stitch will replicate the contents of the entire file, including the rows forFinn andJake even if they haven’t been updated.

Stitch will use the Primary Key to de-dupe the records, making the table in the destination look similar to the following:

Loading without defined Primary Keys

If the destination is Append-Only, or if Primary Keys aren’t defined during setup, data will be loaded in an Append-Only manner.

Additionally, Stitch will append a column (__sdc_primary_key) to the table to function as a Primary Key if one isn’t defined.

Note: Appending this column will not enable Stitch to de-dupe data, as a unique value is inserted every time a row is loaded, regardless of whether it’s ever been replicated before. This means that a record can have multiple__sdc_primary_key values, each of them unique.

For example: The following rows are replicated during the initial replication job:

Before the next job, the file containing these rows is modified. This means that Stitch will replicate the contents of the entire file, including the rows forFinn andJake even if they haven’t been updated.

In the destination, the table might now look like the table below. Notice that records forFinn andJake have been appended to the end of the table with new__sdc_primary_key values:

Note: Querying Append-Only tables requires a different strategy than you might normally use. For instructions and a sample query, check out theQuerying Append-Only tables guide.

Did this article help? If you have questions or feedback, feel free tosubmit a pull request with your suggestions,open an issue on GitHub, orreach out to us.