Fact and Dimension Tables: How They Join, With a Real Example

Every data warehouse conversation eventually lands on the same two words: fact and dimension. The concept is simple once it clicks, one table holds the numbers, the other tables describe the context around those numbers, but most explanations stay abstract enough that it never quite clicks. This one uses a single running example: a retail chain's sales data, built table by table, queried, and joined for real.

Fact vs. Dimension, in One Table

	Fact table	Dimension table
What it stores	Measurements/events: amounts, quantities, counts	Descriptive context: names, categories, attributes
Typical size	Large, grows with every transaction	Small to medium, grows slowly
Keys	Mostly foreign keys pointing at dimensions	One surrogate primary key per row
Changes over time	Rows are appended, rarely updated	Rows are updated as attributes change (see Slowly Changing Dimensions)
Example in this article	`FactSales`, one row per sold order line	`DimDate`, `DimProduct`, `DimCustomer`, `DimStore`

Grain: The One Decision Everything Else Depends On

Before any column is named, decide what one row in the fact table represents, that's the grain. "One row per order line" and "one row per order" sound similar and behave completely differently: the first lets you analyze individual products within an order, the second collapses that detail into an order total you can never get back. Get the grain wrong and every report built on top inherits the mistake; there's no query that reconstructs detail that was never stored.

For this example, the grain is: one row per product sold, per order, per store. That's specific enough to answer questions at the product level while still rolling up cleanly to order, store, month, or region totals.

The Schema

CREATE TABLE dbo.DimProduct
(
    ProductKey    INT           NOT NULL PRIMARY KEY,
    ProductName   VARCHAR(100)  NOT NULL,
    Category      VARCHAR(50)   NOT NULL,
    Subcategory   VARCHAR(50)   NOT NULL
);

CREATE TABLE dbo.DimCustomer
(
    CustomerKey   INT           NOT NULL PRIMARY KEY,
    CustomerName  VARCHAR(100)  NOT NULL,
    Segment       VARCHAR(30)   NOT NULL   -- Retail, Wholesale, Online
);

CREATE TABLE dbo.DimStore
(
    StoreKey      INT           NOT NULL PRIMARY KEY,
    StoreName     VARCHAR(50)   NOT NULL,
    Region        VARCHAR(50)   NOT NULL
);

-- DimDate: see "How to Build a Date Dimension Table"

CREATE TABLE dbo.FactSales
(
    DateKey           INT     NOT NULL REFERENCES dbo.DimDate(DateKey),
    ProductKey        INT     NOT NULL REFERENCES dbo.DimProduct(ProductKey),
    CustomerKey       INT     NOT NULL REFERENCES dbo.DimCustomer(CustomerKey),
    StoreKey          INT     NOT NULL REFERENCES dbo.DimStore(StoreKey),
    OrderLineNumber   INT     NOT NULL,
    Quantity          INT     NOT NULL,
    UnitPrice         MONEY   NOT NULL,
    DiscountAmount    MONEY   NOT NULL DEFAULT (0),
    SalesAmount       MONEY   NOT NULL   -- Quantity * UnitPrice - DiscountAmount
);
CREATE INDEX IX_FactSales_DateKey    ON dbo.FactSales(DateKey);
CREATE INDEX IX_FactSales_ProductKey ON dbo.FactSales(ProductKey);
CREATE INDEX IX_FactSales_StoreKey   ON dbo.FactSales(StoreKey);

Notice the fact table's own columns: only foreign keys, a line number, and numbers. No product name, no store name, nothing descriptive lives here, that's what the joins are for.

A Real Business Question

The question: "What was total sales amount and quantity sold, by month and product category, for stores in the North region during 2026?"

SELECT
    d.CalendarYear,
    d.MonthName,
    p.Category,
    SUM(f.SalesAmount)  AS TotalSalesAmount,
    SUM(f.Quantity)     AS TotalQuantity
FROM dbo.FactSales AS f
INNER JOIN dbo.DimDate    AS d ON f.DateKey    = d.DateKey
INNER JOIN dbo.DimProduct AS p ON f.ProductKey = p.ProductKey
INNER JOIN dbo.DimStore   AS s ON f.StoreKey   = s.StoreKey
WHERE d.CalendarYear = 2026
  AND s.Region = 'North'
GROUP BY d.CalendarYear, d.MonthName, d.MonthNumber, p.Category
ORDER BY d.MonthNumber, p.Category;

One fact table, three dimension joins (a fourth, DimCustomer, simply isn't needed for this particular question, and isn't included), a filter on two different dimensions, and a business-meaningful grouping. This is the entire point of the model: the question was asked in business language (region, category, month) and answered without a single line of application code.

CalendarYear	MonthName	Category	TotalSalesAmount	TotalQuantity
2026	January	Beverages	48,210.50	3,912
2026	January	Snacks	21,340.00	2,105
2026	February	Beverages	52,880.75	4,203
2026	February	Snacks	19,875.25	1,980

Architecture at a Glance

Types of Facts

Additive, safe to sum across every dimension: SalesAmount and Quantity here. Sum by day, by month, by region, all valid.
Semi-additive, safe to sum across most dimensions but not time: an account balance or an on-hand inventory snapshot can be summed across stores at a point in time, but summing across days produces nonsense (you'd be adding the same stock levels to themselves).
Non-additive, never safe to sum: a discount percentage, a margin ratio, a unit price. These need to be recalculated from their additive components (SUM(DiscountAmount) / SUM(SalesAmount)), never summed directly.

Common Gotchas

Grain mismatches causing silent double-counting. Joining a fact table at order-line grain to a dimension that isn't unique per key (a customer dimension with duplicate rows for the same customer, for instance) multiplies every measure in the result. Always know the grain of both sides of a join.
Missing "Unknown" dimension rows. A fact row with a NULL-worthy foreign key (unresolved customer, data quality gap) needs to point at an explicit unknown member, exactly the pattern covered in How to Build a Date Dimension Table, not a NULL that turns every inner join into a silent row-dropping filter.
The fan trap. Joining a fact table to two dimensions that both fan out into a many-to-many relationship with each other (not just with the fact) inflates sums. This is why DimCustomer was deliberately left out of the example query above, it wasn't needed, and adding unnecessary joins is how fan traps sneak in.
Forgetting dimension attributes change. If DimProduct.Category gets reassigned next quarter, historical fact rows need to either keep pointing at the category that was true when the sale happened (Type 2) or reflect the new category everywhere (Type 1), that decision belongs in the dimension design, not something the fact table can fix after the fact.
Filtering on unindexed dimension columns. The join columns themselves are indexed above, but if WHERE clauses regularly filter on s.Region or p.Category, index those dimension columns too, they're small tables, the index cost is negligible.

The Bottom Line

A fact table is just numbers and foreign keys; the dimensions around it carry all the meaning. Decide the grain before anything else, keep the fact table free of descriptive columns, and every business question becomes the same shape of query: join the fact to whichever dimensions the question actually mentions, filter, group, sum. The retail example here is deliberately small, four dimensions, one fact table, but the pattern scales to warehouses with dozens of fact tables without changing.

← Back to Blog

Fact and Dimension Tables: How They Join Together, With a Real-World Example