An open-source Klaviyo to Snowflake sync: how it works and when to use it

I built a Klaviyo to Snowflake pipeline for a DTC brand I work with, and I've now released the core of it as open source. It loads Klaviyo profiles and events into Snowflake in short, resumable runs, and it is MIT licensed: linerss/klaviyo-data on GitHub.

If you'd rather have someone set it up, tune it, or decide whether you need it at all, that is part of Greyfield's Snowflake consulting and ecommerce data warehouse work.

Key takeaways

• The project loads two Klaviyo objects, profiles (email and consent flags) and events for the metrics you choose, into three Snowflake tables, using MERGE so every load is idempotent.
• Each stream keeps its watermark, active window, pagination cursor and lease in one state table, and rows and checkpoint commit in the same transaction. A run can stop at any point and the next one carries on.
• It is a good fit when profile volume or Klaviyo's rate limits are the problem and you only need a few fields. A managed connector is the better fit when you want many Klaviyo objects and no code to run.
• It was extracted from a production pipeline, but this standalone version has only been tested with unit tests, not run end to end against a live Klaviyo account. Start in a development schema.

What does the Klaviyo to Snowflake sync load?

It loads Klaviyo profiles and the events for each metric ID you list, into two raw tables plus a state table. The profile request uses Klaviyo's sparse fieldsets, so only the stored fields are transferred.

EMAIL_MARKETING and SMS_MARKETING hold the profile's can_receive_email_marketing and can_receive_sms_marketing flags. Event properties are not stored. If you need them, the README points to where to add columns and parsing.

That is deliberately narrow. It covers the questions most reports ask of Klaviyo: who is reachable by email or SMS, and which profiles triggered a metric such as an open or a click, and when. For joining those tables to orders, see how to combine Shopify, Klaviyo, and Recharge data.

How does the sync work?

The sync splits Klaviyo into streams, works through each stream in 24-hour windows, and saves its position after every committed group of pages. Five ideas carry the design.

Streams

Every stream is a job key: profiles, plus one event:<METRIC_ID> per metric ID. Workers refuse any job key that isn't configured, so a typo can't create a new stream.

The state table

KLAVIYO_SYNC_STATE holds one row per stream. Besides the watermark and cursor, it keeps the active window, a shared target cutoff, the status, rows written, the last error (one line, at most 500 characters) and a VERSION counter that every update checks and increments.

Leases

A worker starts by taking the stream's lease with a conditional UPDATE. If another worker holds an unexpired lease, it exits with code 75 and changes nothing. Leases last longer than the worker's whole time budget, so a slow run can't lose its lease mid-commit, and an expired lease can be taken over by the next run.

Watermarks and windows

Each stream works through 24-hour windows that start at its committed watermark. Profiles are requested sorted by updated, so the watermark can move forward after each committed group of pages, to one second before the largest updated in that group. The database also refuses to move a watermark backwards. The deeper post on watermarks and rate limits walks through why that one second matters.

The cursor

Inside a window the worker follows Klaviyo's links.next cursor and saves it with each commit. If Klaviyo rejects a saved cursor with HTTP 400, 404 or 410, the worker clears it and queries again from the committed watermark. Pagination URLs are only followed if they are HTTPS on a.klaviyo.com.

One transaction per page group

Staged rows, the MERGE, the new cursor and the new watermark are committed together, guarded by the lease token and VERSION. A crash before the commit rolls everything back. A crash after it loses nothing. Profile pages are grouped up to 100 per transaction, and event streams commit one page at a time.

A shared cutoff for downstream jobs

All streams work towards the same target cutoff, set to five minutes before the time the cohort starts. CohortHealth moves every stream to a new cutoff only after all of them have reached the current one. Downstream models can treat the previous cutoff as a consistent "loaded through" time.

How do you set it up?

You need Java 17 or newer with Maven, Python 3.9 or newer, bash with flock and timeout, a Klaviyo private API key with read access to profiles and events, and a Snowflake user with key-pair authentication. The steps below follow the README.

1. Build. mvn package compiles the code, runs the unit tests and copies the Snowflake JDBC and json-simple jars to target/lib.
2. Create the Snowflake objects. Edit the placeholders, then run sql/02_raw_tables.sql (raw tables), sql/03_sync_state.sql (state table) and sql/01_role.sql (a least-privilege role and grants). If you haven't set up roles and warehouses yet, start with how I set up a new Snowflake account.
3. Configure the environment. Copy .env.example to .env and fill in the API key file, Snowflake account, user, private key file and warehouse. Add the metric IDs you want to KLAVIYO_EVENT_METRICS; GET /api/metrics lists them.
4. Seed the state table. Run StateBootstrap seed to insert a row for each configured stream. Run it again after adding a metric ID; existing rows are never changed.
5. Run one worker by hand. scripts/klaviyo-incremental.sh profiles runs one bounded worker. Its log lines start with KLAVIYO_ and never contain profile IDs, emails, payloads or cursors.
6. Schedule it. Pick one regular schedule from the table below, and check state with StateBootstrap inspect.

cp .env.example .env    # then edit .env
set -a; . ./.env; set +a
scripts/klaviyo-incremental.sh profiles
scripts/klaviyo-incremental.sh event:<METRIC_ID>

Use the five-minute cycle or the batch runner, not both. Don't run the batch and catch-up runners at the same time; give them the same lock file, as the README's cron example does.

When should you use it instead of Fivetran, Airbyte, or Daton?

Use this sync when Klaviyo profile volume, rate limits or per-row pricing are the problem and you only report on consent, email and a handful of event metrics. Use a managed connector when you want many Klaviyo objects, a vendor to maintain the API changes, and nobody on your team to run a scheduler.

Klaviyo also sells a managed Snowflake export as part of its Advanced KDP subscription. If you have that entitlement and its fields cover your reports, it can replace both options.

For a source-by-source view across loaders, read how to choose a data ingestion tool for ecommerce and the earlier Fivetran vs Airbyte vs Daton comparison. If you're weighing a packaged product against your own warehouse, see Saras Analytics (Daton) vs a custom data warehouse and Funnel vs a custom data warehouse.

What are its limits?

The most important limit is that this exact code is new. It comes from a pipeline I built and run in production, generalized so it stands alone, and it passes its unit tests (22 Java, 53 Python). It has not yet been run end to end against a live Klaviyo account in this form.

• Test it first. Point it at a development schema, load a short window, and compare row counts with Klaviyo before you trust it. Open an issue on GitHub if something breaks.
• Deletions aren't tracked. A query on updated only returns profiles that still exist, so profiles deleted in Klaviyo stay in RAW_KLAVIYO_PROFILES.
• Few fields. Only the columns in the table above are stored. Event properties need code changes.
• Pinned API revision. The Klaviyo API revision is fixed in SyncPlan.API_REVISION. Read Klaviyo's versioning policy and changelog before you change it.
• You operate it. It runs on one host with cron, flock and timeout. Alerting, log storage and a process supervisor for long catch-ups are up to you.

The tests need no network, Klaviyo or Snowflake. To test crash recovery against a real account, the README describes two failpoints that only work in the configured development schema.

Frequently asked questions

What to do next

Clone the repository on GitHub, run it against a development schema, and read the deeper post on watermarks, rate limits and profile updates before you change the checkpoint logic.

If you want help deciding between this, a managed connector and Klaviyo's own export, or want it set up and monitored in your Snowflake account, see Snowflake consulting or ecommerce data warehouses with Greyfield, or book a call with Linus.