Page tree
Skip to end of metadata
Go to start of metadata

Extract/Transform/Load jobs, or ETL, are the basic steps needed to import data from one system into another. During extraction the incoming data is parsed into generic in-memory records. The extracted data is then transformed by converting the generic records into domain objects understood by the target system. Finally, this data is loaded by persisting the domain objects in the target system.

This Article Explains

This article details how to navigate the Feeds Dashboard to setup and deploy ETL feeds loaded by a Gear developer. Please refer to the articles in the CSV Feed Requirements section of this knowledge base for more information on CSV file setup parameters.

Sections in this Article

Overview of ETL Process

At a high level this is how the ETL process works for automatic uploads:

  1. You have data you want to get into Interaction Studio. For example:
    1. You want to let Interaction Studio know about all of the people you know about in your various systems
    2. You want to upload your catalog to Interaction Studio
  2. You extract data from your systems and create a file in the format outlined in the CSV Feed Requirements article in this knowledge base, directed toward the appropriate feed
  3. While not required, you can test your CSV file using the instruction in the Test a File section in this article
  4. Once you are confident that your file is error free and in the right format required by Interaction Studio, you create an automatic job to upload the file generated into the Inbound folder of your configured Interaction Studio SFTP location
  5. To encrypt your data prior to sending it to Interaction Studio, use a PGP Public Key
  6. You activate the feed so it will continue to automatically pull files from the SFTP site
  7. Your job runs automatically and it moves from Inbound to Processing to Processed
  8. You can monitor behavior on the Feeds Dashboard
  9. SFTP Files will be retained for 60 days then automatically purged. This policy ensures compliance with regulations and reduces risk from persisted data. You can delete files manually from your FTP client.

If you upload a file manually from the Feeds Dashboard, which you may do periodically to confirm CSV format, if you don't have a frequent amount of data to add to Interaction Studio, or if you do not want to use an automatic feed to an SFTP site: 

  1. Steps 1 and 2 above
  2. After your file has finished processing, it is available in the Testing folder
  3. You can review file changes on the Feeds Dashboard before committing those changes to Interaction Studio

Key Terms

  • ETL -  is a type of data integration that includes three steps (extract, transform, load) used to blend data from multiple sources. It's often used to build a data warehouse.
  • CSV - a Comma Separated Values (CSV) file is a plain text file containing a list of data used to exchange data between systems
  • Feed - is a way to send structured, current, and up-to-date information from one system to another. Feeds are the pathway for sending files. Once a feed is configured, you'll be able to view and manage it from the Interaction Studio Feeds Dashboard
  • File - this is the data sent during the ETL job. Files are sent by feeds. After a file has been sent, you'll be able to view changed records and load the changes to Interaction Studio directly on the Feeds Dashboard
  • SFTP - is a file protocol for transferring large files securely over the web using encryption and authentication
  • Commit - overwrite existing data in Interaction Studio with new data in a file. This is done automatically with a feed, but must be done manually with a manual file upload

Navigate the Feeds Dashboard

On the Feeds Dashboard, you can review processed feeds or execute new feeds.  The feeds dashboard will show the last 30 days of history.  

  1. From the main navigation at the left, select Feeds > Feeds Dashboard
  2. The Feeds Dashboard will open on a new tab
  3. All configured feeds will be listed at the left in the vertical Feeds pane
  4. In the History section at the top of the main pane of the Feeds Dashboard, you can see how many files were loaded and how many records were updated during the current and previous weeks so you can look for discrepancies
    1. To upload a file manually, select the correct feed 
    2. Click VALIDATE OR EXECUTE and follow the instruction in the Manually Upload a File section below
  5. Below the History section is a list of recent batches that have run
    1. Click a label to sort ascending. Click again to sort descending. Click a third time to return to the default sort
    2. Click the three dots to add or remove columns or change the column sort order
    3. Select a row to see details below the list including successes and errors
  6. Select a row and click ACTIVATE to activate a feed so files begin processing. If a feed is disabled (not activated), you can still test it, but it will not check for files uploaded to the FTP site

Manually Upload a File

Typically, files are automatically uploaded, there may be instances when you need to manually upload a file. For example, you may want to confirm CSV format, you may not have a frequent amount of data to add to Interaction Studio, or you do not want to use an automatic feed to an SFTP site. You can manually upload, inspect, and commit a file to the Interaction Studio ETL system.

  1. Log into Interaction Studio
  2. From the main navigation at the left, select Feeds > Feeds Dashboard
  3. The Feeds Dashboard will open on a new tab
  4. Click Validate or Execute
  5. Select or upload a file
    1. Click UPLOAD FILE to upload a new file
      1. Locate the file on your computer
      2. Click Open
      3. The file will load and then be available for review on the Feeds Dashboard
    2. Click SELECT FILE to choose a file already manually uploaded
      1. Uploaded files appear in the Testing folder

Test a File

If you manually upload a file, you need to review staged changes so you know what that file will do to the existing data.

  1. At the bottom of the Upload File screen, in the Review Staged Changes section, click RUN TEST
  2. To the right, you can see details about file successes and errors
    1. Extracted rows indicate how many rows were extracted from your file
    2. Staged rows are how many rows will be added to Interaction Studio when you commit changes
  3. Adds, changes, and deletions are indicated below the RUN TEST button:
    1. Blue indicates an entry that has been changed
    2. Green  indicates that a new object entry has been detected
    3. Red indicates that an object entry will be removed by this test batch
    4. Rows that are not highlighted do not contain any changes
  4. Click EXPORT PRODUCTS to view a CSV of each data row and what it will change to
  5. Click COMMIT to add the data to Interaction Studio

View Uploaded Files

  1. From the main navigation at the left, select Feeds > SFTP Files
  2. The Feed Files screen will open on a new tab
  3. Uploaded files will appear in the following folders, listed in alphabetical not chronological order
    1. Failed - the file failed to load due to any error in the system (e.g. missing headers, incorrect file type)
    2. Inbound - this folder is the first destination for FTP files
    3. Outbound - this folder is where we put things to export
    4. Processed - the file has been imported
    5. Processing - the file is in the middle of importing which can take a variable amount of time based on the size of the file
    6. Testing - the file is ready for review and testing to determine whether changes are valid before you commit those changes

Use a PGP Public Key (Optional)

You can use the Interaction Studio public PGP key to encrypt sensitive data in your data feeds. Interaction Studio holds the decryption key and only decrypts ETL feed files during reading and processing. The files themselves remain encrypted. Since Interaction Studio uses a secure file transfer protocol (SFTP) to ingest feed data, you aren’t required to use PGP encryption unless your company requires it.

To encrypt your data before sending it to Interaction Studio, enter the public key into your PGP application. The PGP public key is listed in this article. Refer to the instructions provided by your PGP application for more details. No additional Interaction Studio configuration is required.

To test the decryption:

  1. Navigate to Feeds > Feeds Dashboard.
  2. Select any ETL such as UserETL.
  3. Click Validate and Execute.
  4. You can choose to upload a test file, at this time, or select an existing file.
  5. Click Select File.
  6. Choose a file from the desired folder. For example, if you uploaded a file testing, the file is in the testing folder. If the file is from an active ETL, look in the inbound folder.
  7. After you select a file, you can preview some of the file contents. If the file is encrypted, Interaction Studio decrypts the contents.

To check the existence of files without needing a preview (encrypted or not):

  1. Select the dataset you want to inspect for files.
  2. Navigate to Feeds > SFTP Files.
  3. View the files in folders such as testing or inbound.

PGP Public Key