Databricks reverse ETL

Import people, objects, and events from Databricks into Customer.io. You write SQL queries against a Databricks SQL warehouse; we run them on a schedule and send the results to your destinations.

Best practices

Before you add a Reverse ETL source, you should take some measures to ensure the security of your customers’ data and limit performance impacts to your database and Customer.io workspace.

• Create a new database user/service account. Implement a database user with minimal privileges specifically for Customer.io import/sync operations. This account only requires read permissions with access limited to the tables you want to sync from.

• Avoid using your main database instance. Consider creating a read-only database instance with replication in place, lightening the load and preventing data loss on your main instance.

• Sync only the data that you’ll use in Customer.io. Limiting your query can improve performance, and minimizes the potential to expose sensitive data. Select only the columns you care about, and make sure you use the {{last_sync_time}} to limit your query to data that changed since the previous sync.

• Limit your sync frequency so you don’t sync more than necessary and consume unnecessary resources. If the previous reverse ETL operation is still in progress when the next interval occurs, we’ll skip the operation and catch up your data on the next interval. You should monitor your first few reverse ETL intervals to ensure that your sync doesn’t impact your system’s security and performance—frequently skipped operations may indicate that you’re syncing too often.

Set up your Databricks environment

As a part of setup, you'll need to provide OAuth client credentials (a client ID and client secret) for a Databricks service principal with read access to the data you want to sync.

To integrate with Customer.io, you’ll need to:

• Set up a service principal and grant it permissions to the data you want to sync. This is how Customer.io connects to your Databricks workspace.
• Allowlist Customer.io’s IP addresses if you use IP access lists.
• Set up a SQL warehouse for your syncs if you haven’t already.

Set up a service principal

Customer.io connects to Databricks as a service principal, a non-human identity with its own credentials and permissions (sometimes called a “service account”). You must be an admin in Databricks to create a service principal.

1. Create a service principal ↗ in your Databricks workspace and generate an OAuth secret for it. Save two values for the person setting up the integration in Customer.io:

   • The service principal’s application ID (a UUID). This is the client ID.
   • The OAuth secret. This is the client secret. Databricks shows it once, when you generate it.

2. Grant the service principal access to the data you want to sync. It needs three privileges: USE CATALOG on the catalog and USE SCHEMA on the schema so it can connect, and SELECT on each table you want to sync so that sync queries can read your data. You can grant these in Catalog Explorer or in a SQL editor.

   Catalog ExplorerSQL editor

   Catalog Explorer

   You can work through these steps separately at the catalog, schema, and table levels. Or, you can grant access to the entire catalog, which should grant all the necessary privileges.

   1. In your Databricks workspace, go to Catalog.
   2. Select the catalog, schema, or table you want to grant access to.
   3. Go to the Permissions tab and click Grant.
   4. Search for your service principal by name.
   5. Select the privilege for the object you selected: USE CATALOG for a catalog, USE SCHEMA for a schema, or SELECT for a table.
   6. Click OK.

   To give the service principal read access to every table in a schema, grant SELECT on the schema instead of on each table.

   SQL editor

   Run these statements in a Databricks SQL editor. Replace the catalog, schema, and table names with your own, and <application-id> with the service principal’s application ID:

   -- connect-time access: lets the service principal see the catalog and schema
   GRANT USE CATALOG ON CATALOG my_catalog TO `<application-id>`;
   GRANT USE SCHEMA ON SCHEMA my_catalog.my_schema TO `<application-id>`;
   
   -- sync-time access: lets sync queries read the tables
   GRANT SELECT ON TABLE my_catalog.my_schema.my_table TO `<application-id>`;

   Grant SELECT on each table you plan to sync. To grant read access to every table in the schema instead, run GRANT SELECT ON SCHEMA my_catalog.my_schema TO ....

3. Grant the service principal access to your SQL warehouse. This is compute access, not a Unity Catalog privilege, so it isn’t a SQL GRANT. In Databricks, go to SQL Warehouses, open your warehouse, click Permissions, and add the service principal with Can use.

Allowlist Customer.io’s IP addresses

If your workspace uses IP access lists ↗, add our IP addresses to the allow list. Otherwise, Databricks rejects our connections before we can authenticate.

If you use a firewall or an allowlist, you must allow the following IP addresses so we can connect to your database. Make sure you use the correct IP addresses for your account region.

Choosing a SQL warehouse

Every sync and every query preview runs on a Databricks SQL warehouse in your account. Databricks bills you for the compute, so there are a few things you’ll want to account for to keep your syncs reliable and costs predictable:

