Affirm Financial Data Sharding And Partitioning

What's being tested

This topic tests whether you can design a distributed storage layer that preserves financial correctness while scaling reads, writes, and operational workload. The interviewer is probing shard-key selection, request routing, cross-shard transaction handling, hot-partition mitigation, and safe resharding—not merely whether you can say “hash by customer ID.” Affirm cares because payment and loan data must remain correct, auditable, available, and isolated even during retries, failures, and traffic spikes.

Core knowledge

Sharding distributes rows across independent database partitions, while partitioning may divide data within one database. Explain whether the design needs horizontal write scaling, operational isolation, archival boundaries, or all three.
Choose a shard key that matches dominant access paths and spreads load. For customer-centric financial data, account_id or user_id often preserves locality; avoid keys such as merchant category or status that create skew and hot shards.
Consistent hashing reduces movement when nodes change, but virtual nodes and explicit shard maps are usually easier to reason about for transactional databases. A lookup can be shard = hash(account_id) mod N, but changing N remaps many accounts unless indirection is added.
A directory-based router maps a logical tenant or account to a physical shard. Store the mapping durably, cache it with bounded staleness, and define behavior for cache misses, concurrent moves, and a shard-map version mismatch.
Keep operations within one shard whenever possible. Cross-shard joins and transactions require two-phase commit, an application-level saga, or an event-driven workflow; each increases latency and introduces partial-failure states.
Financial balances should use an append-only double-entry ledger, where every transaction has equal debits and credits: $\sum debits = \sum credits$ . Materialized balances can be rebuilt or reconciled from ledger entries rather than treated as the sole source of truth.
Design retries around idempotency keys. Persist the key with the operation result on the authoritative shard, enforce uniqueness, and return the original result for a duplicate request instead of applying the payment twice.
Hot keys arise when one account, merchant, or settlement window receives disproportionate traffic. Mitigations include per-account serialization, optimistic concurrency with version checks, bounded queues, logical sub-buckets, or routing read-heavy projections separately from the write path.
Replication improves availability but creates consistency choices. For money movement, route reads requiring read-your-write behavior to the primary or use a session/commit version; never let a stale replica authorize a debit.
Resharding should be an online migration: dual-read or dual-write carefully, copy a consistent snapshot, replay changes, validate counts and checksums, quiesce or fence writes briefly, then atomically switch the directory mapping and retain rollback capability.
Enforce tenant isolation at multiple layers: routing authorization, database credentials, row-level ownership checks, and audit logging. A correct shard lookup is not sufficient if an attacker can submit another customer’s identifier.
Define failure semantics explicitly: shard unavailable, router unavailable, timeout after commit, duplicate retry, and partial cross-shard completion. Use durable operation states such as PENDING, COMMITTED, and FAILED, with reconciliation rather than ambiguous success.

Worked example

Design a sharded financial ledger for customer accounts

In the first 30 seconds, a strong candidate clarifies transaction volume, read/write ratios, account-to-user cardinality, required availability, whether transfers can cross accounts, and the consistency guarantee for balance reads. They would state an assumption such as “all ledger entries for one account are colocated, but transfers between accounts may span shards.” The answer would be organized around four pillars: shard-key and routing design, single-shard ledger correctness, cross-shard transfer coordination, and failure recovery/observability. They would use an append-only double-entry ledger with a unique idempotency key and balance version, then expose a read model that is rebuilt or reconciled from ledger events. For a cross-shard transfer, they might reserve funds on the source shard, persist a durable transfer state, credit the destination shard, and finalize or compensate based on recovery state. They should explicitly flag the tradeoff between two-phase commit, which offers stronger atomicity but couples shard availability, and a saga, which improves availability but requires visible intermediate states and reconciliation. They would explain that the router uses a versioned account-to-shard directory rather than embedding physical shard numbers in clients. If they had more time, they would detail online resharding, disaster recovery, invariant checks, and load-test scenarios for a hot account.

A second angle

Design account-history queries across many shards

Here the dominant constraint is fan-out latency rather than atomic writes: a query for a user’s complete history may touch multiple account shards and replicas. The service should maintain a bounded parallelism budget, apply per-shard deadlines, merge results by a stable cursor such as (event_time, ledger_id), and avoid returning duplicate or missing pages during concurrent writes. A denormalized user-history index can reduce fan-out, but it introduces asynchronous lag and must expose a clear consistency contract. The same partitioning decision therefore shifts from “keep a transaction local” to “make the read path predictable without sacrificing auditability.”

Common pitfalls

Pitfall: Saying “hash by user ID” without checking access patterns. If transfers, merchant settlement, or compliance queries dominate, the chosen key may force expensive fan-out or create skew; tie the key to concrete request paths and cardinality.

A common analytical mistake is treating replication as a substitute for partitioning. Replicas improve read capacity and failover, but they do not solve a single-shard write bottleneck, cross-shard coordination, or a hot account receiving all writes.

A communication mistake is presenting cross-shard transfers as magically atomic. Say exactly what happens after a timeout, how retries find the prior operation, whether intermediate states are visible, and which invariant or reconciliation process detects an incomplete transfer.

A depth mistake is focusing only on normal routing and ignoring movement. Interviewers often probe resharding, stale router caches, lost acknowledgments, duplicate requests, and a shard failing after the source debit but before the destination credit.

Connections

Expect pivots to consistent hashing, two-phase commit, sagas, idempotency, leader election, and replica consistency. The interviewer may also connect this to database indexing, event sourcing, auditability, rate limiting, and disaster-recovery objectives such as RPO and RTO.

Visa Tools

Visa Sponsors

H-1B Odds Calculator

Visa Bulletin