Introduction to Data Warehousing

I have been working as a Data Engineer for almost a year now, and a large part of that time has gone into data warehousing.

This post is the first of a series on data warehousing and the concepts around it. I want it to be a kind of roadmap: something that helps new team members get up to speed quickly, and that anyone on the internet can use to start their own data warehouse journey.

1) Data management in an organization

Every organization does two very different things with its data.

• It runs the business. A customer places an order, an invoice is issued, a payment is recorded, stock is decremented. These are operational or transactional needs: many small, fast reads and writes, each touching a few rows, which must be correct right now.
• It understands the business. How much did we sell last quarter, by region? Which customers are we losing? Is this product line growing? These are analytical needs: fewer queries, but each one reads and aggregates large amounts of data, often over long periods.

Data management is the set of systems and practices that serve both needs. The history of the data warehouse is the history of how organizations learned to serve them separately.

2) The early era: one database for everything

When organizations first moved their records into databases, the natural design was a single system. The applications wrote to it, and the reports read from it. Same database, same schema, same tables, often the same people writing both the application and the reports.

At small scale this works well, and it is the right choice for many young products today. There is only one copy of the data, so nothing is out of sync. Reports are always up to date. There is no extra infrastructure to build or pay for. When a manager wants a number, someone writes a query against the production tables and gets it.

3) Why it stopped working

As the organization grows, so do the data volumes, the number of applications and the number of people asking questions. The single-database model starts to crack in several places at once:

• Contention. Transactions are short writes that must finish in milliseconds. Analytical queries are long scans over millions of rows. On the same system, a heavy report competes for CPU, memory, disk and locks with the transactions the business depends on. Production slows down, and reports end up banned during business hours.
• A schema built for writes, not reads. Operational schemas are normalized so that each fact is stored once and updates are safe. That is exactly right for writing, but answering a simple business question can require joining a dozen tables, and the column names reflect the application, not the business.
• No history. Operational systems keep the current state. When a customer moves or a product changes category, the old value is overwritten, and “what did this look like last year?” can no longer be answered.
• Many systems, inconsistent definitions. A growing organization does not have one database; it has a CRM, a billing system, a support tool, a website. The same customer appears in each with different identifiers, and every team computes its indicators with its own filters. The result is the “five versions of revenue” problem.
• Point-to-point extracts. To work around all of this, teams start extracting data from each system into spreadsheets and small departmental databases. With N sources and M consumers, the number of extracts grows toward N × M, each one a fragile interface and a load on production.

These problems share one cause. The same system is asked to run the business and to analyse it: short transactions and long scans compete for the same resources, a schema designed for writing is used for reading, current-state tables are expected to answer questions about the past, and data spread across many systems is expected to give one consistent answer.

4) The evolution: two kinds of systems

Over the past few decades, organizations converged on a simple idea: stop asking one system to do both jobs. Keep operational systems optimized for running the business, and copy their data into a separate analytical system optimized for understanding it. The two families have names: OLTP (online transaction processing) and OLAP (online analytical processing).

The two are optimized for opposite workloads:

The split addresses each of those problems. Heavy queries no longer run on production, so they no longer slow down transactions. The analytical system can use a schema designed for reading, keep the history that operational systems overwrite, and integrate data from every source into one consistent view. This separate analytical system is the data warehouse.

5) The Data Warehouse

Definition

Bill Inmon, often called the father of the data warehouse, defined it as a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision-making process. These four properties map onto the problems of the single-database model:

• Subject-oriented: organized around business subjects (customer, product, sale), not around applications. This fixes the “schema built for writes, not reads” problem.
• Integrated: data from different sources is reconciled into consistent keys, formats and definitions. This fixes the “many systems, inconsistent definitions” problem: one customer, one definition of revenue.
• Time-variant: data is stored with time, so you can look at any point in the past. This fixes the “no history” problem.
• Non-volatile: once loaded, data is not updated in place by transactions; it is added to. The past stays the past, so reports are reproducible.

What it was built for

Decision support. The warehouse exists to separate running the business from understanding it, and to give the whole organization one consistent, historical version of the numbers. It is not a backup of production, and it is not a place to dump every table “just in case”.

Where it came from

• 1988, IBM. Barry Devlin and Paul Murphy published “An architecture for a business and information system” in the IBM Systems Journal (vol. 27, no. 1), describing a “business data warehouse”: one integrated store of business data, separate from operational systems, built for reporting and analysis.
• 1990s, Bill Inmon. Building the Data Warehouse (first edition 1992) popularized the concept and the definition above.
• 1996, Ralph Kimball. The Data Warehouse Toolkit brought dimensional modelling (star schemas, facts and dimensions) to a wide audience.

