Incremental Klaviyo loads: watermarks, rate limits, and why profile updated misleads you

The hard part of loading Klaviyo incrementally isn't calling the API. It's stopping and starting a long crawl of data that keeps changing, without skipping a row or counting one twice. This post walks through how the open-source klaviyo-data sync on GitHub handles it, with short excerpts from the code.

For what the project loads and how to set it up, start with the open-source Klaviyo to Snowflake sync: how it works and when to use it. If you want this built or reviewed in your own account, that's Greyfield's Snowflake consulting.

Key takeaways

• "Fetch everything since the max timestamp" skips rows when several rows share that timestamp across a page boundary. Committing a watermark one second behind the page group's maximum, and merging on ID, turns those skips into harmless re-reads.
• Rows, cursor and watermark must commit in one transaction. If the data commits without the checkpoint, the next run repeats work; if the checkpoint commits without the data, rows are lost.
• A saved cursor can be rejected. Treat HTTP 400, 404 and 410 on a cursor request as "start again from the watermark", not as a failure.
• Klaviyo's 429 responses carry a Retry-After header. Wait that long, cap the wait, and make every wait fit inside the run's time budget so a throttled run ends cleanly.
• Klaviyo profile updated changes when any property changes, so an updated filter returns far more profiles than changed in the columns you store. Keep the filter and make each page cheaper instead.

Why does "load everything since the last timestamp" skip rows?

It skips rows when the run stops between two pages that share a timestamp. Say a page ends on a profile updated at 10:15:30 and the next page starts with two more profiles at 10:15:30. If you save 10:15:30 as the watermark and resume with updated > 10:15:30, those two profiles are never read.

It also goes wrong when the data and the checkpoint are saved separately. Data saved without the checkpoint means repeated work; a checkpoint saved without the data means rows that look loaded and aren't. The sync fixes the first problem with an overlap and the second with a single transaction.

How does the watermark handle timestamp ties?

The watermark moves to one second before the largest updated in a committed group of pages. Rows tied at that timestamp are read again on resume, and the ID-keyed MERGE absorbs the repeat.

Instant candidate = maxUpdated.minusSeconds(PROFILE_OVERLAP_SECONDS);
return candidate.isAfter(committedWatermark) ? candidate : committedWatermark;

That's from SyncPlan.progressiveWatermark. The watermark never moves backwards, and a maxUpdated at or after the window end never advances it, because the query excludes those rows and one appearing means something is off. The SQL that saves it adds a second guard: greatest(COMMITTED_WATERMARK_TZ, ?).

The resume query then subtracts the overlap again, so it starts strictly after one second before the watermark. Profiles are requested with sort=updated and a filter like this:

and(greater-than(updated,<watermark minus 1s>),less-than(updated,<window end>))

A worked example

These timestamps are synthetic, chosen to show the rule. The window runs from 2026-01-01 00:00:00 to 2026-01-02 00:00:00 UTC.

Nothing is skipped, and the cost of the overlap is re-reading at most a second or two of profiles.

When should the watermark stay where it is?

It should stay put when a page group gives no safe evidence of progress. SyncPlan.groupMaxUpdated returns null, which keeps the current watermark, if the group is empty, isn't in ascending order, or has any row outside the window.

if (updated == null
    || !updated.isAfter(lowest)
    || !updated.isBefore(windowEnd)
    || (previous != null && updated.isBefore(previous))) {
    return null;
}

Out-of-order pages happen on live data: a profile edited while the crawl is running can show up where you don't expect it. The rows are still loaded and the cursor still advances; only the watermark waits. Moving it on doubtful evidence is how incremental loads lose data quietly.

How do you make the checkpoint crash-safe?

Commit the staged rows, the MERGE, the next cursor and the new watermark in one Snowflake transaction, and only if this worker still holds the stream. The checkpoint update ends with this condition:

WHERE JOB_KEY=? AND LEASE_TOKEN=? AND VERSION=?

If the lease was lost or another update got there first, no row matches, the worker raises an error and the transaction rolls back. A crash before the commit rolls back everything; a crash after it loses nothing. Snowflake's MERGE is what makes the replay safe, because loading the same page twice leaves the same table.

