Data warehouse

Teams build a data warehouse when the questions they need to answer span more than one system. Revenue by product by region needs the orders database, the catalog and the CRM, and nobody wants to join them by hand every Monday.

When do you need a data warehouse?

• Reporting across systems. The same customer appears in billing, support and marketing tools, and the business wants one view.
• Protecting production. Analytical queries scan whole tables and compete with the application for the same database.
• Keeping history. Application databases overwrite rows. A warehouse keeps every version.
• One definition of each metric. When “active customer” is computed in one place, every dashboard agrees.

What are the parts of a data warehouse?

The diagram at the top of this page shows a typical setup. The warehouse is the green region, but it only works with what surrounds it.

• Sources. The systems of record: application databases, SaaS tools, spreadsheets and event streams.
• Pipelines. Jobs that copy data in. Batch loads run on a schedule, usually nightly, while streaming loads land events within minutes. See ETL pipeline for how these are built and data pipeline architecture for how batch and streaming fit together.
• Staging. Raw copies of the source tables, loaded as they are. Only the next layer reads from here, which makes reloads safe.
• Core tables. Cleaned, deduplicated and joined tables that model the business: customers, orders, subscriptions. This is where data modeling happens.
• Data marts. Subject-area tables built for one audience, such as a sales mart, often in a star schema.
• Consumers. BI dashboards, analysts writing SQL and ML pipelines, each reading from the layer that suits them.

How does a data warehouse compare with a data lake, a lakehouse and a database?

The names get used loosely. The practical differences are what is stored, when the schema is applied and what the system is tuned for.

System	What it stores	Schema applied	Tuned for
Application database	Current state of one application	On write, strictly	Many small reads and writes
Data warehouse	Structured tables from many systems, with history	On load	Large scans and aggregations
Data lake	Raw files of any format, cheaply	On read	Storage and flexibility
Lakehouse	Files in the lake with table metadata on top	On load, loosely	Warehouse queries over lake storage

A lakehouse is a data lake with a table format layered over the files, so SQL engines can treat them as warehouse tables. There, the staging, core and mart layers described above are usually called bronze, silver and gold, the medallion architecture.

What is a star schema?

A star schema is the most common way to organize a data mart. It separates measurements from the things they describe.

• Fact table. One row per event, such as a sale, with numeric measures like quantity and revenue and a foreign key to each dimension.
• Dimension tables. One row per thing you group by: a date, a product, a customer, a store. They hold descriptive columns and change slowly.

A query like “revenue by product category by month” joins the fact table to two dimensions and sums one column, which the warehouse does quickly over billions of rows. The dimensions are deliberately denormalized compared with an application database schema, because the goal is simple joins, not avoiding duplicate text. The diagram uses standard ERD notation.

How does data move through a data warehouse?

Follow the hero diagram left to right. The app database, CRM and spreadsheets are copied nightly by a batch job, and the event stream is loaded continuously. Both land in staging as raw copies.

Inside the warehouse, SQL transformations turn staging into core tables, and aggregations turn core tables into data marts. Each arrow is a job someone can rerun, so when a dashboard number looks wrong you can trace it back one layer at a time, which is what data lineage tools automate. On the right, ML models and analysts read the core tables, and BI dashboards read the marts.

How do you build a data warehouse?

1. Start from the questions. List the ten metrics the business needs most, and the sources each one draws on.
2. Load sources raw. Copy the tables you need into staging without changing them. Resist cleaning on the way in.
3. Model the core. Define customers, orders and the other business entities once, with tests for uniqueness and referential integrity.
4. Build marts for each audience. Give finance and sales their own star schemas with the grain and names they use.
5. Point consumers at the right layer. Dashboards read marts, analysts read core, nobody reads staging.
6. Diagram it. Keep an architecture diagram of the sources, layers and consumers, and a data flow diagram for any pipeline that is not obvious.

What are common data warehouse mistakes?

• Transforming on the way in. If the raw copy is lost, every bug in a transformation is unrecoverable. Load first, transform in SQL.
• One giant layer. Without staging, core and marts, every table depends on every other, and one change breaks dashboards nobody knew about.
• Modeling the source system. A warehouse that mirrors the CRM’s tables is as hard to query as the CRM. Model the business instead.
• No owner for definitions. When each team computes revenue its own way, the warehouse becomes one more source of disagreement.
• Skipping cost controls. Cloud warehouses charge per scan, and one unbounded dashboard query can cost more than the pipelines.

What tools help with a data warehouse?

• Cloud warehouse platforms. Managed services that separate storage from compute, so you pay for scans rather than servers.
• Transformation and orchestration tools. Frameworks that run SQL models in dependency order and test them, plus schedulers that run the loads.
• AI diagramming tools. Tools like Eraser turn a description of your sources, layers and consumers into an architecture diagram, and a schema into a database schema diagram, which is the quickest way to document a warehouse that already exists.

For more on this kind of diagram, see data model diagrams.

Frequently asked questions