SQL Projects / Warehouse and Reporting

Project 04 · Warehouse and Reporting

Turn raw data into trusted reports.

Clean business records, build a tested model and create repeatable reports.

The brief

The business question: Profile raw CRM/sales records, build Silver and Gold tables in the disposable lab, explore the fact table, and publish repeatable reporting logic.
What you’ll deliver: Clean dimensions and facts, quality checks and customer, product and monthly reports.Exported as a report with business questions, checked SQL, result evidence and your written findings. Labeled Draft until every milestone has a finding.
Skills: Data cleaning · Data modeling · Reporting
Scope: Advanced level · 6 milestones · 6 tables

Available data

Fictional CRM/catalog/sales extracts and curated star-schema tables. Raw keys, dates, quantities, and sales need validation. One fact row is one sales line.

bronze_customers4 columns · 7 rows · raw_id, name, country, updated_at

raw_idtext
nametext
countrytext
updated_attext

First 5 of 7 rows · original sample data

#	raw_id	name	country	updated_at
1	C001	Alice	USA	2025-01-01
2	C001	Alice Nguyen	US	2025-01-05
3	C002	Ben	GB	2025-01-02
4	C003	Cara	UK	2025-01-03
5	C004	Dev	IND	2025-01-04

bronze_products4 columns · 4 rows · raw_id, name, category, price

raw_idtext
nametext
categorytext
pricetext

First 4 of 4 rows · original sample data

#	raw_id	name	category	price
1	P01	Notebook	Stationery	12
2	P02	Desk lamp	Home	45
3	P03	Keyboard	Tech	80
4	P04	Headphones	Tech	120

bronze_sales6 columns · 13 rows · row_id, customer_id, product_id, quantity, …

row_idintegerPK
customer_idtext
product_idtext
quantitytext
salestext
order_datetext

First 5 of 13 rows · original sample data

#	row_id	customer_id	product_id	quantity	sales	order_date
1	1	C001	P01	2	24	2025-01-01
2	2	C002	P03	1	80	2025-01-02
3	3	C001	P02	1	45	2025-01-15
4	4	C003	P04	2	240	2025-02-01
5	5	C004	P03	2	160	2025-02-05

dim_customer4 columns · 5 rows · customer_key, customer_id, name, country

customer_keyintegerPK
customer_idtext
nametext
countrytext

First 5 of 5 rows · original sample data

#	customer_key	customer_id	name	country
1	1	C001	Alice Nguyen	United States
2	2	C002	Ben	United Kingdom
3	3	C003	Cara	United Kingdom
4	4	C004	Dev	India
5	5	C005	Elena	Canada

dim_product5 columns · 4 rows · product_key, product_id, name, category, …

product_keyintegerPK
product_idtext
nametext
categorytext
pricereal

First 4 of 4 rows · original sample data

#	product_key	product_id	name	category	price
1	1	P01	Notebook	Stationery	12
2	2	P02	Desk lamp	Home	45
3	3	P03	Keyboard	Tech	80
4	4	P04	Headphones	Tech	120

fact_sales6 columns · 9 rows · sale_key, customer_key, product_key, quantity, …

sale_keyintegerPK
customer_keyinteger
product_keyinteger
quantityinteger
salesreal
order_datetext

First 5 of 9 rows · original sample data

#	sale_key	customer_key	product_key	quantity	sales	order_date
1	1	1	1	2	24	2025-01-01
2	2	2	3	1	80	2025-01-02
3	3	1	2	1	45	2025-01-15
4	4	3	4	2	240	2025-02-01
5	5	4	3	2	160	2025-02-05

Open a table to see its columns and sample rows. You do not need to read every table before starting a milestone.

Each run starts with the original sample data. Changes to tables do not carry over to later runs or milestones. Findings are optional for continuing — required only for a complete report.

Milestones

Start

Bronze — inspect raw data

See what arrived before changing anything.

Bronze: inspect raw data before cleaning

Inspect incoming records before making cleaning decisions.

Not started

Silver — clean and deduplicate

Trim, map and keep the latest version of each record.

Silver: clean, map & deduplicate

Clean values, apply mappings and remove duplicate records.

Not started

Gold — model and test

Build dimensions and facts with quality gates.

Gold: dimensions, facts & quality gates

Build dimensions and facts with checks for trustworthy data.

Not started

Report

Explore the model and publish repeatable reports.

EDA: explore before telling the story

Explore distributions and relationships before reporting.

Not started

Monthly change & cumulative reports

Measure monthly change and cumulative totals.

Not started

Customer & product reporting contracts

Produce customer and product reports at a defined level of detail.

Not started

Project report

Loading saved progress…

Waiting for saved progress to load.

How a data warehouse is built

This project follows the standard layers: understand source data → load raw records (Bronze) → clean and validate (Silver) → build reporting tables (Gold) → produce reports.

sources

Source contract

Structured fictional CRM, ERP and sales extracts. The foundation keeps latest state. Confirm source ownership, extraction counts, key formats, grain and refresh expectations before choosing architecture.

bronze

Bronze · preserve evidence

Raw source-shaped records retain spaces, sentinels, duplicates and malformed values. Compare extraction counts, inspect column alignment, and retain source row identity for replay and diagnosis.

silver

Silver · standardize with explicit rules

Latest CRM row wins with a deterministic source_row tie-breaker. Normalize ERP keys under its format contract. Validate calendar dates, quarantine failures and reconcile every source row.

gold

Gold · integrate business entities

CRM owns meaningful gender; ERP supplies fallback and country. Stable dimension keys link each validated sales line. Choose current-state or effective-time semantics before reporting. Views or stored tables are workload choices.

report

Reports · declare the metric

Customer totals use one row per customer and preserve zero-activity entities. Northstar sales means positive quantity × catalog price under a stated teaching rule, not an automatic accounting correction.