Phase 1: Foundations

Star schemas, snowflake schemas & fact/dimension tables

Beginner ~3 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine you're playing an amazing board game that tracks lots of stuff: how many points each player scores, what special items they collect, and even what time of day they play. To understand how people play the game best, you need to organize all that information!

You have two main kinds of information. First, there's the main game scoreboard or logbook. This tracks "what happened"—like "Player A scored 10 points," or "Player B collected a magic potion." These are usually numbers, and they make up your "Fact Tables." Then, you have the rulebook, character sheets, and item cards. These describe the "who, what, where, when, why." A character sheet tells you Player A's name, their special abilities, and what color token they use. An item card tells you what a "magic potion" does and how rare it is. These descriptive bits are your "Dimension Tables." They give context to the numbers on your scoreboard.

Now, let's think about how to arrange all these game pieces. The simplest way is called a "Star Schema." Imagine your game's main scoreboard (Fact Table) is in the very center. Directly connected to it, like the points of a star, are your complete piles of Dimension Tables. You have one pile of character sheets, where each sheet has all the details about a player, like their name, their character type (wizard, warrior), and their special power, all on one piece of paper. You also have a separate, complete pile of item cards, each with all its details. This makes it super-fast to answer questions like, "Which type of character collected the most magic potions today?" You just look at the scoreboard, then quickly jump to the full character sheet pile to find out.

Sometimes, a game might have a huge number of items, and putting all their details on one card for each item would make those cards enormous! So, you might organize them a little differently, which is like a "Snowflake Schema." Instead of one big item card for "Sword of Fire," you might have a smaller card for "Sword" (explaining it's a weapon), another smaller card for "Fire" (explaining it's an enchantment), and then a tiny card linking "Sword" and "Fire" together to describe the "Sword of Fire." This means some of your dimension piles (like items) might have their own little sub-piles that connect to each other, branching out like a snowflake. It helps keep things super neat, but it means you might have to look at a few more cards to get the full story about one specific item.

So, by organizing your game data this way, you can easily figure out interesting things. You can find out which special abilities helped players score the most points in the morning, or which items are most popular with different character types. This helps you understand the game much better and even helps you design new rules or strategies!

At the heart of dimensional modeling are two types of tables: Fact Tables and Dimension Tables. Think of Fact Tables as recording "what happened" – they store the measurable, quantitative data about business events, like sales amounts, quantities sold, or event durations. These tables are typically very large and contain foreign keys that link to Dimension Tables. Dimension Tables, on the other hand, provide the "who, what, where, when, why" context for these facts. They store descriptive attributes about entities like customers (name, address), products (category, brand), locations (city, state), or time (date, month, year). Together, they allow you to analyze facts through various business perspectives.

A Star Schema is the simplest and most common arrangement of fact and dimension tables. In this design, a single, central Fact Table is directly connected to multiple Dimension Tables, resembling a star with its points. The key characteristic here is that each Dimension Table is denormalized, meaning all attributes related to that dimension (e.g., product name, category, brand) are stored directly within that single dimension table. This structure makes queries very fast and intuitive to write because you typically only need direct joins between the fact and a few dimensions to get your analytical results. It's often favored for its simplicity and query performance in data warehousing.

The Snowflake Schema is an extension of the Star Schema, where the Dimension Tables themselves are further normalized. Instead of having all product attributes in one DimProduct table, for example, a Snowflake schema might break it down into DimProduct, DimProductCategory, and DimProductBrand tables, linked by foreign keys. This normalization reduces data redundancy and can be useful when dimensions have very complex hierarchies or many attributes that rarely change. However, the trade-off is increased query complexity, as you'll often need more joins to retrieve all the contextual information, which can sometimes lead to slower query performance compared to a Star Schema.

Key Takeaways

  • Fact tables record measurable events; Dimension tables provide descriptive context.
  • Star Schema: central fact table directly linked to denormalized dimension tables (simpler, faster for most analytics).
  • Snowflake Schema: central fact table, but dimension tables are normalized into sub-dimensions (less redundancy, more complex joins).
  • Choose Star for performance and simplicity; Snowflake for reduced redundancy and complex dimension hierarchies.

Code Example

sql
CREATE TABLE DimDate (
    DateKey INT PRIMARY KEY,
    FullDate DATE,
    DayOfMonth INT,
    Month INT,
    Year INT
);

CREATE TABLE DimProduct (
    ProductKey INT PRIMARY KEY,
    ProductName VARCHAR(255),
    ProductCategory VARCHAR(100),
    ProductBrand VARCHAR(100)
);

CREATE TABLE FactSales (
    SaleKey INT PRIMARY KEY AUTO_INCREMENT,
    DateKey INT NOT NULL,
    ProductKey INT NOT NULL,
    SalesAmount DECIMAL(10, 2),
    QuantitySold INT,
    FOREIGN KEY (DateKey) REFERENCES DimDate(DateKey),
    FOREIGN KEY (ProductKey) REFERENCES DimProduct(ProductKey)
);

How this code works

This SQL code establishes a basic "star schema," a popular data modeling technique used to organize data for easier reporting and analysis. It defines three distinct tables: DimDate, DimProduct, and FactSales. The Dim tables (short for Dimension) act as descriptive lookup tables, storing detailed attributes about things like specific dates (FullDate, Month, Year) or product characteristics (ProductName, ProductCategory). The FactSales table, on the other hand, is the central table where measurable events, like individual sales transactions, are recorded, capturing numerical facts such as SalesAmount and QuantitySold.

The CREATE TABLE statements define the structure of each. PRIMARY KEY ensures that each record in a dimension table (like DateKey in DimDate) has a unique identifier, making it easy to reference. Crucially, the FactSales table uses FOREIGN KEY constraints to link back to the DimDate and DimProduct tables using their respective DateKey and ProductKey. This forms the "star" shape of the schema, allowing sales data to be enriched with product and date details. A subtle but important detail is the AUTO_INCREMENT for SaleKey in FactSales. This automatically assigns a unique identifier to each new sale, relieving the data loader from manually generating keys and preventing potential errors from duplicate or missing IDs.

Related Articles

Hand-picked reads to go deeper on data modeling.

Back to Data Engineer Roadmap