Data Architecture Approaches

Editorial

Introduction

Before building a data warehouse, you need to decide how to organize the data. This decision is your data architecture — the blueprint that dictates how data flows from source systems into a structure that analysts can query.

Over the decades, the industry has developed several proven approaches. Each has trade-offs in complexity, flexibility, and speed of delivery. In this lesson, you'll explore four of the most widely used architectures, compare them side by side, and understand why Medallion Architecture is the right fit for this project.

Kimball Methodology (Bottom-Up)

The Kimball approach, pioneered by Ralph Kimball, builds the data warehouse from the bottom up. You start by identifying a single business process (e.g., sales), model it as a star schema, and deliver it to users. Then you repeat for the next process.

Key characteristics:

Dimensional modeling — data is organized into facts and dimensions.
Star schema — one fact table surrounded by dimension tables.
Bus architecture — shared (conformed) dimensions allow individual star schemas to be queried together.
Fast time-to-value — you deliver usable data marts quickly.

Kimball — bottom-up: one business process at a time, each its own star schema. Three sources each go through their own ETL into their own data mart — data mart 1, 2 and 3, each a star schema — and on to the users. Underneath, the conformed dimensions the marts share tie them together, so they can be queried together.

Pros:

Quick wins — analysts get usable data fast.
Easy for business users to understand (star schema is intuitive).
Well-suited for BI tools and OLAP queries.

Cons:

Can become inconsistent if conformed dimensions are not maintained.
Harder to retrofit when new business processes arrive.
No single "source of truth" layer — each mart is somewhat independent.

Inmon Methodology (Top-Down)

The Inmon approach, championed by Bill Inmon (often called the "Father of Data Warehousing"), takes the opposite direction: top down. You first build a single, enterprise-wide, normalized data warehouse, and then derive data marts from it.

Key characteristics:

Third Normal Form (3NF) — the central warehouse is fully normalized to eliminate redundancy.
Enterprise Data Warehouse (EDW) — one centralized repository of all organizational data.
Dependent data marts — star schemas are created on top of the EDW for specific departments.

Inmon — top-down: one normalized warehouse first, then marts built from it. Three sources go through ETL into one enterprise data warehouse in third normal form — the single source of truth. Data marts, each a star schema, are built from it and serve the users. A long build before anyone sees data — then every mart agrees.

Pros:

True single source of truth — all data is integrated in one place.
High data consistency and minimal redundancy.
Easier to add new subject areas over time.

Cons:

Long initial build time before analysts see any results.
3NF can be complex and harder for business users to query directly.
Requires significant upfront planning and enterprise-wide agreement.

Data Vault

Data Vault, created by Dan Linstedt, is a hybrid modeling technique designed for agility and auditability. It breaks data into three building blocks:

Component	Purpose	Example
Hub	Stores unique business keys	`hub_customer` (customer_id)
Satellite	Stores descriptive attributes + history	`sat_customer` (name, email, load_date)
Link	Stores relationships between hubs	`link_order` (customer_id ↔ product_id)

Data Vault — hubs, links, satellites: keys in hubs, relationships in links, history in satellites. The hubs hold the business keys: hub_customer (customer_id) and hub_product (product_id). The link link_order joins the two hubs — customer to product. Each hub has a satellite with its attributes and their history: sat_customer (name, email, load_date) on hub_customer, sat_product (attributes, load_date) on hub_product. Flexible and fully historized — but rebuilding a record takes many JOINs.

Pros:

Highly flexible — adding new sources doesn't break existing structures.
Full historization built in (satellites track every change).
Parallel loading — hubs, satellites, and links can be loaded independently.

Cons:

Complex to understand for beginners.
Requires many JOINs to reconstruct a usable record.
Typically needs a star-schema presentation layer on top for BI tools.
Overkill for small or simple projects.

Medallion Architecture (Bronze / Silver / Gold)

The Medallion Architecture organizes data into three progressive layers, each with a clear responsibility:

Layer	Purpose	Data State
🥉 Bronze	Land raw data exactly as-is from sources	Raw, untransformed
🥈 Silver	Clean, standardize, and validate data	Cleansed, conformed
🥇 Gold	Model data for business consumption	Business-ready, star schema

