Fact Tables Fundamentals

The fact table functions as the primary repository for measurements within the architecture of an analytical database. While dimension tables provide the descriptive context, the "who, what, where, and when", the fact table stores the quantitative data generated by business events. It sits at the center of the Star Schema, linking various dimensions together through foreign keys.

A properly designed fact table is deep and narrow. It may contain billions of rows representing individual transactions or events, but it typically possesses fewer columns than the surrounding dimension tables. These columns generally fall into two categories: foreign keys that reference dimension tables and numeric measures that serve as the subject of analysis.

Anatomy of a Fact Row

Every row in a fact table corresponds to a specific event defined by the grain. If the grain is defined as "one row per line item on a customer receipt," then the fact table must capture the specific metrics associated with that single line item, such as quantity sold and extended price.

The structural integrity of the fact table relies on the relationship between its foreign keys and the primary keys of the dimension tables. The combination of these foreign keys defines the composite primary key of the fact table itself. This ensures that each record is unique within the defined grain.

The fact table structure acts as the convergence point for dimensions. It contains foreign keys (FK) pointing to dimensions and numerical measures (bottom three rows) derived from the transaction.

Additivity of Measures

A critical aspect of fact table design is understanding how measures behave when aggregated. Analysts frequently sum data across different dimensions, grouping sales by region, by month, or by product category. The behavior of a measure during this summarization defines its additivity.

Fully Additive Measures

These measures can be summed across any and all dimensions associated with the fact table. This is the most flexible and desirable type of measure. For example, Quantity_Sold in a retail transaction table is fully additive. You can sum the quantity sold across stores, across days, or across products, and the result remains logically valid.

$Total\_Quantity = \sum_{i=1}^{n} q_i$

Semi-Additive Measures

Semi-additive measures can be summed across some dimensions but not others. The most common example is inventory snapshots or account balances.

Consider a warehouse inventory fact table. You can sum the quantity of Product A across all warehouses to get the total stock for a specific day. However, you cannot sum the inventory of Product A across time. Adding the inventory count from January 1st to the inventory count from January 2nd results in a nonsensical figure that implies double the actual stock.

To handle semi-additive facts correctly in SQL, you often use aggregation functions like SUM() for spatial dimensions (Warehouse, Region) but strictly use LAST_VALUE() or filtered queries for the temporal dimension (Time).

Aggregating inventory balances across locations (left) yields a valid total. Aggregating balances across time (right) creates incorrect data, indicated by the red bar.

Non-Additive Measures

Non-additive measures cannot be summed across any dimension. These are typically ratios, percentages, or unit prices. For instance, Gross_Margin_Percentage cannot be aggregated by summing the percentages of individual transactions.

To analyze non-additive measures at a higher grain, you must store the underlying components that make up the ratio (e.g., Total_Revenue and Total_Cost) and calculate the ratio after aggregation.

$\text{Correct Margin} = \frac{\sum (\text{Revenue} - \text{Cost})}{\sum \text{Revenue}}$

$\text{Incorrect Margin} \neq \sum \left( \frac{\text{Revenue} - \text{Cost}}{\text{Revenue}} \right)$

Handling Null Foreign Keys

In a production environment, source data is rarely perfect. A transaction might arrive with a missing customer ID or an undefined store code. In a normalized operational database, this might be represented as a NULL value.

In a dimensional model, however, fact tables should strictly avoid NULL values in foreign key columns. If a NULL exists in a foreign key, many reporting tools and standard SQL INNER JOIN operations will drop that row from the result set, causing the total metrics (like Revenue) to underreport.

The standard pattern to address this is the assignment of a default "Unknown" or "N/A" record in the corresponding dimension table. This record is typically assigned a surrogate key of -1 or 0. During the ETL (Extract, Transform, Load) process, any missing or invalid source keys are mapped to this surrogate key. This technique ensures referential integrity is maintained and that every dollar of revenue is accounted for, even if the context is temporarily unknown.

Fact Table Types Overview

While the structure described above covers the most common use cases, different business processes require specialized fact table architectures. These patterns dictate how the table captures data relative to time and events.

1. Transaction Fact Tables: The most granular level. One row corresponds to one event (e.g., a single click, a single scan at a register). These are dense with data and typically fully additive.
2. Periodic Snapshot Fact Tables: Data is aggregated over a standard time period, such as daily or monthly. This is common for performance monitoring or account balances (semi-additive).
3. Accumulating Snapshot Fact Tables: Used for workflows with a definite start and end, such as order processing. A single row represents the entire lifecycle of an order, with multiple date foreign keys updated as the process moves from "Placed" to "Shipped" to "Delivered."

We will examine the specific implementation details and use cases for Periodic and Accumulating snapshots in Chapter 4. For the initial design phase, identifying the base transaction grain and ensuring the correct additivity of measures allows you to build a solid foundation for the broader analytical schema.