Microservice Data Reconciliation: Detecting and Repairing Drift

What microservice data reconciliation is and where data drift comes from

Microservice data reconciliation is a permanent subsystem that periodically compares data owned by one service with the copies other services derive from it, then repairs any disagreement. It runs alongside your event pipeline and catches the failures the pipeline cannot report about itself.

Eventual consistency promises convergence only if every change is delivered and applied correctly. Once a single event is dropped or misapplied, nothing will ever correct that copy, so it stays wrong while every service reports healthy. Backend and platform teams running event-driven services with derived data should own reconciliation, because they own the pipelines that cause drift.

Each source of drift leaves a different signature. Knowing the signature tells you which detection method will find it.

Random, isolated mismatches usually point to delivery problems. Clustered or systematic mismatches point to logic bugs.

Defining ownership and what 'consistent' means before comparing anything

Before any comparison runs, each reconciled entity needs an ownership map: for every attribute, which service is authoritative, which services hold derived copies, and what rule makes two values "consistent." Without this, a mismatch is only a symptom. You cannot repair it because you don't know which side to change. If orders and billing disagree on a total, overwriting billing from orders is correct only if orders owns that field. If billing applies tax adjustments that orders never sees, the same overwrite destroys valid data.

Each field gets one of three rule types:

• Exact equality. IDs, status enums, and currency codes are compared byte for byte.
• Equality after normalization. Values match once both sides are canonicalized: timestamps truncated to the second and converted to UTC, emails lowercased, decimals rounded to the currency's minor unit. The normalization belongs in the rule itself. If it lives in ad hoc comparison code, it drifts between jobs.
• Relationship. Some invariants span fields or services, such as sum(line_items.amount) == order_total. Relationship rules catch errors that field-level equality misses.

The worked example in section 5 uses this map for the order entity:

Keep this map in version control next to the services' schemas and review it like an API contract. When ownership changes, the repair direction changes with it.

Copy# reconciliation/order.yaml -- ownership map for the `order` entity.
# Reviewed like an API contract; repair always writes owner -> derived copies.
entity: order
key: order_id
fields:
  order_id:
    owner: orders
    copies: [billing.order_id]
    rule: exact
  status:
    owner: orders
    copies: [billing.status]
    rule: exact
    # Billing uses its own enum; map orders values before comparing.
    mapping: {placed: open, shipped: open, cancelled: void, completed: closed}
  order_total:
    owner: orders
    copies: [billing.invoice_amount]
    rule: normalized
    # Compare integer minor units (e.g. cents) so 19.9 and 19.90 match.
    normalize: {to_minor_units: {currency_field: currency_code}}
  line_items:
    owner: orders
    copies: []
    rule: relationship
    # Invariant inside the owner; a violation is a bug in orders, not drift.
    assert: sum(line_items.amount) == order_total
  paid_at:
    owner: billing          # ownership flips: billing is authoritative here
    copies: [orders.paid_at]
    rule: normalized
    normalize: {timezone: UTC, truncate: second}
# Fields not listed here are ignored by the comparer and never repaired.

A declarative ownership and rules config for the order entity that records each field's owner, its derived copies, and its exact, normalized, or relationship rule, so the comparer and the repair job read the same definition.

How detection works: aggregates, checksums, row diffs, and cross-service invariants

Detection runs in tiers, cheapest first. Each tier narrows the key space the next tier has to examine, so row-level comparison only runs on partitions that already failed a cheaper check.

Aggregates alone are not enough. Two compensating mistakes can cancel out and leave the sums equal. Always run the hash tier as well, at least on a rotating sample of partitions. Row diffs should page through rows with keyset pagination in bounded batches, not OFFSET, so each query costs the same however deep into the range it reads.

In-flight data causes false positives. Measure propagation lag between the two services and set a settle window around its high percentiles. Only compare rows whose version or updated_at is older than a watermark. Do not compare against wall-clock now(), because clock skew between hosts makes it unreliable. Re-check a mismatch after a delay before declaring it drift. A longer settle window gives fewer false alarms but finds real drift later.

Read from replicas, CDC streams, or warehouse snapshots to keep load off the primary database, and rate-limit the scans. These sources are stale by their own replication or load lag, so add that lag to the settle window.

Copy-- Tiers 1+2: per-bucket count, sum and hash. Run the same query on both
-- services' read replicas and compare the rows by bucket.
-- The watermark is (source watermark - settle window), not now().
SELECT
  id / 100000                       AS bucket,        -- fixed key ranges
  count(*)                          AS row_count,
  sum(amount_cents)                 AS amount_total,  -- misses offsetting errors
  md5(string_agg(
        concat_ws('|', id, amount_cents,
                  coalesce(status, '<null>'), currency),  -- normalized columns
        ',' ORDER BY id))           AS bucket_hash    -- catches what sums miss
