Snowflake to Salesforce Integration: Best Code-Free Connectors

Table of contents

1. Introduction
2. What Is Snowflake?
3. Why Connect Salesforce and Snowflake?
4. How Can I Connect Salesforce and Snowflake?
   • Method 1: Tableau CRM Connector
   • Method 2: Code-Free Data Integration with Skyvia
5. Summary

Introduction

Ah, data. Where would our modern world be without our ability to collate and understand data? Our modern world and latest innovations were made possible, and continue to evolve, as a direct result of businesses and entrepreneurs harnessing the ridiculous amounts of data that are being created and collected (current estimates by Jacquelyn Bulao at TechJury put the number at 1.7MB of data created every second in 2020).

We’re all familiar with Salesforce’s primary role: as a CRM system, it helps us collect, organise, manage, and benefit from our data. We organise our Accounts, Contacts, Opportunities, Cases, and Files together in a single database that connects familiar records together. That in itself is a lot of data, but when you add in Activities, financial transactions, and marketing automation, the amount of data that is being captured skyrockets once more. It’s safe to say that data is becoming more and more important than ever (critical, in fact, to business effectiveness and longevity).

As time goes on, businesses will need to collect and utilise more and more data to be relevant and meet the needs of their customers. With more and more data needing to be collected, this means more and more data needs to be stored, and with great storage comes great storage costs, especially native storage on the Salesforce platform. Also generally speaking, the more data you have and the more sources it comes from, the more complexity involved in gaining insight from your analytics.

The important questions that come out of this information are as follows: How can your business get prepared to collect and utilise larger amounts of data while still keeping costs at a reasonable level? How can you create a single source of truth for data analytics? There’s one solution that I’m going to discuss in detail today, and that’s outsourcing your data to a solution called Snowflake through an integration with your Salesforce org.

What Is Snowflake?

Snowflake positions themselves as a Data Cloud (essentially a cloud-based data warehouse) that is powered by an advanced data platform and supports data in many ways (storage, processing, and analytics). They offer a SaaS (Software as a Service) product for businesses looking to adopt a cloud-first approach to data management.

Snowflake can be used to create a single source of truth for data analytics. Having multiple data sources all flowing into a single data warehouse and augmenting these sources together mean that you’ll gain richer insights into your business by analysing your complete data set.

Snowflake can be used as a source for data backups, data archives, or simply an off-platform file storage platform for all your business data including your Salesforce org. As it can easily be scaled up or down dynamically based on your requirements and is quite easy to set up, Snowflake is a great candidate for the job.

Why Connect Salesforce and Snowflake?

I’ve already talked about one reason why you may want to connect Salesforce and Snowflake — data archiving and data backups. Having your larger data sets archiving to a cheaper, yet still accessible, data warehouse is one way to help your business save some money without impacting productivity.

I’ve also mentioned the fact that you can pull your data from Salesforce and other sources together into Snowflake and gain rich business insights by letting Snowflake be your single source of truth for data analytics.

For example, say your business has an external POS system that records transactions, and you’d like to augment this data along with your Salesforce data to build a complete data set and gain an insight into the buying habits of your customers. This is where Snowflake can come in handy and assist your business by being able to provide you with a single point to perform your data analytics from.

How Can I Connect Salesforce and Snowflake?

As per usual when it comes to Salesforce and integration, there’s a number of different ways you could connect and integrate Snowflake with your Salesforce org, depending on budget, technical expertise, and requirements. In the sections below, I’ll go through a few of the main methods you could go about doing this, including the main steps of each alongside their pros and cons.

Method 1: Tableau CRM Connector

Probably the most well known way to connect Salesforce and Snowflake is through Tableau CRM. There are a number of steps required to connect Salesforce and Snowflake. First, you’ll need to create the objects inside of Snowflake. Second, enable the Tableau CRM Sync Out setting in Salesforce. Finally, you’ll need to configure the Snowflake Output Connection.

PREREQUISITES

To use the Tableau CRM Connector to connect Salesforce and Snowflake together, you need to ensure you have a paid Tableau CRM license. Pricing will differ depending on your circumstances and country, but here is the pricing page for Tableau CRM in the US.

STEP 1: BUILD SNOWFLAKE OBJECTS

Before doing anything else, your Snowflake environment should be set up correctly. You need to create the following:

Any Salesforce data that is going to be pushed into Snowflake needs somewhere to live. Create a new Database and Schema that aligns with how your data is set up within Salesforce. You’ll also need a warehouse that will be used to load your data. Once you’ve set these up, you’ll need to create a new Role with usage access to the warehouse, database, and schema you’ve just created, and a new User with this Role assigned. The user will need the ability to Create and Replace tables. They will need the ability to Insert, Update, Truncate, and Merge Data. Finally, they’ll also need the ability to create a temporary internal stage.

Snowflake has documentation that outlines how to create your objects, including a handy script that you can use to speed the process up. You can read it here. The script is below, and full credit goes to the original author, Andries Engelbrecht.

USE ROLE SECURITYADMIN;
 
CREATE ROLE SYNCOUT;
 