6) Anatomy of a data warehouse

Most warehouse platforms are made of the same building blocks:

• Sources: operational databases, applications, files, APIs, events.
• Ingestion, ETL vs ELT: ETL (extract, transform, load) transforms data before loading it into the warehouse, historically because warehouse compute was expensive. ELT loads raw data first and transforms it inside the warehouse, which became common with cheap, scalable cloud warehouses. Both are valid; what matters is where the logic lives and whether it is versioned and tested.
• Staging: a landing zone holding source data as close to the original as possible. It lets you reprocess without re-extracting.
• Core / integrated layer: where integration happens: consistent keys, cleaned values, history. How this layer is modelled is where warehouse architectures differ most.
• Data marts: subsets organized for a subject area or department (sales, finance), usually as star schemas.
• Semantic / BI layer: business names, metric definitions and relationships, so that “revenue” is defined once and reused by every dashboard.
• Cross-cutting concerns: metadata and lineage (where does this number come from?), data quality tests, orchestration (what runs when, in which order), and security.

Every warehouse has these building blocks. Where architectures differ is in how they model the core and the marts, and in which order they build them.

7) The architectures

Inmon: the Corporate Information Factory (top-down)

Inmon’s approach starts with an enterprise data warehouse modelled in third normal form (3NF), integrated across the whole company. Data marts are then built from the warehouse: they are dependent data marts. The wider framework, described with Claudia Imhoff and Ryan Sousa in the 1998 book of the same name, is called the Corporate Information Factory (CIF).

• Strengths: one integrated, non-redundant source of truth; resilient to change in business processes; strong for consistency and audit.
• Weaknesses: large upfront modelling effort, long time before the first business value, 3NF models are hard for business users to query directly.

Kimball: dimensional modelling and the bus architecture (bottom-up)

Kimball starts from the business process (taking an order, shipping, invoicing) and models each one as a star schema: a central fact table of measurements, surrounded by dimension tables that give context (who, what, where, when). The warehouse is the sum of these stars, tied together by conformed dimensions: dimensions such as Date, Customer or Product that mean exactly the same thing in every star. The plan that lists which processes share which dimensions is the bus matrix.

The core concepts:

• Grain: what exactly one row of the fact table represents (“one product line on one receipt”). Declaring the grain is the first and most important design decision.
• Facts: numeric measurements at that grain (quantity, amount). Additive facts can be summed across all dimensions; semi-additive ones (balances) cannot be summed over time; non-additive ones (ratios) should be computed from their components.
• Dimensions: descriptive attributes used to filter and group. Use surrogate keys, not source system keys.
• Slowly Changing Dimensions (SCD): how to handle attribute changes. Type 1 overwrites the old value (no history). Type 2 adds a new row with validity dates, keeping full history. Type 3 keeps the previous value in an extra column (limited history).

Serving users: the presentation area and aggregate fact tables.

In Kimball’s terms, the stars form the presentation area: the dimensional data marts that BI tools and users query. Staging is the back room where data is cleaned and conformed, and users never query it directly.

The fact tables in the presentation area are kept at the finest grain declared above, such as one product line on one receipt, so they quickly reach millions or billions of rows. When every report computes its indicators on the fly with ad hoc queries over those rows, two problems follow: dashboards slow down as the fact tables grow, and two reports can compute the same indicator in slightly different ways.

Kimball’s answer is the aggregate fact table: a second fact table at a coarser grain whose indicators are precomputed during the nightly load. Instead of one row per receipt line, agg_sales_daily holds one row per day, store and product category, with revenue, units and margin already summed. It is built from the detailed fact_sales and joins to the same conformed dimensions, often through shrunken dimensions: conformed subsets such as a category-level version of dim_product. Dashboards read the aggregate and answer in seconds, while the detailed star stays available for drill-down and for indicators that are not precomputed. Each indicator is then defined once, in the load that builds the aggregate, rather than in every report. A semantic layer, described in the anatomy above, can sit on top and expose both levels under the same business names.

Aggregates have a cost: extra storage, a longer nightly load, and one more table to keep consistent with the detail. They stay consistent only if they are rebuilt from the detailed facts and share the same conformed dimensions, and they are worth adding only for queries that have been measured as slow.

• Strengths: fast time to value, one process at a time; intuitive for business users; excellent query performance.
• Weaknesses: without discipline on conformed dimensions, it degenerates into isolated marts, exactly the silos the warehouse was supposed to remove.

Data Vault: auditable integration

Data Vault, created by Dan Linstedt (Data Vault 2.0 is its current version), models the integrated layer with three types of tables: hubs (unique business keys, e.g. a customer number), links (relationships between business keys) and satellites (descriptive attributes with their history and load timestamps). It is insert-only and records where and when every piece of data arrived.

