Data Consistency and Idempotency Questions

Understand how to maintain correct data in distributed and asynchronous systems and how to design idempotent operations so retries do not produce duplicate effects. Cover the relationship between consistency models and idempotency, transactional guarantees across components, patterns for idempotent request handling, unique request identifiers, deduplication, compensating transactions, and when to use eventual reconciliation or strong transactional boundaries. Discuss how idempotency affects API design, retry strategies, and user visible correctness.

EasyTechnical

59 practiced

Explain how cache invalidation strategies (write-through, write-around, TTL-based) can cause stale reads in downstream services and how designing idempotent operations can mitigate user-visible inconsistencies caused by race conditions between cache and persistent store.

Sample Answer

Cache invalidation strategies each create windows where downstream readers can see stale data:

- Write-through: writes go to cache and store synchronously, so reads usually consistent. But if the cache write succeeds and DB write is delayed or fails, readers may see data that was never persisted — a different form of inconsistency.- Write-around (write to DB, bypass cache): an update writes only to DB; until the cache entry expires or is explicitly invalidated, readers hitting cache get old values (stale reads).- TTL-based: even after a DB update, cached entries persist until TTL expiry, so readers during the TTL window see stale data.

These produce race conditions: e.g., client A updates record -> DB updated -> cache still has old value (or update/invalidate hasn't happened yet) -> client B reads cache and sees stale data.

Designing idempotent operations reduces user-visible inconsistency and eases recovery:- Make writes idempotent (PUT semantics, include full resource state or idempotency keys) so repeated retries or out-of-order processing don't corrupt state.- Use versioning/timestamps: write only if incoming version > stored version (optimistic concurrency). This prevents a late-arriving stale write from overwriting a newer value.- Combine with cache patterns: when updating, perform DB update with versioning, then invalidate cache; on retries, idempotency prevents duplicate application.- For read-after-write needs, return the canonical source (DB) or read-through the cache after confirming invalidation.

Example (pseudo):

python

# idempotent update with version
def update_record(id, new_value, version):
    current = db.get(id)
    if version <= current.version:
        return current  # idempotent / no-op
    db.update(id, new_value, version)
    cache.invalidate(id)
    return db.get(id)

Other mitigations: short TTLs for hot items, write-through for critical data, strong consistency reads when necessary, and background reconciliation jobs. Combining idempotency + versioned writes + careful invalidation minimizes user-visible stale reads.

MediumSystem Design

59 practiced

Design a transactional outbox + message relay service to ensure atomic writes and event delivery to external systems. Outline the components (application DB, outbox table, relay worker, message broker), the flow for ensuring at-least-once delivery without losing events, and how consumers should be designed to be idempotent.

Sample Answer

Requirements (clarify): atomic persistence of state + events, durable delivery to external systems, at-least-once semantics, scalable relay, observability.

High-level architecture:- Application DB (single transactional DB)- Outbox table in same DB (append-only)- Relay worker(s) (polling or CDC-based)- Message broker / external target (Kafka, SNS, HTTP endpoints)

Components & schema:- outbox(id UUID, aggregate_id, type, payload JSON, created_at, status ENUM {pending,sent,failed}, attempt_count INT, dedup_id UUID) - dedup_id is a stable id per logical event for consumer dedup.

Flow to ensure atomic write + no lost events:1. In application transaction, write business state AND insert an outbox row (same DB txn). Commit => event durably stored.2. Relay worker reads pending rows (polling or consume DB CDC like Debezium for lower latency). Use SELECT ... FOR UPDATE SKIP LOCKED to claim batches.3. Relay publishes to broker or external endpoint. Attach headers: dedup_id, outbox_id, created_at.4. On successful publish (ACK from broker or HTTP 2xx), relay marks outbox row status=sent (or deletes/archive) in DB.5. On failure, increment attempt_count, set backoff delay, eventually move to dead-letter for manual handling.

Guarantees:- At-least-once: events persisted before ack; relay retries on failure; duplicate possible if relay crashes after publish but before marking sent.- No lost events: durability in DB + broker durability + persisted outbox.

Relay design details:- Use horizontal workers with SKIP LOCKED to avoid double-claim.- Use idempotent publish to broker when supported (Kafka producer idempotence).- Use transactional or exactly-once where available (Kafka transactions) if stronger guarantees needed.- Exponential backoff, jitter, circuit breaker to external endpoints.- Archival for sent rows to keep outbox small (move to archive table or S3).

Consumer idempotency patterns:- Require dedup_id in event header.- Consumers persist processed dedup_ids (e.g., Redis, DB) and reject duplicates (TTL or compacted store).- Use upserts with unique constraint on dedup_id to make processing atomic.- Design operations to be idempotent (upsert, set-state rather than increment-only).- For side effects (emails, external charges), check idempotency key against transactional ledger before acting.

Observability & ops:- Metrics: outbox queue depth, publish latency, failure rates, attempt_count histogram.- Alerts for growing pending/backlog, DLQ size.- Automated reprocessing for DLQ with safeguards.

Trade-offs:- Polling vs CDC: polling simpler; CDC lower latency and more scalable.- Keep outbox co-located with application DB for atomicity; cross-db transactions add complexity.- Exactly-once (Kafka TX) reduces duplicates but increases complexity and coupling.

This pattern ensures atomic writes and durable, at-least-once delivery while relying on idempotent consumers to make the system effectively de-duplicated.

MediumTechnical

60 practiced

When is eventual reconciliation (periodic jobs to reconcile and fix inconsistencies) acceptable versus requiring strong transactional boundaries? Provide decision criteria (RPO/RTO, regulatory constraints, user impact) and concrete examples where reconciliation is appropriate and where it is not.

Sample Answer

Answer:

Decision framework (make trade-offs explicit):- RPO / RTO: If business can tolerate stale or missing data for X time (RPO) and recovery within Y (RTO), reconciliation can be used. Tight RPO/RTO (seconds) favors strong transactions.- Regulatory / audit: Financial, healthcare, or compliance regimes that require immutable, auditable, and immediate correctness require transactional guarantees.- User impact / UX: If inconsistency breaks a user flow (double-charge, wrong inventory at checkout), use strong consistency. If inconsistency only affects downstream analytics or dashboards, reconciliation is acceptable.- Cost & scale: Strong transactions at scale (cross-partition/global) are expensive and slow; if cost/latency unacceptable, prefer reconciliation plus compensating logic.- Failure modes & detectability: If errors are easy to detect and correct deterministically and compensations are safe/idempotent, periodic reconciliation is viable.- Complexity & developer velocity: If transactional implementation is high complexity that blocks delivery and business risk is low, prefer reconciliation.

Concrete examples where reconciliation is appropriate:- Analytics pipelines: ETL jobs that rebuild aggregates nightly; transient duplication or lag doesn’t affect core product.- Denormalized caches/OLAP mater tables: Periodic jobs re-sync from source of truth (e.g., nightly backfills).- Eventual counters and metrics: Use reconciliation to repair drift between event stream and aggregated views.

Concrete examples where strong transactional boundaries are required:- Monetary transfers/payments: Prevent double-spend or inconsistent balances—use ACID DBs or distributed transactions, or strict idempotent write protocols.- Inventory reservation at checkout: Prevent oversell during concurrent purchases—use strong locking/compare-and-swap or single-partition transactions.- Clinical records updates / legal documents: Immediate consistency and auditability required.

Operational guidance for reconciliation:- Define SLAs (max staleness), detection alerts, and idempotent repair actions.- Keep authoritative source of truth and small reconciliation window to reduce blast radius.- Log reconciliation actions for audit and measure reconciliation success rate.- Use hybrid patterns: strong consistency for critical operations; eventual reconciliation for derived systems.

EasyTechnical

57 practiced

Define idempotency in the context of data engineering and distributed systems. Explain with concrete examples (for example: event ingestion, ETL job, and an API call) why idempotent operations matter for data correctness. Describe common symptoms you would see in production when idempotency guarantees are missing.

HardSystem Design

53 practiced

Design a deduplication/compaction service on top of an object store (e.g., S3) for nightly data batches. It must handle eventual writes and updates, ensure no duplicate user records are materialized, and support safe rollbacks. Discuss manifest design, idempotent commit semantics, metadata store choices, garbage collection, and how to surface correctness to users.

Sample Answer

Requirements & constraints:- Nightly batch ingest to an object store (S3). Batches may contain late/duplicate writes or updates for the same user across files/ nights.- Guarantee: no duplicate user records materialized (exactly-one visible record per user per dataset snapshot), support safe rollback, tolerate eventual arrival and replays, provide user-observable correctness/lineage.

High-level approach:- Treat each nightly run as producing immutable partitioned data files + a manifest describing which keys (user_id) map to which object (file, byte range, version).- Maintain snapshots: atomic manifests that point to a consistent set of objects representing the dataset at a point in time. Snapshots are append-only metadata objects in S3 (or metadata DB) and are the unit of commit/rollback.

Manifest design:- Store manifests as compact files (e.g., parquet/json) listing entries: user_id, object_path, file_offset/row_id (or record id), record_hash, source_batch_id, record_ts. Also include manifest-level metadata: parent_snapshot_id, commit_id, commit_ts, schema_hash.- Support bloom filters / min-max per-file to speed lookup.- Keep manifests immutable and deduplicated by content-addressed naming (e.g., SHA256 of content).

Deduplication algorithm & commit semantics:- During batch processing, produce candidate records and assign deterministic keys (user_id). Partition and sort so duplicates aggregate locally (e.g., Spark reduceByKey using user_id, choose latest record by record_ts or business priority).- Generate per-partition compacted files and per-partition manifests mapping keys -> file. Calculate a proposed manifest for the snapshot.- Use a two-phase idempotent commit: 1) Prepare: write proposed manifest to a staging location with a unique commit_id; validate no conflicting commit_id exists. 2) Commit: attempt to create a pointer (atomic object or metadata row) that advances current_snapshot -> new_snapshot using conditional update (compare-and-set). On S3 only, use an external metadata store (see below) to do atomic CAS. If CAS succeeds, new snapshot is visible. If not, read winning manifest and either merge or discard; because manifests and files are content-addressed and immutable, replays are safe (idempotent).- Ensure idempotency by making commit_id deterministic per input batch (hash of inputs + pipeline version) so retries produce identical manifests.

