Fact Tables vs Dimension Tables Explained

Why this comes up so often

If you have read anything about star schema, you already ran into the terms fact table and dimension table without maybe fully sitting with what they mean. This one is worth slowing down on, because almost every reporting model you build on Azure, whether it is in Synapse or in the Gold layer of a Databricks lakehouse, is going to be structured around this exact split.

The short version is this. A fact table stores what happened. A dimension table stores who, what, where, and when it happened to.

Let's go slower than that though, because the short version alone does not stick.

Fact tables

A fact table stores measurable events, things you can count, sum, or average. Every row usually represents a single transaction or occurrence, like one sale, one shipment, one login, one support ticket.

Take a retail sales example. A fact table for sales might look like this.

Notice two things about this table. First, most of the columns are just IDs, referencing other tables. Second, the columns that are not IDs, like quantity and sales_amount, are numbers you would actually want to aggregate. That is the whole identity of a fact table. It is mostly foreign keys plus a few measurable numeric values.

A fact table for sales in Azure Synapse or a Delta table would typically be created something like this.

CREATE TABLE Fact_Sales (
    sale_id BIGINT,
    date_id INT,
    customer_id INT,
    product_id INT,
    store_id INT,
    quantity INT,
    sales_amount DECIMAL(10,2)
);

Fact tables also tend to be the largest tables in a warehouse by row count, since every single transaction adds a new row. A busy retail company could easily generate millions of rows a day in a table like this.

Dimension tables

A dimension table stores the descriptive context around a fact. It answers who, what, where, and when, in a way that is meant to be read and understood by a human, not just referenced by ID.

Using the same example, here is what Dim_Product might look like.

And Dim_Customer.

Dimension tables are usually much smaller than fact tables in row count. You might have millions of sales rows but only a few thousand customers or a few hundred products. They are also the tables people actually filter and group by when writing a report, like "show me sales by category" or "show me revenue by city."

A dimension table in Synapse would typically look like this.

CREATE TABLE Dim_Product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    brand VARCHAR(50)
);

How to tell them apart when you are not sure

A trick that helps early on is asking yourself two questions about any piece of data you are modeling.

• Can you meaningfully sum, count, or average this value across many rows. If yes, it probably belongs in a fact table. Sales amount, quantity, duration, all things you would want totaled up.
• Is this describing a real world entity that has properties someone would want to filter or group by. If yes, it probably belongs in a dimension table. Customer name, product category, store location, all things people use to slice a report, not add up.
• There is one detail that trips people up early on. Numeric looking values are not automatically facts. A product's price per unit sitting in Dim_Product is fine as a dimension attribute if you are not aggregating it directly, but the actual sales_amount tied to a specific transaction belongs in the fact table, because that is the number you actually want summed across millions of rows.

How this comes together in a query

This is really where the split earns its keep. Once your fact and dimension tables are set up properly, reporting queries become almost mechanical.

SELECT
    p.category,
    c.city,
    SUM(f.sales_amount) AS total_sales
FROM Fact_Sales f
JOIN Dim_Product p ON f.product_id = p.product_id
JOIN Dim_Customer c ON f.customer_id = c.customer_id
GROUP BY p.category, c.city;

You are joining the fact table to whichever dimensions you need context from, then aggregating the numeric fact column. This pattern barely changes no matter how complex the actual business question gets, which is exactly why this modeling approach has stuck around for decades.

Where this fits in an Azure pipeline

In a typical Medallion Architecture setup using Azure Databricks and Delta Lake, the Bronze and Silver layers usually hold data closer to its raw form, not yet split into this fact and dimension structure. It is really in the Gold layer, right before the data is handed off to Power BI or Azure Synapse for reporting, that data gets organized into proper fact and dimension tables.

So if someone asks where in the pipeline you would design fact and dimension tables, the answer is at the Gold layer, once the data has already been cleaned and is ready for consumption by business users.

Interview angle

A common follow up question after explaining fact versus dimension tables is why fact tables tend to be so much larger than dimension tables. The answer is straightforward, every single transaction or event generates a new fact row, while the entities being described, like customers or products, change far less often and get reused across many facts.

Another common question is whether a table can ever behave as both. It can, in specific cases. A table like Dim_Date is sometimes treated a bit differently since it is generated in advance and referenced constantly, but it still functions as a dimension since you are not aggregating its columns, you are using them to group and filter.

Quick recap

A fact table stores measurable events, mostly foreign keys plus a handful of numeric values you want to aggregate, and tends to be the largest table in a warehouse. A dimension table stores descriptive details about the entities involved, like customers, products, or stores, and is what people use to filter and group their reports. Together, they form the backbone of a star schema, and in an Azure pipeline, this structure typically gets built out in the Gold layer, right before the data reaches reporting tools like Power BI or Azure Synapse.