Data Warehouse Concepts & Architecture
A data warehouse is a central, analytics-optimized store of integrated historical data. Understand OLTP vs OLAP, warehouse layers, and how modern cloud warehouses separate storage from compute.
What is a Data Warehouse?
A data warehouse is a centralized repository that integrates data from multiple operational systems and stores it in a structure optimized for analysis and reporting. Bill Inmon defined it as *subject-oriented, integrated, time-variant, and non-volatile*.
OLTP vs OLAP
Operational databases and warehouses serve very different workloads:
- OLTP — Many small, fast reads/writes (orders, payments). Row-oriented, highly normalized.
- OLAP — Fewer, large analytical scans and aggregations. Column-oriented, denormalized.
- OLTP optimizes for transaction throughput; OLAP optimizes for query speed over large volumes.
- Running heavy analytics on an OLTP system slows down the application — hence the warehouse.
Warehouse Layers
Most warehouses organize data into progressive layers, often called the *medallion* or *multi-hop* architecture:
- Raw / Bronze — Data landed exactly as received from sources.
- Staging / Silver — Cleaned, deduplicated, typed, and conformed data.
- Mart / Gold — Business-ready models (facts and dimensions, aggregates) for BI tools.
Modern Cloud Warehouses
Cloud platforms like Snowflake, Google BigQuery, Amazon Redshift, and Azure Synapse changed warehousing by:
- Separating storage and compute so each scales independently. - Using columnar storage with compression for fast scans. - Offering pay-per-use or elastic pricing. - Supporting semi-structured data such as JSON and Parquet natively.
-- Columnar warehouses only read the columns you select
SELECT product_category, SUM(revenue) AS total_revenue
FROM gold.sales_summary
WHERE sale_date >= DATEADD(month, -3, CURRENT_DATE)
GROUP BY product_category
ORDER BY total_revenue DESC;Key Takeaways
- Warehouses are built for analytics; OLTP databases are built for transactions.
- Layered architecture (raw → staging → marts) improves quality and traceability.
- Cloud warehouses decouple storage from compute for elastic scaling.
- Columnar storage makes aggregations over large tables fast.
Contact Us
Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com