FROM orders
WHERE updated_at < TIMESTAMPTZ '2026-10-27 11:45:00+00'  -- watermark
GROUP BY id / 100000
ORDER BY bucket;

-- Tier 3: row diff on one mismatched bucket (e.g. bucket 42 = ids 4200000..4299999).
-- Keyset pagination: each batch starts after the last id seen, so batch cost
-- stays flat however deep into the range it reads. Bounded by LIMIT.
SELECT id, amount_cents, status, currency, version
FROM orders
WHERE id >= 4200000 AND id < 4300000                     -- suspect range only
  AND id > 4251377                                       -- last id from previous batch
  AND updated_at < TIMESTAMPTZ '2026-10-27 11:45:00+00'  -- same watermark as tier 2
ORDER BY id
LIMIT 1000;                                              -- bounded batch
-- Repeat with the last returned id until fewer than 1000 rows come back.
-- Sleep between batches to rate-limit load on the replica.

PostgreSQL queries that compute per-bucket counts, sums and hashes below a watermark, then page one mismatched bucket by keyset in bounded batches for a row-level diff.

Implementing repair: re-emit, re-sync, or patch, with classification and guards

Once a mismatch is confirmed, prefer the repair path that reuses the most of your normal code. Every bypassed layer is validation and audit you have to rebuild by hand.

Reserve direct patches for cases the API cannot express. Require a ticket, a second approver, and stored before/after values.

Guards

Every repair must be idempotent, keyed by discrepancy ID, so a retried job does nothing new. It must also be a compare-and-set on the version observed at detection: update only where version = expected, or publish with that version so consumers discard stale events. If the version moved, re-check instead of writing. A live write may have already fixed the record or changed it legitimately.

Classification

Auto-repair only categories you have seen and understood, such as a missing status projection. Route money, entitlements, and compliance-relevant records to a human queue. Treat a new mismatch type as a bug in a source system: file it rather than patch it.

Risks

Auto-repair applies a source-of-truth assumption at scale. If that assumption is wrong, the job corrupts data faster than any bug would. Cap repairs per run and halt when the rate spikes. Re-emitted events cause duplicate charges or notifications unless consumers deduplicate. When both sides changed legitimately, such as a refund issued in billing while the order was edited, no rule can pick a winner. That is a business decision for the owners.

Copy-- Repair one confirmed discrepancy in PostgreSQL. The client runs this in a
-- transaction and rolls back if the UPDATE returns no row.
BEGIN;

WITH claim AS (
  -- Idempotency: discrepancy_id is the primary key of repair_log.
  -- On a retry the INSERT does nothing, the CTE is empty, and nothing is written.
  INSERT INTO repair_log
    (discrepancy_id, entity_id, expected_version,
     before_status, after_status, reason, applied_at)
  VALUES
    ('disc-7f3a2c', 'ord-1042', 17,
     'PENDING', 'SHIPPED', 'missing_status_projection', now())
  ON CONFLICT (discrepancy_id) DO NOTHING
  RETURNING entity_id, expected_version, after_status
)
UPDATE order_status_projection AS p
SET    status     = c.after_status,
       version    = p.version + 1,
       updated_at = now()
FROM   claim AS c
WHERE  p.order_id = c.entity_id
  -- Compare-and-set: write only if the row still has the version seen at
  -- detection. If a live write moved it, zero rows match.
  AND  p.version = c.expected_version
RETURNING p.order_id, p.version;

-- Client logic:
--   one row returned  -> COMMIT
--   zero rows         -> ROLLBACK, which also removes the repair_log claim,
--                        then re-run the comparison against current state
--                        instead of forcing the write.
COMMIT;

A PostgreSQL repair that uses a repair_log keyed by discrepancy ID for idempotency and a version compare-and-set so it cannot overwrite a newer value, rolling back and re-checking when the version has moved.

Worked example: reconciling an orders service with a billing service

All figures here are illustrative estimates built on assumed inputs. None of them are measurements.

Using the ownership map from earlier, the orders service is authoritative for order ID, status and total. Billing must hold exactly one invoice per confirmed order, and the invoice amount must match the order total. Assume billing's consumer lag is 4 minutes at p99 and 12 minutes at p99.9. The settle window is set to 30 minutes after an hour closes. That is roughly double the p99.9 lag, so slow but healthy events have arrived before anything is compared.

Detection. The hourly job compares count and sum of confirmed orders against invoices for the 14:00 bucket:

The gap is one record worth 129.00.

Drill-down. The job splits the bucket by hash(order_id) mod 64 and compares count and sum per range. Only one range differs. A row diff of its roughly 130 keys shows that order A-77310 has no invoice.

Re-check and classify. Fifteen minutes later the job queries again, which rules out an event that was still in flight. The invoice is still missing. The job classifies it as "missing downstream, upstream authoritative," a class that is approved for automatic repair.

