Data engineering interview: How do you make DynamoDB TransactWriteItems idempotent?

Prompt and context

An order service must create an order, decrement inventory, and write a ledger entry in one business operation. A network timeout may happen after DynamoDB commits, so the client can send the request again. Use TransactWriteItems to design atomic writes, conditions, idempotent retries, and reconciliation, including capacity cost and regional boundaries.

What the interviewer evaluates

• Whether you distinguish atomic commit, conditional failure, and an unknown result after a network break.
• Whether you use ClientRequestToken, a durable business idempotency key, and condition expressions correctly.
• Whether you account for extra transactional capacity, API limits, and hot-key contention.
• Whether retries, compensation, reconciliation, metrics, and cross-region consistency form one design.

Clarifying questions

1. Are the order, inventory, and ledger tables in the same AWS Region and account boundary?
2. Is overselling allowed, and must the ledger be an immutable append-only record?
3. Can the client query final state by a business idempotency key after a timeout?
4. Could a retry occur after the token validity window?
5. Is cross-region disaster-recovery writing required, or is single-region atomicity enough?

30-second answer

I would create a stable idempotency key for each business operation and use TransactWriteItems in one Region to create the order, conditionally decrement inventory, and write a ledger entry atomically. The request carries a ClientRequestToken; durable business state and conditions prevent duplicate decrements. After a timeout, read by the business key before retrying. Monitor conflicts, conditional failures, capacity, and unknown outcomes. For cross-region work, use events and reconciliation because the transaction’s atomic boundary is regional.

Deep-dive answer

Step 1: State business invariants

State the invariant: a successful order reduces inventory once and creates one ledger entry. Use one operationId for order and ledger records, and require available >= quantity on the inventory item. Do not use a client timestamp as the sole key; retries and clock skew can create duplicate operations.

Step 2: Compose transaction actions

Combine Put, Update, Delete, and ConditionCheck actions in one TransactWriteItems request. The order Put requires a missing key, the inventory Update checks a version or available quantity, and the ledger Put uses operationId as a unique key. Do not operate on the same item twice in one transaction, and respect action and request-size limits.

Step 3: Build two idempotency layers

ClientRequestToken makes the same transaction request safely retryable during its validity window. A business idempotency key persisted in the order and ledger covers a longer lifecycle. Both must identify the same operation; a retry must not invent a new business key. After the token window, read business state before rebuilding a transaction.

Step 4: Separate failure from an unknown result

Conditional conflicts, capacity errors, and validation failures usually provide an actionable failure path: return a business error or retry with bounded backoff according to the error. A connection lost after submission is unknown; do not immediately reverse inventory. Read the order, inventory version, and ledger by operationId, then let the state machine choose retry or reconciliation.

Step 5: Account for cost and hot keys

DynamoDB performs underlying reads or writes for transaction preparation and commit, so capacity use is higher than a single ordinary write. Estimate throughput from item size, concurrency, and retry amplification, and shard hot inventory or use a reservation queue. Average WCU is insufficient; conditional failures and conflicts consume latency and capacity budgets too.

Step 6: Handle regional requirements

The transaction’s atomicity applies to the transaction boundary in the calling Region. If analytics or a replica lives elsewhere, publish an event carrying operationId; consumers write idempotently and reconciliation finds gaps or duplicates. During replication lag, a remote read model is not proof of immediate transaction commit.

Step 7: Observe and recover

Record transaction success, conditional failures, conflicts, throttling, retry count, unknown outcomes, per-table capacity, and end-to-end latency. Use states such as PENDING, COMMITTED, RECONCILING, and FAILED; a scanner handles unknown states and either completes the ledger or creates a human queue. Correlate request logs, table items, and events by operationId.

Model answer

For each order I would create a stable operationId and use TransactWriteItems in one Region to create the order, conditionally decrement inventory, and write a ledger item keyed by that ID. The order requires a missing key, inventory checks its version and available >= quantity, and the ledger is naturally deduplicated. The request also carries a stable ClientRequestToken. A conditional failure returns an explainable business error; a post-commit timeout first reads all three records by operationId instead of blindly reversing inventory. Capacity estimates include transactional preparation and commit work, hot keys, and retry amplification. Cross-region propagation uses idempotent events and reconciliation rather than claiming cross-region atomicity. Metrics cover conflicts, capacity, unknown outcomes, and state-machine recovery time.

Common mistakes

• Treating a timeout as proof that the transaction failed and reversing inventory immediately.
• Generating a new business key on every retry, creating duplicate orders or ledger entries.
• Depending only on ClientRequestToken and omitting durable business idempotency.
• Ignoring conditional conflicts, transaction limits, and extra transactional capacity.
• Treating global-table replication or a remote read as proof of transaction commit.
• Having no unknown-state scanner or reconciliation path beyond manual log inspection.

Follow-up questions

Follow-up 1: Does ClientRequestToken guarantee permanent idempotency?

No. It covers the API-defined validity window. Long-lived idempotency requires a business key and final state in the order, ledger, or an operation table.

Follow-up 2: Should an inventory condition failure be retried?

If inventory is insufficient, retrying cannot change the result and should return a business failure. A transient conflict or throttle can be retried with backoff, the same operation key, and a bounded attempt count.

Follow-up 3: How do you decide the result after a timeout?

Read the order, inventory version, and ledger by operationId. Consistent records indicate commit; partial visibility or disagreement enters RECONCILING, where a workflow chooses completion or human handling.

Follow-up 4: Why not put cross-region writes in one transaction?

The API does not extend atomicity across multiple Regions. Use events, idempotent consumers, and reconciliation while documenting the eventual-consistency window and degradation behavior.

Follow-up 5: How do you locate transaction conflicts?

Log operationId, item keys, condition versions, and retry count, then aggregate conflicts by hot key. A persistently contended inventory item may need sharding, reservations, or queue-based allocation.

Follow-up 6: How do you test the unknown-result path?

Drop the response after the server confirms commit to simulate a client timeout. Verify lookup, retry, state transitions, event deduplication, and reconciliation so inventory and the ledger are never applied twice.