How to Design a Data Warehouse Schema Step

Most companies collect data in dozens of places. Sales transactions live in one database. Customer records live in another. Marketing campaign data sits in a third system. Web analytics go into a fourth. Each system was built to handle its specific job efficiently, not to answer questions across all of them at once.

Then someone asks: which customer segment generated the most revenue last quarter broken down by product category and region? And the answer requires pulling data from four different systems, joining tables that were never designed to talk to each other, and hoping the definitions of things like “customer” and “revenue” mean the same thing across all of them.

This is the problem a data warehouse solves. It is a central repository designed specifically for analytical queries, where data from every source system is cleaned, standardized, and organized in a structure that makes answering complex business questions fast and consistent.

The structure that makes it work is called the schema. Designing that schema correctly from the start is what separates a data warehouse that business teams actually use from one that data engineers spend months apologizing for.

This guide walks through designing a data warehouse schema step by step, from understanding the core concepts to building a working example with SQL.

What Is a Data Warehouse Schema?

A data warehouse schema is the blueprint that defines how tables are organized, what data each table holds, and how the tables relate to each other. Unlike an operational database schema designed to minimize redundancy and handle fast writes, a data warehouse schema is designed to maximize query performance and make analytical questions easy to express and fast to run.

Think of it like the difference between a stockroom and a showroom. An operational database is the stockroom, organized for efficient storage and retrieval of individual items. A data warehouse schema is the showroom, organized so customers can quickly find, compare, and understand everything available without digging through storage boxes.

The two most common schema designs in data warehousing are the star schema and the snowflake schema. Both organize data around a central fact table surrounded by dimension tables. The difference is in how the dimension tables themselves are structured.

Core Concepts Before You Design

Fact Tables store the measurable events you want to analyze. Sales transactions, page views, support tickets, shipments. Every row in a fact table represents one event. The columns are either numeric measures like revenue, quantity, and duration, or foreign keys pointing to dimension tables that describe the context of that event.

Dimension Tables store the descriptive context for those events. Who was the customer? What product was sold? Which store did it happen in? What date was it? Dimension tables have one row per entity and many descriptive columns that analysts use to filter, group, and label the results of queries against the fact table.

Grain is the single most important decision in data warehouse design. It defines exactly what one row in the fact table represents. One row per individual transaction. One row per daily store summary. One row per product per customer per month. Every other design decision follows from the grain. If you get the grain wrong, the schema is wrong.

Step by Step: Designing a Data Warehouse Schema

The example used throughout this guide is a retail company that wants to analyze sales performance across products, customers, stores, and time.

Step 1: Identify the Business Process You Are Modeling

Before drawing a single table, define what business process the schema will support. A schema built to analyze retail sales is different from one built to analyze customer support tickets or website traffic. Each business process becomes its own subject area in the warehouse.

For this example the business process is retail sales. The questions the schema needs to answer include things like total revenue by product category per month, average transaction value by customer segment and region, top selling products by store, and year over year sales growth by region.

Write these questions down before designing anything. They determine what measures go in the fact table and what dimensions you need to answer them.

Step 2: Declare the Grain

For retail sales the most useful grain is one row per individual sales transaction line item. Not one row per order, because one order can contain multiple products. Not one row per day, because that loses the ability to drill down into individual transactions.

One row per line item means: every time a customer buys one unit of one product in one transaction, that is one row in the fact table. This grain supports every question listed in Step 1 and can always be aggregated up to order level, daily, or monthly without losing information.

Step 3: Identify the Dimensions

Given the grain of one row per transaction line item, ask what context describes each row. For a retail sales line item the relevant context is the customer who bought it, the product that was sold, the store where it happened, the date it occurred, and the promotion or discount that was applied if any.

Each piece of context becomes a dimension table:

dim_customer    — who bought it
dim_product     — what was sold
dim_store       — where it happened
dim_date        — when it happened
dim_promotion   — under what offer

Step 4: Identify the Facts

Facts are the numeric measures you want to aggregate in your analysis. For a retail sales transaction the measures are quantity sold, unit price, discount amount, and total sale amount. These become columns in the fact table alongside the foreign keys pointing to each dimension.

