Integration

Integration Is Easy Until the Data Lies: Designing for Messy Records and Exceptions

Most “connect system A to system B” work fails in production because the real problem is not the API call—it’s inconsistent identifiers, partial records, and one-off exceptions that operators need handled safely. This article outlines a practical approach to data normalization, exception routing, and operational controls that keep integrations stable as reality shifts.

Start by mapping failure modes, not endpoints

Before choosing tooling, list what can go wrong in business terms. Common integration failure modes include: missing or ambiguous identifiers (no shared customer ID), conflicting truth sources (CRM says closed-won, billing says trial), partial updates (address changed in one system only), invalid states (cancelled order still billed), and timing issues (event arrives before the referenced record exists). Turn these into explicit “exceptions” with definitions and owners. Example: “Customer match required” (no unique match), “Policy conflict” (system-of-record disagreement), “Validation failure” (payload violates downstream rules), “Retryable delivery” (timeouts, rate limits), and “Manual review required” (cannot decide safely). Designing around these categories prevents the common trap of writing a happy-path sync and later layering ad-hoc fixes.

Define a canonical model and translation rules (and document the edges)

Pick a canonical representation for the integration boundary—even if it’s only a subset of fields. This model should reflect operational truth, not vendor schemas. Then define translation rules for each system: - Field normalization: date/time zone handling, phone formatting, address decomposition, currency precision. - Enumerations: map statuses with a table, not conditionals scattered across code. - Identity: decide how entities are keyed (e.g., internal UUID + external IDs per system), and how merges/splits are represented. - Defaulting: explicitly decide when missing fields become null vs inferred vs blocked. Document “unknown” and “not applicable” separately. Many integrations treat both as null, which later causes accidental overwrites (e.g., blanking an email because one system omits it). If possible, implement patch semantics: only update fields that were present and validated, and keep a record of field-level provenance.

Build an exception path with operators in mind

Exceptions are not bugs; they are the product. Provide a consistent workflow: 1) Detect: validate inputs early (schema + business rules) and classify the exception type. 2) Contain: store the payload and context immutably (what you received, when, correlation ID, upstream headers). 3) Route: choose a handling channel based on severity and ownership (ticket queue, internal tool inbox, on-call page only for true incidents). 4) Resolve: enable operators to fix the root cause without engineers (e.g., map an external customer to an internal record, choose the correct address, approve a disputed status). 5) Replay: after resolution, re-run the exact event deterministically. An effective pattern is an “exceptions table” with: exception_code, entity_type, entity_keys, snapshot_payload, suggested_matches, status (new/in_progress/resolved/ignored), resolver, resolution_notes, and replay_result. Keep resolution actions auditable (who did what, when) and make replay idempotent so operators can safely retry.

Make the sync resilient: idempotency, ordering, and reconciliation

Resilience is largely about accepting that the same thing will happen twice, out of order, or not at all. - Idempotency: for writes, use an idempotency key per event or per logical change. Record downstream write results so retries don’t create duplicates. - Ordering: if ordering matters, model it explicitly (sequence numbers, version timestamps) and reject/regress stale updates. If it doesn’t, build commutative updates or last-write-wins with clear rules. - Dead letters: don’t drop failures. Persist them with enough context to replay. - Backfills: treat backfills as first-class operations with their own rate limits, checkpoints, and monitoring. - Reconciliation: schedule periodic comparisons (counts, checksums, key fields) to detect drift. Reconciliation is often cheaper than trying to prevent every edge case upfront. Example scenario: a subscription cancellation event arrives before the customer exists in the billing mirror. Rather than failing permanently, classify as “dependency missing,” queue it with a retry policy, and attempt again after customer creation—or allow an operator to link the external customer ID to an internal record and replay.