Code examples

Bulk sync (ELT) from Salesforce to Snowflake

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 three steps:

  1. Connecting to Salesforce.
  2. Connecting to Snowflake.
  3. Loading data from Salesforce into Snowflake.

1. 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.

2. 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"
>        }
>    }'

3. Create a bulk sync

Before you create the sync, be sure to poll the Get Schema Status endpoint with your Salesforce connection ID until you get cache_status: cached in the response.

This ensures that Polytomic has read the full Salesforce schema before you select from it. This takes a few seconds after creating the connection in Step 1 and is a general recommendation after connecting Polytomic to any SaaS platform.

Polytomic may take a few moments to cache source schemas after creating new connections. The message {"status":400,"message":"No schemas found. This can mean that the Polytomic has not cached any schemas yet. Please try again.","metadata":null} indicates that the schemas are not ready to be used in bulk sync configuration yet. This request is safe to retry until receiving a 2xx response.

Once the source schema has been cached, the code below will create a bulk sync of the Account and Contact Salesforce objects into Snowflake (omitting the schemas argument below will sync all objects from Salesforce):

$curl --request POST \
>     --url https://app.polytomic.com/api/bulk/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": "Salesforce Bulk Sync",
>          "source_connection_id": "YOUR-SALESFORCE-CONNECTION-ID",
>          "destination_connection_id": "YOUR-SNOWFLAKE-CONNECTION-ID",
>          "mode": "replicate",
>          "schedule": {
>              "frequency": "continuous"
>          },
>          "destination_configuration": {
>               "schema": "SALESFORCE_CONNECTION"
>          },
>          "schemas": ["Account", "Contact"]
>    	}'