Facts, dimensions, star and snowflake schemas — ETL & Data Warehousing Concepts

5. Facts, dimensions, star and snowflake schemas

Intermediate★ Must knowLesson 5 of 12

Fact table — numeric, measurable events (sales amount, quantity) at a defined grain, with foreign keys to dimensions. Usually long and narrow.
Dimension table — descriptive context (who, what, where, when): Customer, Product, Date, Store. Wide, with attributes for filtering and grouping.
Star schema — fact in the middle, denormalised dimensions around it (one join per dimension) → simple and fast.
Snowflake schema — dimensions normalised into sub-dimensions (Product → Subcategory → Category) → less redundancy, more joins.
Galaxy / fact constellation — multiple facts sharing conformed dimensions (e.g. DimDate, DimCustomer used by FactSales and FactReturns).

sql

CREATE TABLE dw.DimDate (DateKey INT PRIMARY KEY, FullDate DATE, [Month] TINYINT, MonthName VARCHAR(10), [Quarter] TINYINT, [Year] SMALLINT, IsWeekend BIT);
CREATE TABLE dw.DimProduct (ProductKey INT IDENTITY PRIMARY KEY, ProductId VARCHAR(20), Name NVARCHAR(100), Category NVARCHAR(50));
CREATE TABLE dw.DimCustomer (CustomerKey INT IDENTITY PRIMARY KEY, CustomerId VARCHAR(20), Name NVARCHAR(100), City NVARCHAR(50),
                             ValidFrom DATE, ValidTo DATE, IsCurrent BIT);
CREATE TABLE dw.FactSales (
    DateKey INT REFERENCES dw.DimDate, ProductKey INT REFERENCES dw.DimProduct, CustomerKey INT REFERENCES dw.DimCustomer,
    OrderNumber VARCHAR(20),          -- degenerate dimension
    Quantity INT, SalesAmount DECIMAL(18,2), CostAmount DECIMAL(18,2));