CREATE USER TCRMSYNC PASSWORD = '<your password="">' LOGIN_NAME = 'TCRMSYNC' DISPLAY_NAME = 'TCRMSYNC'
DEFAULT_ROLE = SYNCOUT  DEFAULT_WAREHOUSE = 'SYNC_WH'
DEFAULT_NAMESPACE = 'SFDC_DB.PUBLIC' MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE SYNCOUT TO USER TCRMSYNC;
 
USE ROLE SYSADMIN;
 
CREATE OR REPLACE WAREHOUSE SYNC_WH
    WITH WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND =60
    INITIALLY_SUSPENDED = TRUE
    AUTO_RESUME = TRUE;
 
GRANT ALL ON WAREHOUSE SYNC_WH TO ROLE SYNCOUT;
 
CREATE DATABASE SYNC_DB;
CREATE SCHEMA SYNC_DB.SYNCOUT;
GRANT USAGE ON DATABASE SYNC_DB TO ROLE SYNCOUT;
GRANT USAGE, CREATE TABLE, CREATE STAGE ON SCHEMA SYNC_DB.SYNCOUT TO ROLE SYNCOUT;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON FUTURE TABLES IN SCHEMA SYNC_DB.SYNCOUT TO ROLE SYNCOUT;</your>

STEP 2: ENABLE TABLEAU CRM SYNC OUT

You will only be able to perform the following step once you’ve purchased a Tableau CRM license for your Salesforce org, at an extra cost. Tableau CRM is available for Enterprise, Performance, and Unlimited Salesforce editions. It’s also possible to configure this in a Tableau CRM enabled Developer Environment, which you can sign up to here.

From your Salesforce Setup menu, search for ‘Analytics’ in the Quick Find box, then click Settings under the Tableau CRM section.

Once in the Settings menu, check both ‘Enable Data Sync and Connections’ and ‘Enable Snowflake Output Connection’, then click Save.

STEP 3: CONFIGURE THE SNOWFLAKE OUTPUT CONNECTION

Almost there! Open the Analytics Studio app from within Salesforce, then go to Data Manager (accessible in the left-hand-side panel). Once in Data Manager, open the Connect page. Click Connect to Data (at the top-right of the Connect page).

From here you’ll be able to select a source to connect to. Click Output Connections, then Add Connection , then Snowflake Output Connector.

You’ll need to enter your Connection Name, Developer Name, Description, Username, Password, Account, Warehouse, Role, Database, and Schema details to proceed.

Finally, click Save & Test to ensure you’ve set it up correctly. If you’ve done everything correctly so far, you should see a message like this:

If you’re running into issues, check that you’ve completed Step 1 and Step 2 correctly above. Ensure that your Snowflake account details do not contain any capital letters, and ensure that Database, Schema, Role, and Username do not contain any lower case letters.

STEP 4: CONNECT TO SALESFORCE DATA

Now that you’ve set up your Output Connection, it’s time to set up the Input Connection. Head over to the Input Connection panel and select SFDC_LOCAL to open your Salesforce database sync settings. Enable the object you’d like to sync by clicking on it, then click Continue.

Select the fields on that object you’d like to sync, then click Continue once again.

You’ll now be presented with a Preview screen. If you’re happy with what’s displayed, click Save.

STEP 5: ENABLE TABLEAU CRM SYNC OUT AND CREATE SCHEDULE

Everything is set up, all you need to do is create a schedule for your Tableau CRM Sync Out job. To do this, click the dropdown arrow at the top-right of the table, and click Sync Out.

Flick the toggle, select your connection, and click Save.

Now, to create your schedule, click the same dropdown then click Schedule. Depending on your business requirements, you’ll need to configure this step on your own, but the settings are fairly self explanatory.

Finally, to perform your first run, click the dropdown one last time and click Run Now. This will send your data into Snowflake for the first time.

Voila, you’re done! Your Salesforce data is now available within Snowflake and is being synced on a schedule through the Tableau CRM Sync Out connector.

Method 2: Code-Free Data Integration with Skyvia

Unlike the method mentioned above, Skyvia offers a different but more effective and advanced method for Salesforce and Snowflake integration. The platform supports 80+ native connectors and is a complete ETL, ELT and Reverse ETL tool. Skyvia greatly simplifies a one-way data loading to the data warehouse as well as allows loading data in any direction, including the scenarios of loading already enriched data from Snowflake back to Salesforce or to other applications if needed.

As for loading data back from Snowflake to Salesforce, Skyvia offers several options. You can either use the Import package functionality to load data physically or use the Skyvia Connect product to create OData endpoints to Snowflake and display only necessary data in Salesforce. The latter is possible through external objects in Salesforce Connect product.

Right now let’s take a closer look at how you can load data from Snowflake to Salesforce and in a reverse direction. To load data from Snowflake to Salesforce, you can use Skyvia Replication, and to send Snowflake data back to Salesforce – Skyvia Import. Regarding Skyvia Connect, you can find more information here. In this article, we won’t describe it in detail. So let’s proceed!

PREREQUISITES

