Tutorials

4 Ways to Move Data from Snowflake to Salesforce (w/ Examples)

Parker Rogers
Parker Rogers October 25, 2021

Parker is a data community advocate at Census with a background in data analytics. He's interested in finding the best and most efficient ways to make use of data, and help other data folks in the community grow their careers. Salt Lake City, Utah, United States

In this article, you'll learn four ways to integrate Snowflake data to Salesforce, including how to get the job done with:

First things first: Congratulations! If you’re looking to move your data from Snowflake to Salesforce, then you’ve started to realize all the things you can do when you treat your data warehouse as the hub of your data architecture. This might seem like an obvious thing, but the hub-and-spoke philosophy sets you up to use your warehouse as a central powerhouse for biz ops, and syncing it with frontline tools like Salesforce is a huge milestone. Integrating data from Snowflake to Salesforce has several use cases, including:

  • Providing sales teams rich first-party data to help them close new business.
  • Helping marketing teams create high-quality, customized email drip campaigns.
  • Supplying product teams valuable data to understand and improve the customer experience.

Whatever your use case, being able to integrate Snowflake to Salesforce is essential. In this article, we’ll cover four different methods and describe the pros and cons of each.

Method #1: Data Loader

Data Loader is a web-based 3rd party application that allows you to import/export data from Salesforce. It's more flexible than Data Import Wizard (discussed in method #3) because it allows you to upload up to 5 million records per sync. You can schedule the loader so Salesforce can retrieve recent data regularly. Plus, you can use Data Loader for objects that aren’t supported by Data Import Wizard. To implement Data Loader, complete the following:

  • Install Data Loader on your macOS or Windows. When you execute the installer file, you can select and download Data Loader and its installer.command file.
  • Configure Data Loader by deciding batch size, null value handling, host address, login credentials, compression type, timeout, query request size, encoding, and more. You can view detailed documentation here.
Configure salesforce data loader
Data loader warning for Mac M1 hardware users
For reference: Running Data Loader on Mac M1 Hardware

Once you have Data Loader installed and configured, it’s time to get down to business.

To execute the data loader, you can use either batch mode or Data Loader Command Line Interface (CLI).

  • Batch mode: You can run a series of data loading tasks using the Windows command-line tool (Note: This only works on Windows!).
  • Data Loader CLI: You can perform more flexible tasks using the CLI tool, including inserting, updating, or deleting records. You can also programmatically map fields between the source and target data and use CSV data sources to import into Salesforce.

Like any data solution, there are pros and cons depending on your use case and needs. Here’s a quick breakdown:

Pros and cons of using Salesforce data loader to connect data from Snowflake

Method #1 helps you move data from Snowflake to Salesforce using your terminal or command line. Let’s take a look at an entirely different approach: reverse ETL with Census.

Method #2: Reverse ETL with Census

Reverse ETL syncs data from a system of record (e.g. Snowflake) and to a system of action (e.g. Salesforce). Reverse ETL allows you to send data seamlessly between Snowflake and Salesforce without custom code or having to worry about the integration breaking when either source or destination updates.

Our reverse ETL tool has built-in sync and native integrations for both Snowflake and Salesforce. These integrations are flexible and allow you to integrate Snowflake data into several Salesforce destinations and objects. Census also has SQL modeling capabilities, allowing you to easily create database instances. Finally, we’ve worked hard to provide an intuitive UI to make implementation and troubleshooting quick and easy. Here’s the breakdown for integrating Snowflake data into Salesforce using Census reverse ETL:

  • Connect Snowflake as a data source using your Snowflake account credentials
  • Connect Salesforce as a service connection using your Salesforce account credentials
  • Write a SQL model to create an instance of your Snowflake database. Here’s some example code to get you started:


with score as (
	select user_id,
		sum(case
			when name = 'webinar attended' then 3
			when name = 'appointment created' then 4
			when name = 'appointment shared' then 2
			when name = 'content downloaded' then 2
			when name = 'email opened' then 1
			else 0
			end) 
         as lead_score
	from "demo".events
	group by user_id),

webinar_attended as (
	select user_id, count(*) as count
	from "demo".events
	where name = 'webinar attended'
	group by user_id),

content_downloaded as (
	select user_id, count(*) as count
	from "demo".events
	where name = 'content downloaded'
	group by user_id),

appointment_created as (
	select user_id, count(*) as count
	from "demo".events
	where name = 'appointment created'
	group by user_id)

select email, lead_score, webinar_attended.count as webinar_attended, content_downloaded.count as content_downloaded, appointment_created.count as appointment_created, first_name, last_name, company_domain, role, website, location, u.user_id, 'subscribed' as status
from "demo".users u
	join score on score.user_id = u.user_id
	join webinar_attended on webinar_attended.user_id = u.user_id
	join content_downloaded on content_downloaded.user_id = u.user_id
	join appointment_created on appointment_created.user_id = u.user_id
