Skip to main content

Fact Tables & Dimension Tables

At the core of all Data Warehousing lies Dimensional Modeling (often called the Kimball Methodology). The fundamental concept is dividing your data into two distinct types of tables: Facts and Dimensions.

Understanding how to design these tables is the most important skill a Data Engineer can possess.

Topics Covered​

  • What is a Fact Table? (Types and Grain)
  • What is a Dimension Table? (Types and Keys)
  • Surrogate Keys vs Natural Keys
  • Many-to-Many Relationships (Bridge Tables)

1. Fact Tables​

A Fact Table records a business event or measurement. It usually contains numbers (metrics) and foreign keys that point to dimension tables.

Think of a Fact table as a verb: A customer purchased a product. A user clicked a button.

The Concept of "Grain"​

The most critical decision when building a fact table is defining its "Grain". The grain is the definition of what exactly one row in the table represents.

  • Good Grain: "One row represents one item purchased on a receipt."
  • Bad Grain: "One row represents a receipt, but sometimes it represents a monthly summary if the ID is null." (Never mix grains!)

Types of Fact Tables​

  1. Transactional Fact Table: Records an event at a point in time. (e.g., A single ride-share booking in Mumbai). This is the most common type.
  2. Periodic Snapshot Fact Table: Records a summary at a regular interval. (e.g., The total account balance of a bank customer at exactly 11:59 PM every day).
  3. Accumulating Snapshot Fact Table: Tracks a process with a defined start and end. (e.g., An order fulfilling process: ORDER_PLACED_DATE, SHIPPED_DATE, DELIVERED_DATE). The same row is updated as the process advances.

2. Dimension Tables​

A Dimension Table contains the descriptive context surrounding a fact. It contains the "Who, What, Where, and When".

Think of a Dimension table as a noun: The Customer (Arjun). The Product (Laptop). The Store (Chennai).

Types of Dimension Tables​

  1. Conformed Dimension: A dimension shared across multiple fact tables. For example, a DIM_DATE table is used by the Sales Fact table and the HR Fact table.
  2. Role-Playing Dimension: A single dimension table that is joined to a fact table multiple times for different purposes. (e.g., A Fact table has ORDER_DATE_ID and SHIPPING_DATE_ID. Both join to the exact same DIM_DATE table).
  3. Junk Dimension: A table used to group random, low-cardinality boolean flags (e.g., IS_GIFT_WRAPPED, IS_EXPEDITED_SHIPPING) so you don't clutter the Fact table with dozens of Yes/No columns.

3. Surrogate Keys vs Natural Keys​

Every Dimension table needs a Primary Key so the Fact table can join to it.

  • Natural Key: The ID generated by the source system (e.g., an Employee ID like EMP-1024 from the HR software).
  • Surrogate Key: An arbitrary integer sequence generated by the Data Warehouse (e.g., 1, 2, 3).

Best Practice: Always use Surrogate Keys in your Data Warehouse. Why? Because source systems change. If the HR system is replaced next year and Arjun's employee ID changes from EMP-1024 to 998877, a Natural Key architecture breaks instantly. A Surrogate Key isolates the data warehouse from upstream source system changes.

(To generate surrogate keys in Snowflake, you often use AUTOINCREMENT or sequences. Verify your role before creating objects: SELECT CURRENT_ROLE();)

CREATE OR REPLACE TABLE DIM_EMPLOYEE (
EMP_SURROGATE_KEY NUMBER AUTOINCREMENT START 1 INCREMENT 1, -- The Surrogate Key
SOURCE_EMP_ID STRING, -- The Natural Key
NAME STRING,
CITY STRING
);

4. Resolving Many-to-Many Relationships (Bridge Tables)​

Usually, a Fact table joins to a Dimension table in a Many-to-One relationship (Many sales happen at One store).

But what if a patient visits a hospital and is diagnosed with three different diseases during that single visit?

  • You cannot put three diseases in one row of the Fact table.
  • You cannot duplicate the Fact row three times, or you will triple-count the cost of the visit!

You solve this with a Bridge Table.

-- 1. The Fact Table (One row per Hospital Visit)
CREATE TABLE FACT_HOSPITAL_VISIT (
VISIT_ID INT,
PATIENT_ID INT,
TOTAL_COST NUMBER
);

-- 2. The Dimension Table (List of all possible diseases)
CREATE TABLE DIM_DISEASE (
DISEASE_ID INT,
DISEASE_NAME STRING
);

-- 3. The Bridge Table (Connects the Visit to Multiple Diseases)
CREATE TABLE BRIDGE_VISIT_DISEASE (
VISIT_ID INT,
DISEASE_ID INT
);

-- Insert Data: Arjun's visit cost 5000 INR, diagnosed with Flu and Cough
INSERT INTO FACT_HOSPITAL_VISIT VALUES (101, 55, 5000);
INSERT INTO DIM_DISEASE VALUES (1, 'Flu'), (2, 'Cough');
INSERT INTO BRIDGE_VISIT_DISEASE VALUES (101, 1), (101, 2);

To analyze the data, you join the Fact to the Bridge, and the Bridge to the Dimension, ensuring metrics are calculated correctly without duplication.

Summary​

  • Fact Tables store measurable events and metrics (the Verbs). Always define a strict Grain.
  • Dimension Tables store descriptive context (the Nouns).
  • Use Surrogate Keys to insulate the data warehouse from source system changes.
  • Use Bridge Tables to handle complex Many-to-Many relationships without duplicating Fact metrics.