# What is a data warehouse?

A database built for analysis: cleaned, structured data from many systems, kept in one place and shaped for fast reports and dashboards.

- Page: https://tecyfy.com/concepts/data-warehouse
- Subject: Data engineering in the AI era (https://tecyfy.com/concepts)
- Level: beginner

The database that takes a shop's orders is built to write one order at a time, quickly, all day. Ask it for last year's sales by region and month, and it has to read millions of rows while customers are still checking out. A data warehouse exists so that question has somewhere else to go.

## Built for questions, not for transactions

A warehouse collects data from the systems that run the business (orders, payments, stock, the CRM) into one place, cleaned and arranged for analysis. It began as a tool for decision support: reports and dashboards for the people running the company.

Data goes in **schema-on-write**. Before a row is stored it must fit a table whose columns, types and meaning were designed in advance, usually around the questions the business asks. That up-front work is why warehouse numbers are trusted, and why reports on them are fast.

## What the first warehouses could not do

The first generation ran as appliances in a company's own data centre, with storage and computing power in one box. You paid for enough of both to cover your busiest day, and as data grew, so did the bill.

They also only held tables. Video, audio and text documents could not be stored or queried at all. As more of a company's data took those forms, it went somewhere cheaper: the [data lake](https://tecyfy.com/concepts/data-lake).

For about a decade the usual answer was both. Everything landed in a lake, and a cleaned subset was copied again into a cloud warehouse such as Redshift or Snowflake. That meant 2 copies, 2 bills for storage and a warehouse always a load or 2 behind. Many warehouses also kept data in their own formats, which made moving it costly. The [Lakehouse](https://tecyfy.com/concepts/lakehouse) grew out of that 2-tier setup: warehouse tables and guarantees on top of cheap open files.

## What still makes something a warehouse

The products have changed; the job has not. A warehouse, or the warehouse layer of a lakehouse, is the place where data has agreed columns, agreed meaning and enough checking that a finance team will sign off a number from it. In the [medallion](https://tecyfy.com/concepts/medallion-architecture) pattern those are the gold tables.

## Why it matters for AI

An assistant that answers "what was revenue last quarter?" is only as good as what it reads. If it reads raw order events, it has to guess whether refunds count and which currency to use. If it reads the warehouse's agreed tables, those choices were made once, by people, and checked.

That is why the warehouse matters more with AI, not less. The tables, and a [semantic layer](https://tecyfy.com/concepts/semantic-layer) that names the metrics on them, are what turn [text-to-SQL](https://tecyfy.com/concepts/text-to-sql) from a guess into a lookup.

## Related

- [Data Lake](https://tecyfy.com/concepts/data-lake): Cheap storage that holds data of any kind in its raw form, usually as files in cloud object storage, to be cleaned and structured later.
- [Lakehouse](https://tecyfy.com/concepts/lakehouse): One store for all of an organisation's data: cheap files in cloud storage, as in a data lake, with the tables, transactions and fast queries of a data warehouse on top.
- [Semantic Layer](https://tecyfy.com/concepts/semantic-layer): One shared definition of business terms and metrics, such as revenue or active customer, that reports, dashboards and AI assistants all read instead of writing their own.

## Sources

- [Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics](https://www.cidrdb.org/cidr2021/papers/cidr2021_paper17.pdf)
- [What is a data lakehouse?](https://docs.databricks.com/aws/en/lakehouse/)