Part 6 of 6 · 4 min read
Exercises
Easy
Easy 1: Name the measures (10 minutes)
List which columns in a fact table order_fact(order_id, customer_id, product_id, order_date, quantity, unit_price_dollars) are measure attributes and which are dimension attributes.
Done when: you can name all measures and all dimensions with a one-line reason for each.
Hint
quantity and unit_price_dollars are the numbers you would sum or average; the rest are the identifiers pointing elsewhere.
Easy 2: Spot the warehouse (10 minutes)
A company keeps its live checkout database and a separate reporting database refreshed every night at 1 AM that managers query for sales reports. Name which one is the operational system and which is the warehouse, and say why running reports on the first one would be risky.
Done when: both systems are labeled correctly with a one-sentence reason.
Hint
the one refreshed nightly is the warehouse; running reports on the live checkout system risks slowing down real purchases.
Easy 3: ETL or ELT (10 minutes)
A pipeline extracts raw clickstream events, loads them untouched into the warehouse, and only afterward runs a job that groups and cleans them inside the warehouse. Is this ETL or ELT, and why?
Done when: you can state which one it is and point to the step order that proves it.
Hint
load happens before transform here, which is the defining feature of ELT.
Medium
Medium 1: Draw a star schema (20 minutes)
For a fact table recording each book-club meeting attendance (attendance_fact: meeting_id, member_id, book_id, meeting_date, attended as 1 or 0), sketch the dimension tables you would need and their key columns.
Done when: you have a fact table and at least three dimension tables, each connected by a single foreign key.
Hint
think about who attended (member), what book was discussed (book), and when (date) as your three dimensions.
Medium 2: Write the ETL step (25 minutes)
Write a SQL statement that loads new rows from a staging_sales table into a sales_fact table, converting a price_cents column into price_dollars, and skipping any sale_id already present in sales_fact.
Done when: your INSERT ... SELECT runs, performs the conversion, and the NOT IN or equivalent check prevents duplicate loads.
Hint
divide price_cents by 100.0 in the SELECT, and use a NOT IN or NOT EXISTS subquery against sales_fact on sale_id.
Medium 3: Snowflake the dimension (20 minutes)
Take the store_dim table (store_id, city, state, country) and snowflake it by pulling city, state, country into a separate location_dim, referenced from store_dim by a location_id foreign key. Rewrite a query that groups sales by country to use the new design.
Done when: you have the new location_dim table and a working query with the extra join.
Hard
Hard 1: Justify star over snowflake (25 minutes)
The bookstore's book_dim repeats author_country on every row for a prolific author with 40 books. A colleague proposes snowflaking author_country into its own table to save space. Write two or three sentences arguing for keeping the star schema instead, using the actual cost trade-off.
Done when: your argument names both the space saved and the join cost added, and reaches a clear recommendation.
Hint
weigh a small amount of repeated text against an extra join on every single analytical query that touches the author's country.
Hard 2: Design a two-fact warehouse (30 minutes)
The bookstore wants to track both sales_fact (one row per book sold) and returns_fact (one row per book returned), sharing the same book_dim, customer_id, and date_dim dimension tables. Sketch both fact tables and show which dimension tables they share.
Done when: both fact tables are defined and you can point to at least two shared dimension tables.
Hint
date_dim, book_dim, and the customer identifier are natural candidates to share between the two fact tables.
Hard 3: Compute from the star schema (30 minutes)
Using the sales_fact, book_dim, and date_dim tables from this page, write a query that returns total revenue per genre per quarter, assuming date_dim has a quarter column.
Done when: your query joins all three tables correctly and groups by both genre and quarter.
Hint
join sales_fact to book_dim on book_id and to date_dim on sale_date, then GROUP BY genre and quarter together.