Repair. The job re-publishes OrderConfirmed for A-77310 with order version 3. Billing applies the event only if version 3 is newer than the version it has stored. It has none, so it applies. The idempotency key on the event prevents a duplicate invoice if billing receives it twice.

Verify and log. The job re-runs the bucket check and the totals now match. It then writes a discrepancy log entry containing:

• bucket, key, class, action and version
• detection and repair timestamps
• root cause, marked as a suspected offset commit before the write, still to be confirmed

Copy-- Illustrative PostgreSQL. Run the same range query on each service's own database.
-- The job compares the two result sets. Table and column names are assumed.

-- 1) Drill-down: count/sum per hash range for the 14:00 bucket (orders side).
--    md5 is used because it gives identical ranges on both databases.
SELECT get_byte(decode(md5(order_id), 'hex'), 0) % 64 AS key_range,
       count(*)                                       AS n,
       sum(total)                                     AS amount
FROM   orders
WHERE  status = 'CONFIRMED'
  AND  confirmed_at >= '2026-10-27 14:00:00+00'
  AND  confirmed_at <  '2026-10-27 15:00:00+00'
GROUP  BY key_range
ORDER  BY key_range;
-- Billing runs the same query over invoices, filtered on the order's confirmed_at.

-- 2) Repair, applied by billing's consumer for the re-published OrderConfirmed.
BEGIN;
-- Idempotency: a redelivered event_id inserts nothing, so the consumer skips the upsert.
INSERT INTO processed_events (event_id)
VALUES ('evt-A-77310-v3')
ON CONFLICT (event_id) DO NOTHING;

-- Version guard: insert if absent; update only when the incoming version is newer.
INSERT INTO invoices (order_id, amount, order_version, confirmed_at)
VALUES ('A-77310', 129.00, 3, '2026-10-27 14:37:12+00')
ON CONFLICT (order_id) DO UPDATE
   SET amount        = EXCLUDED.amount,
       order_version = EXCLUDED.order_version
 WHERE invoices.order_version < EXCLUDED.order_version;
COMMIT;
-- If the first INSERT reported 0 rows, the consumer rolls back instead of upserting.

Illustrative PostgreSQL showing the per-hash-range count/sum query used to narrow the 14:00 mismatch, and the idempotent, version-guarded upsert billing applies when the orders service re-publishes OrderConfirmed for A-77310.

Operating reconciliation as a product, and how it affects cost and engineering time

That log is what makes the job auditable, but a log alone does not tell you whether drift is getting better or worse. Emit five metrics per run and per bucket: records scanned, mismatches by category, repairs applied, repair failures, and age of the oldest unresolved mismatch. Records scanned catches silent failures, because a job that compares zero rows also reports zero mismatches. Oldest-unresolved age catches stuck review queues.

Do not page on a single mismatch. Some background drift is normal under eventual consistency. Alert when a category's rate rises across consecutive runs, when a new category appears, or when the oldest-mismatch age crosses your repair SLA. A new category usually means a new defect. A rising rate in a known category usually means a regression.

Treat the reconciler as an owned service with an on-call rotation and tests. It reads several schemas, so a renamed column or a new enum value in either service can produce false mismatches or hide real ones. Add contract tests against both schemas to each producer's CI so that schema changes break the build instead of the nightly run.

Illustrative estimate (assumed numbers, not a benchmark). Assume a 3-week build, 2 engineer-hours per week for review, and replica reads that are already provisioned. Then:

annual cost = build hours + (52 × review hours)

That gives 120 + 104 = 224 hours. Next, assume 6 drift incidents per year. Each one costs 30 hours of investigation when support finds it and 6 hours when the log points straight at the defective pipeline stage:

annual savings = incidents × (hours without reconciler − hours with it)

That gives 6 × 24 = 144 hours. So the first year runs at a net cost of 80 hours. From the second year, with no build cost, it saves about 40 hours. That figure leaves out customer impact.

When to use reconciliation, when to avoid it, and the alternatives

That payoff only holds when the data being reconciled is worth protecting, so start by asking whether a copy needs to exist at all.

Reconciliation is warranted when one service holds data derived from another service's records, especially money, entitlements, or anything a customer can dispute. It is also warranted for pipelines that see replays, backfills, and manual database fixes, because those bypass the normal event flow. Avoid it when the value can be recomputed on read, when a single service owns every copy and can enforce consistency in one transaction, or when the data is a low-value cache with a TTL that already bounds staleness.

Reconciliation detects drift after it happens. These source-level fixes stop it from happening:

Use the reconciler's output to choose among these fixes. Group mismatches by category each week and fix the category with the most volume or the highest cost first. If the reconciler sits on the critical path, for example by gating checkout or holding requests until a sync job confirms a match, it has become a crutch. It should be a safety net that shows you which source problem to fix next.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to microservice data reconciliation, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.