Databases
229 bites tagged Databases — interview questions with model answers, and 60-second explainers.
What are the major challenges of blue/green deployments with relational databases?
Tests decoupling schema and code changes in stateful blue/green deployments. Strong answers cover the additive-then-deletive pattern, backward compatibility for both app versions, and shared DB risks. Red flag: split DBs with no rollback or sync plan.
What are the challenges of grouping by a high-cardinality dimension?
Tests columnar storage internals and query engine scalability. A strong answer covers memory pressure from giant hash tables, destroyed compression ratios, and massive result-set overhead.
Implement CDC from an OLTP database to a data warehouse
This tests your grasp of production system trade-offs. A good answer compares log-based and trigger-based CDC, focusing on source impact and data fidelity, then recommends log-based for its low overhead.
Challenges of Grouping by High-Cardinality Dimensions
This tests your grasp of system-level impacts of data shape. A good answer explains how high cardinality strains memory during aggregation, reduces compression, and inflates index size, leading to slow, expensive queries. A red flag is just saying 'it's slow'.
Trade-offs: Pre-aggregation vs. Querying Raw Data
Tests your grasp of data system trade-offs. A great answer weighs pre-aggregation (fast, cheap, stale) against querying raw data (slow, costly, fresh, flexible) and proposes a hybrid solution. A red flag is declaring one method universally superior.
What is a data warehouse vs. a transactional database?
Tests your grasp of systems optimized for different access patterns (writes vs. reads). Define OLTP for transactions and OLAP for analytics. Contrast their schema (normalized vs. denormalized), data, and workload. A red flag is calling it a 'big database'.
How would you implement Change Data Capture (CDC)?
Tests your grasp of data replication trade-offs. A great answer compares log-based CDC (low impact, complete) with query-based methods (higher impact, misses deletes), and recommends log-based CDC for its minimal production impact.
Challenges of Grouping by a High-Cardinality Dimension
This tests your grasp of how data shape impacts system resources. A great answer explains that high cardinality explodes memory usage for aggregation state, increases CPU load, and hurts storage compression.
Trade-offs: Pre-aggregation vs. querying raw event data
This tests your grasp of data engineering trade-offs for analytics dashboards. A great answer contrasts pre-aggregation (fast, cheap, stale) with on-the-fly queries (slow, costly, fresh) and mentions hybrid solutions.
What is a data warehouse vs. a transactional database?
Tests your grasp of read-optimized (OLAP) vs. write-optimized (OLTP) systems. A great answer defines warehouses for analysis, contrasts them with transactional DBs for operations, and explains the resulting differences in workload, schema, and data structure.
Time-Series Compression: Storing More with Less
Time-series compression stores data more efficiently by saving the *difference* between consecutive points, not the full values. It's key for managing terabyte-scale monitoring and IoT data, often saving over 90% on storage.
Inverted Index: The Engine of Fast Log Search
An inverted index makes log search fast by mapping terms to the logs containing them, like a book's index. It powers platforms like Splunk or Elasticsearch, enabling instant searches across terabytes of data. The footgun is indexing high-cardinality fields.
Data Schema Evolution: Changing Your Data's Blueprint
Schema evolution is like updating a building's blueprint while it's occupied. You must change your data's structure without breaking apps or losing data. It's key for adding features that need new DB columns.
Schema-on-Read vs. Write: Pay for Structure Now or Later?
Schema-on-Write pays to structure data upfront for fast, consistent reads. Schema-on-Read defers this cost to query time for flexible ingestion. This choice underpins relational databases (write) vs. data lakes (read). The footgun is creating a data swamp.
Caching: Write-Through for Safety, Write-Back for Speed
Write-through caching writes to the database immediately for data safety, while write-back delays writes for speed. Use write-through for critical data and write-back for high-volume updates.
Phantom Reads: When New Rows Appear Mid-Transaction
A phantom read occurs when a transaction repeats a query and finds new rows that match its search criteria, inserted by another committed transaction. It's common in reporting jobs that need a stable set of data.
Multi-Region Databases: Resilience, Latency, and Compliance
A multi-region database is a strategy for resilience, low latency, and data compliance. It's used to survive region outages, keep data in-country, and serve reads close to users. The footgun is managing low-level replica placement directly, which is complex.
Synchronous vs. Asynchronous Replication: A Trade-off
Replication is a trade-off: synchronous waits for all copies to confirm a write, guaranteeing consistency but risking availability. Asynchronous lets the primary move on immediately, prioritizing speed.
Vectorized Query Execution: Processing Batches, Not Rows
Vectorized execution processes data in batches of thousands of rows, not one at a time. This lets analytical databases like ClickHouse and Snowflake scan billions of rows in seconds by keeping data in CPU cache and using SIMD instructions.
Multi-Leader Replication: Enabling Writes Across Datacenters
Multi-leader replication allows multiple nodes to accept writes, avoiding a single-leader bottleneck. It's used in multi-datacenter systems for low-latency local writes and in offline apps. The main footgun is resolving write conflicts from concurrent updates.
Single-Leader Replication: One Node to Rule Them All
Think of a single source of truth. One 'leader' server takes all writes, while 'follower' servers handle read traffic. This is the default for many databases like PostgreSQL and MongoDB to scale reads.
HNSW: Vector Search with a Graph Highway System
HNSW finds approximate nearest neighbors in huge datasets by building a multi-layered graph, like a highway system over local roads. It's the engine in vector databases for similarity search. The footgun: it trades perfect accuracy for massive speed gains.
In-Memory Data Grid: A Shared RAM Pool for Your Cluster
An In-Memory Data Grid (IMDG) pools the RAM of multiple computers into one massive, shared data space. It's for high-speed processing on datasets too large for one machine. The footgun is mistaking it for a simple cache; it also provides parallel computation.
Approximate Nearest Neighbor (ANN) Search: Good Enough, Fast Enough
ANN search finds 'pretty close' neighbors in a massive dataset for a fraction of the cost of finding the exact closest one. It powers vector databases and semantic search.
Get Databases bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.