• Use a serverless warehouse whenever possible. Serverless warehouses start in seconds. Classic and pro warehouses take 2-5 minutes to start from a stopped state, which delays syncs and can cause “warehouse is starting” errors during setup.
• Set an auto-stop timeout. Each sync wakes the warehouse, and a running warehouse keeps billing until it stops. Set an auto-stop timeout ↗ so the warehouse shuts down between syncs. Serverless warehouses support short timeouts because restarts are fast.
• Size the warehouse for your queries. A larger warehouse runs each query faster but costs more per hour. If individual sync queries run slowly or time out, size up. Databricks’ sizing guidance ↗ can help you pick a starting size.
• Watch for queueing. A warehouse runs a limited number of queries at once and queues the rest. If you run many syncs on the same schedule, or share the warehouse with other consumers, you can experience queues that delay your syncs to Customer.io. Stagger your sync schedules, add capacity, or give Customer.io its own warehouse.

Set up your Databricks integration

Before you start, you’ll need a few things to set up the connection with Databricks:

• Your Databricks service principal’s ↗ client ID (application ID) and client secret.
• Your Databricks warehouse’s server hostname and HTTP path. In Databricks, go to SQL Warehouses, open your warehouse, and click Connection details to find these values.
• The catalog and schema that contain the data you want to sync.

Set up your Databricks connection

1. Go to Integrations. In the Directory tab, pick the Databricks Data in integration.

   

2. Fill in the connection form and click Connect.
   • Name: a friendly name that helps you recognize your database whenever you reference it in Customer.io.
   • Server hostname: the hostname of your Databricks warehouse without https://—for example, dbc-a1b2c3d4-e5f6.cloud.databricks.com. The hostname format differs by cloud provider. Leave the Port at 443 unless Databricks tells you otherwise.
   • HTTP path: the HTTP path of your Databricks warehouse, like /sql/1.0/warehouses/abc123def456.
   • Client ID: the service principal’s application ID.
   • Client secret: the service principal’s client secret. We store this securely and never display it. You’ll need to re-enter it whenever you update the connection.
   • Catalog and Schema: set where your sync queries run. You can also fully qualify table names in a query (catalog.schema.table) to read other tables the service principal has access to.
3. Click Connect to make sure the connection works.
4. When you’re done, click Next: Define Query to set up your first sync.

Set up a sync

Now you can set up a sync. A sync determines the type of data (identify, track, etc) you want to import from your database and how often it runs.

See Queries below for more information about the information you’ll want to select for your sync. Click Run Query to preview results and make sure that your query selects the right information. Click Enable to enable your sync.

After you set up your incoming integration, you can add additional syncs to import different types of data from your database. For example, you might want to import identify data for your users, and track data for their actions. Subsequent syncs can rely on your existing database, or you can add another database within your integration.

1. In your integration, go to the Syncs tab and click Add Sync.

2. Select your database or add a new one and click Next: Create Sync.

3. Set up a sync. You can set up syncs for each type of data you want to import.

   

   1. Provide a Name and Description for the sync. This helps you understand the sync at a glance when you look at your integration’s Overview later.
   2. Select the type of data you want to import.
   3. Set the Sync Frequency, indicating how often you want to query your database for new data. You should set the frequency such that sync operations don’t overlap. Learn more about sync frequency.
   4. Select when you want to start the sync: whether you want to begin importing data immediately, or schedule the sync to start at a later date.

4. Enter the query that selects the data you want to import. See Queries below for more information about the information you’ll want to select for your sync. Click Run Query to preview results and make sure that your query selects the right information.

   

5. Click Enable to enable your sync.

Sync frequency

You can sync data as often as every minute. However, we recommend that you set your sync frequency such that sync operations don’t overlap. If you schedule syncs such that a sync operation is scheduled to start while the previous operation is still we’ll skip the next sync operation.

Each sync run wakes your SQL warehouse, so your sync frequency affects your Databricks bill. If you don’t need near-real-time data, a longer interval paired with an auto-stop timeout keeps the warehouse stopped most of the time.

Queries and sync types

When you create a database sync, you provide a query selecting the people or objects you want to import, and respective properties. You’ll build your queries using the same principles from our Pipelines API.

Each row returned from your query represents an individual operation (like an identify call, a track event, etc). Columns represent the traits or properties that you want to apply to the person, group, or event that your sync imports.

While we support queries that return millions of rows and hundreds of columns, syncing large amounts of data more then once a day can impact your account’s performance—including delaying automations or messages. When you set up your query, consider how much data you want to send and how often; and make sure you limit your results using the last_sync_time.

Arrays, maps, and structs

