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

-----BEGIN PGP PUBLIC KEY BLOCK-----

mQINBF+W4d0BEADMINrr7UXjMNTLd3+4Bx4wIRF+joUt8EcGB2TdK9y5JdwHh4SB
6f1teNYtNynVp8fW1lv5HdOfIX/huwZPpfQkpbGuMXQOaV7foFYfCAhUoEyYIZ4p
GnKtiLvCv/g4BiCsBrzv7lbKSBG8iuzQgWkE0UMPYGSfAsJ1T9Z2yATPllGPkkGs
TmM3VOwriDjuLztwdh/ZnrYo4MEn2SLuFzNAgs1Ef1ImnORhOIAw9cqqeBs41xzu
fqQIuzvSVjbGLU5RRq/itb0cik86E2kwG0oEgA2pmjqNJM3G5rybpma5QC1NHeoD
63KZMbhMZb5KesmlTyHvvfbS8yr71qoWCbE2kKq0ebFD1Bl75PvLqAgwTLLhy8yG
ST39ufmADy/QzetXIEvLzVPvB1me0Wi6w/EVAuBN8tSBXHYiHcfNOG4xpuwUksqf
W4qOz8CecPSAoTujfT8x5KUQtcpalrzVkgIHU1Y8vHmsfBQ9zToOecUXEXzfXs46
1zJ1m7PvZgL044BjrOUbPR4uP73REVwxzPtmjoQ/zozAwInMM59ik52B+O2CP5qx
nrqVAXhcp3n1aPYrrYSHJYFfOmbjawQBjDAjH+u9VLW4n+kb4wpX719G2Msl+b+g
Mrs8GB7zSXppYjOWrcegynHDY0WCe/gqac0KULxc7twEvWLPaN/Epsi8rwARAQAB
tC5JbnRlcmFjdGlvbiBTdHVkaW8gPGVnLXN1cHBvcnRAc2FsZXNmb3JjZS5jb20+
iQJOBBMBCAA4FiEEZ/dEgbFrasW9317uzozX5lhyIqkFAl+W4d0CGwMFCwkIBwIG
FQoJCAsCBBYCAwECHgECF4AACgkQzozX5lhyIqkO0xAAqQanjAXhMk7DkLadLvV4
du60MH/DVu8eGl4a80F6/HsQajY/FlYWkM0FYJkB1APKjAn3lI+hDBVuIJuTZwsC
iRp48a15WPUgNPQWEt2aCb0u6I2kAruYiUDxc7Dds5SMEw+oVFDSdKefVoow2AUe
zyo55Gz77W/ORTmvGOXpb+R10MXpWXiG0ocGWrimsZ+8Xn6M0ruLx7OHFRDbN1Bf
qw6AfgpADbUXPau6qRM6Ne1wPU/T9f33H7e+LNy/Pum0VimcWPxiNz8TYahkexF2
BWwQYbIu4LER0Hyw35wfnrpOrRqt6xOow2D9+caU5jTTyhVfqxASg4FGKff3oeBb
YTIwovtFS4p63RaK477akrCbZ4aokuK3oJ6L0JObHizBrDdU8FjMtDZ7soGCvXWp
eaqxXAFC1y8C/9/t/H461BVLl5l8XFqlcxhbEyKZO/YZblPg8zcBakVonWgXv8D4
kP3VFNo54ha0UehoMbNowRco3+Ae8fJ9/yhj/jyT+hCOauQAz/TM8N8/AYanzmlM
3aNu+vCCyg/hC6vkcH3OJQ3oFU048urG8lhgftjjYDUlqHFbcm/b9g1Rz0EtQnYd
6BQCwf4KqX1Z5VjsT3YMNkk7i3VWaKw6g8igUD86njgW1bVpzi1ymLriQllAbvcx
bbK9zWcv02/YKodQWf7yXyu5Ag0EX5bh3QEQAMDH3/nfIx+b9sirOu5N52qUVYe5
JBkkK9ZQ//hPl2vf9Oi6xDQ9+gRcmHqDKE4WE0X/aWTT/5YJG8HoYbps8t0Blg/H
Jf0Nc/Msqz3A7IbdkLpF4dclCr+UMbnj6zYRL2G1a8pgW7QiZfzwWmH5BiZBW3Xy
5cAc6n6+flYeOF2wJ8YXGeyeRAZS59k4cD7ZECd5O+9k0D+pY5mc4I/6z+zIKSAW
dDy7RghMqy6hdO/iX7KkYiV3qcAWQkcpIgmRphlkdPX2daeXeD5El0ynPGdXbke1
ZrYdp0wSvmZLbiusqEAdsW7DqPYAB3pK1zcLpYKmbIPznFYZ1Ljy/SjBmwPgmuao
Kd2FCKCBfqIRlBcVGXon2N/sz51pcFD5Q1kYjtCTlpPgUQvOYK0cCffo2utBGpSs
a0Z3YjrykKYhzvWYUsGPDOPJPa0WE3ApO0wdxMItJAwFLnPXzVmlbZdXDzBOZr6d
BZMqxR61N8t9Aigp/J6xQRtK/+I7+d/20VqTxf73rA63pyZZNYQKFHYJpgzRWtts
OzXp37rwHmSdO7NMDYPN474QIPtF626uqdPkE6ZoG1Op+IC3okqjtF83suiIyMk7
c+vop6VO0uqrUADMX6ogIeNaXJJtCDbckabAARfnHig13xp5GnlYKYqtKWSWtPzW
Uw8YZ0s6ny6eSCvFABEBAAGJAjYEGAEIACAWIQRn90SBsWtqxb3fXu7OjNfmWHIi
qQUCX5bh3QIbDAAKCRDOjNfmWHIiqZTKEACj6R0KSchwpZ2b88egI7VEXSdmjCsu
uBWj3L2Yoyu0vvUGfhmX0zm6nwkhtbE4z8K7nmtDHTbEDmD+AxaKHzwGHFPqTCEN
AWAawOpX5WHP7ZH3cajWx+9myEfc7nEbqxnn90s31wymDOUNp3VvV50uJuWmUmHd
nxonGmQ1lnKxYZrWO5A6F45H4aTQWVHZ0/2T/l8n/BkS2RUUrBLMgLfspKXlE/6W
qo2lLDPEvAEnWwhhQr9l8X8MHTteM5DMLYo1ZEHqLkJhYRaZ913MpuDN+ltk0mui
DiPrxdI3GaOTlru3zqCUpDfwkfj0xu0eEO+W9QmgU5paYWXtWF5tp8Ensw0Sc6iA
Tr3514map7iwLFyaISETeXkZHWJgY9zB5sUF6JmZNkYGuQNK0TD1Tqj5vAjVYcaR
Lc2+twkcfxRnaDnnGpb3gahngFf7SAkpve7vtVkAmdTBjJJcQwwaymFQb/I8V8DF
+dh5o5kPLhczAicIj0dnZR3ydoRi4IB69T7xNh5Y7AU7dNV+z7ZrIr8dATlJEmO7
LVZ51eZrc/5PfTInEIO2HWyu0hZFzwvtewUC2RWJIR0nHqQSegnfVW3f87GNSnYq
9ztsncDHHvivCgZ6gSSXVTWpC2Et9y+BcwBvIouoMqirRLZjpeQ2XJxDjxq45pTy
2k560UYBIlaB9w==
=muvY
-----END PGP PUBLIC KEY BLOCK-----