lesson 87 Warehouse & reporting projects · advanced

Protect fact grain when joining versions

Join historical versions without multiplying fact rows.

the idea

Joining a fact table to a type-2 history must match exactly one version per fact row. Use either the current version (is_current = 1) for “as now” reporting or the version valid on the fact date for “as then” reporting. Both preserve the fact grain; joining to all versions does not.

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

   See row inflation without a date filter. Joining every history version to every sale multiplies fact_rows—totals would be wrong if you stopped here.the pattern

   example.sql Run

   sql›

   SELECT
     COUNT(*) AS fact_rows,
     (
       SELECT COUNT(*)
       FROM fact_sales s
       JOIN dim_customer c ON c.customer_key = s.customer_key
       JOIN customer_history h
         ON h.customer_id = CAST(c.customer_id AS INTEGER)
     ) AS joined_to_all_versions
   FROM fact_sales;

2. 2

   Count rows with current-version join. Filtering to is_current = 1 matches one history row per customer, so the join count stays equal to fact_sales.

   step-2.sql Run

   sql›

   SELECT COUNT(*) AS current_join
   FROM fact_sales s
   JOIN dim_customer c ON c.customer_key = s.customer_key
   JOIN customer_history h
     ON h.customer_id = CAST(c.customer_id AS INTEGER)
     AND h.is_current = 1;

3. 3

   Count rows with as-of-date join. Matching valid_from and valid_to to each line’s order_date also preserves one row per sale for historical reporting.

   step-3.sql Run

   sql›

   SELECT COUNT(*) AS as_of_join
   FROM fact_sales s
   JOIN dim_customer c ON c.customer_key = s.customer_key
   JOIN customer_history h
     ON h.customer_id = CAST(c.customer_id AS INTEGER)
     AND h.valid_from <= s.order_date
     AND (h.valid_to IS NULL OR s.order_date < h.valid_to);

common mistake

A join on customer only repeats each sale once per version and inflates totals.