Data Correctness Bugs, Drift, and Reconciliation

Wrong Data Is a Production Incident

Some of the most expensive data-system incidents do not start with downtime. The application is up. The database is reachable. Consumers are processing. Dashboards refresh. The failure is that the facts are wrong: revenue does not match billing, search results include deleted records, subscription entitlements drift from payment state, inventory counts go negative, or a backfill silently skips a slice of users.

Wrong-data incidents require a different reflex than availability incidents. Restarting workers may propagate the defect faster. Retrying messages may duplicate bad side effects. Running a manual patch without preserving evidence may destroy the only path to root cause. A dashboard turning green can be meaningless if the repaired output was never reconciled against a trustworthy source.

Correctness work is the discipline of preserving trust in facts. The operating model is to define the facts that must stay true, detect when they drift, trace how the bad fact moved, contain further spread, repair with an audit trail, and verify the result independently. Availability asks, “can the system serve?” Correctness asks, “can anyone still rely on what it served?”

The Shape of a Correctness Incident

A correctness incident has four parts: the fact, the promise, the path, and the proof.

The fact is the thing the business believes it knows: an order is paid, a subscription is active, a user deleted a document, an account balance is $42.17, a warehouse row represents yesterday’s settled revenue. The fact may live in one source table, but users usually encounter it through caches, search indexes, exports, reports, feature tables, notifications, or downstream systems.

The promise is the invariant or freshness expectation attached to that fact. “A deleted document must not appear in search.” “A paid invoice must grant exactly one entitlement.” “Warehouse revenue for closed days must match the ledger within known exclusions.” “The most recent inventory reservation must not drive available quantity below zero.” If the promise is vague, the incident will be vague.

The path is how the fact moved. A source write may emit an event, pass through a stream processor, update a derived store, feed an export, and appear in a dashboard. Each hop can change ordering, units, null semantics, deduplication, filtering, or freshness. The path is also where evidence lives: commit timestamps, transaction ids, event ids, offsets, batch run ids, transform versions, reconciliation rows, and audit logs.

The proof is the independent evidence that repair restored trust. A repair is not complete because a job finished or an engineer says the rows look better. It is complete when invariants, control totals, source-to-derived comparisons, samples, and user-facing checks agree with the intended truth.

Wrong-Data Failure Modes

Wrong data is easier to debug when the symptom is named precisely. “The data is off” is too broad to route. Missing rows leave an undercount, an absent search result, or an incomplete export; compare source and sink across explicit key ranges, tenants, or partitions. Duplicates inflate a metric or repeat a charge, notification, or fulfillment; follow the natural key through retry attempts, acknowledgements, and replay runs. Stale data trails the source beyond its promised bound; compare source commit time with derived update time and locate the lagging partition, refresh, or cache entry.

Ordering defects allow an older status to overwrite a newer one or make a deleted object reappear. Versions, sequence numbers, event time, processing time, and the conflict rule reveal which meaning of “latest” the system actually used. Partial backfills leave one tenant, day, shard, or key range behind; the input manifest, checkpoints, failed-shard list, and reconciliation by partition establish the hole. Transformation and schema defects are semantic: the data arrives, but units, signs, timezones, joins, nulls, enums, or business meaning change underneath a consumer. Those require paired source and output records beside the transform version and contract history, not another pipeline-health graph.

The same technical defect can land in different severity bands depending on the fact. Duplicate debug logs may be noise. Duplicate payments, shipments, medical reminders, access grants, or tax forms are trust incidents. The failure-mode label starts the investigation; the business fact sets the urgency.

Two distinctions prevent wasted work.

First, source wrong and derived wrong are different incidents. If the source of truth is intact, the repair may be replay, recompute, or reindex. If the source is wrong, the repair needs domain authority, compensating actions, and stronger audit handling. Do not let a derived system overwrite a better source record simply because the derived system is easier to patch.

Second, wrong output and wrong decision are different blast radii. A report may be wrong but not yet used. A pricing table, entitlement path, fraud feature, search index, or customer export may already have driven decisions. Triage must ask not only where the bad data sits, but who or what acted on it.

Detection Before Complaints

Customer reports are valid evidence, but they are poor first detectors. By the time a customer sees wrong data, the blast radius may include downstream exports, dashboards, automated decisions, support actions, and copied records in systems the original team does not own. Production correctness needs detectors that run before trust is visibly broken.

