🏭 Modern Data Warehouse — SQL Server + Medallion Architecture

An end-to-end Data Engineering portfolio project implementing a production-grade Data Warehouse on SQL Server.
Raw CSV files are transformed into a business-ready Star Schema through a clean, layered Medallion Architecture (Bronze → Silver → Gold) and ultimately served as BI-ready reporting views.

Built as a self-taught project alongside the Data With Baraa series. Every script, document, and pattern here reflects real industry practice — the same workflows used by data engineers at scale.

---

📌 Project Snapshot

Feature	Specification
Architecture	Medallion (Bronze → Silver → Gold)
Data Modeling	Star Schema (Fact & Dimension Views) + Denormalized BI Reporting Views
ETL Strategy	Full Extract · Batch Processing · Truncate & Reload
SCD Strategy	Type 1 (Overwrite) + Type 2 (History via LEAD())
Data Sources	CRM CSV Files + ERP CSV Files (6 source tables)
Core Stack	SQL Server Express · T-SQL · SSMS · Git
Gold Layer Type	SQL Views (no physical storage — always live)

---

🎓 What This Project Teaches

This project was built to deeply internalize the patterns that repeat across every data warehouse, regardless of the dataset. If you follow it end to end — writing each script yourself — you will come away with hands-on experience in:

Data Engineering Fundamentals

• How a Medallion Architecture separates concerns across Bronze, Silver, and Gold layers
• Why physical tables are used for Bronze/Silver (persistence, debugging) but Views for Gold (always-live, zero storage cost)
• The difference between natural keys (from source systems) and surrogate keys (generated by the warehouse)
• What a Star Schema is, why it outperforms normalized schemas for analytics, and how to build one from scratch
• The 3-Layer CTE Pattern for building complex, highly readable reporting views

Advanced T-SQL & SQL Server Skills

• Window Functions: ROW_NUMBER() for deduplication, LEAD() for SCD Type 2 dates, LAG() for Year-over-Year (YoY) benchmarking, and SUM() OVER() for cumulative running totals and part-to-whole analysis
• Date Manipulation: DATETRUNC() for time-series floors, DATEDIFF for customer lifespans/recency, and FORMAT() for human-readable BI labels
• Stored Procedures: CREATE OR ALTER PROCEDURE with BEGIN TRY / BEGIN CATCH error handling
• Bulk Operations: BULK INSERT from CSV files with FIRSTROW, FIELDTERMINATOR, and TABLOCK
• Data Cleansing: NULLIF() to prevent division-by-zero, ISNULL() / COALESCE() for multi-source data resolution, and CASE WHEN for decoding raw codes into labels and grouping dynamic segments

Data Quality & Governance

• Writing systematic quality check scripts for every layer (row counts, structure checks, sample previews, business rule validation)
• Validating referential integrity between Fact and Dimension tables
• Enforcing naming conventions consistently (snake_case, dim_ / fact_ prefixes, dwh_ audit prefix, _key suffix for surrogate keys)
• Building a professional Gold Layer Data Catalog documenting every object, column, data type, and business description

Data Modeling & Analysis Concepts

• Data Integration: combining CRM and ERP systems with mismatched key formats
• Exploratory Data Analysis (EDA) across six structured phases
• Cohort & Segmentation Analysis: dynamically bucketing customers (VIP/Regular/New) and products (High/Mid/Low performers) based on historical behavior
• KPI Identification: calculating Average Order Value (AOV), Customer Recency, and Monthly Recurring Revenue proxies

---

🏗️ Architecture & Data Flow

[CRM CSV Files]   [ERP CSV Files]
        │                │
        └────────┬────────┘
                 ▼
          ┌─────────────┐
          │  🥉 BRONZE  │  Raw ingestion — no transformation, full source fidelity
          └──────┬──────┘
                 ▼
          ┌─────────────┐
          │  🥈 SILVER  │  Cleansed, standardized, deduplicated, type-cast
          └──────┬──────┘
                 ▼
          ┌─────────────┐
          │   🥇 GOLD   │  Star Schema Views — business-ready, always live
          └──────┬──────┘
                 ▼
          ┌─────────────┐
          │ 📈 BI VIEWS │  Denormalized Reporting Views (report_customers, report_products)
          └──────┬──────┘
                 ▼
        [BI / Analytics / EDA]

Layer Responsibilities

Layer	Storage Type	Purpose	Refresh Method
Bronze	Physical Tables	Land raw data exactly as it arrives	TRUNCATE + BULK INSERT
Silver	Physical Tables	Clean, decode, deduplicate, type-cast	TRUNCATE + INSERT...SELECT
Gold	Views (no data)	Star Schema & Pre-Aggregated BI Reports	Automatic (reads Silver)

---

📁 Repository Structure

data-warehouse-project/
│
├── datasets/
│   ├── source_crm/            ← CRM CSV files (cust_info, prd_info, sales_details)
│   └── source_erp/            ← ERP CSV files (CUST_AZ12, LOC_A101, PX_CAT_G1V2)
│
├── scripts/
│   ├── create_database.sql    ← One-time setup: creates DB + schemas
│   │
│   ├── bronze/                ← DDL, ETL, and QC scripts for Bronze Layer
│   ├── silver/                ← DDL, ETL, and QC scripts for Silver Layer
│   ├── gold/                  ← DDL and QC scripts for Gold Layer (Star Schema)
│   │
│   └── data_analysis/
│       ├── exploratory_data_analysis.sql ← 6-phase EDA with full business commentary
│       └── advance_analytics.sql         ← 7 advanced analytics patterns & BI reporting views
│
└── docs/                      ← Project documentation (Data Catalog, Lineage, ERDs)

---

⚙️ Silver Transformations — The Five Patterns