• Strengths: very auditable, easy to add new sources without remodelling, parallel loading, well suited to automation.
• Weaknesses: many tables and joins; not meant to be queried by business users. You almost always build star schemas (marts) on top of it.

One Big Table (OBT)

A fully denormalized wide table, with facts and all dimension attributes on every row. Columnar engines make this practical. It is great for a specific dashboard or ML feature set, but it duplicates logic, makes history and changes painful, and is best treated as a final output built from a dimensional model, not as a replacement for one.

Data lake, lakehouse and Medallion

A data lake stores raw data of any format (files, JSON, logs, images) cheaply in object storage, without imposing a schema upfront (“schema on read”). It solved scale and variety, but without governance, many lakes became “data swamps”: lots of data, little trust.

The lakehouse adds warehouse features (ACID transactions, schemas, time travel, SQL performance) on top of open file formats in the lake, using table formats such as Delta Lake, Apache Iceberg or Apache Hudi. The term was popularized by Databricks, in a 2020 blog post and a 2021 research paper.

The Medallion architecture, also popularized by Databricks, organizes lakehouse data in three layers: bronze (raw data as ingested), silver (cleaned, deduplicated and conformed) and gold (business-ready tables for reporting and ML).

Be precise about what Medallion is: it is a data layout and quality-progression pattern, not a modelling methodology. It tells you how refined the data is at each layer, but not how to model it. Bronze looks a lot like staging; silver plays the role of the integrated layer; and the gold layer is very often… Kimball star schemas. Medallion does not replace Inmon or Kimball; it can be combined with either.

8) Comparison and how to choose

How to choose, in practice:

• Small team, need results quickly, stable sources: Kimball. Start with one business process, one star, conformed dimensions from day one.
• Many volatile sources, strong audit or regulatory needs: a Data Vault (or a 3NF integrated layer) in the core, with Kimball marts on top.
• Lots of semi-structured data, ML workloads, lakehouse platform: Medallion layers, with a dimensional gold layer for BI.
• Almost always: users should query star schemas. The three options above differ mainly in what sits underneath them.

9) Common mistakes

• Not declaring the grain. If you can’t say in one sentence what a row means, the table is wrong.
• Mixing grains in one fact table. Putting order headers and order lines in the same table guarantees double counting.
• No conformed dimensions. Two “customer” dimensions with different rules is the five-versions-of-revenue problem, rebuilt inside the warehouse.
• Treating the warehouse as a dump. Copying source tables into the warehouse is not integration.
• Ignoring SCDs. Overwriting every attribute silently rewrites history, and last year’s report no longer matches last year.
• Business logic in dashboards. If revenue is computed in ten reports, you have ten definitions. Put it in the model or the semantic layer, once.
• No tests. Uniqueness of keys, non-null foreign keys, row counts reconciled with the source. Without these checks, errors reach dashboards before anyone notices them.

10) A small worked example

Take a generic retail business. The business process is selling; the grain is one product line on one receipt. The dimensions are date, store, product and customer. The customer dimension is SCD type 2, so we keep the customer’s city at the time of each sale.

-- Dimension with SCD type 2
CREATE TABLE dim_customer (
  customer_key   INT PRIMARY KEY,     -- surrogate key
  customer_id    VARCHAR(20),         -- business key from the source
  customer_name  VARCHAR(100),
  city           VARCHAR(50),
  valid_from     DATE,
  valid_to       DATE,                -- '9999-12-31' for the current row
  is_current     BOOLEAN
);

-- Fact table, grain: one product line on one receipt
CREATE TABLE fact_sales (
  date_key       INT REFERENCES dim_date(date_key),
  store_key      INT REFERENCES dim_store(store_key),
  product_key    INT REFERENCES dim_product(product_key),
  customer_key   INT REFERENCES dim_customer(customer_key),
  receipt_number VARCHAR(20),         -- degenerate dimension
  quantity       INT,                 -- additive
  sales_amount   DECIMAL(12,2)        -- additive
);

-- Sales by month and product category, for the customer's city at sale time
SELECT d.year_month,
       p.category,
       c.city,
       SUM(f.sales_amount) AS revenue
FROM fact_sales f
JOIN dim_date d     ON f.date_key = d.date_key
JOIN dim_product p  ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY d.year_month, p.category, c.city
ORDER BY d.year_month, revenue DESC;

The query stays readable: join the fact to its dimensions, group, sum. This is what dimensional modelling is designed for: business questions become simple queries. And because the fact points to the customer row that was valid at the time of the sale, a customer who moved last month does not rewrite last year’s sales by city.