Why Every Data Engineer Should Practice Explaining Their Model Aloud

CREATE TABLE dim_date (
    date_key      INT PRIMARY KEY,      -- e.g. 20240305
    full_date     DATE NOT NULL,
    month_name    VARCHAR(10),
    is_weekend    BOOLEAN
);

CREATE TABLE dim_product (
    product_key   INT PRIMARY KEY,
    product_id    VARCHAR(20) NOT NULL,  -- natural key from source
    category      VARCHAR(50),
    brand         VARCHAR(50)
);

CREATE TABLE fact_order_line (
    order_id      VARCHAR(20) NOT NULL,
    line_number   INT NOT NULL,
    date_key      INT NOT NULL REFERENCES dim_date(date_key),
    product_key   INT NOT NULL REFERENCES dim_product(product_key),
    customer_key  INT NOT NULL,
    quantity      INT NOT NULL,
    unit_price    NUMERIC(10,2) NOT NULL,
    discount      NUMERIC(10,2) DEFAULT 0,
    PRIMARY KEY (order_id, line_number)
);

"The centre is fact_order_line. One row is one product
 line inside an order, so the key is order_id plus
 line_number.

 The measures are quantity, unit price and discount.
 All of them make sense per line, which is how I checked
 the grain was right.

 Around it are date, product and customer dimensions.
 Shipping fee is charged per order, not per line, so I
 kept it in a separate order-level fact. Otherwise any
 sum of shipping would be multiplied by the line count."

-- dim_customer: customer_key (identity), customer_id, city, segment,
--               valid_from, valid_to, is_current
-- PostgreSQL syntax

-- Step 1: close current rows whose tracked columns changed
UPDATE dim_customer AS d
SET valid_to = CURRENT_DATE - 1,
    is_current = FALSE
FROM stg_customer AS s
WHERE d.customer_id = s.customer_id
  AND d.is_current = TRUE
  AND (d.city IS DISTINCT FROM s.city
       OR d.segment IS DISTINCT FROM s.segment);

-- Step 2: insert a current row for new and changed customers
INSERT INTO dim_customer (customer_id, city, segment,
                          valid_from, valid_to, is_current)
SELECT s.customer_id, s.city, s.segment,
       CURRENT_DATE, DATE '9999-12-31', TRUE
FROM stg_customer AS s
LEFT JOIN dim_customer AS d
       ON d.customer_id = s.customer_id
      AND d.is_current = TRUE
WHERE d.customer_id IS NULL;

"I run it in two steps.

 First, any current customer row whose city or segment
 changed gets an end date of yesterday and is marked
 not current.

 Second, I insert a fresh current row for every staging
 customer who no longer has a current row. That covers
 both brand-new customers and the ones I just closed.

 I use IS DISTINCT FROM instead of not-equals so that a
 change from NULL to a value is still detected.

 Facts store customer_key, so each sale points to the
 version of the customer that was true on that day."

-- Monthly revenue by category and customer city,
-- using the city that was true at the time of each sale
SELECT d.month_name,
       p.category,
       c.city,
       SUM(f.quantity * f.unit_price - f.discount) AS net_revenue
FROM fact_order_line AS f
JOIN dim_date     AS d ON d.date_key     = f.date_key
JOIN dim_product  AS p ON p.product_key  = f.product_key
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE d.full_date >= DATE '2024-01-01'
GROUP BY d.month_name, p.category, c.city
ORDER BY d.month_name, net_revenue DESC;

Q: Why not keep it normalised like the source database?

A: "The source is designed for writing orders quickly,
 so it is split into many small tables. Our users are
 analysts who read far more than they write.

 With the star, most questions are one fact table plus
 three or four dimension joins, like the revenue query.
 In the normalised source the same question needs many
 more joins and is easy to get wrong.

 The cost is that I have to maintain the dimensions and
 handle history myself, which the SCD job does."

MY DATA MODEL

Business process modelled: _______________________
Fact table name: ________________________________
Grain (one row = ...): ___________________________
Measures (each checked against grain): ___________
Dimensions: _____________________________________
Which dimension keeps history, and how: __________
Partitioning or clustering choice: _______________
One query this model makes easy: ________________

PRACTISE ANSWERING ALOUD

- What does one row in your fact table mean?
- Which measure would break if the grain changed?
- A product moves to a new category. What happens?
- Why surrogate keys instead of source IDs?
- Why not one wide table for everything?
- How do you handle a fact that arrives before its
  dimension row exists? (late-arriving dimension)
- How would you add a new source to this model?

AFTER YOUR RECORDING

[ ] Grain stated in the first 20 seconds
[ ] Measures explained as making sense at that grain
[ ] History explained as behaviour, then named
[ ] Mentioned facts storing the surrogate key
[ ] Gave one gain and one cost for the star schema
[ ] Walked through one real query

Weakest point: ______________________