How to send data from Snowflake to Salesforce

Introduction

Companies are storing more of their data than ever in warehouses like Snowflake, giving them a "360 degree" view of their customer. This allows for better, more holistic customer insights, which can in turn mean more customer value as apps, products, and services are tailored to the customer in real-time.

But to really capture the value of data insights requires "pushing" those insights back out into the apps and services that matter—like Salesforce, Google Ads, Mailchimp, Zendesk, Intercom and others. In this post, we'll demonstrate a few ways to send data from Snowflake to Salesforce. We'll use the example of a customer lead score calculated in Snowflake, but this will work for any data you'd want to send to Salesforce.

Calculating your lead score

Say you are a SaaS company with events data streaming into your Snowflake data warehouse, including how many times a customer logged in, how many times they viewed the pricing page, and how many times they created a report. A combination of these events represents roughly how engaged the customer is and therefore how likely they are to convert to a paid subscription (or upgrade their paid subscription). Maybe you performed a logistic regression and found the best coefficients to estimate the probability that the customer will upgrade. Your optimal lead score using those 3 variables (a, b, and c) is the following:

1/(1+e^-(-5 + a*.2 + b*.7 + c*1.2))

Which translates in Snowflake to:

1/(1+exp(-(-5+logged_in*.2+viewed_pricing*.7+ created_report*1.2)))

Now you can calculate your lead score for each user with one query in Snowflake. But the lead score isn't all that useful just sitting in your data warehouse. To get value from it, you need to send it to other apps where it can be used to customize customer interactions and experieneces.

Python

Once you've calculated your lead score, one way to send it to Salesforce is with Python. First, you'll have to load the data from Snowflake into your Python environment. Then you can use the simple-salesforce Python package for retrieving, creating, and updating records in Salesforce using the Lightning Platform API.

You can either use your Salesforce domain and an access token, or the security token method which requires your username, password, and a security token (or an IP-whitelisted Organization ID).

This code demonstrates how you can authenticate with your Salesforce instance:

from simple_salesforce import Salesforce
sf = Salesforce(username='myemail@example.com.sandbox', password='password', security_token='token', domain='test')

Then you can create records by using the create() method:

sf.Contact.create({'LastName':'Smith','Email':'example@example.com'})

You can also update records with update():

sf.Contact.update('003e0000003GuNXAA0',{'LastName': 'Jones', 'FirstName': 'John'})

If you had your lead score in a CSV file along with the Salesforce ID, you could update contacts with a For Loop like the following:

import pandas as pd
from simple_salesforce import Salesforce

sf = Salesforce(username='myemail@example.com.sandbox', password='password', security_token='token', domain='test')

lead_scores = pd.read_csv('Your lead score data.csv')
for i in range(len(lead_scores)):
    sf.Contact.update(lead_scores['sf_id'][i],{'LeadScore': lead_scores['lead_score'][i]})

Finally, you can query Salesforce using SOQL, which can be useful to get the Salesforce Id so you can update Contacts or other objects:

sf.query("SELECT Id, Email FROM Contact WHERE LastName = 'Jones'")

The downside to this approach is that it requires manual steps like loading the Snowflake data into Python, so it's not ideal for automating.

Data Loader

You can also use the Salesforce Data Loader for updating, inserting, deleting, or exporting records, or for bulk import or export. Data Loader is supported by loads up to 5 million records, and is useful regular data loads, such as nightly imports. You can install Data Loader here.

SeekWell External Function

For a native solution in Snowflake, you can send your lead score to Salesforce using the SeekWell External Function. Instructions on how to install the SeekWell External Function in your Snowflake instance can be found here.

Once you've installed the SeekWell function in Snowflake, it's really simple to send your lead score (or any data) to Salesforce. All you have to do is alias the column you want to send as its field name in Salesforce (custom fields will end in "__c"). Additionally, your query result has to include the Salesforce ID for each record and an "Object_Construct" (essentially a JSON object in Snowflake) containing the object and operation to perform in Salesforce.

For sending data to Salesforce, the "Object_Construct" should look something like below, depending on the object (such as "Contact") and the operation you want to perform on it (such as "update" or "insert"). It should be aliased as "seekwell" in your query:

OBJECT_CONSTRUCT(
        'type', 'sfdc',
        'object', 'Contact',
        'operation', 'update'
    ) as seekwell

Once you have a query that returns Salesforce ID, lead score (aliased as the field name in Salesforce), and the seekwell object described above, your'e ready to send the data to Salesforce. The query should like something like this:

with to_sfdc as (
    select 
    e.email as "Email", 
    sc.id as "Id", 
    1/(1+exp(-(-5+e.logged_in*.2+e.viewed_pricing*.7+ e.created_report*1.2)))
	 as "LeadScore",
    OBJECT_CONSTRUCT(
        'type', 'sfdc',
        'object', 'Contact',
        'operation', 'update'
    ) as seekwell
    from events as e
    join salesforce.contacts as sc on e.email = sc.email
)

This query assumes your events table is already aggregated for each user. If it isn't, you may need to use a subquery or CTE to aggregate it before calculating the lead score.

Now you can send the results of that query to the SeekWell function, and your data will be sent to Salesforce! You just have to call the seekwell function and send the result of the above query as its argument (the entire query result is converted to an Object_Construct so it is all one object). Sending your query result to the external function should look like this:

select 
to_sfdc.*,
public.seekwell(
  OBJECT_CONSTRUCT(to_sfdc.*)
)::string as seekwell_result
from to_sfdc as to_sfdc

Pretty easy, right? The gif below shows how it would work in real-time:

If you want to schedule this query to run on an automated basis, you can use Snowflake's tasks to automate it—that way your lead score in Salesforce is never stale.

SeekWell Web App

You can also use the SeekWell web app to send data from Snowflake to Salesforce. This allows you to set your repeat schedule (such as daily, hourly, or even five minute intervals), and gives you a smooth interface to write queries. There's also a real-time feed to monitor your scheduled runs. You can sign up for a 14 day free trial here.

All the information you need to start sending data to Salesforce is located here. You can see how it works in the gif below:

Conclusion

Don't let valuable data stay siloed in your data warehouse. There are lots of ways to push data out to apps like Salesforce. SeekWell makes this as easy as writing an SQL query, and also has other integrations like Intercom, Hubspot, and Facebook Custom Audiences so your team's apps are always in sync with your database. We're working on connecting to other apps, so if you have an app you'd love to send data to, let us know at contact@seekwell.io.