How to Load Klaviyo Data into Snowflake

Copy Klaviyo profiles and events into Snowflake tables every night through the Klaviyo API and the Snowflake SQL API, so marketing data can be joined with orders and finance in your warehouse.

What This Integration Does

Klaviyo's data stays in Klaviyo unless something moves it. This Spojit workflow is that something, without an ETL subscription. Each night it reads profiles updated and events created since the last run from the Klaviyo API, flattens them, and loads them into Snowflake with a single SQL statement per batch. The statement uses Snowflake's PARSE_JSON and FLATTEN to turn a JSON array into rows, and MERGE to upsert profiles, so you never need a stage, a file format or a COPY job.

Profiles are merged on their Klaviyo ID, so a profile updated twice is one row with the latest values. Events are inserted with their Klaviyo event ID as a key, and the merge skips IDs that already exist, so overlapping windows and re-runs never duplicate data.

Prerequisites

• A Klaviyo connection with read access to Profiles, Events and Metrics.
• A Snowflake HTTP Requests connection: Base URL https://YOUR_ACCOUNT.snowflakecomputing.com, Bearer token set to a programmatic access token for a user and role that can write to the target schema (see the "How to Generate a PDF Report from Snowflake Query Results" tutorial in this help center for creating the token).
• Target tables, created once in a Snowflake worksheet:

  CREATE TABLE IF NOT EXISTS marketing.klaviyo.profiles (
    id STRING PRIMARY KEY, email STRING, phone_number STRING,
    first_name STRING, last_name STRING, created TIMESTAMP_TZ,
    updated TIMESTAMP_TZ, properties VARIANT, loaded_at TIMESTAMP_TZ);
  CREATE TABLE IF NOT EXISTS marketing.klaviyo.events (
    id STRING PRIMARY KEY, metric_id STRING, profile_id STRING,
    datetime TIMESTAMP_TZ, value FLOAT, properties VARIANT, loaded_at TIMESTAMP_TZ);

Step 1: Schedule and Window

Add a Trigger node with type Schedule and cron 0 2 * * *. Add a Date & Time subtract step named since that takes 25 hours off the trigger time; its ISO 8601 result is in {{ since.iso }}.

Step 2: Read Updated Profiles

Add a Klaviyo step with list-profiles: filter greater-than(updated,{{ since.iso }}), pageSize 100. Page through with a Loop in While mode, passing the cursor from links.next into pageCursor, and collect the records. Then a Transform with Iterate over flattens each record into { id, email, phone_number, first_name, last_name, created, updated, properties } from item.attributes. Name it profiles.

Step 3: Merge Profiles into Snowflake

Add a Connector node on the Snowflake connection with http-post, url /api/v2/statements, and body:

{
  "statement": "MERGE INTO marketing.klaviyo.profiles t USING (SELECT f.value v FROM TABLE(FLATTEN(INPUT => PARSE_JSON(?))) f) s ON t.id = s.v:id::STRING WHEN MATCHED THEN UPDATE SET email = s.v:email::STRING, phone_number = s.v:phone_number::STRING, first_name = s.v:first_name::STRING, last_name = s.v:last_name::STRING, updated = s.v:updated::TIMESTAMP_TZ, properties = s.v:properties, loaded_at = CURRENT_TIMESTAMP() WHEN NOT MATCHED THEN INSERT (id, email, phone_number, first_name, last_name, created, updated, properties, loaded_at) VALUES (s.v:id::STRING, s.v:email::STRING, s.v:phone_number::STRING, s.v:first_name::STRING, s.v:last_name::STRING, s.v:created::TIMESTAMP_TZ, s.v:updated::TIMESTAMP_TZ, s.v:properties, CURRENT_TIMESTAMP())",
  "bindings": { "1": { "type": "TEXT", "value": "{{ profiles_json.result }}" } },
  "timeout": 120,
  "warehouse": "LOAD_WH",
  "role": "LOADER"
}

profiles_json is the flattened list as a JSON string, from JSON Tools stringify (its output is in {{ profiles_json.result }}). Passing it as a bind variable (the ?) means quotes and special characters in names never break the SQL. Add the header X-Snowflake-Authorization-Token-Type: PROGRAMMATIC_ACCESS_TOKEN.

Step 4: Read and Load Events

Repeat for events of the metrics you analyse (Placed Order, Started Checkout, Opened Email): Klaviyo list-events with filter and(equals(metric_id,"YOUR_METRIC_ID"),greater-than(datetime,{{ since.iso }})), flatten each event into { id, metric_id, profile_id, datetime, value, properties } (the profile ID is in relationships.profile.data.id, the value in attributes.event_properties.$value), and run a MERGE INTO marketing.klaviyo.events with only a WHEN NOT MATCHED THEN INSERT clause, so existing event IDs are skipped.

Step 5: Check and Alert

Snowflake's response includes stats with the number of rows inserted and updated. Add a Condition on the HTTP status and, on failure, post the error message to Slack with send-message. Because every load is a merge, the next night's run (with its overlapping window) also catches up anything a failed night missed.

Tips

• Batch size: bind variables have size limits, so load in batches of a few thousand records. Use the Array Tools chunk tool and run the merge once per chunk in a Loop.
• Keep the raw JSON: storing properties as VARIANT keeps every custom property queryable later (properties:loyalty_tier::STRING) without changing the table.
• Backfill: for history, run the workflow manually month by month with a fixed window, rather than one huge run.
• Join with orders: once events are in Snowflake, join Placed Order events with your store's order table on order ID to compare attributed revenue with actual revenue.

Common Pitfalls

• Bind variables in multi-statement requests: Snowflake does not support bindings when the statement contains several SQL statements. Send one MERGE per request.
• Timeouts on large merges: if a merge exceeds timeout, Snowflake returns 202 with a statement handle and keeps running. Poll it, or reduce the batch size.
• Klaviyo rate limits: the events endpoint is rate limited per second. Large backfills should run overnight; Spojit's retries absorb occasional 429 responses.
• Warehouse costs: each load wakes the warehouse. Use a small warehouse with a short auto-suspend for this job.

Testing

Run the workflow manually with a one-hour window, then query the tables in Snowflake: SELECT COUNT(*), MAX(loaded_at) FROM marketing.klaviyo.profiles. Run it again with the same window and check that the counts do not change. Then turn on the schedule.

Learn More

• Klaviyo connector documentation
• HTTP Requests connector documentation
• JSON Tools documentation