SQL & Databases Modern Data Platforms and Scaling Flashcards

24 sample cards from the Modern Data Platforms and Scaling deck

Sampled from the end of the deck, so these are different cards from the ones shown on the syllabus page.

1. What is a data lake, and how does it differ from a data warehouse?

   A data lake stores raw data of any structure (structured, semi-structured, unstructured) at scale in object storage, schema-on-read. A data warehouse stores cleaned, structured, schema-on-write data optimized for SQL analytics.

2. What is a 'lakehouse' architecture?

   An architecture combining data lake storage (cheap object storage, open formats) with data warehouse features (ACID transactions, schema enforcement, performance) via table formats like Delta Lake, Apache Iceberg, or Apache Hudi.

3. In analytical SQL, what does a window function do and how does it differ from GROUP BY?

   A window function computes a value across a set of rows related to the current row (defined by an OVER clause) without collapsing them. Unlike GROUP BY, it returns one output row per input row while still allowing aggregate/ranking computations.

4. What is the purpose of the OVER (PARTITION BY ... ORDER BY ...) clause in a window function?

   PARTITION BY divides rows into groups within which the function is computed independently; ORDER BY defines row ordering within each partition (needed for ranking, running totals, and offset functions like LAG/LEAD).

5. Name three common ranking/analytic window functions and how ROW_NUMBER, RANK, and DENSE_RANK differ on ties.

   ROW_NUMBER assigns unique sequential numbers (ties broken arbitrarily). RANK gives tied rows the same rank then skips subsequent numbers (1,2,2,4). DENSE_RANK gives tied rows the same rank with no gaps (1,2,2,3).

6. How do you compute a running (cumulative) total in analytical SQL?

   Use a window aggregate, e.g., SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), which sums all rows up to and including the current one in the defined order.

7. What are the LAG and LEAD window functions used for?

   They access a value from a previous (LAG) or following (LEAD) row within the partition without a self-join, commonly used for period-over-period comparisons like calculating change from the prior row.

8. What is a rollup/cube in analytical SQL used for?

   GROUP BY ROLLUP and GROUP BY CUBE generate subtotal and grand-total aggregation rows across multiple grouping levels: ROLLUP produces a hierarchy of subtotals, while CUBE produces subtotals for all combinations of the grouped columns.

9. List key dimensions of data quality.

   Accuracy, Completeness, Consistency, Timeliness, Validity (conforms to rules/formats), and Uniqueness (no unwanted duplicates).

10. What is data validation and give examples of validation checks.

    Verifying data meets defined rules before/after loading. Examples: type checks, range/boundary checks, not-null/required checks, format checks (regex, email), referential integrity checks, uniqueness checks, and business-rule assertions.

11. What is idempotency in a data pipeline and why does it matter?

    An idempotent pipeline produces the same result no matter how many times it runs on the same input (e.g., via upserts or partition overwrites). It matters because it makes reruns and retries after failures safe, avoiding duplicate or corrupted data.

12. What is schema-on-read versus schema-on-write?

    Schema-on-write applies and enforces a schema when data is written (traditional databases/warehouses). Schema-on-read stores raw data as-is and applies a schema only when the data is queried (data lakes), offering flexibility at the cost of query-time enforcement.

13. What are common formats for semi-structured data, and what defines semi-structured data?

    Semi-structured data has tags/keys giving it self-describing structure but no rigid tabular schema. Common formats: JSON, XML, Avro, and Parquet (nested). It sits between fully structured (tables) and unstructured (free text/images).

14. How do modern SQL engines let you query nested JSON columns?

    Via JSON path / dot-and-bracket accessors and functions (e.g., BigQuery/Snowflake path syntax, Postgres ->/->> and jsonb, plus UNNEST/FLATTEN to expand arrays into rows), allowing extraction of nested fields without predefining every column.

15. What is the difference between the JSON and JSONB types in PostgreSQL?

    JSON stores an exact text copy (preserves formatting/key order, reparsed each query). JSONB stores a decoded binary form that is faster to query, supports indexing (GIN), but does not preserve whitespace or duplicate keys.

16. What is Trino (formerly PrestoSQL) and its primary use case?

    A distributed, massively parallel SQL query engine designed for interactive analytics that queries data where it lives via connectors (federated queries across data lakes, warehouses, and databases) without moving/copying the data.

17. What is Spark SQL and how does it fit within Apache Spark?

    Spark SQL is Spark's module for structured data processing that lets you run SQL and DataFrame operations over distributed datasets. It uses the Catalyst optimizer and Tungsten execution engine and is suited to large-scale batch ETL and analytics.

18. What is DuckDB and when is it a good fit?

    DuckDB is an in-process (embedded) analytical (OLAP) columnar SQL database, often called 'SQLite for analytics.' It's ideal for fast single-node analytics on local files (Parquet/CSV) without a server, embedded directly in applications or notebooks.

19. How does a query engine like Trino differ from a data warehouse like Snowflake in terms of storage?

    Trino is a compute-only query engine with no storage of its own; it federates queries over external sources via connectors. A warehouse like Snowflake manages its own storage and compute (though decoupled) and optimizes data within its managed format.

20. What is meant by MPP (Massively Parallel Processing) architecture?

    A shared-nothing architecture where a query is split into tasks executed in parallel across many independent nodes, each with its own CPU/memory/disk, and results are combined. It underpins engines like Redshift, Trino, and Spark for large-scale analytics.

21. What is a managed (fully managed) database/data service?

    A cloud provider operates the infrastructure, handling provisioning, patching, backups, replication, scaling, and high availability, so users focus on data and queries rather than server administration (e.g., Amazon RDS, BigQuery, DynamoDB).

22. What is the difference between IaaS-style self-managed databases and serverless managed databases?

    Self-managed (on VMs/IaaS) requires you to handle installation, scaling, patching, and backups. Serverless managed services (e.g., BigQuery, Aurora Serverless, DynamoDB on-demand) auto-scale compute, require no capacity planning, and bill per usage/query.

23. What trade-off do you accept when choosing a managed cloud database service?

    You gain reduced operational burden, built-in HA/scaling, and reliability, but accept less low-level control, potential vendor lock-in, and usage-based cost that can grow with scale.

24. Why might an application use both a relational database and a NoSQL store together (polyglot persistence example)?

    To match each workload to the best-fit engine: e.g., a relational DB for transactional order data needing ACID guarantees, a document store for flexible product catalogs, a graph DB for recommendations, and Redis for session caching, maximizing performance for each use case.

What this deck covers

Modern Data Platforms and Scaling flashcards FAQ