Code examples

Model sync (Reverse ETL) from Snowflake query to Salesforce

UI demo video

Watch the same steps in the Polytomic admin console in this demo video.

Code

Set your Polytomic API key as an environment variable:

$export POLYTOMIC_API_KEY=YOUR-API-TOKEN

This example covers four steps:

  1. Create a Snowflake Connection.
  2. Create a Salesforce Connection.
  3. Create a model over a custom Snowflake query.
  4. Sync data from Snowflake into Salesforce.

1. Create a Snowflake connection

The Snowflake instance must be reachable from Polytomic over the network. For details, see the 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 creates a Salesforce Connection. See the Salesforce connection configuration for the required fields.

$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"}}'

Salesforce Connections authenticate with OAuth. Open the URL returned in the auth_url field of the response to complete the flow.

OAuth redirection

By default, the API expects auth_url to open in a new browser window. Set the optional redirect_url parameter in the request body to change the redirect target.

3. Create a Snowflake model

Create a model over a Snowflake query:

$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"
>          }'
Listing source objects

To enumerate the sources available on a Connection, see this example.

4. Sync the Snowflake model into Salesforce

The sync maps email, first_name, and last_name from the Snowflake model onto the matching Salesforce Contact fields. Add more entries to the fields array to sync additional columns.

$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"
>          }
>     }'