Start here
Data Warehouse in plain language
Operational systems record current business events. A warehouse provides a structure for questions across products, periods and teams without making every report reconstruct those relationships independently.
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 connects to dimension tables that hold descriptive attributes. A snowflake schema normalises some dimension attributes into related tables. Choose the structure around maintainability and query needs; neither shape automatically fixes inconsistent source data.
For revenue by product category and region, join sales facts to the appropriate product and geography dimensions, filter the reporting period and aggregate the measure. Check for duplicate dimension keys: a many-to-many join can multiply revenue even when the query executes successfully.
From operational records to analytical queries
Operational systems prioritise everyday reads and writes such as placing an order or updating a customer. A warehouse integrates their history for analytical questions spanning many records. ETL or ELT feeds raw or staged data, transformation models apply business rules, and curated tables support BI and analytical workloads.
Connectors move source records, orchestration coordinates dependencies and transformation tools such as dbt build analytical models. These are distinct responsibilities even when one platform supplies several of them.
Analytical engines can distribute query work and use storage layouts designed for scans and aggregation. Performance still depends on data volume, layout, filters, joins, concurrency and compute configuration. Separating storage from compute does not mean that storage, ingestion or idle capacity is always free.
Warehouse, data lake and lakehouse
A data lake can retain files and records in varied formats, including raw inputs that are not yet modelled for reporting. A warehouse emphasises governed analytical tables and queries. A lakehouse combines lake storage with table-management and analytical capabilities; the labels overlap across products.
Keeping raw data is useful for reprocessing and machine-learning work, while curated layers make recurring reports easier to interpret. In either architecture, define access, retention, quality checks, ownership and a trusted reporting layer.
Choosing a platform for your organisation
The original platform examples—Snowflake, Amazon Redshift, Google BigQuery and Azure Synapse Analytics—are starting points for a shortlist, not a universal ranking. Compare your existing cloud, source connectors, security requirements, regional availability, team skills and integration with BI tools.
Run a representative workload that includes ingestion, transformation, concurrent reporting and recovery. Compare total cost under the actual pricing model and check data-transfer costs. Existing cloud alignment can simplify operations, but should not replace a workload-based evaluation.
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 be hosted in different environments. Many organisations run warehouses on-premises, while cloud services such as Snowflake, BigQuery, Amazon Redshift and Azure Synapse are popular because they separate storage from compute and scale on demand. The right choice depends on data residency, cost and existing skills.
Is a warehouse a replacement for every operational database?
No. Analytical and transactional workloads have different requirements, though platforms can overlap. Operational databases handle many small reads and writes for live applications, while warehouses are optimised for large analytical queries. Most organisations 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.