To use Skyvia Data Integration (Replication, Import) to connect Salesforce and Snowflake, you’ll need a Skyvia subscription (the free tier will do fine). If you don’t have a Skyvia account yet, you can sign up for free here.

Data Replication

STEP 1: CONNECTING SKYVIA DATA INTEGRATION TO SALESFORCE

Log in to Skyvia and click New, then Connection. This will take you to a list of Skyvia connectors.

Find and click the Salesforce connector. You will be prompted to provide some information to connect Skyvia to your Salesforce org. Skyvia supports both OAuth 2.0 and User Name & Password authentication. For our example, we will use OAuth 2.0.

Give your Connection a name, select the environment you wish to connect to, ensure Authentication is set to OAuth 2.0, then click Sign In with Salesforce and follow the prompts to create your OAuth token.

Click Test Connection to ensure everything is set up correctly before moving on to the next step. You should see a message at the top-right of your screen saying ‘Connection is successful’. Click Create Connection to finish.

STEP 2: CONNECTING SKYVIA DATA INTEGRATION TO SNOWFLAKE

The process for connecting your Snowflake environment to Skyvia is very similar to Tableau CRM. You’ll need to ensure you’ve set up your Snowflake environment correctly. See the below from Method 1, Step 1:

Any Salesforce data that is going to be pushed into Snowflake needs somewhere to live. Create a new Database and Schema that aligns with how your data is set up within Salesforce. You’ll also need a warehouse that will be used to load your data. Once you’ve set these up, you’ll need to create a new Role with usage access to the warehouse, database, and schema you’ve just created, and a new User with this Role assigned. The user will need the ability to Create and Replace tables. They will need the ability to Insert, Update, Truncate, and Merge Data. Finally, they’ll also need the ability to create a temporary internal stage.

Snowflake has documentation that outlines how to create your objects, including a handy script that you can use to speed the process up. You can read it here.

Once Snowflake is ready to go, click New, then Connection again (as in Method 2, Step 1 above), then search for Snowflake. Populate all fields and select your File Storage for Bulk option (Bulk Load Mode is required for the Replication Package in Step 3). Populate your storage information, and ensure Use Bulk Import is checked at the bottom of the page.

Click Test Connection. Once again, you should see a message up the top-right saying ‘Connection is successful’. Click Create Connection to finish.

STEP 3: CREATE REPLICATION PACKAGE

Now that Salesforce and Snowflake are both connected to Skyvia, it’s time to create your Replication Package within Skyvia Data Integration and connect Salesforce to Snowflake (Note: Synchronization is not supported for Snowflake).

For the last time, click New, then under the Integration header, click Replication.

Give your Replication package a name, then select your Salesforce Connection under the Source dropdown, and your Snowflake Connection under the Target dropdown. On the right-hand-side, select the Salesforce Objects you’d like to replicate, then click Validate.

Give Skyvia a few seconds to make sure everything is running as expected and you should see a popup message saying ‘The package is valid’.

Click OK, then Create. Once this is saved, you can click Run to run the first sync and make sure everything is running as expected!

STEP 5: SCHEDULE THE REPLICATION PACKAGE

In your Replication Package, click the Schedule button at the top-left next to the Parameters button. This will open a menu that will allow you to create a recurring schedule so that your Replication Package can run automatically. Create this schedule according to the requirements of your business, and click Save.

Data Import

Let’s assume that your Salesforce data is already in Snowflake. It is well structured and enriched with data from other systems. You might need to load it back to Salesforce to provide your Sales team with actionable data. Loading data back is not as easy as it might seem. However, with Skyvia you can do it easily, using the Import package functionality. As you have already created connections to Salesforce and Snowflake earlier (Step 1 and Step 2 of the Replication package above), all you need this time is to follow the below steps:

CREATE IMPORT PACKAGE

Select Snowflake as a source connection and Salesforce as a target one in the package. Add a task to the package by clicking a corresponding link on the right side of the page. In the task editor that opens, select the Snowflake table from drop-down list that you want to load to Salesforce.

CONFIGURE MAPPING SETTINGS

It is useful to know that Skyvia maps columns with identical names in Snowflake and Salesforce automatically, which is convenient as it reduces manual efforts. All other necessary Snowflake columns can be mapped to Salesforce columns via lookups, expressions, etc.

SCHEDULE IMPORT PACKAGE

Automate your package by means of powerful scheduling settings. Set your package once to run recurrently at certain time intervals and enjoy trouble free automated integrations. In case any error occurs during package running, you will be notified about it. Read more about the email notification functionality here.

You can read more about the reverse ETL, following this link.

Summary

In this article, I’ve explained why you may consider a product such as Snowflake to take some of the heavy data load from your Salesforce environment, and connect the two so your business doesn’t suffer as a result. You may also consider using Snowflake to augment your Salesforce data alongside other data sources.

As you’ve seen, there are multiple ways to connect your Salesforce and Snowflake databases together. You’ll need to consider your business requirements carefully, and analyse the functionality of the various tools available to you to make sure whichever one you choose is able to do what you need it to do.