Step 5: Build the Dimension Tables

Start with the dimension tables because the fact table references them. Here is the SQL for each dimension:

sql

CREATE TABLE dim_customer (
    customer_key    INT PRIMARY KEY,
    customer_id     VARCHAR(20),
    full_name       VARCHAR(100),
    email           VARCHAR(100),
    city            VARCHAR(50),
    state           VARCHAR(50),
    country         VARCHAR(50),
    segment         VARCHAR(30),
    join_date       DATE
);

CREATE TABLE dim_product (
    product_key     INT PRIMARY KEY,
    product_id      VARCHAR(20),
    product_name    VARCHAR(100),
    category        VARCHAR(50),
    subcategory     VARCHAR(50),
    brand           VARCHAR(50),
    unit_cost       DECIMAL(10,2)
);

CREATE TABLE dim_store (
    store_key       INT PRIMARY KEY,
    store_id        VARCHAR(20),
    store_name      VARCHAR(100),
    city            VARCHAR(50),
    state           VARCHAR(50),
    region          VARCHAR(50),
    store_type      VARCHAR(30)
);

CREATE TABLE dim_date (
    date_key        INT PRIMARY KEY,
    full_date       DATE,
    day_of_week     VARCHAR(10),
    day_number      INT,
    month_number    INT,
    month_name      VARCHAR(10),
    quarter         INT,
    year            INT,
    is_weekend      BOOLEAN,
    is_holiday      BOOLEAN
);

CREATE TABLE dim_promotion (
    promotion_key   INT PRIMARY KEY,
    promotion_id    VARCHAR(20),
    promotion_name  VARCHAR(100),
    discount_type   VARCHAR(30),
    discount_value  DECIMAL(5,2),
    start_date      DATE,
    end_date        DATE
);

Notice that every dimension table has a surrogate key, which is the _key column, separate from the natural business ID. The surrogate key is a simple integer generated by the warehouse. It is what the fact table uses as a foreign key. The original business ID is preserved as a separate column for traceability but is not used for joins. This separation protects the warehouse from changes in source system IDs and makes joins faster.

Step 6: Build the Fact Table

With the dimensions defined, the fact table is straightforward. It holds one foreign key per dimension and one column per measure:

sql

CREATE TABLE fact_sales (
    sales_key           BIGINT PRIMARY KEY,
    customer_key        INT REFERENCES dim_customer(customer_key),
    product_key         INT REFERENCES dim_product(product_key),
    store_key           INT REFERENCES dim_store(store_key),
    date_key            INT REFERENCES dim_date(date_key),
    promotion_key       INT REFERENCES dim_promotion(promotion_key),
    transaction_id      VARCHAR(30),
    quantity_sold       INT,
    unit_price          DECIMAL(10,2),
    discount_amount     DECIMAL(10,2),
    total_sale_amount   DECIMAL(10,2)
);

The fact table is intentionally narrow in terms of descriptive columns. All the descriptions live in the dimension tables. The fact table is wide in terms of rows, potentially billions of them, and optimized for aggregation across those rows using the dimension foreign keys as filters and groupings.

Step 7: Write a Query Against Your Schema

The real test of a schema design is how naturally business questions translate into SQL. Here is a query for total revenue by product category and region for the year 2024:

sql

SELECT
    p.category,
    s.region,
    SUM(f.total_sale_amount)    AS total_revenue,
    SUM(f.quantity_sold)        AS total_units,
    COUNT(*)                    AS transaction_count
FROM fact_sales f
JOIN dim_product  p ON f.product_key  = p.product_key
JOIN dim_store    s ON f.store_key    = s.store_key
JOIN dim_date     d ON f.date_key     = d.date_key
WHERE d.year = 2024
GROUP BY p.category, s.region
ORDER BY total_revenue DESC;

The query is readable, the joins are clean, and the logic maps directly to the business question. That is the hallmark of a well-designed schema.

Star Schema vs Snowflake Schema

The schema built above is a star schema. All dimension tables connect directly to the fact table and are fully denormalized, meaning all attributes of a dimension live in one flat table regardless of hierarchical relationships.