Databricks ARRAY, MAP, and STRUCT columns arrive in Customer.io as structured JSON. An ARRAY column becomes an array attribute, and a STRUCT or MAP column becomes an object attribute. You don’t need to serialize these types yourself; select the column and we handle the conversion.

last_sync_time and limiting your results

We strongly recommend that you index a column in your database representing the date-time each row was last-updated. When you write your query, you should add a WHERE clause comparing your “last updated” column to the {{last_sync_time}}.

The last sync time is a Unix timestamp representing the date-time when the previous successful sync started. Comparing a “last-updated” column to this timestamp helps you limit your sync operations to the columns that changed since the previous sync.This helps you avoid syncing the same records repeatedly, which can make your syncs take longer and impact your workspace’s performance.

If you use ISO date-times, you can convert them to Unix timestamps in your query.

We replace the placeholder with a plain number of Unix seconds. If your column stores a TIMESTAMP or a DATE, Databricks won’t compare it to a number, and the query fails with a type error. You need to convert the placeholder to match your column:

SELECT user_id AS userId, email
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

The examples on this page assume TIMESTAMP columns and use this conversion. If your column stores Unix seconds instead, compare it to {{last_sync_time}} directly.

You can convert either side of the comparison. We recommend converting the placeholder, because Databricks applies timestamp_seconds() to a single number, while unix_timestamp(updated_at) runs against every row the query scans.

The examples use >= rather than >. A strict > can permanently skip a row whose timestamp exactly matches the last sync time. With >=, the sync reads that row again on the next run, and we recognize it as a duplicate and don’t process it twice. Compare against a timestamp that changes per row, like a last-updated column, so the number of rows the sync reads again stays small.

When you preview a query, we set {{last_sync_time}} to 30 days ago. To use a different date, click the edit icon next to Last sync time in the preview. If your table’s timestamps are older than the preview date, the preview can return no rows even though a real sync would.

Identify

The identify method tells us who someone is and lets you assign unique traits to a person. Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

You can identify people by anonymousId and/or userId.

• anonymousId only: This assigns traits to a person before you know who they are.
• userId only: Identifies a user and sets traits.
• both userId and anonymousId: Associates the data from the anonymousId with the person you identify by userId.

SELECT user_id AS userId, email, phone, first_name, last_name
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

Track

The track method records things people do. Every track call represents an event.

You should track your audience’s activities with events both as performance indicators and so you can respond to your audience’s activities with automations in Journeys. For example, if your audience performs a Video Viewed or Item Purchased event, you might respond with other videos or products the person might enjoy.

Track calls require an event name describing what a person did. They must also include an anonymousId or a userId. Calls that you make with an anonymousId are associated with a userId when you identify someone by their userId.

In most cases, your query should compare a timestamp to the last_sync_time to ensure that you only import new events.

SELECT user_id AS userId, event_name AS event, products, total_price AS value
FROM events
WHERE created_at >= timestamp_seconds({{last_sync_time}})

Backfilling events

In your initial sync, the last_sync_time is 0, and we’ll capture all events that otherwise match your query. After that, we only capture events that occur after the last_sync_time—events that occurred since the previous sync. This prevents you from importing the same events multiple times, but also means that you can’t backfill event history.

If you need to backfill event history after your initial sync, you’ll need to set up a new sync to import the events you want to backfill. In general, you’ll:

1. Create a new sync with a new query that captures the events you want to backfill.
2. Run the sync to backfill events.
3. Disable the backfilling sync so that you don’t capture events that your normal event query would otherwise import.

Semantic events: Deleting people, groups, and more

You may notice that this integration doesn’t have sync types to delete people, groups, or other objects. To do these kinds of operations, you’ll use what we call semantic events. These are events with specific names that indicate a delete operation. When your Track sync picks up events with an event name we recognize, we’ll perform the associated action—like deleting a person or group.

For example, if you send an event with the name User Deleted, we’ll delete the person from your workspace. See Customer.io Semantic Events for more information.

The semantic events we support are:

Group

The Group method associates a person with a group—like a company, organization, project, online class or any other collective noun you come up with for the same concept. In Customer.io Journeys, we call groups objects. If the group/object or person in your group call don’t exist, this operation creates them.

Group calls require a groupId to represent the group. In almost every case, a group call should also include a userId to associate the person with the group. You can also include traits to provide additional information about the group (or the relationship between the person and the group). Find more details about the group method in our API specifications.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SELECT company_id AS groupId, object_type_id AS objectTypeId, company_name, employees, user_id AS userId
FROM companies
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

Relationship attributes

