Data Synchronization: How to Keep Two Systems Consistent
By Apex Horizon Digital
Data synchronization fails when two systems are allowed to edit the same meaning without rules for authority, timing, conflict, deletion, and recovery. A reliable design begins with a field-level source-of-truth table and a stable identifier shared across systems. It then defines how changes travel, what happens when both sides change, how retries remain safe, and how owners reconcile records that cannot be resolved automatically.
Key takeaways
- Assign one authoritative owner for every synchronized field and business state.
- Treat conflicts, deletion, and uncertain delivery as explicit workflow outcomes.
- Reconcile independently so silent drift is found even when every integration run reports success.
Build the source-of-truth table
Create one row for every field or state that crosses the boundary. Record the business meaning, system of record, permitted editor, destination, direction, identifier, validation, update trigger, and conflict owner. Customer legal name may belong to CRM, payment status to accounting, and fulfillment status to ERP. Do not assign authority by convenience or whichever API is easiest to call. The table should also distinguish copied display data from values that trigger financial or operational action. A destination may display a value without being allowed to originate a change to it.
- Identity: stable internal key, external key, matching rule, and duplicate-resolution owner.
- Authority: source system, permitted role, write direction, and approved override process.
- Quality: required status, format, allowed values, validation, and correction destination.
Define change direction and timing
Choose whether each value moves one way, two ways, or only after a business transition. Event-driven synchronization can reduce delay, while scheduled synchronization may simplify load and recovery. The choice should follow how quickly the destination needs the change and what happens if it is temporarily stale. Record create, update, and status-transition behavior separately. A customer created in CRM may be copied to accounting only after required tax fields are complete. Use a stable version, timestamp, or change token where supported, and document timezone and ordering assumptions.
- One-way flow: one system owns the value and destinations never send edits back.
- Two-way flow: both sides can edit only when a documented conflict rule protects meaning.
- Transition flow: data moves after validation, approval, posting, or another explicit state change.
Handle conflicts and deletion deliberately
A conflict exists when two valid changes cannot both be applied. Do not rely on latest timestamp unless the business accepts losing the earlier decision and system clocks are trustworthy. Prefer field ownership, version comparison, or a review queue that shows both values, editors, timestamps, and downstream impact. Deletion needs its own policy. Hard deletion may be inappropriate for financial or audit records. A source can instead mark a record inactive, revoked, merged, or archived, then send that state. Define whether linked transactions block deletion and how merged identifiers are preserved for history.
- Conflict rule: authoritative system, version check, allowed merge, or human decision with evidence.
- Deletion rule: hard delete, soft delete, archive, deactivate, merge, or retain without further sync.
- Downstream rule: notification, access removal, transaction block, history retention, and restoration path.
Make retries safe and outcomes observable
Every change needs a transaction or event identifier that survives retry. The receiving side should recognize repeated delivery and return the existing outcome instead of creating another record. Distinguish validation rejection, temporary service failure, permission failure, and unknown outcome. Retry temporary failures with bounded delay, but reconcile an unknown outcome before resending a non-repeatable action. Logs should include the business identifier, source version, target action, attempt, result, and error category without exposing sensitive values unnecessarily. An alert must route to an owner who can correct the cause.
- Idempotency: repeated delivery of one change produces one business outcome.
- Recovery: safe retry, corrected resubmission, manual resolution, or reconciliation before another action.
- Observability: correlation ID, record ID, version, direction, timestamp, result, age, and owner.
Reconcile and assign operating ownership
Run an independent comparison on a defined schedule. Compare counts, identifiers, field versions, totals, statuses, and records updated since the previous checkpoint. Separate missing source, missing target, value mismatch, duplicate, stale update, and unresolved conflict. Each category needs an owner and correction procedure. Track the age and recurrence of differences, not only the percentage matched. The OpenAPI Specification can document interfaces, inputs, responses, and security schemes, but business authority and reconciliation remain organizational decisions. Review mapping, credentials, API versions, ownership, and deletion policy whenever either system changes.
- Reconciliation evidence: period, population, comparison rules, differences, owner, and resolution status.
- Operational ownership: process owner, data owner, integration owner, support owner, and access approver.
- Change control: schema, mapping, rule, credential, endpoint, version, testing, and rollback review.