Editor’s note: This article has been updated for accuracy and comprehensiveness in October 2023.
Ah, data. Where would our modern world be without our ability to collate and understand data? Our reality and latest innovations were made possible and continue to evolve due to businesses and entrepreneurs harnessing the ridiculous amounts of data being created and collected (estimates by Jacquelyn Bulao at TechJury put the number at 1.7MB of data created every second on average).
We’re all familiar with Salesforce’s primary role: as a CRM system, it helps us collect, organize, manage, and benefit from our data. We organize our Accounts, Contacts, Opportunities, Cases, and Files in a single database that connects familiar records. That is a lot of data, but when you add in Activities, financial transactions, and marketing automation, the amount of data 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 utilize more and more data to be relevant and meet the needs of their customers. With more and more data needing to be collected, 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 is involved in gaining insight from your analytics.
The crucial questions that come out of this information are:
- How can your business prepare to collect and utilize more significant data while keeping costs reasonable?
- How can you create a single source of truth for data analytics?
Today, we’ll discuss one solution in detail: outsourcing your data to a platform called Snowflake through an integration with your Salesforce org.
Table of contents
- What Is Snowflake?
- Why Connect Salesforce and Snowflake?
- How to Connect Salesforce and Snowflake?
- Summary
What Is Snowflake?
Snowflake positions itself 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.
You can use Snowflake to create a single source of truth for data analytics. Having multiple data sources flowing into a single data warehouse and augmenting these sources together means you’ll gain richer insights into your business by analyzing your complete data set.
Snowflake can be a source for data backups, archives, or simply an off-platform file storage 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 simple to set up, Snowflake is a great candidate for the job.
Why Connect Salesforce and Snowflake?
We’ve already discussed one reason you may want to connect Salesforce and Snowflake — data archiving and data backups. You are having your larger data sets archived in a cheaper yet still accessible way. Data warehouse is one way to help your business save some money without impacting productivity.
We’ve also mentioned that you can pull your data from Salesforce and other sources 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 insight into the buying habits of your customers. This is where Snowflake can come in handy and assist your business by providing you with a single point to perform your data analytics.
How to Connect Salesforce and Snowflake?
As per usual, when it comes to Salesforce and integration, there are several different ways you could connect and integrate Snowflake with your Salesforce org, depending on budget, technical expertise, and requirements. In the sections below, we’ll go through a few of the main methods you could use, including the main steps of each, alongside their pros and cons.
Method 1: Salesforce Data Import Wizard
Salesforce Data Import Wizard is a native, user-friendly solution supporting manual data import from CSV files, spreadsheets, etc., and handling up to 50,000 records per sync, but without scheduling. You can import data to solutions, leads, campaign members, personal accounts, or any unique Salesforce environment’s custom objects here. One of the Snowflake – Salesforce integration approaches is to save Snowflake data in CSV and manually import it into Salesforce through this importing tool.
Let’s see how to work with this tool in a few steps.
STEP 1: THE WIZARD SETUP
Login to the Salesforce app. Type “data import” in the setup search bar, and click Data Import Wizard.
STEP 2: THE OBJECT FOR IMPORT SELECTION
Select some standard or custom object and the import type here.
You can add new records and update existing ones. Don’t forget to click Next after all the selections are made.
STEP 3: THE DATA FIELDS MAPPING
Map the appropriate fields from your CSV to Salesforce by clicking Map Fields and Create New Map. Choose the CSV file for uploading and click Next. Select the object for the update in Salesforce and map the fields selected before, click Next, and Start Import.
NOTE: You must select the object for update and manually map the fields here because the Wizard doesn’t allow doing it automatically.
So, let’s review other solutions for process automation.
Method 2: Tableau CRM Connector
The most well-known way to connect Salesforce and Snowflake is through Tableau CRM. There are several steps required to connect Salesforce and Snowflake:
- You’ll need to create the objects inside of Snowflake.
- Enable the Tableau CRM Sync Out setting in Salesforce.
- You’ll need to configure the Snowflake Output Connection.
PREREQUISITES
To use the Tableau CRM Connector to connect Salesforce and Snowflake, you must ensure you have a paid Tableau CRM license. Pricing will differ depending on your circumstances and country, but here is the US pricing page for Tableau CRM.
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 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.
NOTE: You have to create and replace tables and insert, update, truncate, and merge data. Finally, you also need the ability to create a temporary internal stage here.
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; 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
At an extra cost, you can only perform the following step once you’ve purchased a Tableau CRM license for your Salesforce org. Tableau CRM is available for Enterprise, Performance, and Unlimited Salesforce editions. Configuring this in a Tableau CRM-enabled Developer Environment is also possible, which you can sign up for 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 can 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 encountering issues, check that you’ve completed Step One and Step Two correctly. Ensure that your Snowflake account details do not contain any capital letters, and ensure that the Database, Schema, Role, and Username don’t have any lowercase 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 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. You only need to 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.
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 3: 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 160+ 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 component 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 the Salesforce Connect product.
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.
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
Once Snowflake is ready, click New, then Connection again (as in Method Two, 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 Component in Step Three). Populate your storage information, and ensure Use Bulk Import is checked at the bottom of the page. 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 2, Step 1:
- Any Salesforce data that will be pushed into Snowflake needs somewhere to live. Create a new Database 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.
NOTE: You need the ability to create and replace tables and insert, update, truncate, and merge data. Finally, you have 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.
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 COMPONENT
Now that Salesforce and Snowflake are connected to Skyvia, it’s time to create your Replication component within Skyvia Data Integration and connect it to Snowflake. For the last time, click New, then under the Integration header, click Replication.
Give your Replication integration 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 ensure everything is running as expected, and you should see a popup message saying, ‘The integration 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 4: SCHEDULE THE REPLICATION INTEGRATION
In your Replication scenario, click the Schedule button at the top-left next to the Parameters button to open a menu allowing you to create a recurring schedule. So your Replication integration 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. This process is generally called Reverse ETL. Loading data back is not as easy as it might seem. However, with Skyvia, you can do it easily using the Import component functionality. As you have already created connections to Salesforce and Snowflake earlier (Step one and Step two of the Replication component above), all you need this time is to follow the below steps:
STEP 1: CREATE IMPORT COMPONENT
Select Snowflake as a source connection and Salesforce as a target one in the integration. Add a task to the component by clicking a corresponding link on the right side of the page. In the task editor that opens, select the Snowflake table from the drop-down list that you want to load to Salesforce.
STEP 2: CONFIGURE MAPPING SETTINGS
It’s helpful to know that Skyvia automatically maps columns with identical names in Snowflake and Salesforce, which is convenient as it reduces manual efforts. All other necessary Snowflake columns can be mapped to Salesforce columns via lookups, expressions, etc.
STEP 3: SCHEDULE IMPORT COMPONENT
Automate your integration by means of powerful scheduling settings. Set your scenario once to run recurrently at certain intervals and enjoy trouble-free automated integrations. If any error occurs during component running, you will be notified. Read more about the email notification functionality here.
You can read more about the reverse ETL by following this link.
Summary
In this article, we’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 analyze the functionality of the various tools available to you to make sure whichever you choose can do what you need.
