Data Warehouse Architecture: Layers, Tiers and Modern Designs

Data warehouse architecture is the plan for how data travels from the systems that create it to the reports and models that use it. Almost every design, old or new, has the same idea at its centre: keep raw data, clean and integrate it in one place, then shape it for each group of users. What changes between designs is where each step runs and what each layer is called.

The layers

Layer	Also called	What happens here	Who uses it
Sources	Operational systems	Apps, databases, SaaS tools and files create data	Nobody queries these for analytics
Staging	Landing, raw, bronze	Copies of source data, loaded as they are, with load times	Data engineers
Core	Integration, EDW, silver	Cleaned, typed, deduplicated, joined across sources, history kept	Data engineers, advanced analysts
Marts	Presentation, gold	Facts and dimensions or wide tables for one subject or team	Analysts, dashboards
Semantic and BI	Metrics layer, reports	Shared definitions of measures, dashboards, notebooks	Everyone in the business

Around these layers sit the services that keep the system running: orchestration to run steps in order, data quality checks, a catalog with lineage, access control and monitoring.

See the layers in SQL

The example below uses three PostgreSQL schemas for the three middle layers. Two sources, a shop database and a payments provider, are loaded into staging exactly as they arrive, with every column as text:

-- Three layers as three schemas. Staging: raw copies. Core: cleaned and integrated. Mart: shaped for one team.
CREATE SCHEMA staging;
CREATE SCHEMA core;
CREATE SCHEMA mart;

-- Staging: loaded as-is from two sources (the shop database and the payments provider).
CREATE TABLE staging.shop_orders (order_id text, customer_email text, order_ts text, total text);
CREATE TABLE staging.payments    (payment_ref text, order_id text, status text, paid_ts text);
INSERT INTO staging.shop_orders VALUES
  ('A-1', '[email protected] ', '2026-10-01 09:12', '25.00'),
  ('A-2', '[email protected]',   '2026-10-01 18:40', '40.00'),
  ('A-3', '[email protected]',  '2026-10-02 07:55', '12.50');
INSERT INTO staging.payments VALUES
  ('P-9', 'A-1', 'captured', '2026-10-01 09:13'),
  ('P-7', 'A-2', 'failed',   '2026-10-01 18:41'),
  ('P-8', 'A-3', 'captured', '2026-10-02 07:56');

The core layer fixes types, cleans the email so the same customer matches, and joins the two sources. The mart answers one team’s question:

-- Core: proper types, cleaned keys, one integrated table.
CREATE TABLE core.orders AS
SELECT o.order_id,
       lower(trim(o.customer_email))        AS customer_email,
       o.order_ts::timestamp                AS ordered_at,
       o.total::numeric(10,2)               AS order_total,
       p.status = 'captured'                AS is_paid
FROM staging.shop_orders o
LEFT JOIN staging.payments p ON p.order_id = o.order_id;

-- Mart: what the finance dashboard needs, nothing more.
CREATE VIEW mart.daily_revenue AS
SELECT ordered_at::date AS day, count(*) AS paid_orders, sum(order_total) AS revenue
FROM core.orders
WHERE is_paid
GROUP BY 1;

SELECT * FROM mart.daily_revenue ORDER BY day;

    day     | paid_orders | revenue
------------+-------------+---------
 2026-10-01 |           1 |   25.00
 2026-10-02 |           1 |   12.50

Why bother with three layers for this?

• Staging keeps the evidence. When finance asks why order A-2 is missing, you can show the failed payment exactly as the provider sent it.
• Core fixes problems once. [email protected] and [email protected] become one customer for every report, not just this one.
• Marts stay simple. The dashboard reads one small view and never needs to know about payment providers.

The classic tiers

Textbooks often describe warehouse architecture by tiers:

• Single tier: reports query the operational data directly. Simple, but it puts analytical load on production systems and keeps no history. Rare today beyond very small setups.
• Two tier: sources feed a warehouse, and reporting tools query the warehouse directly. Common for small and mid-sized teams.
• Three tier: the bottom tier is the warehouse database with its loading processes; the middle tier is an OLAP or semantic layer that defines measures and serves fast aggregates; the top tier is the front end of dashboards, reports and notebooks. This is the textbook standard and still describes most enterprise setups, with a semantic layer in the middle.

Kimball, Inmon and Data Vault

The three best-known approaches differ mainly in what the core layer looks like:

	Kimball (bottom-up)	Inmon (top-down)	Data Vault
Core layer	Conformed dimensional marts (the “bus”)	One normalised (3NF) enterprise warehouse	Hubs, links and satellites
Marts	The marts are the warehouse	Built from the central warehouse	Built from the vault
Strength	Fast value, easy for analysts	One integrated version of the truth	Absorbs source changes, full audit history
Cost	Needs discipline to keep dimensions conformed	Slower to first result	More tables and more work to query

Many real warehouses mix them: a vault or normalised core for integration, with Kimball stars on top for reporting. The details are in data modelling methodologies.

Modern cloud and lakehouse architecture

Cloud platforms kept the layers but changed how they are built:

• Storage and compute are separate. Data sits in cheap storage, and compute scales up or down per workload. That makes keeping raw history affordable.
• ELT instead of ETL. Raw data is loaded first and transformed inside the warehouse with SQL, often with dbt. See ETL vs ELT.
• The lakehouse stores all layers as open table formats (such as Delta Lake or Iceberg) on object storage, with warehouse features on top. The layers are usually named bronze, silver and gold: the medallion architecture. How a lakehouse differs from a plain data lake and a warehouse is covered in data lake vs lakehouse vs data warehouse.
• Streaming feeds can land near real time, often through change data capture, while batch jobs still build most marts.

A typical modern stack: ingestion tools and CDC load raw data into a warehouse or lakehouse; dbt builds core and mart models; an orchestrator such as Airflow runs and monitors the steps; a semantic layer and BI tools serve users. The building blocks differ by company, but the layers are the same as in the SQL example above.

Common mistakes

1. No staging layer. Without raw copies, you cannot reprocess history or prove what a source sent.
2. Business logic in dashboards. If “revenue” is calculated differently in three reports, the logic belongs in the core layer or the semantic layer.
3. One giant mart for everyone. Marts work best when they serve a subject or a team.
4. Copying source tables into “core” without integrating them. Core is where keys are matched and definitions agreed.
5. Ignoring the supporting services. Without orchestration, tests and lineage, a layered design becomes hard to trust.

What interviewers look for

For “explain data warehouse architecture”:

• The layers, in order, with one sentence each on what happens there.
• Why raw data is kept and why marts are separate from the core.
• The three-tier picture, if the interviewer uses that term.
• How a modern cloud or lakehouse design maps onto the same layers, and where ELT, dbt and orchestration fit.

Practise with the data engineering interview questions.

Key takeaway

A data warehouse moves data through layers: raw copies in staging, cleaned and integrated data in the core, and team-shaped marts on top, with a semantic layer and BI tools above them. Kimball, Inmon and Data Vault differ in how they build the core. Cloud warehouses and lakehouses keep the same layers, but load first, transform with SQL and scale compute separately. Next: star schemas and the grain.