Search

Key concepts and definitions across all sections.

★ Exam-heavy marks the areas the exam leans on most — start your review here.

M1 SQL

M1Basic queries & aggregates★

Spotify Schema · SELECT · FROM · WHERE · NULL · Three-Valued Logic · IS NULL · GROUP BY · COUNT · SUM · HAVING · WHERE vs HAVING

M1Joins, sets & subqueries★

UNION · UNION ALL · INTERSECT · EXCEPT · Set vs Multiset · INNER JOIN · LEFT JOIN · CROSS JOIN · Self Join · Uncorrelated Subquery · Correlated Subquery · EXISTS · IN · NULL in NOT IN

M1Intermediate SQL, schema, and how a query runs★

CTE (Common Table Expression) · WITH Clause · Window Function · PARTITION BY · ROW_NUMBER · RANK · DENSE_RANK · Debug Tables · Query Equivalence · Output Equivalence · Normalization · Foreign Key · Update Anomaly · Denormalization · Parallel Execution · Query Execution Pipeline

M1BThe agentic data stack

Ambiguity in English · BIRD Benchmark · Output Equivalence · Semantic Layer (Knowledge Model) · Skills · Grounding at Scale · Spec (Pinned Definition) · Join Policy · Verified Library · Primary and Replicas · Read-Heavy Workload · Read Replica

M1CReading and writing queries★

Reading Queries · The Mental Model · The Funnel · The Ladder · The Timeline · The Comparison · The Exception Finder

M2 Systems

M2How does physical hardware limit software?

Storage Hierarchy · IO Cost · Sequential vs Random IO

M2How do we manipulate data efficiently?

Hash Collisions · H3 (Geospatial Index) · Locality Sensitive Hashing (LSH) · Vector/Embedding Hashing · Bloom Filter · False Positive Rate · Run-Length Encoding (RLE) · Dictionary Encoding · Delta Encoding · Bit Packing

M3 Storage, indexing & optimization

M3How is data physically organized on disk?

Row Storage · Columnar Storage · Page

M3How do databases physically execute queries?

Hash Partitioning · BigSort · Block Nested Loop Join (BNLJ) · Hash Partition Join (HPJ) · Sort-Merge Join (SMJ)

M3BHow do databases find data quickly?★

B+ Tree · LSM Tree · SSTable · Compaction

M3CHow does the database choose the fastest path?★

Query Optimizer · Query Plan · Selectivity · Cardinality · IO Cost Summary (Plans)

M4 Transactions

M4What guarantees does a database make?★

Transaction · Scale · Complexity · ACID · Atomicity · Consistency · Isolation · Durability

M4How do we handle concurrent users?★

Serial Schedule · Concurrent Schedule · Transaction Manager · Reordering Rules · Interleaved Schedule · Concurrency · Parallelism · Optimistic Concurrency Control (OCC) · Pessimistic Concurrency Control (PCC) · Multi-Version Concurrency Control (MVCC)

M4Locking & 2PL★

Shared Lock (S-Lock) · Exclusive Lock (X-Lock) · Lock Compatibility Matrix · Two-Phase Locking (2PL) · Strict Two-Phase Locking (S2PL) · Cascading Abort · Growing Phase · Shrinking Phase · Microschedule · WaitsFor Graph · Deadlock · Cycle

M4Proving correctness.★

Conflict · Read-Write Conflict · Write-Read Conflict · Write-Write Conflict · Conflict Serializability · Conflict Graph · Macroschedules · Topological Sort · 2PL Theorem

M4BWhat happens when the power goes out?★

UNDO · REDO · COMMIT · ABORT · Write-Ahead Logging (WAL) · Trace Tables · WAL Protocol Rules · Analysis Phase · Redo Phase · Undo Phase · Checkpoint

M5 Distributed systems

M5How do we scale to 1,000 machines?

Sharding Strategies · Replication Patterns · Consistent Hashing

M5What happens when machines inevitably fail?

MapReduce · Apache Spark · RDDs · Spark SQL · Lineage

M5How do we handle real-time data?

Kafka Architecture · Topics & Partitions · Delivery Semantics · Ordering Guarantees

M5What are the physical limits of scaling?

CAP Theorem

M6 Modern data systems

M6How does Big Tech scale data pipelines?

Data Lake · OLTP · OLAP · ETL · ELT

M6When is SQL the wrong tool?

Pandas · Polars · NoSQL · JSONB

M6How do we protect user data at scale?

Differential Privacy · Synthetic Data