Back home

SFTP Connector

Why Use SFTP with CaliberMind?

There are many use cases for SFTP (Secure File Transfer Protocol) as a means for exporting data out of one of your systems for ingest into CaliberMind. For example:

  • Custom file feeds from your internal data warehouse
  • File imports from your company’s software product
  • Migrating and consolidating legacy systems
  • Ingesting budget and financial data for ROI analysis

All CaliberMind customers get access to a free File Mage SFTP server that they can use to send data to us on a daily schedule. As part of the setup process in the CaliberMind app, you’ll be prompted to create a new File Mage account and will be issued login credentials. You should save this password in a secure password manager and can use these credentials to access the SFTP server.

There are two data load styles that the connection will permit:

  1. Upsert – Each new file will either update existing records or add new records to your table in CaliberMind based on your file’s row numbers.
This is not a sophisticated upsert and your file should be structured so that subsequent files reflect previous file row designations. For example, if you have 10 records and the records correlating to yesterday file’s row 2 and 7 are updated, you will want to update rows 2 and 7 accordingly and leave the other 8 rows as-is. If you have three new records to add, you should assign those records to row 11, 12, and 13.
  1. Append – Your new files will only contain net new data to be written as a new record. This process will not check for existing records or deduplicate data. If you write existing records to this data load file, you will create duplicates. This style works well for login data, event logs, etc.
TIP – When creating a CSV, please be cautious about special characters in your file:
* All quotes before commas should be escaped using backslash () or excluded
* All files should contain column headers and no empty headers
* No files should contain empty rows or footer comments
* Dates should use the YYYY-MM-DD format
* Dollar values should not contact dollar signs (100 versus $100)

For more information on file and folder formatting, check out Fivetran’s documentation.

SFTP Connector Setup

Navigate to the connector and find the SFTP connector card:

 

From there you will be prompted to give the SFTP table a name, after which you will be taken to a step-by-step process to set up the connection. 

Related Articles

Table of Contents