Bulk sync (ELT) from Salesforce to Snowflake

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: 2023-04-25"   \
     --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 a Snowflake connection

The Snowflake instance must be network-accessible from Polytomic. For more information, see 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: 2023-04-25"  \
     --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

This will create a bulk sync of the Account and Contact Salesforce objects into Snowflake.

📘

Schemas

If the schemasparameter is omitted, all objects will be synced.

curl --request POST \
     --url https://app.polytomic.com/api/bulk/syncs \
     --header "accept: application/json" \
     --header "content-type: application/json" \
     --header "X-Polytomic-Version: 2023-04-25" \
     --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": "manual"
          },
          "destination_configuration": {
               "schema": "SALESFORCE_CONNECTION"
          },
          "schemas": ["Account", "Contact"]
    	}'

🚧

Bulk sync schemas

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.

Advanced settings

You can set extra parameters that constitute advanced settings for your bulk sync. These parameters are optional as they have default values but can be overridden:

• automatically_add_new_fields(default: true): if true, any new fields that show up in existing object get automatically added to the sync. For example, when a new custom field is created on a Salesforce object or a new column is created on a PostgreSQL table.
• automatically_add_new_objects (default: false): if true, any new objects that show up in the source get automatically added to the sync. For example, when a new custom object gets created in Salesforce or a new table gets created in PostgreSQL.
• disable_record_timestamps (default: false): if true, then Polytomic bulk syncs will not augment your tables with _polytomic_created_at, polytomic_updated_at, and _polytomic_deleted_at columns.

Below is an example that repeats the previous Salesforce-to-Snowflake sync except with overriding these default parameters:

curl --request POST \
     --url https://app.polytomic.com/api/bulk/syncs \
     --header "accept: application/json" \
     --header "content-type: application/json" \
     --header "X-Polytomic-Version: 2023-04-25" \
     --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",
          "automatically_add_new_objects": true,
          "automatically_add_new_fields": false,
          "disable_record_timestamps": true,
          "schedule": {
            "frequency": "custom",
            "minute": "0",
            "hour": "*/1",
            "day_of_month": "*",
            "month": "*",
            "day_of_week": "*"
          },
          "destination_configuration": {
               "schema": "advanced_salesforce"
               "advanced": {
                 "truncateExisting": true
               }
          },
          "schemas": ["Account", "Contact"]
    	}'