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.
노트
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.
Step 2: Configure flow
This step provides information about the source and destination for your flow. For this example, we will be using Salesforce as the source, and Amazon S3 as the destination.
-
For Source name, choose Salesforce, then Create new connection for Choose Salesforce connection.
-
Use default for Salesforce environment and Data encryption. Give your connection a name (i.e. salesforce) and click Continue.
-
At the salesforce login window, enter your Username and Password. Click Log In
-
Click Allow to allow AppFlow to access your salesforce data and information.
-
Back at the AppFlow Configure flow window, use Salesforce objects, and choose Account to be the Salesforce object.
-
Use Amazon S3 as Destination name. Pick the bucket you created earlier where you want the data to be stored (i.e., ptctsoutput).
-
Flow trigger is Run on demand. Click Next.
Step 3: Map data fields
This step determines how data is transferred from the source to the destination.
- Use Manually map fields as Mapping method
- For simplicity, choose Map all fields directly for Source to destination filed mapping.
Once you click on "Map all fields directly", all the fields will show under Mapped fields. Click on the checkbox for the field(s) you want to Add formula (concatenates), Modify values (mask or truncate field values), or Remove selected mappings.
For this example, no checkbox will be ticked.
- For Validations, add in a condition to ignore the record that contains no "Billing Address" (optional). Click Next.
Step 4: Add filters
You can specify a filter to determine which records to transfer. For this example, add a condition to filter out the records that are deleted (optional). Click Next.
Step 5. Review and create
Review all the information you just entered. Modify if necessary. Click Create flow.
A message of successful flow creation will be displayed with the flow information once the flow is created,