How to export Klaviyo data to Databricks

You will learn

Learn how to set up Databricks as an outbound sync destination so Klaviyo continuously writes your profile, event, and metric data into Delta tables in your own Databricks workspace. Once connected, your data team can join Klaviyo data with the rest of your lakehouse for analysis, modeling, and reporting.

Looking to bring data into Klaviyo from Databricks instead? See Connecting Klaviyo and Databricks.

Before you begin

Make sure you have the following:

• A Databricks workspace with Unity Catalog enabled. Hive metastore–only workspaces aren't supported. If you use Hive metastore, consider Hive metastore federation.
• A SQL warehouse that supports VARIANT columns and session-scoped temporary tables so Klaviyo can write data. Serverless SQL warehouses are recommended; Classic and Pro warehouses can take several minutes to start, which may delay syncs.
• Permission to create schemas, tables, and grants in the catalog you'll use (or help from a Databricks admin).
• Permission to manage integrations in Klaviyo.
• Klaviyo's outbound IP addresses allowlisted, if your workspace restricts inbound traffic with IP access lists:
  • 184.72.183.187/32
  • 52.206.71.52/32
  • 3.227.146.32/32
  • 44.198.39.11/32
  • 35.172.58.121/32
  • 3.228.37.244/32
  • 54.88.219.8/32
  • 3.214.211.176/32

Step 1: Create a schema for Klaviyo data

In Databricks, schemas live inside a catalog. Create a dedicated schema to hold the tables Klaviyo writes to. The SQL examples throughout this article use main as the catalog and KLAVIYO_EXPORT as the schema. If you use different names, replace them in all SQL examples and enter the same names in Klaviyo during Step 5.

USE CATALOG main;  -- or your organization's designated catalog
CREATE SCHEMA IF NOT EXISTS KLAVIYO_EXPORT;

Step 2: Create the destination tables

Run the following script in the schema you created in Step 1. Klaviyo writes to 3 tables:

• klaviyo_profile: One row per profile, including standard fields, location, and custom properties.
• klaviyo_event: One row per event (for example, Placed Order or Opened Email), linked to a profile and metric.
• klaviyo_metric: One row per metric, used to look up the metric name and source integration for each event.

Custom profile properties and event properties are stored in VARIANT columns, so you can query nested JSON directly without managing hundreds of individual columns.

USE SCHEMA main.KLAVIYO_EXPORT;

CREATE TABLE klaviyo_profile (
  id                 STRING NOT NULL,
  external_id        STRING,
  email              STRING,
  phone_number       STRING,
  first_name         STRING,
  last_name          STRING,
  title              STRING,
  organization       STRING,
  properties         VARIANT,
  image              STRING,
  created            TIMESTAMP,
  updated            TIMESTAMP,
  location_address1  STRING,
  location_address2  STRING,
  location_city      STRING,
  location_country   STRING,
  location_latitude  STRING,
  location_longitude STRING,
  location_region    STRING,
  location_zip       STRING,
  PRIMARY KEY (id)
)
TBLPROPERTIES ('delta.feature.variantType-preview' = 'supported');

CREATE TABLE klaviyo_event (
  id               STRING NOT NULL,
  metric_id        STRING NOT NULL,
  profile_id       STRING,
  event_properties VARIANT,
  datetime         TIMESTAMP,
  uuid             STRING,
  PRIMARY KEY (id)
)
TBLPROPERTIES ('delta.feature.variantType-preview' = 'supported');

CREATE TABLE klaviyo_metric (
  id                   STRING NOT NULL,
  name                 STRING NOT NULL,
  integration_id       STRING,
  integration_name     STRING,
  integration_category STRING,
  created              TIMESTAMP,
  updated              TIMESTAMP,
  PRIMARY KEY (id)
);

Step 3: Create a service principal and access token

Klaviyo authenticates to Databricks with a personal access token (PAT). We recommend creating a dedicated service principal used only for this integration, rather than a person's account.

1. Create a service principal (or a workspace user) for Klaviyo.
2. Generate a personal access token:
   • Service principal: Ask a workspace admin to create the initial token with the Databricks CLI.
   • Workspace user: Create a token in the Databricks UI.
3. Store the token somewhere secure. You'll paste it into Klaviyo in Step 5.

If you already created a service principal for importing from Databricks, you can reuse it. Just add the export grants in Step 4.

Step 4: Grant permissions

Grant the Klaviyo service principal access to the catalog, schema, and SQL warehouse. Replace klaviyo_service_user with your service principal's application ID or username.

