Salesforce and Snowflake Native Data Integration Options

Introduction

Salesforce and Snowflake became strong technology partners more than a year ago. That partnership fruited prebuilt, bi-directional integration options between the two leading platforms in CRM and Data domains. The solution offers easy-to-use, point-and-click integration to push CRM data into Snowflake Data Cloud and also receive analytics data from Snowflake into Salesforce. The native Salesforce and Snowflake integration is built on top of Salesforce Tableau CRM (recently renamed CRM Analytics).

Architecture

From a technical perspective, there are 4 options that the Salesforce-Snowflake native data integration features can offer:

For Salesforce to Snowflake Direction 

There are two alternatives. 

We can decide to use the Salesforce Output connector. This allows the user to transform and augment the data with a Data Recipe and then push the transformed data into Snowflake. The other option is Sync Out. It will extract the incremental raw data from Salesforce and push it into Snowflake. Sync Out is built on top of Salesforce Output Connector:

Define the Salesforce Account Table in Snowflake

As a first step, we will need to define the target table inside Snowflake:

CREATE TABLE SALESFORCE_ACCOUNT (
       ACCOUNTNAME VARCHAR(),
       TYPE VARCHAR(),
       SFDCID VARCHAR(18),
       INDUSTRY VARCHAR(255),
       ANNUALREVENUE NUMBER(12,2),
       COUNTRY VARCHAR(255),
       NUMBEROFEMPLOYEES NUMBER(10,0) 
  )

Then we can move over to Salesforce to continue the configuration there.

Enable Output Connector, Sync Out, and Direct Data in Salesforce

Before we can use the Output connector and Sync Out features, they need to be enabled under Setup=>Analytics=>Settings menu (and the same applies to Direct Data, too):

Defining Snowflake Connection

Then we should navigate to the Data Manager Application, click on Connections (or Connections Beta) and choose New Connection:

Here we need to populate the connection parameters, like Snowflake account, warehouse, database and schema, username, password, role, etc. Optionally, for the private key type of authentication, you can enter the private key and the private key passphrase:

In the end, click on Save and Test to verify the connectivity.

Defining Salesforce Data Source

Then under the Recipe menu, we can define your recipe where the input can be our data object (in our showcase, it is going to be Account), and with the help of the Change Columns option, we can also add or remove fields to be considered for data sync:

If required, we can also define transformation, filtering, or aggregation nodes to convert the data into the desired format.

The recipe is also going to let you map the fields between Salesforce fields and Snowflake columns:

Once we have completed the configuration then, we can run the recipe in an ad-hoc way (Run Now) or schedule it to run at predefined dates and times:

Verifying the Data in Snowflake

After the job has been completed, we can verify the records inside Snowflake:

Salesforce Sync Out

For Salesforce Sync Out, we will need to set up the Salesforce Local connector to refer to our desired object for data integration - again, in our case, it is Account. This can be done in the Data Manager application under the Connections menu by clicking on the Connect to Data button.

We can then define the objects from the Data Manager to sync out. The sync type can be incremental or periodic full, or complete:

This feature pushes the raw data from Salesforce to Snowflake. As per Salesforce documentation, the complete Salesforce object data synced to Tableau CRM is pushed to Snowflake with Sync Out, so we need to take special care of access controls to manage data access. 

For Snowflake to Salesforce Integration

There are also two alternatives.

Salesforce Connector to Bring Data Into CRM From Snowflake

As for bringing data into Salesforce CRM from Snowflake, we can define a Snowflake connector and use a recipe - similarly as we described it for the Salesforce => Snowflake direction above.

For instance, we can have a materialized view in Snowflake that aggregates the account sales records:

create materialized view erp_aggregated_sales as 
select accountid, sum(amount) sumamount from erp_sales group by accountid;

This materialized view can then be referred to in a Snowflake connector:

The result will be written into the defined Data Set and can be used in Tableau CRM Lenses and Dashboards for analysis.

Direct Data Integration

Direct Data is the last option out of the four available Salesforce - Snowflake integration options. In this case, the data is not pushed to Salesforce but queried directly within Snowflake as a "live" connection.

Conclusion

In the article, we covered the native Salesforce - Snowflake integration options developed as part of the partnership efforts of the companies. They are easy to use, quick to turn on functionalities and allow users to share data between CRM and Data Cloud to achieve comprehensive insights into their customers.