By Rudderstack Team

How to load Data from Salesforce to SQL Data Warehouse

The purpose of this post is to help you load data from Salesforce to SQL Data Warehouse for further analysis. Information will be given on how to access and extract your data from Salesforce through its API and how to load it into SQL Data Warehouse.
Alternatively, you may check out RudderStack to load your data from Salesforce to SQL Data Warehouse in minutes for you.

Extract your data from Salesforce

You can’t use a Data Warehouse without data, so the first and most important step is to extract the 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 at this excellent post from their Helpdesk about which API to use, we have the following options:

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

Pull Data from the Salesforce Rest 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 REST API can be done by using tools like CURL or Postman by using HTTP clients for your favorite language or framework.

A few suggestions:

The SalesforceREST API supports OAuth 2.0 authentication, more information can be found in the Understanding Authentication article. After you successfully authenticate with the REST API, you have to start interacting with its resources and fetching data to load them into your 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, depending on what you have asked as part of your request.

{ "sobjects" : "/services/data/v26.0/sobjects", "licensing" : "/services/data/v26.0/licensing", "connect" : "/services/data/v26.0/connect", "search" : "/services/data/v26.0/search", "query" : "/services/data/v26.0/query", "tooling" : "/services/data/v26.0/tooling", "chatter" : "/services/data/v26.0/chatter", "recent"