GRANT USE CATALOG ON CATALOG main TO `klaviyo_service_user`;
GRANT USE SCHEMA, SELECT, MODIFY ON SCHEMA main.KLAVIYO_EXPORT TO `klaviyo_service_user`;

Also give the service principal Can use permission on the SQL warehouse you plan to use. In Databricks, go to SQL Warehouses, select your warehouse, click Permissions, and add the service principal.

Verify your setup (optional)

Using the same identity and token you'll give to Klaviyo, confirm the tables and grants:

SHOW TABLES IN main.KLAVIYO_EXPORT;
SHOW GRANTS ON SCHEMA main.KLAVIYO_EXPORT;

You should see all 3 tables, and the service principal should be listed with USE SCHEMA, SELECT, and MODIFY.

Step 5: Connect Databricks in Klaviyo

1. In Klaviyo, navigate to Advanced > Data management > Syncing.
2. If you already have a sync, click Create sync.
3. Select Export data to your data warehouse.
4. Choose Databricks, then click Connect to Databricks.
5. Enter your connection details:

Click Next to configure your sync.

You can also find Databricks in Klaviyo's app marketplace by going to Integrations > Explore apps and searching for Databricks.

Step 6: Configure your data sync

Choose what Klaviyo sends to Databricks and when it syncs.

Data objects

Choose to sync profiles, events, or both. Metric data is synced alongside events so you can look up each event's metric name and source.

Integrations to exclude

Select any integrations whose events you don't want sent to Databricks. This applies to event data only; it doesn't exclude profiles.

Selective sync

Select the metrics whose events you want to sync, such as Placed Order. Leave Selective sync empty to sync all events from integrations you haven't excluded. This field only appears if you select the Events data object.

Sync cadence

Under Every, choose how often to sync new and updated data: every 1, 2, 4, 6, 12, or 24 hours. The default is 1 hour. Set Start Date, Start Time, and Timezone for the schedule.

Historical data

Choose how much historical data to send during the initial sync:

• 30 days
• 90 days
• 1 year
• All time

Step 7: Review, connect, and monitor your sync

1. Click Next to open Review. Check your connection details, shared data, excluded integrations, selected metrics, and initial data sync range.
2. Click Connect. Klaviyo tests the connection and creates the export if the test succeeds.

When setup succeeds, Klaviyo shows a confirmation message and returns you to Syncing. Your export is enabled, and you can monitor its sync status there.

If the connection test fails, review the error details. Click Edit credentials to correct your connection details, then click Next through the data settings to return to Review and click Connect again.

To monitor your sync, go to Advanced > Data management > Syncing and click Databricks in the row marked Export. Use these tabs:

• Historical: The status of your initial backfill.
• Periodic: The status of syncs on your selected cadence, including data freshness (how far behind Klaviyo your Databricks tables are).
• Settings: View and edit your connection details, data selection, sync cadence, and historical range. Click Update information in the section you change to save.

For details on sync statuses, pausing and resuming syncs, and viewing error logs, see Understand data warehouse syncing in Klaviyo.

Query your Klaviyo data in Databricks

Here are a few examples to get started.

Count events by metric name in the last 7 days:

SELECT m.name AS metric, COUNT(*) AS events
FROM main.KLAVIYO_EXPORT.klaviyo_event e
JOIN main.KLAVIYO_EXPORT.klaviyo_metric m ON e.metric_id = m.id
WHERE e.datetime >= current_timestamp() - INTERVAL 7 DAYS
GROUP BY m.name
ORDER BY events DESC;

Read a custom profile property from the properties VARIANT column:

SELECT id, email, properties:loyalty_tier::STRING AS loyalty_tier
FROM main.KLAVIYO_EXPORT.klaviyo_profile
WHERE properties:loyalty_tier IS NOT NULL;

Get order value from Placed Order events:

SELECT e.profile_id, e.datetime, e.event_properties:`$value`::DOUBLE AS order_value
FROM main.KLAVIYO_EXPORT.klaviyo_event e
JOIN main.KLAVIYO_EXPORT.klaviyo_metric m ON e.metric_id = m.id
WHERE m.name = 'Placed Order';

Troubleshooting

Remove a Databricks export

Removing the export stops future syncs. Data already written to your Databricks tables isn't deleted.

1. Go to Integrations in Klaviyo.
2. Open the menu next to the Databricks export integration and select Remove integration.
3. Click Remove integration in the confirmation dialog.

Additional resources

• Connecting Klaviyo and Databricks (import)
• Understand data warehouse syncing in Klaviyo
• Getting started with Advanced KDP