Data Warehouse: meaning and definition
Data Warehouse in plain language
Operational systems record what is happening in the business now. A warehouse gives you a structure for questions across products, time periods and teams, so each report does not have to rebuild those links itself.
The grain of a table describes what one row represents. Confusing an order row with an order-line row is a common route to double-counting.
How it works
Choose the analytical grain
State whether each row represents an order, line item, daily snapshot or another unit.
Integrate keys and history
Connect records consistently and decide how changes over time will be represented.
Validate measures
Reconcile totals and inspect joins before exposing data to reports.
Illustrative example · Fictional data
Avoiding a fictional join error
- Input
- Order O-20 has a total of ₹900 and three line items. Joining the order total onto each line repeats ₹900 three times.
- Process
- Aggregate using the correct grain: count the order total once, or sum properly defined line values.
- Expected output
- Order revenue remains ₹900, not ₹2,700.
- How to check it
- A valid SQL query can still answer the wrong question. Test joins with one-to-many relationships and compare with known source totals.
Where it helps
- Combining reporting across operational systems.
- Preserving useful historical views.
- Providing consistent analytical datasets.
Operational database, warehouse and lake
- Operational database
- Supports the application’s transactions and current state.
- Warehouse
- Organises integrated data for analysis and reporting.
- Data lake
- Stores diverse data for later or varied processing. Modern platforms may combine capabilities.
Fact tables, dimensions, star and snowflake schemas
Define the grain of a fact table first: one row might represent an order line, a daily inventory snapshot or a support event. Measures belong at that grain. Foreign keys connect the facts to dimensions such as date, customer, product and geography.
In a star schema, the fact table links to dimension tables that hold descriptions. A snowflake schema splits some of those descriptions into further related tables. Choose the shape that is easier to maintain and query; neither one fixes messy source data on its own.
For revenue by product category and region, join the sales table to the product and region tables, filter the reporting period and add up the measure. Check for duplicate keys: a join that matches many rows to many rows can inflate revenue even when the query runs without errors.
From operational records to analytical queries
Operational systems focus on everyday reads and writes, such as placing an order or updating a customer. A warehouse brings their history together to answer questions across many records. ETL or ELT tools load the raw data, transformation models apply business rules, and cleaned-up tables feed BI and analysis.
Connectors move records from the source, orchestration runs the steps in the right order and transformation tools such as dbt build the tables for analysis. These are separate jobs, even when one platform does several of them.
Analysis engines can spread a query across many machines and store data in ways built for scanning and adding up. Speed still depends on how much data there is, how it is laid out, filters, joins, how many people query at once and the computing set-up. Keeping storage and computing separate does not mean storage, loading or idle capacity is always free.
Warehouse, data lake and lakehouse
A data lake can hold files and records in many formats, including raw data that is not yet shaped for reporting. A warehouse focuses on well-managed tables and queries for analysis. A lakehouse combines lake storage with table management and analysis features; the labels overlap between products.
Keeping raw data helps if you need to reprocess it or use it for machine learning, while cleaned-up layers make regular reports easier to read. Either way, set access, how long data is kept, quality checks, owners and one trusted layer for reporting.
Choosing a platform for your organisation
The original platform examples (Snowflake, Amazon Redshift, Google BigQuery and Azure Synapse Analytics) are a starting point for a shortlist, not a ranking. Compare them on your current cloud, the connectors you need, security, regional availability, your team’s skills and how they work with your BI tools.
Run a realistic workload that includes loading data, transforming it, many people reporting at once and recovery. Compare the total cost under the real pricing model, and check data-transfer costs. Using the cloud you already have can make running it simpler, but it should not replace testing with your own workload.
Related reading and learning pathways
Common pitfalls
- Central storage does not automatically create agreed metrics.
- Joins can multiply values.
- Overwriting changed attributes may erase needed historical context.
Put the idea to work
Try it yourself
Draw an order table and a line-item table. Mark the row grain and identify which values may safely be summed after a join.
Common questions
Must a warehouse run in the cloud?
No. Warehouses can run in different places. Many companies run them on their own servers, while cloud services such as Snowflake, BigQuery, Amazon Redshift and Azure Synapse are popular because they keep storage and computing separate and grow on demand. The right choice depends on where data must be kept, cost and your team’s skills.
Is a warehouse a replacement for every operational database?
No. Analysis and day-to-day transactions need different things, though some platforms do both. Operational databases handle many small reads and writes for live apps, while warehouses are built for large analysis queries. Most companies run both and move data between them.
Your next step
Continue learning
Connected concepts
Sources and further reading
Examples on this page are teaching exercises using fictional information. They are not client case studies or measured outcomes.