Two different jobs
Organizations use data in two broadly different ways. One is running day-to-day operations: taking an order, updating a stock level, recording a payment. The other is looking back across lots of that activity to understand it: which products sell best, how sales vary by season, where costs are rising.
A database in the everyday sense, such as the one behind a shop’s checkout, is built for the first job. A data warehouse is built for the second. The two are related, and a warehouse is itself a kind of database, but they are designed around different needs.
What a database does
An operational database, often called a transactional database, handles a constant stream of small actions. Each action reads or changes a few records: add this order, change this address, deduct one item from stock. The system must do this quickly for many users at once, and it must keep the data correct. If a payment is recorded, the matching order must be recorded too, and if something fails halfway, the system should not be left in a confused state.
This type of work is often called online transaction processing. Data is typically stored in many related tables arranged to avoid repetition, so a customer’s address is held in one place rather than copied onto every order. That design is efficient for updates, but it spreads information across many tables.
What a data warehouse does
A data warehouse is a central store of data collected from several sources and organized for analysis. It typically holds history, not just the current state. Where an operational system might keep only a customer’s present address, a warehouse can keep the sequence of changes, so past reports can be reproduced.
Its workload is different: fewer users, but large queries that scan many records and summarize them, such as “total sales by region for each month over five years.” This is often called online analytical processing. Warehouses are usually arranged so that these questions are easy and fast to answer, with measurable events such as sales kept alongside descriptive categories such as product, store and date.
How data gets there
Data usually reaches a warehouse through a process commonly described as extract, transform, load:
- Extract data from source systems such as sales, finance or customer support.
- Transform it by cleaning errors, converting formats, and applying shared definitions.
- Load it into the warehouse on a schedule, such as nightly, or continuously.
The transformation step is where much of the value lies, because it gives different sources a common meaning. It also depends on data quality: if the sources contain duplicates or inconsistent values, those problems need to be handled or they appear in every report.
Key differences
| Aspect | Operational database | Data warehouse |
|---|---|---|
| Main purpose | Run daily operations | Analyze and report |
| Typical action | Small reads and writes | Large reads and summaries |
| Data held | Current state | Current plus history |
| Sources | Usually one application | Many systems combined |
| Design goal | Fast, reliable updates | Fast, consistent queries |
| Users | Applications, staff, customers | Analysts, managers, reporting tools |
Why not just report from the operational database?
Small organizations often do exactly that, and it can be perfectly adequate. Problems tend to appear as scale and complexity grow:
- Heavy reports can slow the system that customers and staff depend on.
- Data is spread across several applications that do not share definitions.
- Operational systems may keep little history.
- Table structures built for updates can make analytical queries awkward.
A warehouse separates the two workloads, so analysis does not interfere with operations, and gives different departments a shared set of figures.
What a warehouse is used for
Warehouses commonly feed reports and the dashboards covered in the article on business intelligence, support general data analytics, and supply cleaned data to teams building machine learning models. Their value depends heavily on agreed definitions and ownership, which is a data governance matter. Without it, a warehouse can simply centralize the disagreements.
Where it sits among other options
A newer approach, the data lake, stores raw data in flexible form rather than organized tables, and the choice between them is the subject of a separate comparison of data lakes and warehouses. The topic of very large and varied data is covered under big data.
Cloud warehouses
Many organizations now rent warehouse capacity through cloud computing instead of running their own hardware. This can make it easier to start small and scale up, but it brings its own considerations: how charges are calculated (see cloud pricing), where the data physically sits, and how hard it would be to move later, a concern known as vendor lock-in. Security of stored data, including encryption basics and controls on who can see what, remains important either way. Responsibility is typically split between provider and customer, as explained in the shared responsibility model.
Costs and caveats
Building a warehouse takes planning, skilled people and ongoing maintenance. Loading pipelines break, definitions drift, and reports can lag behind real events. Not every organization needs one, and a well-run operational database with a few reporting tools may be enough for a long time. The sensible question is not whether a warehouse is modern, but whether the questions being asked have outgrown what the existing systems can reliably answer.