Every transformation written in the Silver layer falls into one of five reusable patterns.

#	Pattern	Problem Solved	Function Used
1	TRIM	Whitespace in string fields from human-entered source data	TRIM()
2	CASE WHEN Decode	Single-char codes ('S', 'M', 'F') → readable labels	CASE WHEN + UPPER(TRIM())
3	ROW_NUMBER Dedup	Multiple versions of same customer — keep only the latest	ROW_NUMBER() OVER (PARTITION BY ... ORDER BY DESC)
4	Integer Date Cast	Dates stored as YYYYMMDD integers — convert to real DATE	CAST(CAST(x AS VARCHAR) AS DATE)
5	Business Rule Recalc	sales ≠ quantity × price — recalculate from trusted fields	CASE WHEN + NULLIF()

Bonus — SCD Type 2 with LEAD(): Product records change over time. LEAD() peeks at the next version's start date to derive the current version's end date, creating a full historized dimension automatically.

---

🥇 Gold Layer — Star Schema

Three core objects form the foundational Star Schema:

gold.dim_customers — Integrates CRM + ERP demographics + ERP location.

gold.dim_products — Integrates CRM product master + ERP categories. Filters to active products only.

gold.fact_sales — Transactional grain (one row per order line). Stores surrogate keys + measures only.

---

📈 Advanced Analytics & BI Reporting Views

The scripts/data_analysis/advance_analytics.sql script applies seven advanced SQL patterns on top of the Gold layer to extract deep business insights and build denormalized views specifically designed for BI tools (Power BI, Tableau, etc.).

The 5 Analytical Patterns

1. Change Over Time: Time-series analysis utilizing DATETRUNC() and FORMAT() to track seasonality.
2. Cumulative Analysis: Generating running totals and moving averages utilizing SUM() OVER(ORDER BY).
3. Performance Benchmarking: Year-over-Year (YoY) analysis and historical baseline comparisons using LAG().
4. Data Segmentation: Behavioral bucketing using logical CASE statements (e.g., VIP vs. Regular customers based on lifespan and spend).
5. Part-to-Whole Analysis: Calculating category contribution percentages via Grand Total Window Functions (SUM() OVER()).

The BI Reporting Views (The 3-Layer CTE Stack)

To serve insights directly to stakeholders, two highly aggregated views were created: gold.report_customers and gold.report_products. These views were built using a highly readable 3-Layer CTE Design Pattern:

• Layer 1 (Base Query): Flattens the Star Schema by joining Facts and Dimensions at the transactional level.
• Layer 2 (Aggregations): Rolls up the data to the entity level (one row per customer or product), calculating lifespans, unique order counts, and total revenue.
• Layer 3 (Final Output): Derives complex KPIs (Average Order Value, Recency, Monthly Spend) and applies dynamic segmentation labels.

---

📊 Exploratory Data Analysis — Six Phases

The EDA script (scripts/data_analysis/exploratory_data_analysis.sql) is structured as a stakeholder conversation with the data:

Phase	Question Being Answered
1 — Database Exploration	What objects exist? What columns do they expose?
2 — Dimensions Exploration	What are the distinct values? Any misspellings or NULL categories?
3 — Date Exploration	How many years of history? What is the customer age range?
4 — Measures Exploration	What are the headline KPIs — total revenue, units, orders, AOV?
5 — Magnitude Analysis	Which countries, categories, and customers drive the business?
6 — Ranking Analysis	Top 5 products, top 10 customers, bottom 5 products — who are the outliers?

---

🚀 How to Run the Project

Prerequisites

• SQL Server Express (any recent version)
• SQL Server Management Studio (SSMS)
• Git

Step-by-Step Execution

Run scripts in this exact order:

Step 1 → scripts/create_database.sql
Step 2 → scripts/bronze/create_bronze_layer.sql
Step 3 → scripts/bronze/loaddata_bronze_layer.sql (Update local paths first!)
Step 4 → scripts/bronze/verify_bronze_layer.sql
Step 5 → scripts/silver/create_silver_layer.sql
Step 6 → scripts/silver/loaddata_silver_layer.sql
Step 7 → scripts/silver/verify_silver_layer.sql
Step 8 → scripts/gold/create_gold_layer.sql
Step 9 → scripts/gold/verify_gold_layer.sql
Step 10 → scripts/data_analysis/exploratory_data_analysis.sql
Step 11 → scripts/data_analysis/advance_analytics.sql

(Note: In loaddata_bronze_layer.sql, remember to update the FROM path in each BULK INSERT block to match your local machine's directory).

---

📚 Documentation Guide

Document	What It Contains	When to Read It
data_warehouse_patterns.md	Complete reusable reference: DDL templates, BULK INSERT pattern, all 5 Silver transformation patterns	Before writing any script; treat as your technical handbook
data_catalog.md	Every Gold object with purpose, column names, data types, and business descriptions	When building reports or onboarding someone to the warehouse
star_schema.md	ERD diagram, relationship cardinalities, business rules, accepted column values	When designing queries or validating joins
data_integration.md	How CRM and ERP source tables map to each other; the cross-system key resolution	Before writing Silver transformations involving both systems
data_lineage.md	Full lifecycle map: which source tables feed which Silver tables, which feed which Gold objects	For debugging: trace any metric back to its source
naming_conventions.md	Rules for all object names: schemas, tables, views, columns, stored procedures	Before creating any new object

---

🙏 Acknowledgements

• Curriculum: Inspired by and developed alongside the Data With Baraa Data Engineering Series.

---

📄 License

This project is licensed under the MIT License. See LICENSE for details.

---

Built on SQL Server · Medallion Architecture · Star Schema · T-SQL · SSMS Last updated: June 2026 — DataWarehouse v1.0