SQL & Databases Modern Data Platforms and Scaling Syllabus

Describe the cache-aside (lazy loading) pattern.

The application first checks the cache; on a hit it returns the cached value; on a miss it reads from the database, writes the result into the cache, then returns it. The cache is populated only on demand.

Contrast write-through and write-back (write-behind) caching.

Write-through: writes go to cache and database synchronously, keeping them consistent but adding write latency. Write-back: writes go to cache first and are flushed to the database asynchronously, giving fast writes but risking data loss if the cache fails before flushing.

What is a TTL in the context of caching?

Time-To-Live: an expiration duration set on a cached entry after which it is automatically evicted/invalidated, ensuring stale data is eventually refreshed from the source.

What is the difference between OLTP and OLAP systems?

OLTP (Online Transaction Processing) handles many short, frequent read/write transactions (e.g., orders, payments) on current, normalized operational data. OLAP (Online Analytical Processing) handles complex, read-heavy analytical queries aggregating large volumes of historical data for reporting and analysis.

Give three contrasting characteristics of OLTP vs OLAP workloads.

OLTP: row-oriented storage, highly normalized, many small transactions, low latency per query. OLAP: columnar storage, denormalized/dimensional, few large aggregating queries, high throughput over big scans.

What is dimensional modeling and its core building blocks?

A data warehouse design technique optimized for analytics, organizing data into fact tables (measurable business events/metrics) and dimension tables (descriptive context like time, product, customer) connected by keys.

Compare the star schema and the snowflake schema.

Star schema: a central fact table joined directly to denormalized dimension tables (simple, fast joins). Snowflake schema: dimension tables are normalized into sub-dimensions (less redundancy, more joins, slightly more complex).

What is the difference between a fact table and a dimension table?

A fact table stores quantitative measures (e.g., sales_amount, quantity) plus foreign keys to dimensions, usually many rows. A dimension table stores descriptive attributes used to filter/group facts (e.g., date, product name, region), usually fewer rows.

What is a slowly changing dimension (SCD), and what does Type 2 do?

An SCD is a dimension whose attribute values change over time. SCD Type 2 preserves history by adding a new row (with effective/expiry dates or a version flag) for each change, keeping old rows intact so historical facts map to the correct attribute values.

What is a grain (granularity) in dimensional modeling?

The grain defines what a single row of a fact table represents (the level of detail), e.g., 'one row per order line item.' Declaring the grain first is a foundational step in dimensional design.

What is columnar storage and why is it efficient for analytics?

Data is stored by column rather than by row, so a query reading a few columns scans only those columns. This reduces I/O, enables high compression (similar values grouped together), and speeds up aggregations over large tables.

Why does columnar storage achieve better compression than row storage?

Values within a single column share the same data type and often have low cardinality or repetition, so encoding techniques like run-length encoding, dictionary encoding, and delta encoding compress them far more effectively than mixed-type rows.

Name two widely used open columnar file formats for analytics.

Apache Parquet and Apache ORC (Optimized Row Columnar). Both are compressed, columnar, self-describing formats used in data lakes and warehouses.

What is a cloud data warehouse? Give examples.

A fully managed, elastically scalable analytics database service in the cloud that separates storage and compute for OLAP workloads. Examples: Amazon Redshift, Google BigQuery, Snowflake, Azure Synapse.

What key architectural feature lets modern cloud warehouses (e.g., Snowflake, BigQuery) scale compute and storage independently?

Separation (decoupling) of storage and compute: data lives in cheap object storage while independent compute clusters/warehouses read it, so each can scale up or down separately and multiple compute engines can query the same data.

What is the difference between ETL and ELT?

ETL (Extract, Transform, Load) transforms data in a staging area before loading it into the target. ELT (Extract, Load, Transform) loads raw data into the target warehouse first, then transforms it there using the warehouse's compute.

Why has ELT become popular with modern cloud data warehouses?

Cloud warehouses offer cheap scalable storage and powerful elastic compute, so it is efficient to load raw data first and transform in-place with SQL. ELT preserves raw data for reprocessing and leverages the warehouse's parallelism.

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.

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.

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.

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).

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).

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.

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.

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.

List key dimensions of data quality.

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

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.

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.

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.

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).

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.

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.

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.

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.

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.

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.

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.

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).

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.

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.

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.