In a dimensional model, what distinguishes a fact table from a dimension table?
answer
- two table types, two different jobs
- long and narrow versus short and wide
- SUM these, GROUP BY those
- measurements plus the context describing them
- foreign keys sit on the measurement side
basics
~20 sA fact table holds the numeric measurements of a business process, one row per event at a declared grain, plus foreign keys to dimensions. A dimension table holds the descriptive attributes you filter, group and label by.
solid answer
~40 sA dimensional model splits a business process into measurements and context. The **fact table** is long and narrow: one row per occurrence of the process at a declared level of detail, holding numeric measures such as `quantity` and `sales_amount`, plus one foreign key per dimension that describes the row. The **dimension tables** are short and wide: one row per product, store, customer or calendar day, holding mostly textual descriptive attributes — brand, category, region, day-of-week — that queries constrain and group by. The practical test is how SQL touches a column: what you `SUM` or `AVG` is a fact, what appears in `WHERE`, `GROUP BY` or as a report label is a dimension attribute. Fact tables carry nearly all the volume; dimensions carry nearly all the vocabulary the business actually reads.
code
sql · 17 linesCREATE TABLE dim_product (
product_key BIGINT PRIMARY KEY,
product_id VARCHAR(40),
product_name VARCHAR(200),
brand VARCHAR(100),
category VARCHAR(100),
department VARCHAR(100)
);
CREATE TABLE fact_sales_line (
date_key INTEGER NOT NULL,
store_key BIGINT NOT NULL,
product_key BIGINT NOT NULL,
quantity INTEGER NOT NULL,
sales_amount DECIMAL(18,2) NOT NULL,
discount_amount DECIMAL(18,2) NOT NULL
);go deeper
Be ready to name the two table types and give an example column of each: sales_amount is a fact, product_category is a dimension attribute. Say which table the foreign keys live on.
Explain the classification test by usage — aggregated versus filtered and grouped — and why fact tables end up long and narrow while dimensions end up short and wide.
Show judgment on awkward columns and volume: which descriptive data belongs in a dimension so it can be corrected cheaply, and why verbose text is kept off a billion-row fact table.
Own the modelling standard: how the fact/dimension split gives the business a stable vocabulary across processes, and what it costs when teams put descriptive text on fact rows to skip a join.
## The split every dimensional model starts from A dimensional model describes one business process — retail sales, insurance claims, web orders, support tickets — with exactly two kinds of table. One records the measurements the process produces; the other records the context that gives those measurements meaning. Nearly everything else in dimensional modelling is a refinement of that split. ## The fact table: measurements at a grain A fact table contains one row for each occurrence of the business process at a stated level of detail, called its grain — for example, "one row per line on a customer order". Each row carries two kinds of column: - **Measures (the facts)**: numeric values produced by the event itself — `quantity`, `extended_amount`, `discount_amount`, `cost_amount`. These are what reports add up. - **Dimension foreign keys**: one key per dimension that describes the row — `date_key`, `product_key`, `store_key`, `customer_key`. Fact tables are long and narrow. A retail chain's sales fact may have a dozen columns and billions of rows, and virtually all of a warehouse's storage sits in fact tables. Because those columns repeat billions of times they are kept numeric and compact, and verbose descriptive text is deliberately kept out of them. ## The dimension table: context and vocabulary A dimension table contains one row per instance of a thing the business talks about — a product, a store, an employee, a calendar day — identified by a warehouse key that the fact table references. Its columns are mostly descriptive attributes: `product_name`, `brand`, `category`, `department`, `store_region`, `day_of_week`, `fiscal_period`. Dimensions are short and wide: often tens of thousands of rows and fifty to a hundred columns. They are deliberately verbose, because those attribute values become the row headers, column headers, filters and drop-downs that people see in a report. An attribute that reads `Beverages / Carbonated / Diet` is worth far more to a business user than a coded `03/12/7`, so dimension design favours spelled-out, human-readable values. ## The usage test The reliable way to classify a column is to ask how a query touches it. If it sits inside an aggregate over many rows of the process, it is a fact. If it sits in a filter, a grouping or a label, it is a dimension attribute. ```sql SELECT p.brand, d.calendar_month, SUM(f.sales_amount) FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key JOIN dim_date d ON d.date_key = f.date_key GROUP BY p.brand, d.calendar_month; ``` That shape — aggregate measures from one fact table, constrain and group by attributes from its dimensions — is the shape of nearly every warehouse query, and it is why the two table types look so different. ## Why the split pays Separating measurement from context lets each side be optimised for its job. Facts stay narrow, so scanning hundreds of millions of rows to add up a single column stays cheap. Dimensions absorb the messy, verbose, frequently-corrected descriptive data in one place, so relabelling a product category is an update to a few thousand rows rather than a rewrite of billions. The split also gives the business a stable mental model: choose a process, choose the measures, slice by whatever attributes exist on the dimensions attached to it. ## Edges people get wrong - **A numeric column is not automatically a fact.** A product's shelf weight or a customer's income band is numeric but descriptive: you group by it, you do not add it across the business process. - **A text column is not automatically a dimension table.** Some identifiers, such as an order number, live directly on the fact row because they have no attributes worth describing. - **Dimensions are not always small.** A customer dimension can hold hundreds of millions of rows and is still a dimension, because it describes rather than measures. - **The foreign keys live on the fact side.** Dimensions do not point at facts; the fact row assembles the context by referencing one key per dimension. - **A fact table without a stated grain is not finished.** Two people can read the same columns and disagree about what one row means, and every aggregate they write will then disagree too. ## What an interviewer is really checking Almost no one fails on the definition. What separates answers is whether you attach a grain sentence to the fact table without being asked — "the fact table holds one row per X" — and whether you can classify an awkward column by asking how it is used rather than by looking at its data type.
- Can a dimension table legitimately contain numeric columns?Yes. A product's weight, a customer's income band, a store's square footage are all numeric but descriptive: they are used to filter and group, not summed across the business process. The classification comes from how the column is used, not from its data type. Continuous descriptors are often also stored as a banded text attribute so reports can group by them.
- Why does the fact table store dimension foreign keys instead of the descriptive attributes themselves?Volume and maintenance. Attribute text repeated across billions of fact rows is expensive to store and impossible to correct cheaply; keeping it in a dimension means a relabelling touches a few thousand rows. It also gives every fact table one shared, consistent set of labels rather than its own drifting copy.
- Looking only at a fact table's columns, can you tell its grain?You can usually infer a candidate grain from the set of dimension foreign keys, but inference is not the same as a declaration. Only the documented business statement — 'one row per order line' — is authoritative, and data can silently violate the grain you inferred. Verify with a uniqueness query over the columns you believe define it.
saying these in an interview costs you the question
- Says fact tables hold text and dimensions hold numbers
- Assumes any numeric column must be a fact
- Thinks a large table cannot be a dimension
- Cannot state what a single fact row represents
- Puts dimension foreign keys on the dimension side