Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Distinguish the two times (15 minutes)
A customer's address change is entered on June 1st but takes effect June 10th. State the valid time and the transaction time.
Done when: June 10th is correctly given as valid time and June 1st as transaction time.
Hint
Valid time is June 10th (when the change takes effect); transaction time is June 1st (when it was recorded).
Easy 2: Plain versus history table (15 minutes)
State which kind of table can answer "what was this book's price last month," and which cannot.
Done when: a history table is correctly identified as able to answer it, and a plain overwritten table as unable to.
Hint
A history table can answer this; a plain table, having overwritten the old price, cannot.
Easy 3: Name the warehousing term (15 minutes)
Name the data-warehousing term for a dimension table that occasionally changes and needs a history-preserving strategy.
Done when: slowly changing dimension is correctly named.
Hint
Slowly changing dimension.
Medium
Medium 1: Design a history table (25 minutes)
Design a history table for customers.address, tracking every past address with a valid-from date.
Done when: a correct table with customer_id, address, and valid_from is given.
Hint
customer_address_history (customer_id, address, valid_from).
Medium 2: Query a specific point in time (25 minutes)
Using the Medium 1 table, write a query finding a specific customer's address as of a given past date.
Done when: a correct query filtering by valid_from and ordering descending, limited to one row, is given.
Hint
SELECT address FROM customer_address_history WHERE customer_id = 107 AND valid_from <= '2026-03-10' ORDER BY valid_from DESC LIMIT 1;
Medium 3: Decide if history is needed (25 minutes)
Decide whether a books.page_count column, which essentially never changes after a book is published, needs a history table.
Done when: no is correctly judged, with the reasoning that a value that essentially never changes gains little from history-tracking machinery.
Hint
No, since page_count essentially never changes after publication, making history-tracking overhead unnecessary.
Hard
Hard 1: Attributing a past order correctly (30 minutes)
Explain why an order's receipt should show the customer's address as it was on the order's placed_on date, not the customer's current address, and how a history table makes this possible.
Done when: the explanation correctly ties the receipt's correctness to looking up the address valid at placed_on, using the history table's valid_from column.
Hint
Looking up the address whose valid_from is the latest one on or before placed_on correctly reconstructs the address as it was at order time, which a single current-address column could never do.
Hard 2: Valid time scheduled ahead of transaction time (35 minutes)
Describe a realistic scenario where a price change is recorded in the system today but is deliberately scheduled to take effect at a specific future valid time, and explain why both timestamps need to be stored separately to handle this correctly.
Done when: a realistic scenario is described, with both valid_from and recorded_on correctly identified as necessary to support a change that is known now but takes effect later.
Hint
An online store announcing a price increase in advance, effective from a specific future date, needs recorded_on to know when the change was entered and valid_from to know when it should actually start applying.
Hard 3: Type 2 slowly changing dimension in a real report (40 minutes)
A sales report, run today, needs to show which city each historical sale should be attributed to, matching the customer's city at the time of each sale, not their current city. Describe how a type 2 slowly changing customer_dimension table supports this correctly.
Done when: the explanation correctly describes joining each historical sale to the customer_dimension row whose valid period covers that sale's own date, rather than joining to the customer's single current row.
Hint
Each historical sale joins to the customer_dimension row whose valid_from-to-valid_to range covers that sale's own date, correctly reconstructing the customer's city as it was at the time of that specific sale, rather than their city today.