Everything in Databases & Architecture, page 4
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.
Connection pooling and its key parameters
Reuse open connections to skip costly handshakes; tune max pool size and connection timeout.
Least privilege for database service accounts
Grant each account only the minimum rights its job needs; for an app service account, scope grants to specific tables and verbs, never use the superuser.
Full, differential, and incremental backups
Full copies everything; differential copies all changes since the last full; incremental copies changes since the last backup of any type.
Mitigating a database shard hot spot
Short-term, add read replicas or cache the hot keys; long-term, fix the partition key with hashing, salting, or finer-grained splitting.
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.
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.
What is eventual consistency?
Replicas converge to the same value if writes stop, allowing temporary staleness for higher availability and lower latency.
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.
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…
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.
What is sharding and why shard over vertical scaling?
Sharding splits one dataset across servers by a shard key so each holds a subset; you shard because vertical scaling hits hardware ceilings, gets costly, and remains a single point of failure.
What is database replication and why use it?
Replication keeps copies of data on multiple servers; primary benefits are high availability through failover and improved read scalability by spreading reads.
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.
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 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.
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.
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.
What is the difference between ETL and ELT?
ETL transforms before loading, on a separate engine; ELT loads raw then transforms inside a scalable warehouse. Choose ELT with cloud warehouses and large raw or schema-on-read data.
What is a star schema?
A central fact table of measures and foreign keys surrounded by denormalized dimension tables of descriptive attributes, joined in one hop for fast, simple analytical queries.
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