fact tables and dimension tables serve distinct but complementary roles. Here’s a breakdown of their differences:
Fact Table:
Definition: A fact table contains quantitative data for analysis and is often denormalized.
Content: It holds measurable, numerical data (facts) such as sales amounts, transaction counts, or revenue.
Keys: Fact tables typically contain foreign keys that reference dimension tables and often have a composite primary key made up of these foreign keys.
Example: A sales fact table might include columns for order ID, product ID, customer ID, sales amount, and date.
Dimension Table:
Definition: A dimension table contains descriptive attributes (or fields) that provide context to the facts.
Content: It holds textual or categorical data that can be used to filter or group facts. This might include names, dates, locations, and other characteristics.
Keys: Dimension tables usually have a primary key that uniquely identifies each record, which is referenced by the fact table.
Example: A product dimension table might include columns for product ID, product name, category, and brand.