An integration keeps data consistent when an upstream system fails mid-sync by making every step safe to repeat and every failure visible. That means idempotency keys on writes, retries with backoff for temporary errors, a dead-letter queue with replay for the rest, a saved checkpoint, and scheduled reconciliation to catch whatever slipped through.
Most integration guides explain which pattern to choose. This one is about what happens on the day a system goes down halfway through a sync, which is the day that decides whether your finance team trusts the data. It is written for CTOs, engineering leads and IT directors who are building integrations or judging an integration platform. For the choice of approach, start with system integration approaches and the enterprise integration patterns decision guide; we do not repeat them here. If you want a team to build resilient integrations for you, see our systems and API integration service.
Key takeaways
- You cannot make two separate systems commit together, so design for "safe to repeat" instead of "never fails".
- A timeout is ambiguous: the other system may or may not have done the work. Idempotency keys remove the ambiguity.
- Retry only errors that can succeed later, with exponential backoff and random jitter, at one layer only.
- A dead-letter queue is useful only if each item keeps its payload, error and correlation ID, and can be replayed safely.
- Order matters per record, not globally. Use version numbers, not arrival time, to decide which update wins.
- Reconciliation is the only control that proves the data on both sides agrees; a flow can be healthy and still wrong.
Why does a sync fail halfway, and what does that do to your data?
A sync fails halfway because separate systems cannot commit one shared transaction. A batch of records may be partly written, a request may succeed while its response is lost, or a target may go down after accepting some changes. Without safeguards the result is missing records, duplicates or stale values.
Four situations come up again and again:
- The ambiguous timeout. You send "create invoice 5001" and the connection drops. Retrying blindly may create two.
- The partial batch. A job sends 1,000 records and the target stops after record 612.
- The duplicate delivery. Webhook senders and brokers commonly deliver at least once, so an event can arrive twice.
- The stale overwrite. A delayed retry arrives after a newer update and restores old data.
Each has a standard remedy, set out below.
Which iPaaS tools maintain data consistency when an upstream system fails mid-sync?
No integration platform can guarantee consistency by itself, because consistency depends on how each flow is designed. Judge any iPaaS on the features that make a flow safe to repeat: key-based deduplication, checkpoints, configurable retries, error queues with replay, per-record traces and alerting. Ask for a demonstration with a deliberately failed target.
Features differ by plan and change often, so use a test rather than a ranking. Run your most important flow while the target is switched off for ten minutes, then ask: Was any record created twice? Did any go missing? Can you see each failed record with its payload and error? Can you replay only the failed ones, safely?
What are the common failure modes and what handles each?
Eleven common failure modes each have a standard control: keys for duplicates, version checks for stale data, backoff for temporary errors, a dead-letter queue for bad data, checkpoints for partial runs and reconciliation for everything that slips through. Use the table as a design review checklist.
| Failure mode | What goes wrong | Standard handling |
|---|
| Timeout after the target did the work | Retry creates a duplicate | Idempotency key or upsert by external ID |
| Timeout before the target did the work | Record is missing | Retry with backoff; reconcile to confirm |
| Partial batch | Some records written, some not | Per-record results, checkpoint after each confirmed record or chunk |
| Duplicate delivery | Same event processed twice | Processed-event log or key-based deduplication |
| Out-of-order events | Older update overwrites newer one | Version number or source timestamp check; per-record ordering |
| Bad data (poison message) | One record fails forever and blocks the queue | Dead-letter queue after a set number of attempts |
| Rate limit (HTTP 429) | Target rejects calls | Honour Retry-After, slow down, queue |
| Target down for hours | Retries exhaust | Buffer in a durable queue; resume from checkpoint; reconcile |
| Expired credentials | Every call fails | Alert on auth errors; do not retry endlessly |
| Schema change at the source | Mapping breaks silently | Contract tests; alert on unknown or missing fields |
| Replay of old failures | Stale data overwrites current data | Replay through the same version check, not around it |
How do idempotency keys work in a sync flow?
An idempotency key is a stable identifier for one intended change, stored with its result so that repeating the request returns the original outcome instead of creating a second one. It must be derived from the business event, not generated fresh on every attempt.
The basic idea is covered in the system integration approaches guide linked above. Three points matter in practice:
- Derive the key from the event. Combine the source system, the record ID and the version or event ID, for example "shop-orders / 88231 / v3". A random value generated per attempt defeats the purpose.
- Store the key with the outcome. The receiver records the key and the response it gave. A repeat with the same key and the same payload returns the stored response. A repeat with the same key and a different payload should be rejected as a client error, because it signals a bug.
- Prefer natural idempotency where the target allows it. An upsert by external ID, or a PUT to a known resource address, is repeatable without a key store.
Idempotency stops duplicates but not stale overwrites, so add a version check. Many APIs support conditional updates, for example with an ETag and an If-Match header, so the write fails if the record changed since you read it.
Retry only errors that can succeed later: timeouts, connection resets, HTTP 429 and 5xx responses. Wait longer after each attempt (exponential backoff), add random jitter so many clients do not retry together, set a maximum number of attempts, and move the item to a dead-letter queue when they run out.
Do not retry validation errors (HTTP 400 or 422), missing records (404) or permission errors (403); they will fail again, and each repeat wastes quota. The exception is a 404 for a record that should appear shortly, such as a parent created seconds earlier. A 401 usually needs a token refresh, not a retry loop.
Three mistakes recur:
- Retries at every layer. If the client, the gateway and the job runner each retry three times, one failure becomes twenty-seven calls. Retry at one layer and make the others pass errors through.
- No timeout. A hung connection blocks a worker indefinitely. Set a timeout shorter than the interval between runs.
- No circuit breaker. When a target is clearly down, pause sending for a cooling-off period and queue the work.
What is a dead-letter queue, and how do you replay from it?
A dead-letter queue (DLQ) holds messages that could not be processed after the allowed attempts. Each entry should keep the original payload, the error, the attempt count, timestamps and a correlation ID. After the cause is fixed, entries are replayed through the normal path.
The difference between a useful DLQ and a graveyard is what you can do with it:
- Classify. Group entries by error class. A thousand entries with "unknown product code" are one data problem, not a thousand.
- Fix the cause. Correct the master data, deploy the code fix or refresh the credentials.
- Replay safely. Replay goes through the same idempotency and version checks as live traffic. A replay that bypasses them can overwrite data that changed after the original failure.
- Record the outcome. Keep who replayed what and when, since replays change financial data.
- Set an owner and an age limit. Alert when the oldest entry passes an agreed age. Many DLQ items are business data problems that IT cannot fix alone.
How do you keep records in the right order?
Global ordering is expensive and rarely needed. What you need is ordering per record: all changes to one customer or order are applied in sequence. Partition work by record key, process one change at a time per key, and decide the winner by version number, not by arrival time.
In Apache Kafka, for example, order is preserved only within a partition, so changes to the same record must share a partition key. Whatever the technology:
- Carry a monotonically increasing version or a source-side timestamp on each change.
- Discard or park any change older than the version already applied.
- Send deletes as explicit events (tombstones), because a sync that only looks for recently changed rows will never see a hard-deleted one.
- Load parents before children, such as customers before their orders, or park the child until the parent exists.
How do checkpoints and the outbox pattern prevent lost updates?
A checkpoint records how far a sync has safely progressed, such as the last confirmed record ID or change timestamp. Advance it only after the target confirms the write. On the source side, the transactional outbox pattern writes the business change and the event in one database transaction so neither can be lost.
If a job reads "everything changed since 10:00" and the checkpoint moves before the writes are confirmed, a crash loses records. Move it after confirmation, and re-read a small overlapping window, relying on idempotency to absorb the duplicates. For event publishing from your own application, the outbox pattern avoids the classic gap where the database commit succeeds and the message publish fails.
What should you reconcile, and how often?
Reconciliation compares what the source holds with what the target holds, and reports the differences. Run it on a schedule, daily for important flows, using counts, totals and checksums by period, then drill into mismatches. It catches what retries and queues cannot, such as silent mapping errors.
| Flow | Compare | Typical grain |
|---|
| Orders to ERP | Count and value of orders per day | Day, channel |
| Payments to ledger | Captured amount against posted amount | Day, payment method |
| Customers to CRM | Active customer count; sample field checks | Daily, plus random sample |
| Stock to storefront | On-hand quantity per item | Item, location |
| Deletions | Records present in target but gone from source | Weekly or daily |
Make the repair job idempotent too, so fixing a gap never creates a new one.
What should a real-time error-monitoring dashboard for failed syncs show?
A useful failed-sync dashboard shows, per flow and in near real time: success rate, failures grouped by error class, retry volume, dead-letter queue depth and oldest-item age, end-to-end lag, and a trace for any single record with a replay action. Alerts should reach an owner, not a shared mailbox.
When you evaluate an iPaaS or design your own, check this list:
- Failed records are visible with payload and error, with sensitive fields masked by role.
- Failures can be filtered by flow, tenant, error class and time.
- Alerts fire on zero throughput during business hours, as well as on errors.
- A correlation ID follows a record across every hop.
- Replay is available per record and in bulk, and is logged.
- History is kept long enough to investigate month-end issues.
Worked scenario: the ERP goes down halfway through an order sync
This scenario is illustrative; the numbers are invented to show the mechanics.
An online store sends orders to an ERP in a 15-minute job. At 10:00 there are 1,200 new orders. Each is sent with the key "store / order number / version".
- The ERP accepts 779 orders, then returns server errors. The checkpoint sits after the last confirmed order, number 779.
- The request for order 780 times out, so its outcome is unknown. The job retries with backoff and jitter. After five attempts it stops and the circuit breaker opens. The 421 unsent orders stay in the durable queue.
- When order 780 is retried later, the ERP either has it and recognises the key, returning the existing order, or does not have it and creates it once. Either way there is no duplicate.
- The ERP recovers at 11:10. The breaker half-opens, sends a few test calls, then resumes from the checkpoint at a controlled rate under the ERP's limits.
- Eleven orders fail with an unknown product code. They go to the dead-letter queue after their attempts. The product team adds the codes and an operator replays the eleven through the normal path.
- The nightly reconciliation compares 1,200 orders and their total value on both sides. They match, so the day closes with evidence.
How long does it take to onboard a reverse ETL integration, and which controls apply?
There is no standard onboarding timeline for a reverse ETL integration, because the time is driven by readiness, not by the tool. Reverse ETL copies modelled data from a warehouse back into operational systems such as a CRM. The steps are fixed; the schedule is set from your requirements.
The work usually runs in this order: confirm the modelled tables are trusted and have a stable unique key; agree the matching key in the target (email, account ID or external ID); map fields and choose a sync mode (insert only, upsert, or mirror with deletes); pass the security review; test in a sandbox within the target's API limits; backfill history in batches; add monitoring and reconciliation. The slowest steps are usually data trust and approvals, not configuration. Reverse ETL fails like any sync, so the controls above apply.
When is this the wrong approach?
Do not build queues, checkpoints and replay tooling for a few dozen records a day whose errors a person would notice at once; a scheduled, idempotent re-sync is simpler. If two systems must change together or not at all, such as moving money, use the transaction support of the system that owns the funds instead of stitching it together with retries. Match the machinery to the value of the data.
Glossary: idempotent, repeating an operation has the same effect as doing it once; dead-letter queue, a store for messages that could not be processed; backoff, the increasing wait between retries; checkpoint (or watermark), a marker of how far a sync has progressed.
How Timeline Digital approaches resilient integrations
For integrations that must survive failures, Timeline Digital uses the Plan step to agree which resilience controls belong in the scope, such as keyed writes, retries, error queues, replay and reconciliation reports. Timeline Digital was founded in 2013, is based in Islamabad and delivers remotely. A free pilot of 2 to 3 key modules lets you test the result before the final agreement, schedules are set from your requirements in the Plan step, and the code is handed over on full payment. See our API development services and enterprise system integrations pages, or ERP integration services if an ERP is involved.