Data Modeling Basics: Star Schemas and Denormalization

Load the same three orders into a normalized schema and a star schema, then run the identical aggregate query against both.

Normalized: every fact stored exactly once

import duckdb
con = duckdb.connect()

con.execute("create table customers (customer_id int, name varchar, country varchar)")
con.execute("create table products (product_id int, name varchar, category varchar)")
con.execute("create table orders (order_id int, customer_id int, order_date date)")
con.execute("create table order_items (order_id int, product_id int, quantity int, unit_price decimal(10,2))")

con.execute("insert into customers values (1,'Ada','GB'), (2,'Grace','US')")
con.execute("insert into products values (10,'Widget','Hardware'), (11,'Gadget','Hardware'), (12,'Manual','Media')")
con.execute("insert into orders values (100,1,'2026-01-04'), (101,2,'2026-01-05')")
con.execute("""insert into order_items values
    (100,10,2,9.99), (100,12,1,4.50),
    (101,11,1,19.99)""")

print(con.execute("""
    select p.category, sum(oi.quantity * oi.unit_price) as revenue
    from order_items oi
    join orders o on o.order_id = oi.order_id
    join products p on p.product_id = oi.product_id
    join customers c on c.customer_id = o.customer_id
    group by p.category
    order by p.category
""").df().to_string(index=False))

$ python data_modeling.py
== normalized: revenue by category needs a 4-table join ==
category  revenue
Hardware    39.97
   Media     4.50

Four tables, three joins, for one grouped sum. Every one of customers, products, and orders has to be touched just to label order_items rows with a category and confirm they belong to a real order.

Star schema: pre-joined once, queried many times

con.execute("""
    create table fact_order_items as
    select oi.order_id, o.order_date, c.customer_id, c.name as customer_name, c.country,
           p.product_id, p.name as product_name, p.category,
           oi.quantity, oi.unit_price, oi.quantity * oi.unit_price as revenue
    from order_items oi
    join orders o on o.order_id = oi.order_id
    join products p on p.product_id = oi.product_id
    join customers c on c.customer_id = o.customer_id
""")

print(con.execute("""
    select category, sum(revenue) as revenue
    from fact_order_items
    group by category
    order by category
""").df().to_string(index=False))

== star schema: revenue by category needs zero joins ==
category  revenue
Hardware    39.97
   Media     4.50

Same answer, no join. The join happened exactly once, when fact_order_items was built — every downstream query pays for it zero more times, no matter how many analysts run this query today.

What that costs in storage

row_count_normalized = sum(con.execute(f"select count(*) from {t}").fetchone()[0]
                            for t in ["customers", "products", "orders", "order_items"])
row_count_star = con.execute("select count(*) from fact_order_items").fetchone()[0]
print(f"normalized: {row_count_normalized} rows spread across 4 tables, customer 'Ada' stored once")
print(f"star schema: {row_count_star} rows in 1 fact table, customer name 'Ada' repeated on every row of her orders")

normalized: 10 rows spread across 4 tables, customer 'Ada' stored once
star schema: 3 rows in 1 fact table, customer name 'Ada' repeated on every row of her orders

fact_order_items repeats Ada, GB, Widget, and Hardware on every line item they touch. That repetition is the actual trade: storage and write complexity (update Ada’s name in a million historical fact rows if she changes it) for read simplicity. Transactional systems avoid it because they update constantly and want each fact to exist once; analytics tables embrace it because they are written once (or once a day) and read thousands of times.

Practice

create table fact_daily_revenue as
select order_date, category, sum(revenue) as revenue
from fact_order_items
group by order_date, category

Next: data quality — checks that run on every load rather than a dashboard nobody reads.