Metadata store choices:- Use a strongly consistent metadata service for snapshot pointer + transactional CAS: - Preferred: DynamoDB (conditional writes) or Spanner / CockroachDB / RDS with serializable transactions. - Alternative: use S3 object tagging with object creation as the pointer plus object lock + Lambda verification, but this is less robust.- Store manifests in S3 (immutable), store snapshot lineage and small indexes in the metadata DB. Keep per-user indices (optional) in DB or secondary index service for fast key->snapshot_file resolution.

Garbage collection:- GC only deletes unreferenced files after safe retention window and successful compaction commits.- Keep reference counts: manifests list object references; metadata DB maintains refcounts or use manifest graph traversal to compute liveness.- Two-phase delete: mark-for-deletion snapshot IDs, wait retention period (to allow rollbacks), then background job computes objects no longer referenced by any live snapshot and deletes them from S3. Use S3 object versioning and Lifecycle rules as additional safety net.

Safe rollbacks:- Because snapshots are immutable and advancement is via atomic pointer in metadata DB, rollback is a controlled pointer update to an earlier snapshot (via CAS). Retention window prevents GC of data needed for rollback.- Provide guarded rollback operations requiring audit/approval for production.

Surface correctness to users:- Expose snapshot metadata and lineage via a REST/API and UI: show snapshot_id, parent, commit_id, batch sources, counts (records in, deduped, retained), sample hashes.- Provide deterministic record lookup API: get(user_id, snapshot_id) -> object_path + offset or record. Allow scanning diffs between snapshots (added/removed/changed user_ids).- Surface verification: compute and expose dataset-level checksums (Merkle tree or partition-level hashes) and per-snapshot counts. Offer automated verification jobs that re-run aggregation on raw inputs and assert equality.- Provide observability: alerts on unexpected changes in dedupe rates, surge in collisions, or inconsistent commits. Keep audit logs of commits/rollbacks.

Trade-offs:- Strong metadata DB adds cost but enables atomic commits and simple CAS. S3-only approaches (e.g., manifest pointers as S3 objects) are possible but weaker on consistency.- Per-user index in DB improves read latency but increases metadata footprint; can be built lazily.

This design yields idempotent commits, immutable manifests for lineage, safe GC with retention, deterministic deduplication, and clear correctness signals for consumers.

Unlock Full Question Bank

Get access to hundreds of Data Consistency and Idempotency interview questions and detailed answers.

Join thousands of developers preparing for their dream job.