where lead_score > 10

Next, you’ll need to map your required fields and remaining fields. Remaining fields aren’t required to sync, but can further enrich your data in Salesforce.

Snowflake to salesforce reverse etl fields

As I said above, everything has its pros and cons. Here’s the breakdown for reverse ETL/Census.

Pros and cons of using Census reverse etl to connect Snowflake to Salesforce

Now you’ve seen reverse ETL with Census, let’s check out another method that is comparable to Method #1: The Data Import Wizard.

Method #3: Data Import Wizard

Data Import Wizard is a Salesforce native feature that allows users to easily upload data from various sources, including Snowflake. You can upload customer-related data such as accounts, contacts, leads, solutions, campaign members, and more. It supports the import of up to 50,000 records at a time.

Before we begin, you need to export your data in CSV from Snowflake. The simplest way to export is using a SQL client connected to your Snowflake cluster to run a SELECT query. Then, export the result in a CSV file. Here’s an example:


snowsql -c my_example_connection
  -d sales_db
  -s public
  -q 'select * from mytable limit 10'
  -o output_format=csv
  -o header=false
  -o timing=false > output_file.csv

Alternatively, if you’re familiar with Snowflake queries, you can consider using a COPY command to export data into an AWS S3 bucket. You can download the CSV file from there, too.

Lastly, Snowflake has a CLI tool called SnowSQL. Using a SnowSQL CLI command, you can directly download a query result into your Linux server or local hard drive. Once you’ve prepared your CSV data, you’ll need to do two things:

  1. Go to Setup in Salesforce and type Data Import Wizard in the Quick Find bar (as seen in the image below). Then, select Data Import Wizard.
    Salesforce data import wizard
  2. Check the prompt information and click Launch Wizard.

From here, you can either select standard objects (to import accounts, contacts, leads, solutions, person accounts, or articles) or custom objects (to import custom data). Then, decide the type of import: add new records, update existing records, or add and update.

Add new records on Salesforce Data Import wizard

The rest of the fields depend on your use case.

You’ll then be prompted to upload your CSV file. Select comma or tab for the value separator, and then hit “next”.

Import data into salesforce
Salesforce data import warning message

The Data Import Wizard isn’t an automatic way to sync between the two platforms. If you want to have the freshest data possible in Salesforce and want to use a wizard, we recommend using method #1. However, if you only want to load data once, or if there is a security policy that blocks connection from Salesforce to Snowflake, we’ll take a look at one more option at your disposal.

Before we move on, however, here’s a breakdown of the pros and cons of using the Data Import Wizard.

Salesforce data import wizard pros and cons

Lastly, let’s take a look at method #4: The Snowflake Connector.

Method #4: Snowflake Connector

For our final method, we'll break down how (and when) to use the Snowflake Connector, which lets you sync between Snowflake Salesforce’s using Tableau CRM Analytics Studio. To do this, we’ll need to create  Snowflake objects in our Snowflake account and prepare the following:

  • Database and schema for the Salesforce data
  • XSMALL or SMALL warehouses to load the data
  • A proper role with the right permissions to use the data in the prepared database and schema
  • A user that has the role defined above
Snowflake connector warning message

Now, you can enable data to sync out to Salesforce with the following steps:

  • Find Setup in Salesforce
  • Type “analytics” into the quick find search bar
  • Go to Settings
  • Select “enable data sync and connections”, “enable Snowflake output connection”, and “enable direct data for external data sources”

Finally, let’s create an outbound connection from Analytics Studio in Salesforce to Snowflake.

Create outbound connection from analytics studio in salesforce to snowflake
Snowflake connector on Analytics Studio (Tableau CRM Data Manager)

First, go to Analytics Studio in Salesforce and Data Manager and click the Connect tab. From there, click the “connect to data” button in the top right. Then, click “output connection” followed by “add connection”.

Next, choose the Snowflake Output Connector and fill out the required fields. Select “save & test” to make sure the connection works.

And voila! You’ve successfully synced your data between Snowflake and Salesforce using the Snowflake Connector. Here’s a quick breakdown of when this method works best, and when you might want to consider another method on this list.

Snowflake connector pros and cons

Where to go from here: Choosing the best option to export data from Snowflake to Salesforce

In this article, you’ve learned about four different ways to move data from A (Snowflake) to B (Salesforce).

As we touched on in each section, each respective method has its strengths and its weaknesses. As you consider your data needs for this use case (and beyond), you should choose the one that best fits your workflows.

If you’re doing this integration as a one-off sync, methods one, three, and four will get the job done without too much sweat. However, if you’re using this as the jumping-off point to integrate data from the warehouse to a bunch of frontline tools, we’d recommend reverse ETL (method three).

Reverse ETL removes the headaches of syncing your Snowflake data warehouse to your frontline tools like Salesforce, giving everyone the freshest data possible. Plus, it unlocks the true potential of your data teams and your frontline teams to spend less time building and working on integrations and more time doing great things with great data. Oh, and don’t forget: You can try it for free!

