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
- 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.
- 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).
- 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
- Conformed Dimension: A dimension shared across multiple fact tables. For example, a
DIM_DATEtable is used by the Sales Fact table and the HR Fact table. - 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_IDandSHIPPING_DATE_ID. Both join to the exact sameDIM_DATEtable). - 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-1024from 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.