Facts And Dimensions In Data Warehouse

Understanding the architecture of a data warehouse begins with mastering two fundamental building blocks: facts and dimensions. These components form the backbone of dimensional modeling, a design technique pioneered by Ralph Kimball that prioritizes understandability and query performance. Whether you are a business analyst building dashboards, a data engineer designing ETL pipelines, or a decision-maker relying on business intelligence, a deep grasp of how facts and dimensions interact is essential for turning raw data into actionable insights.

The Core Philosophy: Dimensional Modeling

Before diving into the specifics, it helps to understand why we separate data this way. Which means traditional transactional databases (OLTP) are optimized for writing data—recording a sale, updating inventory, or logging a user login. They are highly normalized to prevent redundancy and ensure data integrity Simple, but easy to overlook..

A data warehouse (OLAP), however, is optimized for reading and analyzing massive volumes of historical data. Dimensional modeling denormalizes data into a structure that mirrors how humans naturally ask business questions: "How many [Fact] of [Business Process] happened, sliced by [Dimension]?" This separation creates a semantic layer that is intuitive for end-users and highly performant for query engines It's one of those things that adds up..

What Are Fact Tables?

A fact table captures the quantitative metrics of a specific business process. That said, it represents the "verbs" of your business—the events that happen. Every row in a fact table corresponds to a measurement event, such as a single sales transaction, a website click, a bank deposit, or a sensor reading The details matter here..

No fluff here — just what actually works.

Key Characteristics of Fact Tables

• Numeric and Additive: Facts are almost always numerical values (integers, decimals, currency). The most useful facts are additive, meaning they can be summed across any dimension (e.g., Sales Amount summed by day, by store, by product, or all three).
• Foreign Keys: Fact tables are narrow but deep. They consist primarily of foreign keys linking to dimension tables and the numerical measurements themselves.
• Grain (Granularity): This is the single most critical design decision. The grain defines exactly what a single row represents. For example: "One row per line item on a retail receipt" vs. "One row per store per day." You must declare the grain before choosing dimensions or facts. A consistent grain ensures accurate aggregation; mixing grains leads to the "double counting" trap.

The Three Fundamental Fact Types

Classifying your facts determines how they can be aggregated and analyzed And that's really what it comes down to. Took long enough..

1. Additive Facts These are the gold standard. They can be summed across all dimensions associated with the fact table.

• Example: Sales_Amount, Quantity_Sold, Revenue.
• Usage: SUM(Sales_Amount) GROUP BY Store, Product, Date works perfectly.

2. Semi-Additive Facts These can be summed across some dimensions but not others—typically not across the Time dimension.

• Example: Account_Balance, Inventory_On_Hand.
• Usage: You can sum Account_Balance across all accounts for a specific day (snapshot), but summing it across days (Jan 1 + Jan 2 + Jan 3) yields a meaningless number. You need LAST_VALUE or AVG over time, not SUM.

3. Non-Additive Facts These cannot be summed across any dimension. They usually represent ratios, percentages, or temperatures And that's really what it comes down to. Surprisingly effective..

• Example: Profit_Margin_Percent, Temperature_Celsius, Unit_Price (if not multiplied by quantity).
• Strategy: Store the components that make up the ratio in the fact table (e.g., Revenue and Cost instead of Margin %). Calculate the ratio in the semantic layer or BI tool (SUM(Revenue) / SUM(Cost)).

Special Fact Table Variations

• Factless Fact Tables: These contain no numeric measures—only foreign keys. They capture events or coverage. Examples: student attendance (student + class + date), insurance policy eligibility, or promotional events that didn't result in a sale. They answer "Did this happen?" or "What didn't happen?" (via outer joins).
• Snapshot Fact Tables: Capture the state of the world at a specific interval (e.g., monthly account balances, daily inventory levels).
• Transaction Fact Tables: Record a specific event at a point in time (e.g., a POS sale, a web click). These are the most common and usually have the finest grain.
• Accumulating Snapshot Fact Tables: Track a process with a defined beginning and end (e.g., order processing: Order Placed -> Picked -> Packed -> Shipped -> Delivered). Rows are updated as the workflow progresses.

What Are Dimension Tables?

If facts are the verbs, dimensions are the nouns. And dimension tables are wide, shallow (fewer rows), and highly descriptive. They provide the context—the "Who, What, Where, When, Why, and How"—surrounding a business event. They are the entry points for filtering, grouping, and labeling reports Worth knowing..

Anatomy of a Dimension Table

• Surrogate Key: A system-generated integer primary key (e.g., Product_Key, Date_Key). Never use the natural business key (like SKU or Email) as the primary key in the warehouse. Surrogate keys insulate the warehouse from source system changes, allow integration of multiple sources, and handle Slowly Changing Dimensions (SCDs).
• Natural Key: The business identifier from the source system (e.g., Product_ID, Customer_Email). Used for ETL lookups and traceability.
• Attributes: Textual, descriptive fields used for filtering and labeling (e.g., Product_Name, Category, Color, Size, Brand, Manufacturer).

The Date Dimension: A Special Case

The Date Dimension is the most important dimension in almost every warehouse. In practice, do not rely solely on SQL date functions (YEAR(), MONTH()). Still, a dedicated Date Dimension table allows you to model complex business calendars:

• Fiscal years, quarters, and weeks that don't align with the calendar. Here's the thing — * Holiday flags, weekend flags, "Day of Fiscal Year. "
• Relative time buckets: "Rolling 13 Weeks," "Year-to-Date," "Same Period Last Year."
• It enables time-intelligence calculations that are impossible or painfully slow with native SQL functions alone.

Slowly Changing Dimensions (SCD)

Business attributes change over time. A customer moves, a product gets rebranded, a sales rep changes territory. How you handle this history defines your SCD strategy:

• Type 0 (Retain Original): Never change the attribute. The product remains "Classic Coke" forever, even if renamed.
• Type 1 (Overwrite): Update the row in place. History is lost. The customer always lived in New York. Good for corrections (typos).
• Type 2 (Add New Row): Create a new row with a new surrogate key, start/end dates, and a Current_Flag. This preserves full history. The fact table links to the specific surrogate key valid at the time of the transaction. This is the standard for most analytical needs.
• Type 3 (Add Attribute): Add a "Previous Value" column (e.g., Current_Territory, Previous_Territory). Limited history, useful for "As Was / As Is" reporting.
• Type 4 (History Table): Move old versions to a separate audit table. Keeps the main dimension small.
• Type 6 (Hybrid): Combines Type 1, 2, and 3. Overwrite for corrections, new row for major changes, previous column for immediate comparison.

Common Dimension Patterns