Table of Contents

SFTP Connector - Updated 2021

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

  1. Make sure you are not logged in to the “Classic” version of CaliberMind:
Ensure you aren't logged in to the classic version of CaliberMind
  1. Navigate to the top right of your page and click the cog to get to the admin section:
Navigating to the admin section of CaliberMind
  1. On the left menu, you will see “SFTP." Select this option:

Navigating to the SFTP option in CaliberMind
  1. Click the “Create SFTP User" button.
Clicking on the create SFTP User button in CaliberMind
You can create more than one user, and each will have a unique username and password.
  1. Be sure to copy this password which will be used when creating a connection to your file storage area:
Copy your password that you will use to create a CaliberMind connection to  your file storage area
  1. In addition to the password you will need the additional information displayed on the page after closing the SFTP User Password popup:
Besides your password, you'll need additional info displayed on CaliberMind
  1. If you’ve forgotten or misplaced your password you can follow the same navigation listed above and select the “Reset Password” button:
Resetting your password using CaliberMind Reset Password button
  1. Be sure to copy this password which will be used when creating a connection to your file storage area:
Creating a CaliberMind connection to your SFTP file storage area

 

How did we do?

Act-On

Connecting Your Data Warehouse to CaliberMind

Contact