Data warehouse
A database built for analysis and reporting, where cleaned data from many systems is kept together in one place.
Last updated
What it is.
Your app's database is built for many small reads and writes. A warehouse is built for large queries over lots of rows, such as revenue by month by region. Data from the app, payments, CRM and marketing tools is copied in, usually by an ETL or ELT pipeline, and shaped into tables that are easy to query.
Warehouses usually store data by column, which makes big scans and totals fast. Analysts and BI tools query the warehouse, not the live app.
Why it matters
Running heavy reports on the production database slows the app down for users. A warehouse separates the two and gives one trusted place for numbers, so finance, product and sales stop arguing over whose spreadsheet is right.
In a product build
We send product events and database changes to the warehouse through a pipeline, define key metrics once in shared models, and leave personal data out or masked where the analysis does not need it. Access to the warehouse follows the same role rules as the app.
Common mistakes.
- 01
Copying raw data in with no shared definitions, so every report counts differently.
- 02
Putting personal data in the warehouse with weaker access controls than the app.
- 03
Using the warehouse to power user-facing features that need fast, fresh reads.
Work it out with a tool.
Related terms.
- ETL (extract, transform, load)A data pipeline that pulls data out of source systems, cleans and reshapes it, and loads it into a destination such as a data warehouse.
- Read replicaA read-only copy of a database that is kept in sync with the main database and takes read queries off it.
- Event-driven architectureA way of designing software where parts of the system publish events about what happened, and other parts react to them on their own.
- A/B testAn experiment that shows two versions of something to random groups of users and measures which one does better on a chosen metric.
- Object storageA way of storing files as objects in buckets, each with an ID and metadata, reached over HTTP and built to scale to huge amounts of data cheaply.
