​

Syncing the warehouse into your own systems

​

The pattern

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)

-- 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

​

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