5 exercises on data warehouse modeling — facts, dimensions, star schema, snowflake, and SCDs.
0 / 5 completed
1 / 5
What is a fact table in dimensional modelling?
Fact table: facts are numeric measures — revenue, quantity, duration. Each row represents an event at a grain (e.g. one order line). Fact tables are typically wide (many dimension foreign keys) and very tall (billions of rows), optimised for aggregation.
2 / 5
What is a dimension table?
Dimension table: dimensions provide the filtering, grouping, and labelling for fact aggregations. A customer dimension might contain name, segment, signup date, and country. Dimensions are typically smaller, wider, and queried for their descriptive text.
3 / 5
What distinguishes a star schema from a snowflake schema?
Star vs snowflake: in a star schema a product dimension contains category_name and subcategory_name directly. In a snowflake, those live in separate normalised tables. Stars are simpler to query; snowflakes save storage and are easier to maintain but join-heavy.
4 / 5
What is a Slowly Changing Dimension (SCD) Type 2?
SCD Type 2: when a customer moves city, you close the old row (set effective_to = today) and insert a new row with the new city and effective_from = today. Historical sales still join to the old row, preserving what the customer's address was at the time of purchase.
5 / 5
What is the grain of a fact table?
Grain: declaring the grain is the first step in dimensional modelling. "One row per order line item" vs "one row per daily product summary" are different grains. Mixing grains in one fact table causes incorrect aggregations and is a common design error.