By Rudderstack Team

How to load data from Salesforce to Redshift

This post will help you with syncing your Salesforce data to Amazon Redshift. By doing this you will be able to perform advanced analytics on a system that is designed for this kind of data payload, like Amazon Redshift. Alternatively, you can simplify the process of syncing data from Salesforce to Redshift by using RudderStack, where the whole process will be handled by RudderStack and you can focus on what matters, the analysis of your data.

Extract your data from Salesforce

You can’t use a Data Warehouse without data, so the first and most important step is to extract any data you want from Salesforce. Salesforce has many products and it’s also a pioneer in cloud computing and the API economy. This means that it offers a plethora of APIs to access the services and the underlying data. In this post, we’ll focus only on Salesforce CRM, which again exposes a large number of APIs to the world. More specifically and as it can be found in this excellent post from their Helpdesk about which API to use, we have the following options:

  • Chatter API
  • Bulk API
  • Metadata API
  • Streaming API
  • Apex API
  • Apex SOAP API
  • Tooling API

Pull data from the Salesforce API

From the above list, the complexity and feature richness of the Salesforce API is more than evident. The REST API and the SOAP API are exposing the same functionalities but using different protocols. Interacting with the first can be done by using tools like CURL or by using HTTP clients for your favorite language or framework. A few suggestions:

The Salesforce REST API supports OAuth 2.0 authentication, more information can be found in the Understanding Authentication article. After you successfully authenticate with the API, you have to start interacting with its resources and start fetching data from it in order to load them on a data warehouse. It’s easy to get a list of all the resource we have access to, for example using curl we can execute the following:

curl -H "Authorization: Bearer token"

A typical response from the server will be a list of available resources in JSON or XML. The Salesforce API is very expressive, it also supports a language called Salesforce Object Query Language (SOQL) for executing arbitrarily complex queries. Again, the result can be either in JSON or XML serialization. We would recommend using JSON as it will make the whole data moving process easier because the most popular data warehousing solutions natively support it. With XML you might have to transform it first into JSON before loading data to the repository.

More information about SOQL can be found on the Salesforce Object Query Language specification page. Although the protocol changes the architecture of the API remains the same so again you will be able to access the same resources etc. After you have your client ready and you are able to communicate with Salesforce you need to perform the following steps:

  1. Decide which resources to extract
  2. Map these resources to a schema of the data warehouse repository that you will use
  3. Transform your data into it and
  4. Load the transformed data on the repository based on the instructions below

As you can see, accessing their API alone is not enough for ensuring the operation of a pipeline that will safely and on time deliver your data on a data warehousing solution for analysis.

Pull Data using the Salesforce Streaming API

Another interesting way of interacting with SalesForce is through the Streaming API. With it you define queries and every time something changes to the data that register to this query you get notifications. So for example, every time you get a new account created Salesforce’s API will push notification about the event to your desired service. This is an extremely powerful mechanism that can guarantee almost real-time updates on a Data Warehouse repository. In order to implement something like that though, you need to take into consideration the limitations of both ends, while ensuring that delivery semantics that your use case requires for the data management infrastructure that you will build.
For more information, you can read the documentation of the Streaming API.

About Salesforce

Salesforce is the innovative company behind the world’s #1 CRM platform that employees can access entirely over the Internet — there’s no infrastructure to buy, set up, or manage — you just log in and get to work.

But Salesforce has become something much bigger than its CRM solution, currently, it offers products for analytics, marketing, data, and even cloud infrastructure for IoT. It’s easy to understand that there’s an abundance of data that your company is generating on Salesforce if you are using its products.

Prepare your Salesforce data for Amazon Redshift

Amazon Redshift is built around industry-standard SQL with added functionality to manage very large datasets and high-performance analysis. So, in order to load data into it, you will have to follow its data model which is a typical relational database model. The data yo