Advanced interview questions in Databases & Architecture
Read Committed versus Serializable isolation levels
Name the four levels, map each anomaly (dirty read, non-repeatable read, phantom) to the level that blocks it.
Diagnosing and fixing the N+1 query problem
Define the 1 parent plus N child queries, fix via JOIN or batched IN, and ORM eager loading.
When to intentionally denormalize a schema
Identify read-heavy join cost, duplicate or precompute data, and own the consistency burden.
Polymorphic associations and referential integrity
A single column can't FK two tables, so integrity is unenforced; alternatives use exclusive arcs or per-type tables.
Relational versus wide-column for a news feed
Relational gives flexible joins but read-time fan-out; Cassandra precomputes per-user feed rows for fast writes-side fan-out.
Shard key impact on uniqueness and cross-shard lookups
Uniqueness and FKs hold only within a shard; non-shard-key lookups need scatter-gather or a secondary index.
Write skew under snapshot isolation
Two transactions read an overlapping set, each writes disjoint rows, jointly violating an invariant.
How the write-ahead log ensures atomicity and durability
Log changes before applying, flush log at commit, replay redo and undo on recovery.
Optimizer picks nested loop over hash join
Nested loop wins on few rows, so bad row estimates from stale stats or skewed data trick it; fix by refreshing statistics, adding histograms, rewriting predicates, or ensuring memory for hashing.
Indexing a low-cardinality status column
With three values each matches a third of rows, so the optimizer prefers a scan over costly random heap fetches; alternatives include partial indexes on rare values and composite indexes leading with status.
The three phases of ARIES recovery
Analysis rebuilds dirty-page and transaction tables from the last checkpoint, Redo replays all logged changes to restore state, Undo rolls back losers; Redo is idempotent via per-page LSN comparison so…
Row-oriented versus columnar storage
Row stores keep whole rows together, ideal for point reads and writes; columnar stores keep each column contiguous, enabling reading only needed columns and strong compression, ideal for scans and…
Using page LSN to decide redo
Each page stores the LSN of its last applied change; during redo the engine reapplies a log record only if its LSN exceeds the page's LSN, meaning the change is not yet reflected on disk.
How does a hash join handle memory overflow?
The build table is partitioned by hash and spilled to disk, then probe rows are partitioned the same way, and pairs are joined per partition.
How do you keep consistency without multi-document transactions?
A Saga runs a sequence of local transactions, each with a compensating action to undo on failure, coordinated via choreography or orchestration.
What consistency do you sacrifice in an AP system?
You give up linearizability and often sequential consistency, accepting stale reads and conflicts, then mitigate with quorums, vector clocks or CRDTs, and…
Why separate storage and compute in a cloud warehouse?
Data lives in cheap shared object storage while independent compute clusters scale separately, enabling elastic, concurrent, isolated workloads and pay-per-use.
What is an OLAP cube and its operations?
A cube pre-aggregates measures across dimensions; operations are slice, dice, drill-down, roll-up, and pivot.
Durable write path in a sharded KV store
Route by key hash to the shard leader, append to WAL and fsync, replicate to two followers, ack on quorum, then confirm.
Split-brain, consensus, and quorum
Split-brain is two nodes both believing they are leader during a partition; Raft/Paxos require a majority quorum to elect a leader and commit, so the minority side cannot make progress.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles