Connect Teradata Vantage to Salesforce using Amazon Appflow
Overview
This how-to describes the process to migrate data between Salesforce and Teradata Vantage. It contains two use cases:
- Retrieve customer information from Salesforce, and combine it with order and shipping information from Vantage to derive analytical insights.
- Update
newleads
table on Vantage with the Salesforce data, then add the new lead(s) back to Salesforce using AppFlow.
Amazon AppFlow transfers the customer account data from Salesforce to Amazon S3. Vantage then uses Native Object Store (NOS) read functionality to join the data in Amazon S3 with data in Vantage with a single query.
The account information is used to update the newleads
table on Vantage. Once the table is updated, Vantage writes it back to the Amazon S3 bucket with NOS Write. A Lambda function is triggered upon arrival of the new lead data file to convert the data file from Parquet format to CSV format, and AppFlow then inserts the new lead(s) back into Salesforce.
About Amazon AppFlow
Amazon AppFlow is a fully managed integration service that enables users to securely transfer data between Software-as-a-Service (SaaS) applications like Salesforce, Marketo, Slack, and ServiceNow, and AWS services like Amazon S3 and Amazon Redshift. AppFlow automatically encrypts data in motion, and allows users to restrict data from flowing over the public internet for SaaS applications that are integrated with AWS PrivateLink, reducing exposure to security threats.
As of today, Amazon AppFlow has 16 sources to choose from, and can send the data to four destinations.
About Teradata Vantage
Teradata Vantage is the connected multi-cloud data platform for enterprise analytics, solving data challenges from start to scale.
Vantage enables companies to start small and elastically scale compute or storage, paying only for what they use, harnessing low-cost object stores and integrating their analytic workloads. Vantage supports R, Python, Teradata Studio, and any other SQL-based tools.
Vantage combines descriptive, predictive, prescriptive analytics, autonomous decision-making, ML functions, and visualization tools into a unified, integrated platform that uncovers real-time business intelligence at scale, no matter where the data resides.
Teradata Vantage Native Object Store (NOS) can be used to explore data in external object stores, like Amazon S3, using standard SQL. No special object storage-side compute infrastructure is required to use NOS. Users can explore data located in an Amazon S3 bucket by simply creating a NOS table definition that points to your bucket. With NOS, you can quickly import data from Amazon S3 or even join it with other tables in the Vantage database.
Prerequisites
You are expected to be familiar with Amazon AppFlow service and Teradata Vantage.
You will need the following accounts, and systems:
- Access to a Teradata Vantage instance.
Remarque
If you need a test instance of Vantage, you can provision one for free at https://clearscape.teradata.com
- An AWS account with the role that can create and run flows.
- An Amazon S3 bucket to store Salesforce data (i.e., ptctsoutput)
- An Amazon S3 bucket to store raw Vantage data (Parquet file) (i.e., vantageparquet). This bucket needs to have policy to allow Amazon AppFlow access
- An Amazon S3 bucket to store converted Vantage data (CSV file) (i.e., vantagecsv)
- A Salesforce account that satisfies the following requirements:
- Your Salesforce account must be enabled for API access. API access is enabled by default for Enterprise, Unlimited, Developer, and Performance editions.
- Your Salesforce account must allow you to install connected apps. If this is disabled, contact your Salesforce administrator. After you create a Salesforce connection in Amazon AppFlow, verify that the connected app named "Amazon AppFlow Embedded Login App" is installed in your Salesforce account.
- The refresh token policy for the "Amazon AppFlow Embedded Login App" must be set to "Refresh token is valid until revoked". Otherwise, your flows will fail when your refresh token expires.
- You must enable Change Data Capture in Salesforce to use event-driven flow triggers. From Setup, enter "Change Data Capture" in Quick Find.
- If your Salesforce app enforces IP address restrictions, you must whitelist the addresses used by Amazon AppFlow. For more information, see AWS IP address ranges in the Amazon Web Services General Reference.
- If you are transferring over 1 million Salesforce records, you cannot choose any Salesforce compound field. Amazon AppFlow uses Salesforce Bulk APIs for the transfer, which does not allow transfer of compound fields.
- To create private connections using AWS PrivateLink, you must enable both "Manager Metadata" and "Manage External Connections" user permissions in your Salesforce account. Private connections are currently available in the us-east-1 and us-west-2 AWS Regions.
- Some Salesforce objects can't be updated, such as history objects. For these objects, Amazon AppFlow does not support incremental export (the "Transfer new data only" option) for schedule-triggered flows. Instead, you can choose the "Transfer all data" option and then select the appropriate filter to limit the records you transfer.
Procedure
Once you have met the prerequisites, follow these steps:
- Create a Salesforce to Amazon S3 Flow
- Exploring Data using NOS
- Export Vantage Data to Amazon S3 using NOS
- Create an Amazon S3 to Salesforce Flow
Create a Salesforce to Amazon S3 Flow
This step creates a flow using Amazon AppFlow. For this example, we're using a Salesforce developer account to connect to Salesforce.
Go to AppFlow console, sign in with your AWS login credentials and click Create flow. Make sure you are in the right region, and the bucket is created to store Salesforce data.
Step 1: Specify flow details
This step provides basic information for your flow.
Fill in Flow name (i.e. salesforce) and Flow description (optional), leave Customize encryption settings (advanced) unchecked. Click Next.