Intermediate interview questions in Databases & Architecture, page 2
Why fit Cassandra to a high-read, high-write workload?
Consistent-hash partitioning spreads load, replication and no single master give availability, log-structured writes are fast, tunable consistency balances per query.
Star schema vs snowflake schema trade-offs?
Star keeps dimensions denormalized for fewer joins and faster simpler queries; snowflake normalizes dimensions into sub-tables saving space and easing maintenance but adding joins.
How does columnar storage speed up analytics?
Columnar stores each column contiguously, so aggregations read only needed columns, scan far less data, and compress better with vectorized execution.
What is a Type 2 slowly changing dimension?
An SCD handles dimension attributes that change over time; Type 2 inserts a new row per change with a surrogate key and validity dates, marking one current.
Apply the CAP theorem to a real system
Define C, A, P; note partitions are unavoidable, so the real choice during one is consistency versus availability; then classify a system as CP or AP with reasoning.
Range-based vs hash-based sharding trade-offs?
Range sharding keeps ordered keys together, great for range scans but prone to hot spots on sequential keys; hash sharding spreads keys evenly, avoiding hot spots but killing efficient range…
Leader-follower vs multi-leader replication
Single-writer leader-follower is simple but a write bottleneck; multi-leader accepts writes in many regions for latency and availability.
What is eventual consistency?
Replicas converge to the same value if writes stop, allowing temporary staleness for higher availability and lower latency.
Connection pooling and its key parameters
Reuse open connections to skip costly handshakes; tune max pool size and connection timeout.
Point-in-Time Recovery (PITR)
Restore a base backup then replay archived write-ahead logs up to a chosen moment, enabling recovery to just before an error.
RBAC vs direct user grants
Direct grants tie rights to individuals; RBAC groups rights into roles users inherit, so changes happen once per role.
Pooled connection lifecycle and close() semantics
Borrow from pool, use, then close() returns it to the pool rather than tearing down the socket.
Eager vs lazy loading in an ORM
Eager fetches related data up front (joins/extra query); lazy defers until accessed. Lazy in a loop causes the N+1 query problem.
Transaction isolation levels and their tradeoffs
Isolation levels control which concurrency anomalies (dirty/non-repeatable reads, phantoms) are allowed; higher levels mean stronger consistency but more blocking and less concurrency.
Aurora Serverless v2 for spiky traffic
Serverless v2 autoscales capacity in fine-grained ACU steps near-instantly, you pay per-ACU-second, and provisioned is fixed cost regardless of load.
Near-zero-downtime database migration to cloud
Assess and provision, do a full load then continuous CDC replication with a tool like DMS, validate, then cut over with a rollback plan.
Cache-aside pattern with Redis and RDS
App checks cache, on miss reads DB and populates, writes invalidate the key, and consistency is eventual.
How Spanner achieves global external consistency
TrueTime gives bounded-uncertainty clocks via GPS and atomic sources, Spanner commit-waits out that uncertainty, and Paxos replicates each shard.
Clickstream architecture for real-time and batch
Ingest events into a log like Kafka, fan out to a real-time path for dashboards and a batch path to a lake for ad-hoc analysis.
Exactly-once semantics in stream processing
Exactly-once means each event affects state once despite retries, it is hard because of failures between processing and committing, and you achieve it via idempotency or atomic…
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