Modelling analytics tables

Why it matters

Application databases are designed for running a product: many small, normalised tables, updated constantly. Raw exports are the opposite: one wide file with everything repeated. Neither is pleasant to analyse. Analysts who query them directly write long, fragile joins, and two people answering the same question get different numbers.

Dimensional modelling, popularised by Ralph Kimball, organises analytics data into fact tables and dimension tables. The result, often called a star schema, is the shape most BI tools (Power BI, Looker, Tableau) work best with, and it is a core skill for analysts moving towards analytics engineering.

Facts and dimensions

• A fact table records events or measurements: orders, page views, payments, support tickets. Each row is one event at a defined grain, with numeric measures (quantity, revenue, duration) and foreign keys to dimensions.
• A dimension table describes the things involved: customers, products, dates, stores. Each row is one entity with descriptive attributes (name, category, country) used to filter and group.

Draw the fact table in the middle and the dimensions around it, and you get a star:

              dim_date
                 |
dim_customer - fact_sales - dim_product
                 |
             dim_store

Step 1: Declare the grain

The grain is the single most important decision: what does one row of the fact table represent? "One row per order line" (a product within an order) is different from "one row per order". Mixing grains in one table leads to double counting. Write the grain in the table's documentation.

Step 2: Build dimensions with surrogate keys

A surrogate key is a simple integer assigned by the warehouse (customer_key = 1, 2, 3...) instead of the source system's natural key (email, SKU). Surrogate keys keep joins fast, protect you when source keys change (a customer changes their email), and allow history tracking.

CREATE TABLE dim_customer (
  customer_key   INTEGER PRIMARY KEY,
  customer_email TEXT UNIQUE,
  customer_name  TEXT,
  country        TEXT
);
INSERT INTO dim_customer (customer_email, customer_name, country)
SELECT DISTINCT customer_email, customer_name, country
FROM raw_orders
ORDER BY customer_email;

In SQLite, an INTEGER PRIMARY KEY column is filled automatically with 1, 2, 3... in insert order. Warehouses use sequences, identity columns or hashes of the natural key.

Step 3: Build the fact table

The fact table looks up each dimension's key and keeps the measures:

CREATE TABLE fact_sales AS
SELECT r.order_id,
       r.order_date,
       c.customer_key,
       p.product_key,
       r.quantity,
       r.quantity * r.unit_price AS revenue
FROM raw_orders r
JOIN dim_customer c ON c.customer_email = r.customer_email
JOIN dim_product  p ON p.product_sku    = r.product_sku;

Now an analysis query is short and hard to get wrong:

SELECT p.category, c.country, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product  p USING (product_key)
JOIN dim_customer c USING (customer_key)
GROUP BY p.category, c.country;

A date dimension

Most warehouses include dim_date: one row per calendar day with columns such as year, quarter, month name, week number, weekday, holiday flag and fiscal period. It saves every analyst rewriting date logic and keeps fiscal calendars consistent.

Slowly changing dimensions

Attributes change: a customer moves country, a product changes category. You must decide:

• Type 1: overwrite the old value. Simple, but history is restated (last year's sales now appear under the new country).
• Type 2: add a new row with a new surrogate key and valid_from / valid_to dates, so old facts keep pointing at the old version. More work, but history stays accurate.

Choose per attribute, based on whether historical reports should change.

Common mistakes

• Undeclared or mixed grain, causing double counting when joining.
• Measures in dimensions (putting total revenue on the customer row) or descriptive text in facts.
• Joining facts to facts directly at different grains. Aggregate each to a common grain first.
• Natural keys everywhere, which break when the source changes.
• Dimensions with duplicate keys, which silently multiply fact rows in joins. Test uniqueness (lesson 3).

Quick recap

• Facts are events with measures at a declared grain; dimensions describe entities.
• Dimensions get surrogate keys; facts store those keys plus numeric measures.
• A star schema makes analysis queries short and consistent and suits BI tools.
• Decide how to handle changing attributes (Type 1 vs Type 2).

Try it

In the Do tab, turn a wide raw export into a product dimension and a fact table that uses surrogate keys.