The repository tests this two ways. A Java test simulates crashes and lost cursors across page and commit boundaries with many timestamp ties. For a real account, two failpoints, after-merge-before-checkpoint and after-checkpoint-before-commit, inject a crash, and the worker refuses them outside the configured development schema.

Group pages, not just rows

Each profile commit MERGEs into the full profiles table, and the code comments note that this costs about the same for a small or a larger staged batch. So profile pages are grouped, up to 100 per transaction by default, while event streams commit every page. The trade-off is plain: a crash replays at most one uncommitted group.

What happens when Klaviyo rejects a saved cursor?

The worker clears the cursor and queries again from the committed watermark. Klaviyo pages with a links.next URL, and a saved one can stop working between runs. A 400, 404 or 410 on a cursor request raises CursorInvalidException, the transaction rolls back, and the stream resets its cursor.

if (cursorRequest && (code == 400 || code == 404 || code == 410)) {
    throw new CursorInvalidException("Saved cursor rejected with HTTP " + code);
}

Because the profile watermark guarantees everything at or before it is loaded, restarting from it is always safe. Event windows don't advance a watermark mid-window, so they restart from the window start. The worker also only follows a links.next URL that is HTTPS on a.klaviyo.com, with no user info or custom port.

Why do event streams re-read the last 24 hours?

Events can arrive after the time they describe, so a window that closed can still gain rows. Each event query starts 24 hours before the window start and pages by datetime, and the MERGE on METRIC_ID and ID absorbs the overlap. Event streams keep one watermark per window, moved only when the window completes.

How should a loader handle Klaviyo's rate limits?

Wait as long as Klaviyo says, and never longer than the run can afford. Klaviyo limits requests per account over a short burst window and a longer steady window, and returns HTTP 429 with a Retry-After header when you hit one.

if (retryAfter != null && retryAfter.matches("[0-9]+")) {
    return Math.min(60_000L, Long.parseLong(retryAfter) * 1000L);
}
long base = Math.min(30_000L, 1000L << Math.min(attempt - 1, 5));
return base + jitterMillis;

Every wait is trimmed to fit the worker's time budget (120 seconds by default), and a request is only started if more than 10 seconds remain. One worker runs per stream and the runners take streams one at a time, which keeps request volume modest without a separate rate limiter.

Why does profile updated mislead you?

Because it changes when any profile property changes, not only the ones you load. The README notes that this includes properties Klaviyo or connected integrations update from activity, so an updated filter is much wider than "email or consent changed". Klaviyo documents the profile fields in its Profiles API overview.

The tempting fix is a narrower filter, such as subscribed profiles only. The sync keeps the full updated filter on purpose: a narrower one would miss unsubscribes, suppressions and email changes, which are the changes consent reporting cares about most. It handles the cost instead:

• Ask for less. A sparse fieldset means each page carries only email, updated and the two consent flags.
• Commit less often. Grouping up to 100 profile pages per transaction spreads the fixed cost of each MERGE.
• Never regress a row. The MERGE only overwrites a profile when the incoming updated is the same or newer, so replays and out-of-order pages can't put old values back.
• Watch the lag. check_klaviyo_capacity.py warns when the profiles watermark falls further behind across consecutive batches, before it crosses the freshness limit.

WHEN MATCHED AND source.UPDATED>=target.UPDATED THEN UPDATE SET ...

If you pay a connector by rows, the same effect shows up on the bill: every profile Klaviyo marks as updated counts, whether or not anything you report on changed. How to choose a data ingestion tool for ecommerce compares those pricing models.

Frequently asked questions

What to do next

Read the code in linerss/klaviyo-data, starting with SyncPlan.java and its tests. For setup steps and when a managed connector is the better choice, see the open-source Klaviyo to Snowflake sync.

If you'd like a second pair of eyes on an incremental pipeline, or want Klaviyo data loaded into Snowflake and joined to your orders, see Snowflake consulting or ecommerce data warehouses with Greyfield, or book a call with Linus.