Invariant checks catch states that should never occur. They should run close to the source of truth when possible and emit actionable samples, not only counts. “Eight subscriptions have two active plans” is useful only if the alert includes affected keys, first seen time, owning service, severity, and the query or rule that failed.

Control totals compare independent views of the same facts. Billing revenue can be compared against ledger entries, warehouse aggregates against operational tables, source event counts against sink rows, and deletion requests against derived-store absence. A control total does not need to explain the bug. It needs to prove drift exists and narrow where it begins.

Reconciliation jobs make drift operationally visible. A useful reconciliation job does not only print a mismatch count. It writes mismatch records, preserves source and derived values, assigns ownership, tracks repair status, and keeps enough history to distinguish a new defect from an old unresolved mismatch.

Sampling catches semantic bugs that aggregate checks miss. A row-level audit that samples transformed records against source records may find mapping errors, dropped fields, timezone mistakes, join explosions, and unit conversions hidden by totals. Sampling is especially useful for wide records, user-visible fields, and transformations that are hard to express as a single invariant.

Lineage makes facts traceable. A support engineer or on-call should be able to follow a user, order, payment, or document from source commit through event, transform, derived store, and output. Without lineage, triage becomes log archaeology, and repair becomes guesswork.

Audit logs provide non-repudiable history for high-consequence facts. They should answer who changed a fact, what changed, when it changed, through which path, under which request or job, and what prior value existed. Audit logs are not a replacement for backups or reconciliation, but they often decide whether a repair is defensible.

Drift monitors watch promises, not just pipelines. “Consumer lag is below threshold” is not the same as “search reflects deletes within five minutes” or “closed-day revenue matches the ledger.” A healthy pipeline metric can coexist with wrong semantics.

Triage Workflow

The first minutes of a wrong-data incident are easy to waste. People want to fix the rows they can see. That can be correct for a contained display bug. It is dangerous when the same path is still producing new bad facts or when evidence is fragile.

Freeze propagation before repair when continuing the path spreads harm. That may mean pausing a consumer, disabling a transform, stopping a backfill, turning off an export, blocking a deployment, disabling a bad feature flag, or routing writes away from a defective path. The freeze should be narrow enough to avoid unnecessary outage but strong enough to stop new contamination.

Scope the incident by time, entity, tenant, product surface, and data path. Ask when the first bad fact appeared, which source facts are affected, which derived stores consumed them, which users made decisions from them, and whether the source of truth is still reliable.

Preserve evidence before destructive repair. Keep logs, input files, source snapshots, offsets, batch run ids, deployment identifiers, query text, reconciliation rows, and sample bad records. If privacy rules require careful handling, preserve the minimum evidence needed under the right access controls and record where it lives.

Build the smallest useful affected set. A good affected-set query has explicit bounds: time window, key range, tenant, source predicate, derived predicate, and excluded known-safe cases. Save the query and sample output. Later communication, repair, and verification will depend on it.

Classify the failing path. Is the defect in source write logic, event production, delivery, transformation, derived-store write, cache invalidation, export, reporting query, or user interpretation? Each path has a different owner and repair mode.

Measure user and business impact separately. User impact names the workflows, surfaces, and decisions affected. Business impact names money, compliance, contractual promises, safety, support load, and audit exposure. A technically small row count can still be a severe incident if the rows are high consequence.

Stop new bad writes before fixing old bad data. Otherwise the repair job chases a moving defect and verification results keep changing underneath it.

Follow One Wrong Number

At 07:10 UTC, the closed-day revenue control reports that yesterday’s billing total is 4.7 percent higher than the warehouse dashboard. Both systems are available. The warehouse job finished on time, and its row count looks ordinary. Finance is due to export the number in fifty minutes.

The failed promise is narrower than “the warehouse is wrong”: for a closed billing day, settled charges in the ledger and recognized revenue in the warehouse must agree after documented exclusions for refunds, taxes, and currency conversion. That wording immediately changes the investigation. The on-call pauses the finance export, but does not stop billing. Billing is still recording the better source fact; the dangerous path is publication of an unverified derivative.

