Skip to main content

Star Schema Design (Deep Dive)

The Star Schema, popularized by Ralph Kimball in the 1990s, remains the gold standard for structuring data in a Data Warehouse.

It is called a Star Schema because of its visual shape: A massive central Fact Table (containing metrics) surrounded by smaller Dimension Tables (containing descriptive attributes), radiating outward like the points of a star.

Topics Covered​

  • The Star Schema Architecture
  • Denormalizing Dimensions
  • Implementing a Star Schema in Snowflake
  • Why Star Schemas perform best in Snowflake

1. The Star Schema Architecture​

In a transactional database (like PostgreSQL powering an e-commerce website), data is highly normalized into dozens of tables (3NF) to make INSERT and UPDATE operations extremely fast.

A Data Warehouse does not optimize for rapid inserts; it optimizes for read performance and analytical simplicity. Business analysts using Tableau or PowerBI do not want to write 12-table JOIN queries just to find out how many laptops were sold in Delhi.

The Star Schema solves this by purposely duplicating data (denormalization) to reduce the number of tables.

Key Rules of the Star Schema:​

  1. One Central Fact Table: Holds the numerical measurements (Sales Amount, Discount Applied, Quantity).
  2. Surrounding Dimension Tables: Holds the text attributes (Customer Name, City, Product Category).
  3. Only One Level of Joins: A Fact table joins to a Dimension table. A Dimension table never joins to another Dimension table.

2. Denormalizing Dimensions​

In a transactional database, you might have a STORE table, a CITY table, and a STATE table.

In a Star Schema, you crush all three of those into a single, wide, denormalized DIM_STORE table. Yes, the word "Maharashtra" will be repeated a million times for every store in that state. In the cloud era, storage is cheap, so we trade storage efficiency for query speed.

3. Hands-On: Building a Star Schema​

Let's build a Star Schema for an Indian retail chain.

Database Setup​

(Run this in your Snowflake worksheet. Ensure you have the SYSADMIN role: SELECT CURRENT_ROLE();)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_STAR_DB;
USE SCHEMA BEEMS_STAR_DB.PUBLIC;

-- 1. Create the Denormalized Dimension Tables
CREATE OR REPLACE TABLE DIM_DATE (
DATE_ID INT,
CALENDAR_DATE DATE,
DAY_OF_WEEK STRING,
MONTH_NAME STRING,
QUARTER STRING,
YEAR INT
);

CREATE OR REPLACE TABLE DIM_CUSTOMER (
CUSTOMER_ID INT,
FIRST_NAME STRING,
LAST_NAME STRING,
CITY STRING,
STATE STRING,
VIP_STATUS BOOLEAN
);

CREATE OR REPLACE TABLE DIM_PRODUCT (
PRODUCT_ID INT,
PRODUCT_NAME STRING,
CATEGORY STRING,
BRAND STRING
);

-- 2. Create the Central Fact Table
CREATE OR REPLACE TABLE FACT_SALES (
ORDER_ID INT,
DATE_ID INT, -- FK to DIM_DATE
CUSTOMER_ID INT, -- FK to DIM_CUSTOMER
PRODUCT_ID INT, -- FK to DIM_PRODUCT
QUANTITY INT,
TOTAL_PRICE NUMBER(10,2),
DISCOUNT_AMOUNT NUMBER(10,2)
);

-- 3. Insert Mock Data
INSERT INTO DIM_DATE VALUES (20231001, '2023-10-01', 'Sunday', 'October', 'Q4', 2023);
INSERT INTO DIM_CUSTOMER VALUES (101, 'Arjun', 'Kumar', 'Mumbai', 'Maharashtra', TRUE);
INSERT INTO DIM_PRODUCT VALUES (50, 'MacBook Pro', 'Electronics', 'Apple');
INSERT INTO FACT_SALES VALUES (9999, 20231001, 101, 50, 1, 150000.00, 5000.00);

Querying the Star Schema​

Notice how clean and intuitive the query is for an analyst. Because the dimensions are denormalized, we only need basic joins to the central Fact table.

-- Find total revenue for Electronics sold to VIP customers in Maharashtra in Q4
SELECT
d.MONTH_NAME,
c.CITY,
SUM(f.TOTAL_PRICE) AS REVENUE
FROM FACT_SALES f
JOIN DIM_DATE d ON f.DATE_ID = d.DATE_ID
JOIN DIM_CUSTOMER c ON f.CUSTOMER_ID = c.CUSTOMER_ID
JOIN DIM_PRODUCT p ON f.PRODUCT_ID = p.PRODUCT_ID
WHERE
d.QUARTER = 'Q4'
AND p.CATEGORY = 'Electronics'
AND c.STATE = 'Maharashtra'
AND c.VIP_STATUS = TRUE
GROUP BY
d.MONTH_NAME,
c.CITY;

4. Why Star Schemas Perform Best in Snowflake​

You might wonder: "If I denormalize my dimensions and duplicate text strings millions of times, won't my table be massive and slow?"

Not in Snowflake!

  1. Columnar Storage: Snowflake stores data in columns, not rows. If you query SUM(TOTAL_PRICE), Snowflake completely ignores the CUSTOMER_NAME column, drastically reducing disk I/O.
  2. Automatic Compression: If the string "Maharashtra" appears 5 million times in a column, Snowflake's compression engine automatically dictionaries it. It takes up almost zero space on disk.
  3. Star Schema Join Pruning: Snowflake's query optimizer is specifically designed to recognize Star Schemas. It dynamically prunes partitions based on dimension filters (e.g., if you filter CATEGORY = 'Electronics', Snowflake will skip scanning any micro-partitions in the Fact table that do not contain electronics).

Summary​

  • A Star Schema consists of a central Fact table surrounded by denormalized Dimension tables.
  • It is designed to maximize read performance and simplicity for business analysts.
  • Dimensions should be denormalized (flat and wide). Do not create sub-dimensions (that is a Snowflake Schema).
  • Snowflake's Columnar Storage and Compression make Star Schemas highly efficient in the cloud.