Model sync (Reverse ETL) from BigQuery to Salesforce

This example creates a Model Sync from BigQuery to Salesforce using the Polytomic Terraform provider.

Prerequisites:

• Install the Polytomic Terraform provider.
• Set your Polytomic API key as an environment variable.

1. Configure the Polytomic provider

Configure the Polytomic provider in your Terraform script:

1
terraform {
2
  required_providers {
3
    polytomic = {
4
      source  = "polytomic/polytomic"
5
      version = "1.0.0"
6
    }
7
  }
8
}
9
10
provider "polytomic" {
11
    api_key = var.polytomic_api_key
12
}

Define var.polytomic_api_key in your variables file or pass it at apply time.

🤝 Partner keys

If you are using a Partner Key, specify the key along with an organization user email. A user with that email will be used when operating on an organization. The user will be created if it does not exist.

1
provider "polytomic" {
2
  partner_key = "<value from settings page>"
3
  organization_user = "terraform@polytomic.com"
4
}

2. Create a BigQuery connection

Define a resource for the BigQuery connection:

1
resource "polytomic_bigquery_connection" "user_data" {
2
  name = "BigQuery Users"
3
4
  configuration = {
5
    project_id      = "polytomic-integration-test"
6
    service_account = file("bq.json")
7
  }
8
}

This example loads the service account key with the file function. Any string value or variable works as well.

3. Create a Salesforce connection

Polytomic supports several authorization modes for Salesforce. To manage a Salesforce Connection from Terraform, you must supply the client ID and secret.

If you want to use Polytomic’s OAuth flow instead, create the Connection through the admin console or the authentication API, then reference it from Terraform with the Salesforce datasource.

Define the Salesforce Connection:

1
resource "polytomic_salesforce_connection" "sfdc" {
2
  name = "Salesforce Connection (TF)"
3
  configuration = {
4
    connect_mode        = "api"
5
    domain              = "my--sandbox.sandbox.my.salesforce.com"
6
    client_id           = var.sfdc_client_id
7
    client_secret       = var.sfdc_client_secret
8
    oauth_refresh_token = var.oauth_token
9
  }
10
}

🤝 Partner keys

When using a partner key, you must also set the Organization ID on every resource.

4. Create a model over the user data

Define a model that queries BigQuery:

1
resource "polytomic_model" "users" {
2
  name          = "Users"
3
  connection_id = polytomic_bigquery_connection.user_data.id
4
  configuration = jsonencode({
5
    dataset = "application"
6
    table   = "users"
7
  })
8
}

5. Create the Model Sync

Define a Model Sync to Salesforce. This example creates an update-only sync to Salesforce Contacts, matched on email address:

1
resource "polytomic_sync" "update_contacts" {
2
  name = "Update Contact Names"
3
  mode = "update"
4
  target = {
5
    connection_id = polytomic_salesforce_connection.sfdc.id
6
    object        = "Contact"
7
  }
8
  identity = {
9
    source = {
10
      model_id = polytomic_model.users.id
11
      field    = "email"
12
    }
13
    function = "Equality"
14
    target   = "Email"
15
  }
16
  active = false
17
  schedule = {
18
    frequency = "manual"
19
  }
20
  fields = [
21
    {
22
      source = {
23
        model_id = polytomic_model.users.id
24
        field    = "name"
25
      }
26
      target = "FirstName"
27
    },
28
  ]
29
}

6. Apply the configuration

Initialize Terraform and apply:

$
terraform init
$
terraform apply

Review the planned changes and confirm to proceed.

For more details, see the Polytomic Terraform provider documentation.