In Customer.io, you can assign attributes to both the group (called a custom object in Customer.io) and to the relationship between the object and the person. By default, attributes are stored on the custom object itself, but you can assign relationship attributes using the relationshipAttributes JSON object.

Build the relationshipAttributes object with Databricks’ named_struct function:

SELECT company_id AS groupId, object_type_id AS objectTypeId,
    company_name, employees,
    user_id AS userId,
    named_struct(
        'is_manager', is_manager,
        'role', role,
        'start_date', start_date
    ) AS relationshipAttributes
FROM companies
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

Page

The Page method records page views on your website, along with optional extra information about the page a person visited.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SELECT user_id AS userId, meta_title AS name, url, time_on_page
FROM pages
WHERE created_at >= timestamp_seconds({{last_sync_time}})

Screen

The Screen method sends screen view events for mobile devices. These help you understand the screens that people use in your app.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SELECT user_id AS userId, screen_name AS name, session_started
FROM screens
WHERE created_at >= timestamp_seconds({{last_sync_time}})

Alias

The Alias method combines two previously unassociated user identities. Some integrations automatically reconcile people with different identifiers based on whether you send anonymousId, userId, or another trait that the integration expects to be unique. But for integrations that don’t, you may need to send alias requests to do this.

In general, you won’t need to use the alias call; we try to handle user identification gracefully so you don’t need to merge people. But you may need to send alias calls to manage user identities in some data-out integrations.

For example, in Mixpanel it’s used to associate an anonymous user with an identified user once they sign up.

SELECT user_id AS userId, old_id AS previousId
FROM user_resolution
WHERE updated_at >= timestamp_seconds({{last_sync_time}})

Troubleshooting

Your SQL warehouse is starting

If you test the connection or preview a query while your warehouse is stopped, the request fails with a message that your SQL warehouse is starting. Classic and pro warehouses take 2-5 minutes to start; wait for the warehouse to report Running in Databricks, then try again.

Scheduled syncs handle this on their own: when a sync hits a stopped warehouse, we retry for up to 5 minutes while it wakes. The sync starts late but succeeds. If the warehouse takes longer than 5 minutes to start, the run fails and we try again at the next scheduled interval.

To avoid cold starts entirely, use a serverless warehouse, which starts in seconds.

Authentication failures

If we can’t sign in to Databricks with the stored credentials, the source shows a banner explaining what went wrong. Common causes:

• The client secret expired or changed. Databricks OAuth secrets carry an expiration date from the moment you generate them, and rotating or deleting a secret invalidates the one we stored.
• The service principal no longer exists. Someone deactivated or deleted it in Databricks.

To fix it, generate a new OAuth secret for the service principal in Databricks, then open the source’s connection settings in Customer.io and re-enter the client ID and the new client secret. Saving the form with working credentials clears the error. Because we never display the stored secret, you re-enter it whenever you save the connection.

If authentication fails on a brand new connection, check the Server hostname: a hostname that points at the wrong workspace fails the sign-in even when the credentials are right.

Permission errors

If the connection test reports that the service principal can’t access your data, grant the Unity Catalog privileges: USE CATALOG on the catalog, USE SCHEMA on the schema, and SELECT on the tables you sync.

If the test reports that a catalog or schema is missing or not visible, check the names in Databricks Catalog Explorer. A catalog or schema the service principal has no privileges on reports as missing, so this error can also mean a missing USE CATALOG or USE SCHEMA grant.

A connection can also pass the test and then fail at sync time. The test checks catalog and schema access, but sync queries need SELECT on the tables they read.

Slow syncs and timeouts

If syncs run slower than you expect, or individual queries time out:

• Check for queueing. Your warehouse runs a limited number of queries at once. Other workloads on the same warehouse, or many syncs on the same schedule, queue behind each other. Stagger your sync schedules or give Customer.io a dedicated warehouse.
• Size up the warehouse if a single query is slow on its own. Complex joins over large tables need more compute.
• Narrow the query. Use last_sync_time so each run reads only rows that changed since the previous sync instead of the whole table.

Query results are too large

A single sync run can return at most 20 GiB of results. If your query returns more than that, the run fails with a message telling you to add a filter or split the sync. To fix it, add a last_sync_time filter so each run reads only changed rows, or split the query into separate syncs that each cover part of the table.

Repeated failures pause a sync

If a sync keeps failing on an error that won’t resolve on its own, like a query with a syntax error or a table that no longer exists, we pause the sync. The pause prevents scheduled runs from waking your warehouse when those runs can’t succeed. Authentication failures don’t pause a sync. They show a banner instead.

You can resume the sync by fixing the issue and saving your changes.