The first comparison groups the difference by merchant, currency, and settlement hour. Nearly all of it belongs to EUR charges settled after 22:00 UTC. The affected-set query and a sample of mismatched charge ids are saved before anyone reruns the warehouse job. For each sample, the team records the ledger commit, emitted event id, broker offset, transform run, warehouse row, and dashboard aggregate. This is lineage doing useful work: it turns one bad total into several traceable facts.

The first bad event crossed the pipeline after a transform deployment at 22:14. The new code accepts a producer field named settled_amount_minor, but an older EUR producer still sends minor units under the legacy field settled_amount. The compatibility branch mistakes that value for euros and multiplies by 100 again, then rejects the implausibly large result. The job is green because rejection was an allowed record outcome. The row count is unremarkable because this merchant slice is small. A paired source-output sample exposes what the aggregate health checks concealed.

Now the team can state the blast radius: the ledger is intact; warehouse revenue is understated for a bounded set of EUR settlements; the internal dashboard and an unpublished finance export consumed the bad aggregate; no customer balance or charge was changed. That is enough evidence to choose a repair without pretending the investigation is over.

Repair Choices

Repair is not one action. It is a choice among recompute, replay, patch, compensate, and manual review. The right choice depends on source trust, determinism, side effects, affected-set size, audit requirements, and how much uncertainty remains.

Recompute is clean when the source is trustworthy and the corrected transformation is deterministic. It becomes risky when rebuilding is expensive or readers can observe a half-replaced store. Build into a separate target, reconcile it, then swap or progressively replace when the system permits.

Replay is powerful because it exercises the production path. It is also unforgiving. Replay requires known starting offsets, corrected code or configuration, idempotency keys, duplicate protection, and a way to prove that replayed side effects did not create new harm.

Patch only with discipline. A production data patch should name the source evidence, approver, exact rows changed, query text or script hash, dry-run result, verification query, rollback or compensating plan, and communication owner. A patch that cannot be explained later is an incident artifact, not a fix.

Compensation matters when the bad fact left the system. If a customer was overcharged, a report was sent, an email was duplicated, or an entitlement was wrongly withheld, the repair must address the external consequence. Database correction and trust correction are not the same thing.

Manual review belongs where business judgment is required. The queue should be traceable, sampled for consistency, bounded by an owner and deadline, and reflected in incident status. Otherwise it becomes a private backlog of unresolved correctness risk.

Every repair needs a verification plan before it runs. At minimum, verification should include the original failing detector, an independent control total, targeted samples, and a user-facing check on the affected surface.

For the revenue incident, the team fixes and canaries the compatibility rule, then replays only the saved affected set into a shadow warehouse partition. A direct patch would be shorter, but it would bypass the transformation whose corrected behavior needs proof. A full-day replay would be broader than the evidence requires and would expose unrelated records to replay risk.

Before swapping the repaired partition, the original control total must close for every merchant, currency, and settlement hour after the same documented exclusions. Record counts and sums must match independently; sampled charge ids must carry the expected amount and transform version; the dashboard must show the reconciled total; the finance export must be regenerated, not merely unpaused. The team also checks that the replay emitted no customer-facing side effect. A green replay job is one fact in that proof, not the proof itself.

Prevention Patterns

Prevention is not the promise that the same bug will never happen again. It is the reduction of silent drift, unbounded blast radius, and unverifiable repair.

Contracts reduce semantic drift. Producers should publish field meaning, compatibility rules, allowed nulls, units, versioning, deletion semantics, ordering guarantees, and consumer expectations. Consumers should validate assumptions instead of silently accepting incompatible changes.

Idempotency protects repair and replay. Every side-effecting consumer should be able to receive the same logical fact again without duplicating external harm. Idempotency keys, version checks, natural keys, and deduplication windows make this possible.

Reconciliation turns correctness into routine work. The strongest reconciliation systems compare source and derived facts, create inspectable mismatch records, assign owners, track repair status, expose age and severity, and retain audit history. A reconciliation job without ownership and repair workflow is only a warning light.

Safer deploys limit blast radius. Shadow transforms, sample audits, canary tenants, dual-run comparisons, and rollback paths catch errors before a bad mapping touches every record.

Backfills need operating discipline. Define input scope, output target, idempotency, resume behavior, progress markers, dry-run samples, reconciliation query, rollback or correction plan, and user impact before running. A long-running backfill is a production write path, not a maintenance footnote.

