A data warehouse is a separate database into which a company copies the records of all its other systems, so that analysts can ask questions across all of them at once. Banks, retailers and app companies all use one. It is also called an enterprise data warehouse, or EDW. Today, most companies rent a cloud data warehouse, instead of buying their own servers for it.
A warehouse is different from the database an app runs on. The app's database, such as a relational database, records each order the moment it happens. It is built for many small writes. A warehouse is built for a few very large reads: questions that add up millions of rows. These two kinds of work are explained in OLTP and OLAP. Keeping them apart means that a heavy question never slows down the customers using the app.
Most warehouses store data column by column, as a columnar database does. A query that adds up the price of every sale reads only the price column, not every detail of every sale. A column also holds similar values, so it compresses well and takes less disk space.
Data reaches the warehouse in two ways. An ELT pipeline copies data from each source system on a schedule, often every night. Change data capture (CDC) copies each change within seconds of it happening.
Take, for example, a clothing retailer with 300 stores across south India, run from Coimbatore. Its billing system, its online shop and its returns desk each keep their own records. Every night, the day's 1.5 lakh sales and 4,000 returns are copied into the warehouse. During Pongal week, the head of merchandising asks which silk saree designs sold best in Madurai, and how many of them came back. The query joins sales and returns from three systems and reads 90 lakh rows. It returns in about eight seconds. The answer then appears on the company's business intelligence (BI) dashboards.
How is a cloud warehouse paid for? Usually, storage and computing are charged separately. The company pays a small monthly amount for every terabyte it stores. It pays separately for the computing that each query uses. So a badly written query that reads the whole history every few minutes shows up on the bill. Data observability tools now look for such queries.
The choice of warehouse is hard to reverse. Years of tables, queries and dashboards get built around one product. This is called vendor lock-in. A data lakehouse tries to reduce it, by keeping the data in open file formats that other software can also read.