Fact Tables vs Dimension Tables: Keys, Measures, Descriptors, Width, and Growth Behavior

Learning outcomes

A fact table and a dimension table may both contain integers, strings, dates, and keys, so physical data type does not determine role. Their roles come from the business process and grain: facts record measurement events/states; dimensions provide the descriptive context used to group, filter, label, and navigate those measurements.

1. Fact tables record measurements at a declared grain

fact_sales has one row for each paid order line. Its foreign keys identify dimensional context; order_id and line_no retain transaction identity; quantity and extended_amount are measurements true to that line grain. The table grows when additional qualifying sales events occur.

The optional sales_key in this lab is an ETL row identifier. It is not a business dimension and is not what makes the table a fact table. The grain and measurement role do.

2. Dimensions carry durable descriptive context

Dimensions are intentionally descriptive. dim_customer contains customer name, segment, and region; dim_product contains product name and category. Those attributes are useful because analysts can filter/group facts with labels rather than operational IDs.

3. Width and growth are tendencies, not definitions

Fact tables commonly become tall because every measurement event creates a row, while dimensions are often much smaller in row count but wider with descriptive attributes. Those are useful operational tendencies, not definitions. A very wide transaction fact is still a fact if it records one event per row; a very narrow code dimension is still a dimension if it supplies descriptive context.

Likewise, “numeric = fact” is wrong. customer_key, product_key, date_key, postal codes, and identifiers may be numeric without being measurements. Conversely, a measurement arriving as text still has measurement semantics after validated conversion.

4. Inspect the schema rather than relying on folklore

SELECT 'fact_sales' AS object, COUNT(*) AS rows FROM fact_salesUNION ALL SELECT 'dim_customer', COUNT(*) FROM dim_customerUNION ALL SELECT 'dim_product', COUNT(*) FROM dim_productUNION ALL SELECT 'dim_store', COUNT(*) FROM dim_storeUNION ALL SELECT 'dim_date', COUNT(*) FROM dim_date;PRAGMA table_info(fact_sales);PRAGMA table_info(dim_customer);

For this tiny fixture the fact has 7 rows, customer and product dimensions each have 5 rows including the Unknown member, store has 4, and date has 3. Those counts are intentionally tiny and say nothing about production scale; they simply expose the different table roles and the explicit unknown-member rows.

5. Hands-on lab — classify tables and columns by semantics

Reuse atlasmart_star.sql from Lesson 1 and save this as inspect_roles.py.

import sqlite3from pathlib import Pathconn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")conn.executescript(Path("atlasmart_star.sql").read_text())roles={ "fact_sales":{"row_role":"measurement_event","business_key":"order_id+line_no"}, "dim_customer":{"row_role":"descriptive_entity","business_key":"customer_id"}, "dim_product":{"row_role":"descriptive_entity","business_key":"product_id"}, "dim_store":{"row_role":"descriptive_entity","business_key":"store_id"}, "dim_date":{"row_role":"descriptive_calendar","business_key":"full_date"},}for table, meta in roles.items():    count=conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]    print(table, count, meta)assert conn.execute("SELECT COUNT(*) FROM fact_sales").fetchone()[0] == 7# Numeric keys are context, not measurements.cols=[r[1] for r in conn.execute("PRAGMA table_info(fact_sales)")]assert "customer_key" in cols and "extended_amount" in colsprint("numeric-key != numeric-measure assertion: PASS")

The script prints each object's row count and semantic role, then asserts that a numeric context key and a numeric measurement coexist in the fact. Extend the dictionary with unit_price and label it rate_measure, then explain why summing unit prices across lines is not a meaningful business total.

Cleanup: remove the disposable SQL and Python files.

6. Controlled failure: “put every numeric field in the fact”

If a modeler classifies columns from SQL types, customer_key, product_key, date_key, line_no, and sales_key may all be treated like measures. A BI tool may then offer meaningless sums such as “sum of customer key.” At the same time, important descriptors such as customer segment can be buried in the fact simply because they are strings.

The repair is to start from grain and ask whether a value is a measurement of that event/state or descriptive context of it. Then record permitted aggregation behavior explicitly.

7. Production judgment and bridge

Fact/dimension separation should optimize semantic clarity and governance first. A physical engine may later choose columnar storage, denormalized caches, materialized views, or optimizer-specific transformations underneath or beside the logical star. Those structures do not change the grain contract of the certified model.

The next lesson makes the key boundary explicit: natural/business keys resolve incoming source entities; warehouse surrogate keys join facts to dimension versions; referential integrity and unknown members prevent silent row loss.

Knowledge check

Summary and next step

AtlasMart now has explicit table roles. Fact rows grow with paid sales events; dimensions describe reusable business context; keys and identifiers are not measurements merely because they are numeric.

Next: resolve every incoming natural key to a durable surrogate key and handle missing context without losing facts.

Authoritative references

• Kimball Group — Dimensional Modeling Techniques — Primary index for facts, dimensions, star schemas, grain, surrogate keys, and related dimensional techniques.
• Kimball Group — Fact Tables and Dimension Tables — Explains fact foreign keys, dimension primary keys, referential integrity, unknown members, and degenerate dimensions.
• Kimball Group — Dimension Surrogate Keys — Explains why warehouse-controlled surrogate keys decouple dimensions from operational natural keys.
• Kimball Group — Nulls in Fact Tables — Explains why fact foreign keys should resolve to explicit default dimension members instead of null.
• Kimball Group — Declaring the Grain — Reinforces that grain precedes dimensions and measured facts.
• Python documentation — sqlite3 — Standard-library execution harness used for deterministic local SQL verification.