DotOne teamWhat is a data warehouse?
A data warehouse is a database built to answer questions about the business. It collects data from systems such as your ERP, CRM, accounting software and spreadsheets, cleans it, puts it into a consistent shape, and keeps its history.
Your ERP is designed to record work: create an invoice, receive stock, close a job card. A data warehouse is designed to analyse that work: sales by region over five years, scrap rate by machine and shift, or margin by customer after discounts and returns.
How a data warehouse works
- Sources. Operational systems: ERP, CRM, HRMS, e-commerce, machines and spreadsheets.
- ETL or ELT. Data is extracted from each source, transformed (cleaned, de-duplicated, mapped to common codes) and loaded into the warehouse, usually on a schedule. In ELT, raw data is loaded first and transformed inside the warehouse.
- Storage. Data is stored in tables designed for fast reading of large volumes, often in a star schema: central fact tables surrounded by dimension tables.
- Consumption. BI dashboards, reports, spreadsheets and data science tools query the warehouse.
Example: a data warehouse for a manufacturer
A manufacturer that wants to analyse sales might build a star schema like this:
| Table | Type | What it holds |
|---|---|---|
| fact_sales | Fact | One row per invoice line: date, customer, item, quantity, value, discount, tax |
| dim_date | Dimension | Day, week, month, quarter, financial year |
| dim_customer | Dimension | Customer, city, state, region, sales person, channel |
| dim_item | Dimension | Item, category, brand, size, unit of measure |
| dim_plant | Dimension | Plant or warehouse the goods shipped from |
With this shape, a question like "sales of 18 mm plywood in Gujarat by quarter for the last three financial years" is a simple, fast query.
Data warehouse vs database
| Operational database (OLTP) | Data warehouse (OLAP) | |
|---|---|---|
| Purpose | Run daily transactions | Analyse and report |
| Typical work | Many small reads and writes | Few large reads across millions of rows |
| Data | Current state | History, often many years |
| Sources | One application | Many systems combined |
| Design | Normalised to avoid duplicates | Denormalised (star schema) for fast queries |
What is a data lake, and how is it different?
A data lake is a store of raw data in its original form: database exports, log files, sensor readings, images, PDFs, CCTV video. Nothing has to be structured before it goes in. The structure is applied when someone reads the data.
- Data warehouse: structured, cleaned data with a fixed design. Best for business reporting that has to be right every time.
- Data lake: everything, raw and cheap to store. Best for data science, machine learning and data you are not sure you need yet.
- Lakehouse: a newer pattern that keeps data in a lake but adds warehouse-style tables and controls on top.
A data mart is a smaller warehouse for one department, for example a sales mart or a finance mart.
Does your business need a data warehouse?
Many growing manufacturers do not, at least not yet. If most of your operations run in one ERP, its built-in reports and dashboards already cover sales, stock, production and purchase from a single source of truth.
A data warehouse starts to pay off when:
- Data you need sits in several systems that do not talk to each other.
- You need years of history that your operational system no longer keeps or slows down on.
- Heavy reports are slowing down daily work in the ERP.
- You have analysts or a BI team who need a stable, documented model to build on.
Whichever way you go, the quality of the warehouse depends on the quality of the master data feeding it. See our guide on master data management.