How to build a CRM to warehouse sync

What matters here

• Capture change over time, not current state. Every useful pipeline question is about what moved, and a mirror cannot answer one.
• Handle deletes explicitly. A record that vanishes from the source and from the warehouse rewrites history that already happened.
• Land raw first, model second. A transformation bug found in week three is recoverable if the raw extract is still there.
• Reconcile row counts every run. Silent partial loads are the most common warehouse failure and nothing announces them.
• Watch for schema drift. A field added or a picklist value renamed in the CRM breaks a downstream model quietly.

Who this is for

You run data or analytics engineering. Reporting is built on a nightly export that nobody fully trusts, historical questions cannot be answered, and a failed load is discovered when a dashboard looks wrong.

How it works in practice

Everything that sits between a record changing in the CRM and a dashboard being able to answer for it.

1. 1

   Changed records are extracted against a watermark

   Not a full table copy each night, because the volume grows and the window does not.

2. 2

   Raw lands first, untransformed

   A dated raw layer, so a modelling bug found later is fixed by reprocessing, not by asking for the data again.

3. 3

   History is captured as change over time

   Each version of a record with its valid-from and valid-to, so March can be reconstructed exactly.

4. 4

   Deletes and merges are recorded, not applied

   A record disappearing is an event with a date, not a reason to erase what was true before it.

5. 5

   Row counts are reconciled every run

   Source against raw against modelled. A mismatch fails the run rather than publishing a quiet undercount.

6. 6

   Schema drift is detected before it breaks a model

   A new field or a renamed picklist is reported the day it appears.

Paste these into Claude Code or Codex with the Tray Headless plugin installed. Each stage runs on its own. The systems named in them are the worked example rather than a requirement, and every prompt says so.

Once per project, run /tray-workflows:set-workspace to pick the workspace these build in. Point it at a sandbox first.

1. 1

   Set up and establish what changes and how you can tell

   Everything depends on being able to detect change reliably.

   Headless skills build-workflow tray-patterns

   Copy

   Use build-workflow. The systems in play are Salesforce and Snowflake,
   or whatever we run in those seats.
   
   For each object I care about, Opportunity, Account, Lead, Contact and
   User, I need: the modified timestamp field, whether deletes are soft or
   physical, whether the API exposes a change data feed, and how merges are
   represented.
   
   That last one matters most. A merge looks like a delete plus an update,
   and if the sync treats it as a delete the history disappears.

2. 2

   Extract deltas, land raw, never transform on the way in

   So a modelling bug is a reprocess, not an apology.

   Headless skills build-workflow

   Copy

   Use build-workflow. Extract records modified since the last watermark,
   with a few minutes of overlap so nothing straddling the boundary is
   missed.
   
   Land the raw payload exactly as received into a dated raw table. Do not
   transform, rename, cast or filter on the way in. The raw layer is your
   insurance: a transformation bug found in week three is a reprocess if the
   raw is intact, and an apology if it is not.
   
   Advance the watermark only after a fully successful load. A partial load
   that advances the watermark silently drops every record in the gap, and
   nobody finds out until a number looks wrong months later.

3. 3

   Capture history, not current state

   Because every interesting question is about what changed.

   Copy

   Model the raw into history tables, not a mirror.
   
   For opportunities, track a new version whenever stage, amount, close date,
   owner or forecast category changes. Each version carries valid_from and
   valid_to, with the current version open-ended.
   
   That makes these answerable, and none of them are answerable from a
   mirror:
   
     What did pipeline look like on the last day of March
     How many deals slipped out of Q2 and where did they land
     What was the amount when this deal entered negotiation
     Which deals changed owner mid-cycle and what happened to them
   
   Keep a current-state view on top for convenience, but build it from the
   history instead of maintaining it separately.

   This is the whole reason to build a warehouse sync rather than pointing a dashboard at the CRM. A mirror only ever answers what is true now.

4. 4

   Handle deletes, merges and schema drift

   The three things that corrupt a warehouse quietly.

   Headless skills tray-gotchas

   Copy

   Use tray-gotchas, then handle these explicitly:
   
   Deletes. Soft delete with a deleted_at timestamp. Never remove the row.
   A deleted opportunity was real when it existed and history should still
   show it.
   
   Merges. Record the merge as an event linking loser to survivor, and keep
   both histories. Treating a merge as a delete erases activity that
   happened.
   
   Schema drift. Compare the source schema each run. Report new fields,
   removed fields and renamed picklist values the day they appear. A renamed
   stage silently breaks every model that groups by it, and the dashboard
   just shows a new category nobody notices.
   
   Hard deletes at source. If the API supports a deleted-records feed, read
   it. Otherwise reconcile ids periodically, because a physically deleted
   record simply stops appearing and no delta will ever mention it.

5. 5

   Reconcile counts, every run

   Silent partial loads are the most common failure and the least visible.

   Copy

   After every run, reconcile three numbers per object: the source count of
   records modified in the window, the rows landed in raw, and the rows
   modelled.
   
   A mismatch fails the run and alerts. Do not publish a partial load and do
   not let the next run paper over it.
   
   Also check for staleness: if an object has had no changes for longer than
   its normal quiet period, that is a broken extract rather than a quiet
   day. Alert on it.
   
   Then report per run: rows by object, run duration, reconciliation
   results, schema drift detected and watermark position. Somebody should be
   able to answer whether last night worked in ten seconds.

6. 6

   Validate, then hand it to analytics engineering

   So the object list and the tracked fields belong to the team modelling them.

   Copy

   Run the per-step schema checks and the whole-workflow audit before this
   touches production.
   
   Then open the same workflow in Tray Build so analytics engineering can add
   an object, change which fields are tracked historically, or adjust the
   schedule in the visual canvas. The reporting requirement changes
   constantly, and the extract should keep up without a sprint.

What it connects to

One source, one destination, and the people who need to know when a load failed.

Named systems are the ones most teams run, not the only ones that work. Each is an authentication in your Tray workspace, referenced by name, so the workflow never holds a credential. Where we have a connector page, the name links to it.

Questions people ask

Related guides