Model sync (Reverse ETL) from Snowflake query to Salesforce

UI demo video

You can watch a demo video of these operations in Polytomic’s admin console here.

Code

Before getting started make sure to set your Polytomic API key as an environment variable:

$export POLYTOMIC_API_KEY=YOUR-API-TOKEN

This example tutorial will cover 4 steps:

  1. Connecting to Snowflake.
  2. Connecting to Salesforce.
  3. Creating a model using a custom Snowflake query.
  4. Loading data from Snowflake into Salesforce.

1. Create an Snowflake Connection

The Snowflake instance must be network-accessible from Polytomic. For more information, see our Snowflake connection guide.

$     curl --request POST \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json" \
>     --header "content-type: application/json" \
>     --header "X-Polytomic-Version: 2024-02-08"  \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
>     -d '{
>     "name": "Snowflake Connection",
>     "type": "snowflake",
>     "configuration": {
>        "account": "uc193736182",
>        "username": "user",
>        "password": "secret",
>        "dbname": "database"
>        }
>    }'

2. Create a Salesforce connection

The following request will create a Salesforce connection. The configuration for each type of connection can be found at the connection configuration page.

$curl --request POST  \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2024-02-08"   \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{"name": "Salesforce Connection","type": "salesforce", "configuration": {"domain": "https://example.my.salesforce.com"}}'

Since Salesforce connections uses OAuth to authenticate, we’ll need to follow the link returned in the auth_url parameter in the response

Redirection


By default, the API expects the auth_url to be opened in a new browser window. If you’d like to modify the redirect behavior, there is an optional redirect_url parameter that can be added to the request body.

3. Create a Snowflake data model

$curl --request POST  \
>     --url https://app.polytomic.com/api/models \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2024-02-08"  \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{
>           "name": "Active Users",
>            "configuration": {
>               "query": "SELECT MAX(last_activity_date) AS last_activity_date, plan, first_name, last_name, email FROM users GROUP BY plan, first_name, last_name, email",
>            },
>            "connection_id": "YOUR-SNOWFLAKE-CONNECTION-ID"
>          }'

Enumerating model sources


For information about how to enumerate sources, see this example.


4. Create a sync from Snowflake into Salesforce using the data model

This sync maps the email field in the data model to the email field of Salesforce Contacts. It additionally maps the FirstName and LastName fields. You can augment the list of field mappings to sync additional fields.

$curl --request POST  \
>     --url https://app.polytomic.com/api/syncs \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2024-02-08"  \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{
>          "name": "Snowflake to Salesforce Sync",
>          "mode": "create",
>          "identity": {
>               "source": {
>                    "field": "email",
>                    "model_id": "YOUR-MODEL-ID"
>               },
>               "target": "Email",
>               "function": "equality"
>           },
>          "fields": [
>               {
>                    "source": {
>                         "field": "first_name",
>                         "model_id": "YOUR-MODEL-ID"
>                    },
>                    "target": "FirstName"
>               },
>               {
>                    "source": {
>                         "field": "last_name",
>                         "model_id": "YOUR-MODEL-ID"
>                    },
>                    "target": "LastName"
>               }
>          ],
>          "schedule": {
>               "frequency": "continuous"
>          },
>          "target": {
>               "connection_id": "YOUR-SALESFORCE-CONNECTION-ID",
>               "object": "Contact"
>          }
>     }'