Data Warehouse Concepts Explained in Plain English

What are the core data warehouse concepts?

Data warehouse concepts are the shared vocabulary of the people who design and run warehouses. They cover how data is arranged, how it is loaded, how history is kept and how quality is checked. You do not need to master them to commission a warehouse, but you will make better decisions if you understand the terms.

This page is a reference. It gives a one-line glossary first, then a short explanation of each concept. If you are new to the subject, start with what is a data warehouse, then return here.

We keep this at the conceptual level. Detailed design choices are covered in data warehouse design, and the way platforms implement these ideas changes often, so check the vendor's current documentation for specifics. If you want help applying them, our data warehouse services page explains how we work.

A glossary of the main concepts

What are facts and dimensions?

Most warehouse models separate two kinds of table. Fact tables record what happened. Dimension tables describe the context.

Think of a sales order line. The quantity and the value are facts: numbers you can add up. The customer, the product, the sales region and the order date are dimensions: labels you use to group and filter those numbers.

• Facts are usually numeric, usually additive, and there are lots of them. Examples: revenue, units, hours worked, tickets closed.
• Dimensions are descriptive and far fewer in number. Examples: customer, product, employee, date, cost centre.
• Measures are the specific numbers within a fact, such as net value or discount.
• Attributes are the descriptive fields within a dimension, such as customer sector or product category.

Good dimensional modelling is about choosing the right facts and dimensions for the questions you need to answer. We cover the wider process in design guidance, and the classic book on the subject is reviewed in The Data Warehouse Toolkit.

What is grain, and why does it matter?

Grain is the level of detail that one row of a fact table represents. It is the single most important decision in a model, and the one most often left vague.

For a sales fact, the grain might be one row per order line, one row per order, or one row per customer per day. Each choice answers different questions. Order line grain can tell you which product sold. Daily customer grain cannot.

• Choose the finest grain you can justify. You can always summarise detail upwards. You cannot recover detail you never kept.
• State the grain in one sentence. For example, one row per invoice line per day of posting.
• Never mix grains in one fact table. Mixing them produces double counting and totals that look right but are wrong.
• Match grain to the question. If nobody needs line detail, a coarser grain may be cheaper and quicker.

What are star and snowflake schemas?

A schema is the pattern in which tables are arranged and joined. Two patterns dominate dimensional modelling.

In a star schema, one fact table sits in the middle and each dimension connects directly to it. The picture looks like a star. Queries are simple, reports are fast and business users can usually follow the model.

In a snowflake schema, dimensions are normalised into several linked tables. A product dimension might split into product, category and department tables. This removes repeated text and can ease maintenance, but it adds joins and makes the model harder to read.

For most reporting needs, a star schema is the sensible starting point. Tools such as Power BI work well with it. Snowflaking is worth considering only when it solves a real maintenance problem.

What is the difference between ETL and ELT, and what is staging?

Both terms describe moving data from source systems into the warehouse. The difference is where the transformation work takes place.

• ETL (extract, transform, load) reshapes and cleans the data on a separate processing server, then loads the finished result into the warehouse.
• ELT (extract, load, transform) loads the raw data into the warehouse platform first and transforms it there, using the platform's own processing power.
• Which is better? Neither, always. ELT has become common on cloud platforms where compute is flexible. ETL still suits cases where data must be filtered or masked before it lands.

Staging sits in front of either approach. A staging area is where extracted data lands untouched. Keeping that raw copy gives you a record of what the source said at the time, and lets you rerun the transformation without going back to the source.

Staging also protects the source. You take a single, quick copy and do the heavy work elsewhere. Teams that skip staging often find they cannot explain a figure, because the original has changed or gone. For the movement side of the work, see system integration.

What are slowly changing dimensions and surrogate keys?

Descriptions change. A customer moves region, a product moves category, an employee changes department. A slowly changing dimension is a rule for what the warehouse does when that happens.

• Overwrite (often called type 1). Replace the old value with the new one. History is lost, so reports restate the past using today's description.
• Add a new row (often called type 2). Keep the old row, mark it as ended and add a new row with start and end dates. History is preserved.
• Keep limited history (often called type 3). Store the previous value in an extra column. Useful for one specific comparison, such as old and new region.

The right choice depends on the question. If you report sales by the region a customer belonged to at the time, you need the row-versioning approach. If only the current region matters, overwriting is simpler.

Row versioning is why surrogate keys exist. A surrogate key is an identifier the warehouse generates for each dimension row, separate from any code in the source system. When one customer has three versions over time, each version has its own surrogate key, and each fact row points to the version that applied when it happened.

Surrogate keys also protect you from source changes. If a system is replaced and customer codes are reissued, the warehouse keys stay stable.

How do data quality and metadata fit in?

Data quality describes whether data is complete, valid, consistent and timely enough to rely on. In a warehouse it is managed through checks on the way in, such as missing values, duplicates, out-of-range numbers and records that do not match a known customer or product.

Good practice is to decide what happens to failures. Some should stop the load. Some should be loaded but flagged. Some should go to a review list for the business to correct. The decision belongs to the data owner, not the developer. Our article on improving data accuracy across departments covers the habits needed at source.

Metadata is data about the data. It records where each field came from, what it means, how it was calculated, who owns it and when it was last loaded. Without it, a warehouse becomes a black box that only its builders understand.

• Business metadata gives definitions in plain language, such as what counts as an active customer.
• Technical metadata records table structures, data types and load logic.
• Operational metadata records load times, row counts and failures.

Treat metadata as part of the deliverable. A data dictionary that is kept current is one of the cheapest ways to protect trust.

What is partitioning, at a conceptual level?

Partitioning means splitting a large table into smaller parts, usually by date. Instead of one huge sales table, the platform manages a part for each month or year.

There are two practical benefits. Queries that ask about one period can skip the other parts entirely, which makes them faster. And maintenance becomes easier, because you can load, archive or delete a single part without touching the rest.

You do not need partitioning for small data sets, and some platforms handle it automatically. It becomes relevant as tables grow into very large volumes. How a platform implements it varies, so check the vendor's current documentation when you reach that point.

How do the concepts fit together, and where should you go next?

The concepts describe one pipeline. Here is how they connect:

1. Extract data from the sources and land it in staging unchanged.
2. Check data quality and record the results as metadata.
3. Transform the data, using ETL or ELT, into clean dimension and fact tables.
4. Apply the grain you chose and assign surrogate keys to dimension rows.
5. Handle changes with the slowly changing dimension rule agreed for each dimension.
6. Store in a star schema and, where tables grow large, partition them.
7. Serve the result to reports and dashboards.

Other approaches exist. Data Vault, for example, is a modelling method with different goals, and data vault vs data warehouse explains where it fits. Dimensional modelling remains the usual choice for reporting-focused work.

If you want these concepts applied to your own data, NetMonkeys' data warehouse team can walk through your systems and questions. For the surrounding business reporting, our business intelligence consultants work alongside it.