How to build an ERP to warehouse sync

What matters here

• Pull by last modified date, then re-pull a trailing window. ERPs change records in ways that do not always move the timestamp, and a short overlap catches them.
• Land the raw copy before modelling. A bad transformation is then a rerun, not a re-extract from a system with tight API limits.
• A reversal is a new fact, not an edit. Keep the original, the reversal and the repost, or the period balance stops matching.
• Reconcile money, not rows. Totals per account and period against the ERP's own trial balance is the check finance trusts.
• Closed periods are read-only in the ERP. Treat a change to one in the warehouse as an alert, never as a routine update.
• API limits are shared with every other integration on the ERP. Schedule the heavy pulls outside the close and the billing run.

Who this is for

You run data operations or analytics engineering. Finance reports come out of the warehouse, the numbers drift from the ERP often enough that the controller checks both, and month end is when anyone notices.

How it works in practice

The path from a journal posting in the ERP to a finance table someone can sign off on.

1. 1

   The sync asks the ERP what changed

   Records modified since the last run, plus a trailing overlap for changes that did not move the timestamp.

2. 2

   The raw records land untouched

   In a raw schema in the warehouse, with the run id and the time they were pulled.

3. 3

   Finance tables are built from the raw copy

   Posted, reversed and voided entries kept as separate facts, so history is never rewritten.

4. 4

   Totals are reconciled to the ERP

   Per account, subsidiary and period, against the ERP's trial balance.

5. 5

   A difference goes to a named owner

   With the accounts and periods that disagree, not a failed job and a log link.

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

   Pull only what changed

   A full export every night burns the ERP's API limits and still misses the edge cases.

   Headless skills build-workflow tray-patterns

   Copy

   Use build-workflow. The systems in play are NetSuite and Snowflake, or
   whatever we run in those seats.
   
   For each record type we need (transactions, transaction lines, accounts,
   subsidiaries, customers, vendors, items, accounting periods), pull the
   records modified since the last successful checkpoint. Re-read a trailing
   window of two days on every run, because some ERP changes do not move the
   last modified date and the overlap is what catches them.
   
   Page through results and keep the checkpoint per record type. Commit it
   only after the batch has landed in the warehouse.
   
   Use saved searches or the query API rather than one call per record. A
   sync that calls the ERP once per transaction will hit the account's
   concurrency limit and slow down every other integration that shares it.

   API limits on the ERP are shared across every integration in the account. Schedule the heavy record types outside the billing run and the close.

2. 2

   Land the raw copy first

   A modelling bug should cost a rerun, not a new extract.

   Headless skills build-workflow

   Copy

   Use build-workflow. Write every record to a raw table in Snowflake
   exactly as the ERP returned it, with three extra columns: the run id, the
   time it was pulled and the record type.
   
   Do not rename, cast or drop anything on the way in. Upsert on the ERP's
   internal id and record type, so a record pulled twice by the overlap
   window lands once.
   
   Keep the raw tables append-friendly and partitioned by pull date, so a
   full rebuild of the finance models never has to go back to the ERP.

3. 3

   Keep posted, reversed and voided entries apart

   Overwriting a reversal is how a correct ledger adds up to the wrong number.

   Copy

   Build the finance tables from the raw copy, not from the ERP.
   
   Model journal lines as facts that are never updated in place:
   
     The original posting stays, with its period and posting date
     A reversal is its own line, linked to the original
     A repost is its own line, linked to the reversal
     A voided transaction is marked voided with the date, not deleted
   
   Take the accounting period from the ERP's own period on the
   transaction, never from the transaction date. A transaction dated in
   March and posted to April belongs to April.
   
   Flag any change to a transaction in a period the ERP shows as closed.
   Closed periods should not change, so one that does is something finance
   needs to hear about the same day.

4. 4

   Reconcile totals to the ERP every day

   Row counts can match while the balance does not.

   Headless skills tray-patterns

   Copy

   Use tray-patterns. Once a day, after the sync completes:
   
     Pull the trial balance from the ERP per account, subsidiary and open
     period
     Sum the same from the warehouse finance tables
     Compare, with a tolerance of zero for posted amounts
   
   For every difference record the account, subsidiary, period, both totals
   and the gap. Resolve an owner per subsidiary from a mapping table, and
   send them one message listing what disagrees. Do not alert a shared
   channel, where a difference nobody owns stays open.
   
   Report: run time, records pulled per type, records in the overlap that
   changed, reconciliation differences and API calls used against the
   account's limit.

5. 5

   Test it against a real close, then hand it to finance

   Because the close is when every edge case shows up at once.

   Headless skills tray-gotchas

   Copy

   Use tray-gotchas, then run the per-step schema checks and the
   whole-workflow audit.
   
   In a sandbox account, post a journal, reverse it and repost it. Confirm
   three lines arrive and the period total is right. Void a transaction and
   confirm it is marked, not removed. Change a record without moving its
   last modified date and confirm the overlap window picks it up.
   
   Then open the workflow in Tray Build so data operations can add record
   types and adjust the schedule, and finance can own the subsidiary to
   owner mapping.

What it connects to

The ERP is read, the warehouse holds the raw copy and the finance tables, and differences go to a person.

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