Data Architecture Deep-Dive #1: Data Warehouse — The Foundation of Governed, Self-Service Analytics
From raw ingestion to semantic layer — a technical guide for architects and engineers.
Introduction
If you’re designing a modern data platform, the Data Warehouse (DWH) is often where the conversation starts. Not because it’s the only answer — it isn’t — but because it solves the hardest problem in enterprise analytics: trust. Business decisions made on untrusted data are worse than decisions made on no data. The DWH’s entire architecture is built around solving that trust problem at scale.
This is Post 1 of my 5-part Data Architecture Series. We’ll cover all five architectures — Warehouse, Lake, Lakehouse, Mesh, and Fabric — with a specific lens on which architecture enables self-service analytics and for whom.
What is a Data Warehouse?
A Data Warehouse is a centrally managed, structured repository optimized for analytical query workloads. Data is ingested via ELT pipelines, conformed to a predefined schema, and stored in optimized columnar formats. Modern cloud warehouses — Snowflake, BigQuery, Redshift, Azure Synapse — add elastic compute, compute-storage separation, and near-real-time ingestion on top of the classic model.
The Four-Layer Architecture
Every mature DWH follows a layered pattern. The raw/landing zone holds exact copies of source data untouched. The staging/ODS layer cleanses, deduplicates, and applies business keys. The integration/core layer models facts and dimensions. The semantic/presentation layer abstracts business logic into reusable, governed metrics.
Star Schema vs Snowflake Schema
dim_customer → dim_region), reducing storage redundancy at the cost of additional joins.Star schema stores all dimension attributes flat — customer name, region, tier all in one dim_customer table. Fewer joins means faster BI queries. Snowflake schema normalizes dimensions into sub-tables (e.g. dim_customer → dim_region). It reduces storage redundancy but requires additional joins. For most BI workloads, star schema wins on query performance. Data Vault is a third option worth knowing — see Diagram 3.
Get Chandraknth’s stories in your inbox
Join Medium for free to get updates from this writer.
Kimball vs Inmon vs Data Vault 2.0
Kimball is bottom-up: build subject-area data marts first, integrate via conformed dimensions across a bus matrix. Inmon is top-down: build a normalized 3NF enterprise DWH first, then derive data marts from it. Data Vault 2.0 structures everything into Hubs (business keys), Links (relationships), and Satellites (descriptive attributes) — making schema evolution and full audit trails first-class features. Modern stacks often combine all three: an Inmon-style core, Kimball presentation marts, and Data Vault for CDC-heavy sources.
Self-Service Enablement
The DWH is the most mature architecture for self-service BI. The semantic layer is the key unlock — when business logic is pre-encoded in LookML, dbt metrics, or Cube.dev, analysts and business users get consistent, governed numbers without writing SQL. Tools like ThoughtSpot add natural language querying on top. The DWH serves BI developers, SQL analysts, power users, and executives well. It is not the right platform for raw data exploration or model training — that’s where the Data Lake comes in (Post 2).
The Modern DWH Engineering Stack
A modern Python-first DWH stack typically looks like: Airbyte or Fivetran for batch ELT connectors → Snowflake or BigQuery as the cloud warehouse → dbt Core/Cloud for SQL transformations with built-in lineage and testing → Cube.dev or dbt metrics for the semantic layer → Apache Airflow or Dagster for orchestration → Tableau, Power BI, or Looker for BI consumption.
When to Choose a DWH
Choose a Data Warehouse when your primary workload is structured reporting and BI, governance and compliance are non-negotiable, business users need trusted metrics rather than raw exploration, and your team is SQL-proficient. Don’t choose it as your only platform if you need ML workloads on raw data, process high-volume unstructured data, or want to enable data scientists directly on the platform.
Next in the series: Post 2 will cover the Data Lake — raw power, schema-on-read, and why the “data swamp” problem is an architecture failure, not a storage one.
Follow me to get notified when each post drops. Happy to discuss your DWH architecture challenges in the comments.