Derived stores need explicit freshness and deletion promises. Search, caches, materialized views, warehouses, feature stores, and exports should state what they promise about propagation delay, delete handling, late events, and source corrections. Without those promises, nobody knows when drift becomes an incident.

Ownership must cross the full fact path. The source team, pipeline team, derived-store owner, analytics owner, and product owner may all own part of the truth. Correctness prevention fails when each team can say its component behaved as designed while the user-facing fact is still wrong.

Communication and Trust

Correctness incidents damage trust because users do not know which facts to rely on. Communication should say which facts are affected, which decisions may have been influenced, whether source data is trustworthy, what repair path is running, and when verification is complete.

Avoid vague messages such as “some data may be incorrect.” Name the product surface, time window, entity type, known exclusions, and confidence level. If financial, compliance, contractual, or customer-impacting facts are involved, preserve the audit trail and involve accountable owners early.

Internal updates should distinguish containment, scope, repair, verification, and prevention. A repair that finishes without verification is not complete. A verified repair without prevention leaves the same trust gap open.

External communication should not overclaim. If the source of truth is verified but one derived view remains under repair, say that. If the affected set is known but user decisions are still being assessed, say that. If the team is using samples while a full reconciliation runs, say what the sample proves and what it does not.

The final incident note should include the failed promise, affected set, detection gap, repair evidence, verification evidence, and prevention owner. That is the difference between “we fixed the rows” and “we restored confidence in the fact.”

In the revenue incident, an early internal update can be exact without claiming certainty it does not have: yesterday’s warehouse revenue is understated for a bounded EUR settlement window; the billing ledger remains authoritative; finance export is paused; customer charges are unaffected; reconciliation is running against the ledger. After repair, the message can name the closed control total and the regenerated export. Readers of the update should know which number to trust now, not merely that engineers are active.

Wrong-Data Incident Runbook

The runbook is a sequence of claims, each backed by evidence:

1. Declare the failed promise. Name the fact, invariant, affected surface, consequence, and accountable owner.
2. Freeze the spreading path. Pause the consumer, transform, export, deploy, or narrow write path that can create more harm. Record what remains live.
3. Preserve the trail. Retain the necessary logs, offsets, snapshots, deployment ids, query text, inputs, and bad examples under appropriate access controls.
4. Bound the affected set. Save the time window, entity and tenant predicates, stores reached, known exclusions, sample records, and decisions already influenced.
5. Trace the defect. Follow facts from source through delivery, transformation, derived stores, and user output. Tie the hypothesis to evidence and name an observation that would disprove it.
6. Choose the repair before executing it. State why recompute, replay, patch, compensation, or manual review fits the source trust and side-effect risk. Include the dry run, approval, rollback or compensation path, and communication owner.
7. Repair under change control. Preserve the job id, script hash or query text, input manifest, changed and skipped records, and audit trail.
8. Verify independently. Re-run the failed invariant, a separate control total, targeted samples, and the user-facing path. Document exceptions instead of rounding them away.
9. Communicate what can be trusted. Name impact, repair, remaining uncertainty, and the evidence behind restored confidence.
10. Close the silent gap. Give the missing detector, contract, deploy guard, idempotency control, or ownership change an owner and due date.

Practical Exercises

Use these exercises to practice the workflow before an incident makes it urgent.

1. Investigate a dashboard revenue number that differs from billing revenue. Name the source of truth, every derived path, the control totals to compare, the first affected-set query, the freeze action, and the verification query that proves repair. Reject any answer that jumps directly to “rerun the ETL” without preserving evidence.
2. Repair a search index that missed deletes for six hours. Decide whether the source records are trustworthy, how to find all affected documents, whether to replay deletes or rebuild the index slice, and how to prove deleted objects no longer appear through the user-facing search path.
3. Review a planned backfill. Write its input scope, output target, idempotency key, resume marker, dry-run sample, reconciliation query, rollback or compensation plan, and communication owner. The backfill is not ready if the only success condition is “job completed.”

The mature posture is not that production data never drifts. It is that a team can name the broken promise, preserve the trail, bound every place the fact traveled, stop further harm, choose a repair proportionate to the evidence, and prove the repaired fact through an independent path. The revenue pipeline stayed online throughout the incident. Trust returned only when the ledger, repaired warehouse partition, dashboard, and regenerated export agreed—and the next incompatible producer record could no longer pass silently.