Star Schema, Denormalize, Aggregate and Join

What it is

A star schema organizes analytical data into fact tables, which hold measurable events such as sales lines, and dimension tables, which describe them, such as product, customer and date. Each fact row points to its dimensions through keys.

Building one usually means denormalizing (flattening related lookup tables into one dimension), joining sources together and sometimes aggregating facts to a coarser grain. Semantic models, SQL queries and Direct Lake all work best on this shape.

How it works

Facts and dimensions

Decide the grain first: what one fact row means, such as "one order line". Every measure in the table must be true at that grain.

A surrogate key is an integer created in the warehouse, separate from the source system's business key. It lets you combine sources and keep history. With slowly changing dimension Type 1 you overwrite changed attributes; with Type 2 you add a new row with its own surrogate key and validity dates, so old facts keep the old attribute values.

Denormalizing a dimension

Source systems often split product, subcategory and category into three tables. A star schema flattens them into one dimension with a CREATE TABLE AS SELECT (CTAS):

CREATE TABLE dbo.DimProduct
AS
SELECT
    ROW_NUMBER() OVER (ORDER BY p.ProductID) AS ProductKey,
    p.ProductID,
    p.ProductName,
    sc.SubcategoryName,
    c.CategoryName
FROM stg.Product AS p
LEFT JOIN stg.Subcategory AS sc
    ON sc.SubcategoryID = p.SubcategoryID
LEFT JOIN stg.Category AS c
    ON c.CategoryID = sc.CategoryID;

LEFT JOIN keeps products that have no subcategory. An inner join would silently drop them.

Building the fact table

CREATE TABLE dbo.FactSales
AS
SELECT
    d.DateKey,
    COALESCE(p.ProductKey, -1) AS ProductKey,
    s.OrderID,
    s.Quantity,
    s.Quantity * s.UnitPrice AS SalesAmount
FROM stg.Sales AS s
JOIN dbo.DimDate AS d
    ON d.[Date] = s.OrderDate
LEFT JOIN dbo.DimProduct AS p
    ON p.ProductID = s.ProductID;

A -1 key points to an "Unknown" row that you add to the dimension. Facts with a missing product then stay in totals instead of disappearing.

Join types

In Power Query, Merge queries joins tables side by side, and Append queries stacks tables with the same columns, like UNION ALL. In PySpark, df.join(other, "key", "left_anti") returns unmatched rows.

Aggregating

An aggregate table stores facts at a coarser grain, such as one row per day and product, so large reports read fewer rows.

from pyspark.sql import functions as F

daily = (spark.table("fact_sales")
    .groupBy("DateKey", "ProductKey")
    .agg(F.sum("SalesAmount").alias("TotalSales"),
         F.countDistinct("OrderID").alias("Orders")))
daily.write.format("delta").mode("overwrite").saveAsTable("agg_daily_sales")

The T-SQL equivalent uses GROUP BY, and Power Query has Group by on the Transform tab.

A worked example

An online shop's source has Orders, OrderLines, Products, Subcategories and Categories.

1. Grain: one row per order line.
2. Flatten products, subcategories and categories into DimProduct with a surrogate key and an Unknown row.
3. Join order lines to orders to bring the order date onto each line, then look up DateKey and ProductKey to build FactSales.
4. Use a left anti join to list order lines whose product is missing from the product table, and report them to the source owner.
5. Build agg_daily_sales for an executive dashboard that only shows daily totals.

How the exam asks about this

• "Reduce the number of tables and joins for report authors." Denormalize the snowflaked dimensions into one table.
• "Find fact rows that have no matching customer." A left anti join.
• "Keep the history of customer address changes." A Type 2 slowly changing dimension with surrogate keys.