In a dimensional model, does unit_price belong in the fact table or the product dimension?

## The question behind the question Every dimensional model hits columns that are numeric but arguably descriptive: unit price, product weight, customer income, contract length, store square footage. Junior instinct says "numeric means fact". That instinct is wrong often enough to be worth a test. ## The usage test Ask what a query does with the column: - If it is aggregated across many rows of the business process — summed, averaged, used in arithmetic with other measures — it is a **fact** and lives on the fact row. - If it is used to constrain rows, group results, or label a report — a filter, a `GROUP BY`, an axis — it is a **dimension attribute**. The test works because it asks about the column's role, not its storage class. A `VARCHAR` order number can behave like a fact-side identifier; a `DECIMAL` weight can behave like a pure descriptor. ## When a price is a fact The price charged on a specific transaction is produced by that transaction. It differs between two sales of the same product because of a promotion, a negotiated rate, a currency, or a price change last Tuesday. It participates in arithmetic with the other measures on the row: `quantity * unit_price_at_sale - discount_amount = extended_amount`. All of that is fact behaviour, so it belongs on the fact table — and storing it explicitly means the transaction can be reconstructed and audited without reasoning about which price was in effect. ## When a price is a dimension attribute The product's list price describes the product, not the event. Analysts want to say "show me units sold for products with a list price above $50", or "break revenue down by price tier". Those are filtering and grouping operations, so the value belongs in the product dimension. Because continuous values group poorly — a thousand distinct prices make a useless report axis — dimension designers usually add a banded companion attribute: `price_band` with values like `Under $10`, `$10–$25`, `$25–$100`, `Over $100`. The band is what most reports actually group by, and the raw value stays for precise filters. ```sql CREATE TABLE dim_product ( product_key BIGINT PRIMARY KEY, product_name VARCHAR(200), list_price DECIMAL(18,2), -- descriptor: filter and compare price_band VARCHAR(20) -- banded companion for grouping ); ``` ## Storing it in both places is normal Having `list_price` on the dimension and `unit_price_at_sale` on the fact is not redundancy to be eliminated — the two answer different questions, and the gap between them is itself a metric (discount depth versus list). The mistake would be storing the *same* value in both places and letting them drift; storing two genuinely different values with clear names is good design. Name them so that no one has to guess: `unit_price_at_sale` versus `list_price` is self-documenting in a way that two columns both called `price` never is. ## The averaging trap A per-unit price stored on a fact row is a rate, and rates do not add up. `SUM(unit_price_at_sale)` is meaningless, and `AVG(unit_price_at_sale)` gives the average of the rows rather than the average price actually realised, because it weights a one-unit sale the same as a thousand-unit sale. The correct computation reconstructs it from additive components: ```sql SELECT SUM(extended_amount) / NULLIF(SUM(quantity), 0) AS avg_realised_price FROM fact_sales_line; ``` This is why fact tables prefer storing the fully-extended amount and the quantity: both add up cleanly, and any rate you want can be derived from them at any level of aggregation. ## Other columns that sit on the boundary - **Product weight** — a descriptor of the product, so a dimension attribute; but the *shipped weight* of a specific carton is measured by the shipment and is a fact. - **Customer age or income** — descriptors, and almost always banded into ranges in the dimension because grouping by raw age is unusable. - **Contract length in months** — a descriptor of the contract; you group by it far more often than you sum it. - **Days elapsed between two events** — measured by the process for that row, so a fact, even though it looks like an attribute. ## A working rule If you would put it on the axis of a chart, it is an attribute. If it is the height of the bars, it is a fact. When it is genuinely both, store both, name them distinctly, and record in the model documentation which one answers which question — the ambiguity, not the duplication, is what causes wrong numbers. ## What an interviewer listens for They are checking whether you classify by data type or by usage. The strong answer states the test, applies it to a specific column, and volunteers that both forms often coexist — and a very strong answer adds why you never average a stored per-unit rate.