lesson 84 Warehouse & reporting projects · advanced

Latest-state reporting versus SCD Type 2

Compare current-state reporting with preserved history.

the idea

A latest-state table keeps only the current value of each attribute; a type-2 history keeps every version with a validity interval. Reports about “now” use the current version; reports about the past join each fact to the version valid on its date.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Walk one customer’s history intervals. Each row is a type-2 version with valid_from, valid_to, and is_current—half-open intervals avoid double-counting boundary days.the pattern

   example.sql Run

   sql›

   SELECT customer_id, segment, country, valid_from, valid_to, is_current
   FROM customer_history
   WHERE customer_id = 12359
   ORDER BY valid_from;

2. 2

   See how many versions customers have. The distribution shows how often segment or country changed—historical reports must join facts to the right version, not every row.

   step-2.sql Run

   sql›

   SELECT versions, COUNT(*) AS customers
   FROM (
     SELECT customer_id, COUNT(*) AS versions
     FROM customer_history
     GROUP BY customer_id
   )
   GROUP BY versions;

3. 3

   Pick the version valid on a date. For 2011-06-01 on customer 12359, valid_from <= date and date < valid_to (or NULL end) returns exactly one segment.

   step-3.sql Run

   sql›

   SELECT customer_id, segment, valid_from, valid_to
   FROM customer_history
   WHERE customer_id = 12359
     AND valid_from <= '2011-06-01'
     AND (valid_to IS NULL OR '2011-06-01' < valid_to);

common mistake

Joining facts to every history version without a date condition repeats each sale once per version.