Related articles

Product News
Sync data 100x faster on Snowflake with Census Live Syncs
Sync data 100x faster on Snowflake with Census Live Syncs

For years, working with high-quality data in real time was an elusive goal for data teams. Two hurdles blocked real-time data activation on Snowflake from becoming a reality: Lack of low-latency data flows and transformation pipelines The compute cost of running queries at high frequency in order to provide real-time insights Today, we’re solving both of those challenges by partnering with Snowflake to support our real-time Live Syncs, which can be 100 times faster and 100 times cheaper to operate than traditional Reverse ETL. You can create a Live Sync using any Snowflake table (including Dynamic Tables) as a source, and sync data to over 200 business tools within seconds. We’re proud to offer the fastest Reverse ETL platform on the planet, and the only one capable of real-time activation with Snowflake. 👉 Luke Ambrosetti discusses Live Sync architecture in-depth on Snowflake’s Medium blog here. Real-Time Composable CDP with Snowflake Developed alongside Snowflake’s product team, we’re excited to enable the fastest-ever data activation on Snowflake. Today marks a massive paradigm shift in how quickly companies can leverage their first-party data to stay ahead of their competition. In the past, businesses had to implement their real-time use cases outside their Data Cloud by building a separate fast path, through hosted custom infrastructure and event buses, or piles of if-this-then-that no-code hacks — all with painful limitations such as lack of scalability, data silos, and low adaptability. Census Live Syncs were born to tear down the latency barrier that previously prevented companies from centralizing these integrations with all of their others. Census Live Syncs and Snowflake now combine to offer real-time CDP capabilities without having to abandon the Data Cloud. This Composable CDP approach transforms the Data Cloud infrastructure that companies already have into an engine that drives business growth and revenue, delivering huge cost savings and data-driven decisions without complex engineering. Together we’re enabling marketing and business teams to interact with customers at the moment of intent, deliver the most personalized recommendations, and update AI models with the freshest insights. Doing the Math: 100x Faster and 100x Cheaper There are two primary ways to use Census Live Syncs — through Snowflake Dynamic Tables, or directly through Snowflake Streams. Near real time: Dynamic Tables have a target lag of minimum 1 minute (as of March 2024). Real time: Live Syncs can operate off a Snowflake Stream directly to achieve true real-time activation in single-digit seconds. Using a real-world example, one of our customers was looking for real-time activation to personalize in-app content immediately. They replaced their previous hourly process with Census Live Syncs, achieving an end-to-end latency of <1 minute. They observed that Live Syncs are 144 times cheaper and 150 times faster than their previous Reverse ETL process. It’s rare to offer customers multiple orders of magnitude of improvement as part of a product release, but we did the math. Continuous Syncs (traditional Reverse ETL) Census Live Syncs Improvement Cost 24 hours = 24 Snowflake credits. 24 * $2 * 30 = $1440/month ⅙ of a credit per day. ⅙ * $2 * 30 = $10/month 144x Speed Transformation hourly job + 15 minutes for ETL = 75 minutes on average 30 seconds on average 150x Cost The previous method of lowest latency Reverse ETL, called Continuous Syncs, required a Snowflake compute platform to be live 24/7 in order to continuously detect changes. This was expensive and also wasteful for datasets that don’t change often. Assuming that one Snowflake credit is on average $2, traditional Reverse ETL costs 24 credits * $2 * 30 days = $1440 per month. Using Snowflake’s Streams to detect changes offers a huge saving in credits to detect changes, just 1/6th of a single credit in equivalent cost, lowering the cost to $10 per month. Speed Real-time activation also requires ETL and transformation workflows to be low latency. In this example, our customer needed real-time activation of an event that occurs 10 times per day. First, we reduced their ETL processing time to 1 second with our HTTP Request source. On the activation side, Live Syncs activate data with subsecond latency. 1 second HTTP Live Sync + 1 minute Dynamic Table refresh + 1 second Census Snowflake Live Sync = 1 minute end-to-end latency. This process can be even faster when using Live Syncs with a Snowflake Stream. For this customer, using Census Live Syncs on Snowflake was 144x cheaper and 150x faster than their previous Reverse ETL process How Live Syncs work It’s easy to set up a real-time workflow with Snowflake as a source in three steps:

Best Practices
How Retail Brands Should Implement Real-Time Data Platforms To Drive Revenue
How Retail Brands Should Implement Real-Time Data Platforms To Drive Revenue

Remember when the days of "Dear [First Name]" emails felt like cutting-edge personalization?

Product News
Why Census Embedded?
Why Census Embedded?

Last November, we shipped a new product: Census Embedded. It's a massive expansion of our footprint in the world of data. As I'll lay out here, it's a natural evolution of our platform in service of our mission and it's poised to help a lot of people get access to more great quality data.