A snowflake schema normalizes dimension tables further. Instead of storing category and subcategory directly in dim_product, a snowflake schema would create a separate dim_category table and have dim_product reference it with a foreign key.

Aspect	Star Schema	Snowflake Schema
Dimension structure	Flat, denormalized	Normalized, multiple tables
Query complexity	Simpler joins	More joins required
Storage space	More redundancy	Less redundancy
Query performance	Generally faster	Can be slower
Maintenance	Easier to understand	Easier to update hierarchies
Best for	Most analytical use cases	Large dimensions with deep hierarchies

For most data warehouses serving business analysts, the star schema is the better starting point. The simpler query structure makes it more accessible to non-technical users writing their own queries or using BI tools like Power BI and Tableau.

Common Limitations

Slowly changing dimensions need a strategy. Customer addresses change. Product names get updated. Store regions get reorganized. A basic dimension table with no history cannot tell you what a customer’s address was at the time of a transaction two years ago. Handling slowly changing dimensions requires deliberate design decisions, most commonly adding effective date columns and a current flag to dimension rows so historical states are preserved alongside current ones.

The date dimension needs to be pre-populated. Unlike other dimensions that are loaded from source systems, the date dimension needs to be populated with every date in your analysis range before any fact data is loaded. A common approach is to generate it programmatically for a twenty or thirty year range covering past history and future planning horizons.

Fact table grain mistakes are expensive to fix. If you discover six months into a project that your grain was wrong, fixing it usually means redesigning and reloading the entire fact table. The grain declaration in Step 2 deserves careful thought and stakeholder validation before any table is created.

Common Mistakes to Avoid

Putting descriptive attributes in the fact table. If you find yourself adding columns like product_name or customer_city directly to the fact table, those belong in a dimension. The fact table should contain only foreign keys and numeric measures. Descriptive columns in the fact table duplicate data, make updates painful, and indicate the schema was not fully thought through.

Using natural keys as foreign keys in the fact table. Joining fact rows to dimension rows using business identifiers like customer email addresses or product SKUs is slower, fragile when source systems change their ID formats, and breaks historical accuracy when IDs get reused. Always use integer surrogate keys for dimension joins.

Skipping the date dimension and using a raw date column instead. A raw date column in the fact table can answer questions like what happened on this specific date. A proper date dimension can answer questions like what happened on weekends in Q3 across all years, is this date a holiday, how does this week compare to the same week last year. The date dimension is always worth building properly.

Designing for the source system instead of the business question. A data warehouse schema should reflect how business users think about the data, not how the operational system happened to store it. If the source system splits customer name across five columns and uses internal codes for regions, the warehouse schema should consolidate and decode those values into something an analyst can actually read and use.

Data Warehouse Schema Cheat Sheet

Concept	Definition
Fact table	Central table storing measurable events with numeric measures
Dimension table	Descriptive context table surrounding the fact table
Grain	What exactly one row in the fact table represents
Surrogate key	Integer key generated by the warehouse for dimension joins
Natural key	Original business identifier from the source system
Star schema	Flat denormalized dimensions connected directly to fact table
Snowflake schema	Normalized dimensions with additional lookup tables
Slowly changing dimension	Dimension where attributes change over time and history matters
Measure	Numeric column in the fact table that gets aggregated
Conformed dimension	Dimension shared across multiple fact tables in the warehouse

Designing a data warehouse schema well comes down to four decisions made in the right order. Choose the business process you are modeling. Declare the grain precisely. Identify the dimensions that describe each fact row. Identify the numeric measures you need to analyze.

Everything else, the SQL, the surrogate keys, the date dimension, the choice between star and snowflake, follows from getting those four decisions right. Rush past them and you will spend months fixing a foundation that was never solid. Take the time to define them clearly with input from the business stakeholders who will actually use the data, and the rest of the design falls into place naturally.

Start with a star schema. Keep dimensions flat and readable. Pre-populate the date dimension. Use surrogate keys for all dimension joins. Write the top five business questions before writing the first CREATE TABLE statement and verify the schema can answer all of them cleanly before loading any data.

A data warehouse schema that business analysts can query confidently without asking a data engineer for help every time is a schema designed well. That is the standard worth building toward from day one.

FAQs