Data flows in one direction: Bronze → Silver → Gold. Each layer adds more value, like refining raw ore into a polished product.

The medallion architecture: three layers, each a step closer to business-ready data. Bronze — raw data, as-is from the source systems, kept so it can be reprocessed. Silver — cleaned data: standardized, deduplicated and validated. Gold — business-ready data, modeled for reporting as a star schema. In this project: six Bronze tables, six Silver tables and three Gold views. Data only moves forward: Bronze, then Silver, then Gold.

Why Medallion is popular:

Simple mental model — everyone on the team understands "raw → clean → business-ready".
Debugging friendly — if something looks wrong in Gold, you can trace it back through Silver to Bronze.
Technology agnostic — works equally well in SQL Server, cloud data lakes, or modern data platforms.
Incremental delivery — you can stand up Bronze quickly, then iterate on Silver and Gold.

Pros:

Clear separation of concerns.
Raw data is always preserved (Bronze).
Easy to understand, build, and maintain.
Scales well from small projects to enterprise.

Cons:

Data is stored multiple times (one copy per layer), increasing storage.
Requires disciplined pipeline design to keep layers in sync.
Less prescriptive than Kimball or Inmon — you choose the modeling technique for each layer.

Side-by-Side Comparison

Criteria	Kimball	Inmon	Data Vault	Medallion
Direction	Bottom-up	Top-down	Hybrid	Layered
Core model	Star schema	3NF (EDW)	Hubs, satellites, links	Bronze / Silver / Gold
Complexity	Medium	High	High	Low to medium
Time to value	Fast	Slow	Medium	Fast
Raw data kept?	No	No	Yes	Yes (Bronze)
Historization	SCD types	SCD types	Built in	Optional
Best for	BI / OLAP	Enterprise	Agile, big data	Modern pipelines

There is no single "best" architecture — the right choice depends on team size, data volume, project timeline, and organizational maturity. Many real-world warehouses combine ideas from multiple approaches.

Why Medallion for This Project

For this project, the Medallion Architecture is the ideal fit. Here's why:

Simplicity — With only two source systems (CRM and ERP), a heavyweight approach like Inmon or Data Vault would be overkill.
Raw data preservation — The Bronze layer gives you an exact copy of the source CSVs, making debugging easy.
Clear transformation path — Silver handles all the messy cleansing, so Gold stays clean and focused on business logic.
Star schema in Gold — You still get the benefits of Kimball-style dimensional modeling (fast, intuitive queries) in the final layer.
Beginner-friendly — The layer-by-layer approach is easy to learn, implement, and explain.

In essence, Medallion gives you the best of both worlds: the structured rigor of layered data engineering and the analytical power of a star schema.

Mapping Architecture to SQL Server

In SQL Server, each Medallion layer maps neatly to a database schema:

Layer	SQL Server Schema	Objects
Bronze	`bronze`	Tables (raw imports)
Silver	`silver`	Tables (cleaned data)
Gold	`gold`	Views (star schema)

Using schemas within a single database keeps everything organized without the overhead of managing multiple databases.

-- Creating the three Medallion schemas in SQL Server
CREATE SCHEMA bronze;
GO

CREATE SCHEMA silver;
GO

CREATE SCHEMA gold;
GO

You'll use this structure throughout the entire project. Every table and view you create will live under one of these three schemas.

Summary

Here's what you learned in this lesson:

Kimball builds star schemas bottom-up for fast delivery but lacks a centralized raw layer.
Inmon builds a normalized enterprise warehouse top-down for consistency but takes longer to deliver.
Data Vault uses hubs, satellites, and links for maximum flexibility and auditability but adds complexity.
Medallion Architecture organizes data into Bronze (raw), Silver (clean), and Gold (business-ready) layers — striking the best balance of simplicity, traceability, and analytical power.
This project uses Medallion Architecture with a star schema in the Gold layer, combining layered engineering with dimensional modeling.

Next, you'll dive into the detailed design of each Medallion layer.