> ## Documentation Index
> Fetch the complete documentation index at: https://help.treble.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Incremental Sync

> Keep a copy of your Treble data in your own systems, reliably, using the synced_at watermark.

# Syncing the warehouse into your own systems

If you connect via JDBC/ODBC to replicate warehouse data into your own database or lake, **you do not need periodic full re-downloads**. Every table exposes `synced_at` — the moment each row was written or last corrected — designed exactly for incremental extraction.

## The pattern

Keep one watermark (`T_prev`) per table in your system, then on every sync cycle:

```text theme={null}
1. Freeze the cut:      T = max(synced_at) from the table   (one query, use OUR clock)
2. Pull the delta:      WHERE synced_at > {T_prev} AND synced_at <= {T}
3. UPSERT by key:       insert new rows, replace existing ones by their id
4. Refresh the hot window: re-pull the last 3 days by event date and replace
                        that slice wholesale in your copy
5. Advance:             T_prev = T   (only after steps 2–4 succeeded)
```

### Why each step matters

* **Freeze `T` once (step 1).** Using `now()` inside your queries makes every page of a long extraction see a different instant. Freezing one timestamp makes the whole extraction a consistent snapshot.
* **UPSERT, never blind INSERT (step 3).** When the warehouse corrects a row (a delivery status lands, a late CRM id arrives), it re-emits the **same business key** with a fresh `synced_at`. An upsert replaces your stale version; a blind insert would duplicate it. Every table's key is its `*_id` column (`session_id`, `message_id`, `deployment_id`, …).
* **Replace the hot window (step 4).** In rare cases a row can *disappear* from a table (for example, a duplicate created by an internal correction being cleaned up). A query can return what exists — it cannot tell you what stopped existing. Re-pulling a short recent window by event date (`created_at` / `scheduled_at`) and replacing that slice in your copy guarantees your data matches ours, including removals. Three days is a comfortable window; all corrections happen within 48 hours.

### Reference cycle (example: campaign sends)

```sql theme={null}
-- step 1
SELECT max(synced_at) FROM fact_campaign_sends;   -- => {T}

-- step 2 (then UPSERT by deployment_id on your side)
SELECT *
FROM fact_campaign_sends
WHERE synced_at > {T_prev} AND synced_at <= {T};

-- step 4 (replace this slice wholesale in your copy)
SELECT *
FROM fact_campaign_sends
WHERE scheduled_at >= today() - 3 AND synced_at <= {T};
```

## Initial load

The first sync is the same mechanism with `T_prev` at zero: pull everything with `synced_at <= T`, in date-bounded chunks if the table is large, then start cycling. No special case.

## What not to do

* **Don't re-download history on a schedule** — consolidated data (older than 48 hours) is immutable; re-pulling it is pure cost.
* **Don't build cutoffs from your own clock** — always compare against `synced_at` values read from the warehouse.
* **Don't skip the upsert** — corrections are a feature, not an anomaly; your copy should absorb them.

## Choosing a cadence

Any cadence works — the watermark makes cycles independent. Every 15–60 minutes matches the warehouse's own freshness; hourly or daily is fine for reporting copies. Each cycle's cost is proportional to what changed, not to the size of your history.