Data Warehouse Vocabulary Exercises

1 / 5

A colleague explains: "The star schema has a central fact table and four dimension tables."

What type of data does a fact table contain?

Fact table vs. Dimension table — the core star schema distinction:

Table type	Contains	Example
Fact table	Events/transactions with numeric metrics + FK references	`order_fact`: order_id, customer_id FK, amount, quantity
Dimension table	Descriptive context (names, categories, attributes)	`customer_dim`: customer name, segment, region

Star schema: 1 fact + multiple dimensions, no joins between dimensions. Snowflake schema normalises dimensions further. Key vocabulary: granularity, fact grain, degenerate dimension, surrogate key.

2 / 5

A data engineer says: "The team implemented SCD Type 2 for the customer dimension."

What does SCD Type 2 mean?

Slowly Changing Dimensions (SCD) — managing historical changes in dimension data:

SCD Type	Strategy	History
Type 1	Overwrite existing row	No history preserved
Type 2	New row per change + effective_date/expiry_date	Full history, enables time travel queries
Type 3	Add a "previous_value" column	Limited history (one previous state only)

SCD2 vocabulary: surrogate key, current_flag, effective_date, expiry_date, historical snapshot.

3 / 5

An architect says: "This is an OLAP query — it scans billions of rows across 3 years of data."

How does OLAP usage differ from OLTP?

OLAP vs. OLTP — fundamental database workload distinction:

Workload	Typical query	Optimised for	Examples
OLAP	SUM(revenue) GROUP BY month	Columnar storage, full table scans	Redshift, BigQuery, Snowflake
OLTP	SELECT * FROM orders WHERE id = 123	Row-based, index lookups, low latency	PostgreSQL, MySQL

Key vocabulary: columnar vs. row storage, analytical vs. transactional workload, data warehouse vs. operational DB.

4 / 5

A data engineer explains: "The warehouse uses partitioning on the event_date column."

What performance benefit does partitioning provide?

Partition pruning — the query optimizer skips irrelevant partitions entirely:

Without partitioning	With date partitioning
Full table scan (e.g., 3 years of data)	Scans only the matching date partition
High cost, slow query	Dramatically lower cost and latency

Example: WHERE event_date = '2025-01-01' on a date-partitioned table scans 1 day's data, not years. Key vocabulary: partition pruning, partitioning key, clustering vs. partitioning (BigQuery: partitioning = coarse-grained; clustering = fine-grained sort order within partitions).

5 / 5

An analyst says: "The data model uses a conformed dimension shared across multiple fact tables."

What is the advantage of a conformed dimension?

Conformed dimension = same dimension used consistently across marts:

Conformed Date dimension	Used by
Single, shared definition of date attributes	Sales mart, Marketing mart, Finance mart

Benefit: enables cross-mart analysis ("drill-across") with consistent metric definitions. Contrast with siloed dimensions: each mart owns its own customer definition → inconsistency. Key vocabulary: data mart, enterprise data bus, conformed fact, drill-across query.