Facts and dimensions

The staging layer is live: stg_customers, stg_products, stg_orders, stg_order_items, stg_subscriptions - typed, gated, replayable. But answering "revenue by month by city" still takes a three-table join through OLTP-shaped tables, and every analyst writes that join slightly differently. Tonight you reshape staging into the core layer: one fact table ringed by dimensions. The shape is called a star, and it is the default answer-machine of the entire industry.

Before any SQL, a dimensional modeler says one sentence out loud: "One row of this fact table is one order line." That sentence is the grain, and every later decision - what to measure, what to join, what to sum - either honors it or breaks the table. Facts are events with numbers (an order line happened: quantity 2, price 16.00). Dimensions are nouns with context (who bought, what they bought, when).

SELECT (SELECT count(*) FROM stg_orders)      AS orders,
       (SELECT count(*) FROM stg_order_items) AS order_lines;

The SQL course taught you to normalize - split data into small tables so every fact lives in exactly one place. Analytics deliberately walks that road backwards: we denormalize, copying context onto wide dimensions so questions need fewer joins. How much to denormalize, and when, is the field's oldest argument - and it has two named schools.

Modeling is deciding, column by column: is this a measure (goes on the fact), an attribute (goes on a dimension), or a key (glue)? Here is tonight's full mapping - read it once now, then watch Demo 1 make it real.

Four moves: two dimensions, one fact, one proof. The playground starts with b2's staging layer already in place (data-setup="staging"), so this script is exactly the core-layer build that would run after staging in production.

CREATE TABLE dim_customer AS
SELECT row_number() OVER (ORDER BY customer_id) AS customer_key,
       customer_id, name, city, country, plan, signup_date
FROM stg_customers;

CREATE TABLE dim_product AS
SELECT row_number() OVER (ORDER BY product_id) AS product_key,
       product_id, name, category, price, roast
FROM stg_products;

CREATE TABLE fct_order_line AS
SELECT dc.customer_key,
       dp.product_key,
       o.order_date     AS date_key,
       o.order_id,
       oi.quantity,
       oi.unit_price,
       oi.line_amount   AS line_revenue,
       o.status,
       o.channel
FROM stg_orders o
JOIN stg_order_items oi USING (order_id)
JOIN dim_customer dc ON o.customer_id = dc.customer_id
JOIN dim_product  dp ON oi.product_id = dp.product_id;

SELECT 'dim_customer' AS star_table,
       count(*) AS row_count FROM dim_customer
UNION ALL
SELECT 'dim_product', count(*) FROM dim_product
UNION ALL
SELECT 'fct_order_line', count(*) FROM fct_order_line;

These playgrounds start with the finished star (data-setup="star", which also includes dim_date - built properly in b4). Every question is the same shape: measures from the center, one join per arm you slice by. Feel how little SQL each answer needs now.

SELECT d.year, d.month_name,
       ROUND(SUM(f.line_revenue), 2) AS revenue
FROM fct_order_line f
JOIN dim_date d ON f.date_key = d.date_key
WHERE f.status = 'completed'
GROUP BY d.year, d.month, d.month_name
ORDER BY d.year, d.month;

SELECT c.city, c.country,
       ROUND(SUM(f.line_revenue), 2) AS revenue
FROM fct_order_line f
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE f.status = 'completed'
GROUP BY c.city, c.country
ORDER BY revenue DESC;

SELECT p.name, p.category,
       SUM(f.quantity) AS units,
       ROUND(SUM(f.line_revenue), 2) AS revenue
FROM fct_order_line f
JOIN dim_product p ON f.product_key = p.product_key
WHERE f.status = 'completed'
GROUP BY p.name, p.category
ORDER BY revenue DESC
LIMIT 3;

• Write the grain sentence for a subscriptions fact ("one row = one ___") and list its measures and dimension arms. You will build it for real in b7.
• On the star setup, answer: which channel sells more Equipment, web or app? Two arms this time - dim_product for category, channel is already on the fact.
• Take any spreadsheet or table you use at work and label every column M (measure), A (attribute), or K (key). Where labels feel forced, you have found a grain problem.
• Explain Inmon vs Kimball to a colleague in two sentences each, then say which road their company is on. Most people have never noticed they are on one.
• Bring your star to b4 - the dimensions get their full treatment next: surrogate keys defended, dim_date built properly, conformed dims explained.

This session teaches the dimensional-modeling core of the major curricula as one live build. Certificates, graded labs, and videos stay on the official platforms. This page covers: