Integrating Salesforce Data with Snowflake Using Tableau CRM Sync Out
Salesforce contains some of the most valuable customer data within an organization. Organizations also use Snowflake’s Data Cloud to easily keep internal data and third-party data up to date and in one place. The consolidation of Salesforce and third-party data on a single platform helps organizations more easily find important and valuable insights, while removing many of the challenges typically associated with data silos and movement. The combination also allows for a deeper view of the customer, services needs, or other analytics
As part of a continued collaboration among Salesforce, Snowflake, and Tableau, the Tableau CRM Sync Out connector has been released. As shown in Figure 1, this connector moves Salesforce data directly into Snowflake, simplifying the data pipeline and reducing latency. Then the connector automates Data Definition Language (DDL) creation on the Snowflake side, saving additional setup time. It also keeps data fresh by capturing incremental loads from many Salesforce objects. Analysts can use Tableau or Tableau CRM to explore the data easily.
Combining Salesforce data with external data can benefit many industries. For example:
- Augmenting Salesforce data with third-party data gives sales teams a deeper understanding of customers and their needs. For example, adding historical buying information can indicate upward or downward trends in customers’ interests. Web data can provide insights about new interests the customer may have. Or, adding data about inventory levels could enable a sales team to proactively notify a customer that something could be out of stock soon
- Organizations providing repair services can leverage additional information to be more productive and efficient. In the oil and gas industry, for example, IoT data can help predict failures, enabling organizations to do preventive maintenance and prevent downtime. Location and scheduling data helps find the closest available person to an emergency job. Seeing the repair history on specific equipment could provide insights about recurring problems.
- Hotel chains can use Salesforce to understand who the customers are individually, but often rewards and booking data can live outside of Salesforce. By combining this external data with Salesforce data, the hotel staff has a much clearer picture of the customer and can customize services, increasing satisfaction and loyalty.
The Sync Out connector provides a simple way to bring Salesforce data into Snowflake’s Data Cloud, making it easier to combine data and keep it up to date. With proven connections to both Tableau and Tableau CRM, organizations can provide that data for analysis or AI with Einstein capabilities.
The rest of this blog post will show details about configuring, connecting, and accessing the data.
Configure Salesforce and Snowflake
To configure Salesforce and Snowflake to use the Sync Out connector, you create Snowflake objects, enable Sync Out in Salesforce, and configure the Snowflake output connection. This section describes these steps in detail.
Creating Snowflake objects
To use Sync Out with Snowflake, you need the following Snowflake objects configured appropriately in your Snowflake account:
- Database and schema that will be used for the Salesforce data
- Warehouse for loading the data (start with XSMALL or SMALL warehouses)
- Role with usage permissions on the warehouse as well as privileges on the database and schema that will be used, including Create and Replace tables; Insert, Update, Truncate, and Merge Data functions; and permissions to create a temporary internal stage
- User that has been granted the Role mentioned above
Below is a script that you can leverage to create the Snowflake objects:
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;
Enabling Sync Out
Enable Sync Out and the Snowflake output connection in Salesforce, as shown in the following steps:
1. In Salesforce, click Setup.
2. In the Quick Find bar, type Analytics. Click Settings.
3. Check Enable Data Sync and Connections and Enable Snowflake output connection.
4. If you want to enable live connection to Snowflake, check Enable direct data for external data sources.
Configuring Snowflake output connection
The last configuration step is to configure the Snowflake output connection that will be used by Sync Out.
1. In Salesforce, open Analytics Studio and go to the Data Manager.
2. In Data Manager, select the Connect tab and click the Connect to Data button on the top right.
3. Select Output Connections and click Add Connection.
4. Select the Snowflake Output Connector.
5. Enter all required fields for your connection including the Snowflake information described below. See the Salesforce documentation for more information.
Tips:
- Use all lowercase for the Snowflake account, also the full Snowflake account identifier from the URL <account_identifier>.snowflakecomputing.com i.e. xyz123456 or bc45678.us-east-1
- Use all UPPER CASE for the database, schema, role, and username.
6. Click Save & Test to test the connection.
Connect to Local Salesforce Data and Configure the Data Sync
Use the Salesforce Data Manager to connect to local salesforce data, enable sync out, and set a schedule.
Connecting to Local Salesforce Data
1. Click the Connect to Data button in the Salesforce Data Manager.
2. In the Input Connections tab, select the SFDC_LOCAL object for local Salesforce data.
3. Select the Salesforce object that you want to sync. Click Continue.
4. Select the fields that you want to sync for the object. Click Continue.
5. Click Save in the Preview Source Data screen.
The objects and fields you want to sync will now be visible in the Data Manager. You can edit the fields at any point by clicking on the object.
Additionally you can select whether you want Incremental Sync, Full Sync, or Periodic Full Sync for objects by clicking the down arrow next to the object and select Edit Connection Mode. Consult the Salesforce documentation for sync limitations on some objects.
Enabling Sync Out and Set a Schedule
1. On the right side of SFDC_LOCAL click the down arrow and select Sync Out.
2. Check the Enable Sync Out, and select the Snowflake output connection name. Click Save.
3. To run the Data Sync immediately, click the same down arrow and click Run Now. Alternatively you will see an option to set a Schedule for the Data Sync.
4. To monitor jobs, click the Monitor tab.
Access the Data in Snowflake
The Salesforce objects appear as tables in Snowflake with the Sync Out connector managing the incremental or full sync of data loads as specified previously.
The data is now accessible for direct consumption with other data in Snowflake, whether it is data from internal systems, partner data through data sharing, or data providers in Snowflake Data Marketplace, enabling you to have an immediate and full view of customer data.
Resources
For more information, see the following resources:
- Sync Out connector demo video: https://youtu.be/kh4ZTuIzRO4
- Salesforce documentation: https://help.salesforce.com/articleView?id=sf.bi_integrate_connectors_sync_out_snowflake.htm&type=5
- Snowflake documentation: https://snowflakecloud.wpenginepowered.com/technology-partners/salesforce/
Authors
