In this post we are going to cover the data mart, and answer a classic interview question: what is a data mart, and how is it different from a data warehouse?
A data warehouse integrates data from across the whole organization: orders, customers, products, finance and more, cleaned and conformed in one place. A data mart is a smaller, subject-focused piece for one business area, like sales or finance, shaped around the questions that one team asks.
We will look at the three types of data mart, then build a small sales mart on top of a warehouse star schema with SQL, and query it the way the sales team would.
Question 39 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A data warehouse integrates data from across the whole organization. A data mart is a smaller, subject-focused subset for one business area, like sales or finance, shaped for that team's questions. A dependent mart is built from the warehouse; an independent mart is built directly from sources, which is faster to start but creates inconsistent numbers.
One warehouse, many marts
Think of it as one warehouse in the middle and several marts around it. The sources (ERP, CRM, web) feed the enterprise warehouse, and each department gets its own mart from there: a sales mart, a finance mart, a marketing mart.
A mart holds only the subjects that team needs, often already summarized and with names the team understands. Being smaller and focused brings real benefits:
- Faster queries - less data to scan.
- Simpler models - the team does not need to understand the whole warehouse.
- Tighter security - the sales team never sees payroll.
Dependent, independent and hybrid marts
The next question is usually: where does the mart get its data? There are three answers.
| Type | Fed from | Trade-off |
|---|---|---|
| Dependent | the data warehouse | ✓ consistent numbers |
| Independent | source systems directly | ✗ fast start, numbers drift apart |
| Hybrid | warehouse + extra sources | flexible, needs governance |
- Dependent - fed from the warehouse, so every mart is built from the same cleaned, integrated data, and sales and finance report the same revenue. This is the Inmon style. In Kimball's approach, marts share conformed dimensions, which gives the same consistency.
- Independent - fed straight from the sources, with no warehouse. It is quick for one team, but each mart cleans data its own way, and soon the numbers disagree.
- Hybrid - most data comes from the warehouse, plus a few extra sources of its own.
Let's create some sample data
Our warehouse is a small star schema: a fact_sales table with amount and cost, a dim_product dimension and a dim_customer dimension that includes email addresses.
DROP VIEW IF EXISTS mart_sales_summary;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_product;
-- the enterprise warehouse: a small star schema
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), region VARCHAR(10));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'sara@mail.com', 'West'),
(2, 'Omar Ali', 'omar@mail.com', 'East'),
(3, 'Lina Park', 'lina@mail.com', 'West');
CREATE TABLE dim_product (product_key INT PRIMARY KEY, name VARCHAR(50), category VARCHAR(20));
INSERT INTO dim_product VALUES (1, 'Pen', 'Office'), (2, 'Notebook', 'Office'), (3, 'Headphones', 'Electronics');
CREATE TABLE fact_sales (customer_key INT, product_key INT, amount DECIMAL(10,2), cost DECIMAL(10,2));
INSERT INTO fact_sales VALUES
(1, 1, 5.00, 2.00),
(1, 3, 60.00, 40.00),
(2, 2, 12.00, 7.00),
(3, 3, 60.00, 40.00),
(2, 1, 5.00, 2.00);Here are the customers. Notice the email column: that is personal data the sales team does not need.
SELECT * FROM dim_customer ORDER BY 1;| customer_key | name | region | |
|---|---|---|---|
| 1 | Sara Khan | sara@mail.com | West |
| 2 | Omar Ali | omar@mail.com | East |
| 3 | Lina Park | lina@mail.com | West |
Three products in two categories, Office and Electronics.
SELECT * FROM dim_product ORDER BY 1;| product_key | name | category |
|---|---|---|
| 1 | Pen | Office |
| 2 | Notebook | Office |
| 3 | Headphones | Electronics |
And five sales, each with an amount and a cost, so we can work out margin.
SELECT * FROM fact_sales ORDER BY 1;| customer_key | product_key | amount | cost |
|---|---|---|---|
| 1 | 1 | 5.00 | 2.00 |
| 1 | 3 | 60.00 | 40.00 |
| 2 | 2 | 12.00 | 7.00 |
| 2 | 1 | 5.00 | 2.00 |
| 3 | 3 | 60.00 | 40.00 |
Step 1: Build the sales mart from the warehouse
The sales team only needs sales and margin by product category and region. So let's build a dependent mart: a view that joins the star schema and summarizes it. It could just as well be a table, or a separate schema.
CREATE VIEW mart_sales_summary AS
SELECT p.category, c.region,
SUM(f.amount) AS sales,
SUM(f.amount - f.cost) AS margin
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY p.category, c.region;Now let's check the result.
SELECT * FROM mart_sales_summary ORDER BY category, region;| category | region | sales | margin |
|---|---|---|---|
| Electronics | West | 120.00 | 40.00 |
| Office | East | 17.00 | 8.00 |
| Office | West | 5.00 | 3.00 |
The mart is small and simple: three rows, no customer names, no emails and no row-level detail. Electronics in the West sold 120.00 with a margin of 40.00, Office in the East sold 17.00 with 8.00 margin, and Office in the West sold 5.00 with 3.00 margin.
Step 2: The sales team queries the mart
Now let's answer a question the sales team would ask: total sales by region. They query the mart, not the warehouse.
SELECT region, SUM(sales) AS sales
FROM mart_sales_summary
GROUP BY region
ORDER BY region;| region | sales |
|---|---|
| East | 17.00 |
| West | 125.00 |
The West sold 125.00 and the East 17.00. This is a short query with no joins, no surrogate keys and no fact table to understand. Because the mart is built from the warehouse, a finance mart built from the same fact table gets the same totals.
Grant the sales team access to the mart, not to the whole warehouse.
Data warehouse vs data mart side by side
Here is the comparison to remember for the interview.
| Data warehouse | Data mart | |
|---|---|---|
| Scope | whole organization | one subject area |
| Users | many teams | one team |
| Size | large (TBs) | small |
| Build time | months to years | weeks |
The key point: a mart is not a replacement for a warehouse. Build marts on top of the warehouse, so everyone shares one version of the truth.
Follow-up questions interviewers ask
Why are independent marts risky?
Each one cleans and defines data its own way, so the same metric ends up with different values in different marts.
Is a data mart a table or a view?
Either: a set of views, tables or a separate schema or database, as long as it is focused on one subject area.
Kimball or Inmon: who builds marts first?
Kimball builds conformed dimensional marts incrementally; Inmon builds the normalized enterprise warehouse first and derives marts from it.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #39 - Data mart vs data warehouse
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q39; SET search_path TO dwh_q39;
-- ========================================================================
-- Step 0: create and load the sample data
DROP VIEW IF EXISTS mart_sales_summary;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_product;
-- the enterprise warehouse: a small star schema
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), region VARCHAR(10));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'sara@mail.com', 'West'),
(2, 'Omar Ali', 'omar@mail.com', 'East'),
(3, 'Lina Park', 'lina@mail.com', 'West');
CREATE TABLE dim_product (product_key INT PRIMARY KEY, name VARCHAR(50), category VARCHAR(20));
INSERT INTO dim_product VALUES (1, 'Pen', 'Office'), (2, 'Notebook', 'Office'), (3, 'Headphones', 'Electronics');
CREATE TABLE fact_sales (customer_key INT, product_key INT, amount DECIMAL(10,2), cost DECIMAL(10,2));
INSERT INTO fact_sales VALUES
(1, 1, 5.00, 2.00),
(1, 3, 60.00, 40.00),
(2, 2, 12.00, 7.00),
(3, 3, 60.00, 40.00),
(2, 1, 5.00, 2.00);
-- Step 1: Build the sales mart from the warehouse (a dependent data mart)
CREATE VIEW mart_sales_summary AS
SELECT p.category, c.region,
SUM(f.amount) AS sales,
SUM(f.amount - f.cost) AS margin
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY p.category, c.region;
-- check the result
SELECT * FROM mart_sales_summary ORDER BY category, region;
-- Step 2: The sales team queries the mart, not the warehouse
SELECT region, SUM(sales) AS sales
FROM mart_sales_summary
GROUP BY region
ORDER BY region;
Key points
- Warehouse: integrated, whole organization
- Mart: smaller, one subject area
- Dependent: built from the warehouse
- Independent: from sources, numbers drift
- Build marts on top of the warehouse
Question 40 — Idempotent loads and data quality checks What is an idempotent load, and how do you add data quality checks?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.