Facts & dimensions

Operational systems store data for doing — process this order, update that profile. Analytical questions need data shaped for asking — revenue by category by week, repeat-purchase rate by channel. The shape that has won for thirty years, and that every modern tool still assumes, is the star schema: facts in the middle, dimensions around them.

• A fact table records events that happened: one row per order line, per payment, per support ticket. Facts are tall (millions of rows), narrow-ish, and mostly numbers and foreign keys. They grow forever.
• A dimension table records things that exist: one row per customer, per product, per store, per date. Dimensions are short (thousands of rows), wide, and descriptive — names, categories, segments. They change slowly.
• A question is a join: 'net revenue by product category last month' = fact order_lines joined to dim_product, filtered by dim_date, summed. If the model is right, every business question is a short query with one correct answer.

Why AI systems specifically need this

A human analyst can compensate for a messy schema — they know which of the four revenue columns is real. An AI agent can't. When the Conversational Analytics Agent course wires an LLM to a warehouse, the model sees table names, column names, and whatever descriptions exist. A clean star schema with honest names is the highest-leverage prompt engineering you can do for analytical AI: the schema is the prompt.

There's a second, quieter reason. Facts and dimensions separate what happened (immutable events) from what we currently believe (descriptions that get corrected, merged, re-categorized). When Module 4 merges duplicate customers, we rewrite a dimension — the facts don't move. That separation is what makes correction possible without rewriting history.

Harbor Lane's star

                 dim_product
                (sku, name, category, cost)
                        |
  dim_customer ---- fct_order_lines ---- dim_date
  (golden record,   (one row per         (calendar,
   built in M4)      order line:          implicit in
                     qty, price,          order_date)
                     status, refs)

One fact table at order-line grain; product and customer dimensions; dates as a plain column (DuckDB's date functions make a separate date dimension optional at this scale — we'll note when you'd want one). Deliberately small, deliberately complete: this little star can answer every question the capstone will throw at it.