What is a Dimension Table?
A Dimension Table is a core component of Dimensional Modeling and the Star Schema. While the central Fact Table records the raw numeric measures of a business (e.g., $150.00 Revenue, 2 Items Sold), a Dimension Table provides the descriptive context surrounding those numbers. Dimension tables answer the “Who, What, Where, When, and Why” of the business transaction.
If an executive views a dashboard displaying the raw number 10,000,000, the number means little without context. A Dimension table provides the ability to “slice and dice” that number. By joining the Fact table to the Dimension tables, an analyst can filter the $10,000,000 down into actionable insights, such as: “Total Revenue generated by Female Customers (Who) purchasing Winter Boots (What) in retail stores located in Germany (Where) during Q3 (When).”
The Anatomy of a Dimension Table
Dimension tables have a different physical footprint than Fact tables. While a Fact table is deep (often millions or billions of rows) and narrow (mostly keys and numbers), a Dimension table is relatively shallow but wide (often containing dozens of columns).
The Primary Key
Every Dimension table is anchored by a unique Primary Key. In Kimball-style data warehousing, this is usually a meaningless integer known as a Surrogate Key (e.g., 1045). This Surrogate Key is what the Fact table uses to link the transaction to the descriptive context.
Descriptive Attributes (The Text)
Most of a Dimension table consists of descriptive text columns. For a Product_Dimension table, the attributes might include:
Product_Name(“Air Max Running Shoe”)Brand(“Nike”)Category(“Footwear”)Sub_Category(“Athletic”)Color(“Red”)
These text attributes are what populate the dropdown filters in a business intelligence tool like Tableau. When a user selects “Brand: Nike” from a dropdown, the SQL engine filters the Product_Dimension table for the word “Nike”, finds all the associated Surrogate Keys, and uses those keys to filter the Fact table and compute the final numbers.
Denormalization and Query Speed
In traditional operational databases (OLTP), data is normalized. The “Brand” would be stored in a separate table from the “Product” to avoid redundancy and update anomalies.
Dimension tables intentionally break this rule. They are Denormalized. The Brand, the Category, and the Color are all flattened into a single wide table.
While this duplicates the word “Nike” many times in storage, it speeds up analytical queries (OLAP). Because all the descriptive context exists in a single table, the query engine does not need several extra JOIN operations to reconstruct the product profile. In distributed engines it can often broadcast the small dimension table to every worker and run a fast hash join.
Handling Historical Change (SCD)
A hard engineering challenge with Dimension tables is handling historical changes. If a product is rebranded, or a customer moves to a new city, the data engineer cannot simply overwrite the old text, or historical reports will change retroactively.
Data engineers manage this via Slowly Changing Dimensions (SCD), most notably SCD Type 2. They preserve the old row, mark it as inactive using explicit Start_Date and End_Date timestamp columns, and insert a new row with a new Surrogate Key to track the updated descriptive context going forward, preserving accurate history.
Summary of Technical Value
Dimension tables are the translation layer between raw numbers and human business intuition. By providing denormalized, descriptive text about the entities involved in enterprise transactions, they let analysts filter, group, and segment large datasets with simple SQL, and they sit behind most executive dashboards.
Learn More
To learn more about the Data Lakehouse, read The Lakehouse Built for Everyone by Alex Merced. You can find this and other books by Alex Merced at books.alexmerced.com.