SQL & Databases Modern Data Platforms and Scaling Syllabus

Modern Data Platforms and Scaling syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Modern Data Platforms and Scaling in SQL & Databases, not a summary of it.

1. 1

   SQL vs NoSQL

   4 topics

   • Relational vs Non-Relational Models
   • NoSQL Categories
   • CAP Theorem and Trade-offs
   • Polyglot Persistence
2. 2

   Scaling Relational Databases

   4 topics

   • Vertical vs Horizontal Scaling
   • Read Replicas and Load Balancing
   • Sharding Strategies
   • Caching Layers (Redis, Memcached)
3. 3

   Data Warehousing and Analytics

   5 topics

   • OLTP vs OLAP
   • Dimensional Modeling
   • Columnar Storage
   • Cloud Data Warehouses
   • ETL vs ELT Pipelines
4. 4

   SQL in the Data Engineering Workflow

   4 topics

   • Analytical SQL Patterns
   • Data Quality and Validation
   • Working with Semi-Structured Data
   • Query Engines (Trino, Spark SQL, DuckDB)
5. 5

   Cloud and Managed Databases

   3 topics

   • Managed Services
   • Serverless Databases
   • Cost and Performance in the Cloud

Modern Data Platforms and Scaling flashcards for SQL & Databases

18 of 61 cards from the Modern Data Platforms and Scaling deck — real questions with worked answers.

1. What is the fundamental difference between relational and non-relational (NoSQL) data models?

   Relational models store data in tables (rows/columns) with a fixed schema, enforce relationships via foreign keys, and use SQL. Non-relational models store data in flexible, schema-less structures (documents, key-value, columns, graphs) optimized for scale and varied data shapes rather than strict normalized relations.

2. What guarantees does the relational model enforce for transactions?

   ACID: Atomicity (all-or-nothing), Consistency (valid state transitions preserving constraints), Isolation (concurrent transactions don't interfere), and Durability (committed data survives crashes).

3. Name the four main categories of NoSQL databases.

   1) Key-Value stores (e.g., Redis, DynamoDB), 2) Document stores (e.g., MongoDB, Couchbase), 3) Wide-column / column-family stores (e.g., Cassandra, HBase), 4) Graph databases (e.g., Neo4j).

4. What kind of NoSQL database is best suited for highly connected data with many relationships (e.g., social networks)?

   A graph database (e.g., Neo4j), which stores data as nodes and edges and makes traversing relationships efficient.

5. State the CAP theorem.

   In a distributed data store, you can simultaneously guarantee at most two of three properties: Consistency (every read sees the latest write), Availability (every request gets a non-error response), and Partition tolerance (the system continues despite network partitions).

6. When a network partition occurs, which two design choices does CAP force, and what do they mean?

   You must choose CP or AP. CP (consistency + partition tolerance) rejects/blocks requests to avoid stale data. AP (availability + partition tolerance) keeps serving requests but may return stale/inconsistent data.

7. Why is partition tolerance (P) generally non-negotiable in distributed systems, making CAP a real choice between C and A?

   Network partitions are inevitable in any distributed system, so P must be tolerated. The practical trade-off is therefore between Consistency and Availability during a partition.

8. What does the BASE model stand for, and how does it contrast with ACID?

   BASE = Basically Available, Soft state, Eventual consistency. Unlike ACID's strict immediate consistency, BASE favors availability and allows data to converge to consistency over time, common in AP NoSQL systems.

9. Define polyglot persistence.

   An architectural approach of using multiple different database technologies within one application, choosing the best-fit data store for each specific data type or workload (e.g., relational for transactions, a graph DB for relationships, Redis for caching).

10. What is the difference between vertical and horizontal scaling?

    Vertical scaling (scale up) adds more power (CPU, RAM, disk) to a single machine. Horizontal scaling (scale out) adds more machines/nodes and distributes load across them.

11. What is a key limitation of vertical scaling and a key advantage of horizontal scaling?

    Vertical scaling has a hard hardware ceiling and a single point of failure. Horizontal scaling can grow near-linearly and adds redundancy, but introduces complexity (data distribution, consistency, coordination).

12. What is a read replica and what problem does it solve?

    A read replica is a copy of a primary database that asynchronously receives its changes and serves read-only queries. It offloads read traffic from the primary, improving read scalability and availability.

13. In a primary/replica setup, why can read replicas return stale data?

    Because replication is typically asynchronous, there is replication lag: a replica may not yet have applied the latest writes committed on the primary, so reads can be slightly out of date (eventual consistency).

14. What is the role of a load balancer in a scaled database/application tier?

    It distributes incoming requests across multiple servers/replicas to prevent any single node from being overloaded, improving throughput, latency, and availability. Common strategies include round-robin, least-connections, and hashing.

15. Define sharding (horizontal partitioning) in databases.

    Splitting a large dataset horizontally across multiple database nodes (shards), where each shard holds a distinct subset of rows, so the total data and load are distributed across machines.

16. Compare range-based, hash-based, and directory-based (lookup) sharding strategies.

    Range-based: rows assigned by key ranges (good for range scans, risk of hotspots). Hash-based: shard = hash(key) mod N, giving even distribution but poor range queries and hard resharding. Directory-based: a lookup table maps keys to shards, offering flexibility at the cost of an extra lookup and a potential bottleneck.

17. What is a 'hotspot' in a sharded system and how is it caused?

    A hotspot is a single shard receiving a disproportionate share of traffic or data, causing uneven load. It arises from a poorly chosen shard key (e.g., monotonically increasing keys or skewed access patterns).

18. What is consistent hashing and why is it used in sharding/distributed caches?

    A hashing scheme that maps both keys and nodes onto a ring; each key belongs to the next node clockwise. Adding or removing a node only remaps a small fraction (about $\frac{K}{N}$ of keys) rather than nearly all keys, minimizing data movement during rebalancing.

See more Modern Data Platforms and Scaling flashcards →

Planning Modern Data Platforms and Scaling for SQL & Databases

Modern Data Platforms and Scaling (SQL & Databases) FAQ