Data Warehouse Design: Schemas, Steps, and Best Practices

Data warehouse design is the process of structuring a database for analysis instead of daily transactions. The core method is dimensional modeling: store your measurable numbers in one large fact table, and describe them with small dimension tables around it. That layout is called a star schema, and it is the default shape of a well-designed warehouse.

The design matters because analytical queries are different from application queries. An application reads one order. An analyst sums ten million orders by month, region, and product. A schema built for the first job answers the second job slowly, or not at all.

This guide explains the warehouse layout, the two schema styles, and the four design steps. It ends with the practices that keep a warehouse fast as it grows.

What a Data Warehouse Is

A data warehouse is a central database that collects data from many source systems for reporting and analysis. The sources are usually the databases behind your applications, plus files and third-party tools.

The two workloads have names worth knowing. OLTP (online transaction processing) is the application workload: many small reads and writes, one record at a time. OLAP (online analytical processing) is the warehouse workload: few queries, each scanning millions of rows to produce totals and trends.

The design consequences are direct:

• An OLTP database is normalized. Normalization means splitting data into many small tables so each fact is stored once.
• A warehouse is deliberately denormalized. It repeats descriptive data so queries need fewer joins and scans stay fast.
• OLTP tables change constantly. Warehouse tables load on a schedule and are mostly read.

Facts and Dimensions

Dimensional modeling divides every piece of data into two kinds.

A fact is a measurement of a business event. An order amount, a quantity shipped, a page view. Facts are numbers you sum, count, or average. They live in fact tables, which grow into millions or billions of rows.

A dimension is the context of that measurement. The customer, the product, the store, the date. Dimensions are the words after "by" in a business question: revenue by region, orders by month. They live in dimension tables, which stay comparatively small.

The Star Schema

A star schema is one fact table in the center, joined directly to its dimension tables. Drawn on a whiteboard, it looks like a star.

Take a retail example. The fact table sales holds one row per item sold: the quantity, the price paid, and a key to each dimension. Around it sit dim_date, dim_customer, dim_product, and dim_store.

"Total revenue by product category per month" is then one query: join sales to dim_product and dim_date, group, and sum. Every business question follows the same two-hop pattern, which is why analysts and BI tools handle star schemas so well.

Two conventions make the star work:

• Surrogate keys. Each dimension row gets its own integer key, generated by the warehouse. A surrogate key is independent of the source system's IDs, which can change or collide across sources.
• The grain. The grain is what one fact row represents: one order line, one page view, one daily account balance. Every fact in the table must match it exactly.

Star vs Snowflake Schema

A snowflake schema is a star schema whose dimensions are split further. Instead of dim_product holding the category name in a column, a separate dim_category table hangs off dim_product. The diagram grows branches, like a snowflake.

Start with a star. Storage is cheap, and the repeated category names a snowflake saves are rarely worth the extra joins. Snowflake only the dimensions that clearly earn it, such as a very large customer dimension shared by many fact tables.

The Four Design Steps

Dimensional modeling has a standard four-step method. It comes from Ralph Kimball, whose approach most warehouse teams follow.

1. Pick the business process. One process at a time: order fulfillment, payments, sign-ups. A process produces measurable events, and those events become one fact table.
2. Declare the grain. Decide what one fact row means before naming any column. "One row per order line" and "one row per order" are different tables. The lowest useful grain is the safe choice, because you can always sum upward.
3. Choose the dimensions. List the context every event carries: date, customer, product, store, promotion. Each becomes a dimension table with a surrogate key.
4. Choose the facts. Keep only measurements that match the grain. An order-line row can hold quantity and line amount. It cannot hold the order's shipping fee, which belongs to a different grain.

These four steps are the warehouse-specific version of a more general process. For the full schema design method, from requirements through normalization to physical design, see the 7 steps in designing a database.

Handling Change: Slowly Changing Dimensions

Dimension data changes. A customer moves cities. A product changes category. A slowly changing dimension (SCD) is the named pattern for handling this, and two types cover most needs.

• Type 1: overwrite. Update the row in place. History is lost. Use it when the old value has no analytical worth, such as fixing a typo.
• Type 2: add a row. Keep the old row, insert a new one with a new surrogate key, and mark which row is current with date ranges. Old facts keep pointing at the old row, so history stays correct.

Type 2 is the default for anything a report might group by. If revenue is analyzed by region, and customers change regions, only Type 2 reports the past truthfully.

Design Choices That Keep a Warehouse Fast

A good schema is half the work. These practices carry the other half:

• Columnar storage. Modern warehouse engines store each column separately, so a query reads only the columns it touches. Design wide fact tables without guilt, but keep each column narrow and typed.
• Partitioning. Split the fact table by date so queries scan only the range they ask about. Partitioning is one of the standard database design patterns and matters most at warehouse scale.
• Load with ELT. Land raw data first, then transform it inside the warehouse with SQL. Keeping the raw layer means you can rebuild the model when requirements change.
• Summary tables. Precompute the totals that dashboards read every morning. A materialized view, a stored query result that refreshes on a schedule, turns a minute-long scan into a lookup.

These choices repeat across system design more broadly. The database chapters of Grokking System Design Fundamentals cover partitioning, indexes, and replication from first principles.

Common Mistakes

• No declared grain. Facts at mixed grains end up in one table, and every sum double-counts something.
• Snowflaking by habit. Normalizing every dimension because normalization feels correct. The warehouse is the one place where repetition is the right call.
• Using source-system keys. When a source reuses or changes an ID, every fact pointing at it silently corrupts.
• One giant "do everything" table. Skipping dimensions entirely and loading one flat table works until the first change request, then every query needs rewriting.
• Ignoring history. Overwriting dimensions everywhere (Type 1) makes last year's report change retroactively, and nobody trusts the numbers afterward.

Frequently asked questions

What is the difference between a database and a data warehouse?

A database runs an application: many small reads and writes, one record at a time. A data warehouse supports analysis: it collects data from many databases and serves large scanning queries. The schemas differ accordingly, normalized for the database and dimensional for the warehouse.

What is the difference between a data warehouse and a data lake?

A data lake stores raw files in any format, cheaply, without a schema. A data warehouse stores structured tables designed for fast SQL queries. Many teams use both: land everything in the lake, then model the useful parts into the warehouse.

When should I use a snowflake schema instead of a star schema?

Only when a dimension is very large or shared across many fact tables. The storage a snowflake saves is small, and the extra joins slow every query. Star is the right default.

What is a fact table?

The central table in a star schema. Each row records one measurable business event at a declared grain, such as one order line. It holds the numeric measurements plus a key to each dimension table.

Should a data warehouse be normalized?

No. Normalization serves transactional workloads by removing duplication. A warehouse deliberately repeats descriptive data so analytical queries need fewer joins. Normalize the sources, denormalize the warehouse.

What are the steps in designing a data warehouse?

Pick one business process, declare the grain of the fact table, choose the dimensions, then choose the facts. Repeat per process. The general database method behind it is covered in the 7 steps in designing a database.

Related reading

• Database design patterns: the twelve reusable patterns, including partitioning, indexing, and materialized views.
• The 7 steps in designing a database: the full schema design process.
• Grokking the System Design Interview: how storage choices like these are tested in interviews.