Fact Table vs Dimension Table

A fact table holds 4 kinds of column. Dimension keys, 1 per dimension, each a foreign key to a dimension's surrogate key. Measures, the numbers the event produced: quantity and extended_amount, for example, or discount_amount. Degenerate dimensions, identifiers such as an order or invoice number that belong to the event but have no attributes worth a table of their own. And, optionally, audit columns that record which load wrote or changed the row.

Everything in the table must be true at the declared grain. Quantity belongs on an order line fact because each order line has exactly 1 quantity, and the same goes for extended price. The store manager's salary does not, even though every sale happens in a store with a manager: it'sn't a measurement of the order line, and summing it across lines would multiply it by the number of lines. Kimball's rule is that only facts consistent with the declared grain are allowed.

Fact tables rarely declare a primary key. Microsoft's Fabric warehouse guidance leaves it off because the combination of dimension keys and degenerate identifiers already implies 1, and an extra key only adds storage. What must hold is that the grain is unique: 2 rows for the same order line is a double count waiting to happen. Nearly every fact table also carries at least 1 date key, since almost every event happens on a day.

Choosing the grain comes before choosing the columns. The first 2 steps of dimensional modeling are to pick the business process and declare its grain, and the atomic grain, the lowest level the source system records, is the safe default because a detailed table can always be rolled up and a summary can never be split back down.

A dimension table holds 1 row per member of a business entity: a customer, a product, a store, a calendar day. Its columns are the words people use in reports: customer_name, city, segment, brand, category, fiscal_quarter, is_holiday. The more descriptive attributes a dimension carries, the more ways a fact table can be sliced, which is why dimensions are wide and why Kimball calls them the soul of the warehouse.

Every dimension row has a surrogate key, a meaningless integer the warehouse assigns, beside the natural key the source system uses, such as customer_id C-1042 or a SKU. Hierarchies are usually flattened into the same row: each product row in dim_product also carries its subcategory and category, so a report can drill from 1 level to the next without extra joins. The date dimension is the common exception to the surrogate rule, since a key such as 20260927 is stable and readable.

Dimensions also hold special members for the cases the facts cannot fill: an Unknown customer for an anonymous sale, a Not applicable ship date for an order that hasn't shipped. Pointing a fact at 1 of these rows keeps every foreign key NOT NULL, so inner joins never silently drop events.

Each foreign key on the fact row holds the surrogate key of exactly 1 dimension row, so many fact rows point to 1 dimension row. That many to one direction is what makes a table a fact or a dimension: Microsoft's star schema guidance notes that no table property sets the type, and that in a 1 to many relationship the one side is always the dimension and the many side always the fact. Draw the fact in the middle with its dimensions around it and you have a star schema.

A typical query joins the fact to the dimensions it needs and aggregates the measures, using dimension attributes to filter and group: SELECT d.fiscal_quarter, p.category, SUM(f.extended_amount) FROM fact_order_line f JOIN dim_date d ON f.date_key = d.date_key JOIN dim_product p ON f.product_key = p.product_key GROUP BY 1, 2. Every column in the select list is either a dimension attribute or an aggregate of a fact, which is a quick way to check a query against the model.

1 dimension can play several roles. An order line with an order date and a ship date carries 2 keys to the same dim_date, order_date_key and ship_date_key. Which role a query sees depends on the join column. Analysts often expose each role as its own view with renamed columns, so a report never confuses order year with ship year.

Ask what the column describes. If it's a measurement of the event at the fact table's grain, it's a fact. If it describes an entity involved in the event, and would be the same no matter which event you looked at, it's a dimension attribute. quantity on an order line describes that line; brand describes the product whatever order it appears on.

Then ask how it will be used. A value that mostly gets summed or averaged, or used as a divisor, belongs in the fact table. A stable value that mostly labels rows, or gets filtered and grouped on, belongs in a dimension. Kimball's guidance on numeric values uses the product's standard list price as the hard case: it is a number, but if analysts mostly ask for products priced over $100, it is an attribute, often paired with a price band column such as $50 to $100.

Price shows why some values live in both places. The price paid on a given order line is net of discounts and promotions and varies by event, so it is a fact. The current list price describes the product and is a dimension attribute. Keeping both lets a report compare what customers paid with what the catalogue asked.

How fast the value changes matters too. An attribute that changes on most events, such as a stock's market price, is really a measurement and is stored as a fact. An attribute that changes rarely, such as a customer's city, stays in the dimension and is versioned when history matters. Text is not a test: a text value captured with the event, such as a cancellation reason, is often best modelled as a small dimension, and a numeric value such as a store's floor area is a dimension attribute.

Not every fact can be summed along every dimension, and knowing which can is what keeps totals right. An additive fact sums across every dimension of the table: day, product, store, customer. extended_amount and quantity both work this way. It's the most useful kind, and a model should store facts in additive form wherever it can. Revenue is additive only in 1 currency; a fact with a currency key can't be summed across currencies.

A semi-additive fact sums across some dimensions but not time. Balances are the classic case: an inventory value or an account balance can be added across products or stores on 1 day, but adding Monday's balance to Tuesday's counts the same stock twice. Across dates, take the balance at the end of the period or the average of the daily balances.

A non-additive fact can't be summed at all: unit prices, ratios, percentages, temperatures. Store its additive parts instead and compute the ratio at query time. A discount rate is better stored as discount_amount beside extended_amount, so a quarter's rate is SUM(discount_amount) / SUM(extended_amount), weighted correctly, rather than an average of line rates that treats a $5 line like a $5,000 one.

A factless fact table has no measures at all, only dimension keys. Each row records that some entities came together: a student attended a class on a date, a customer signed in, a product was on promotion in a store for a week. It's still a fact table, because each row is an event at a grain, and it's measured by counting rows. Paired with a coverage table of what could have happened, it answers what did not: products on promotion that sold nothing, students enrolled who never attended.

A degenerate dimension is the reverse case: a dimension key with no dimension table. The order number on an order line identifies which lines were bought together. Analysts filter and group by it, but every descriptive fact about the order already lives in other dimensions. It stays on the fact table as a plain column.

Tables that look like both usually come from mixing grains. An order header with a total and a customer, joined to order lines, looks like a dimension to the lines and a fact to the customer. The standard fix keeps the line as the fact, pushes header level amounts such as shipping cost down to lines by allocation, and keeps the order number as a degenerate dimension. A bridge table between 2 dimensions, such as salespeople assigned to several regions, is a factless fact in structure even though it's used like part of a dimension.

Interviewers rarely ask for the definition on its own. They hand over a business process, such as rides or subscriptions, and ask for a schema. Ad impressions come up as well. The fact vs dimension split is tested through the answer: whether the grain is stated before any column, whether measures sit at that grain, whether a balance is summed across days, whether history survives a customer's move.

The strongest answers say the grain out loud in 1 sentence, name the fact type it implies, and then place each column with a reason. The data modeling interview questions are drawn from real interviews and cover these follow ups. The fact table interview questions go deeper on grain and additivity, and on snapshots. In the data modeling practice problems you build the tables for a real prompt. The data modeling interview round guide explains how that round runs and what interviewers look for.