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 Klaviyo can use to 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).
• 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. Replace main with your catalog if you use a different one.

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 NOT NULL,
  integration_name     STRING NOT NULL,
  integration_category STRING NOT NULL,
  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: Create a 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 KDP > Data management > Syncing.
2. Click Create sync.
3. Select Export data to your data warehouse.
4. Choose Databricks.
5. Enter your connection details:

Then click Test connection. Klaviyo confirms that it can reach your workspace and write to the destination tables.

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

Step 6: Choose what data to sync

After your connection is verified, choose what Klaviyo sends to Databricks.

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

Choose specific events to sync. By default, all events are included. This field only appears if you select the Events data object.

Sync cadence

Klaviyo syncs new and updated data to Databricks every hour. This cadence can't be changed.

Historical data

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

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

Step 7: Review and monitor your sync

When setup completes successfully, you'll see that the connection is Enabled, along with a summary of what's being shared and any excluded integrations. If the connection fails, you'll see an Unable to connect status with options to retry or edit your credentials.

To monitor your sync, go to Advanced KDP > Data management > Syncing and click your Databricks destination. You'll see 2 tabs:

• Historical: The status of your initial backfill.
• Periodic: The status of hourly syncs, including data freshness (how far behind Klaviyo your Databricks tables are).

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

To stop exporting to Databricks, go to Integrations, open the menu next to the Databricks integration, and select Remove integration. Data already written to your Databricks tables isn't deleted.

Additional resources

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