SQL Data Warehouse Project

A modern data warehouse built in SQL Server, consolidating raw CRM and ERP CSV exports into a clean, business-ready star schema using Medallion (Bronze / Silver / Gold) architecture — full ETL pipeline, data quality checks, and documentation included.

Overview

Raw operational exports from two different source systems (a CRM and an ERP system) don't answer business questions on their own — they're inconsistent, duplicated, and structured for transactions, not analysis. This project builds a complete pipeline that takes those raw CSV exports and turns them into a single, query-ready data warehouse:

• Bronze layer — raw CRM/ERP data loaded as-is via BULK INSERT, no transformation
• Silver layer — cleaned, standardized, deduplicated, and type-corrected
• Gold layer — a business-ready star schema (gold.dim_customers, gold.dim_products, gold.fact_sales) exposed as views, ready for reporting or ad-hoc SQL analysis

This is a learning project built by following Baraa Khatib Salkini's ("DataWithBaraa") free Data Warehouse and Analytics Project tutorial series end-to-end in SQL Server — see Acknowledgments below. Every script here was written and run by me while working through it; the companion analytics project (my own set of analytical SQL queries built on top of this warehouse) is in a separate repo: sql-data-analytics-project.

Key features

• Full Bronze → Silver → Gold ETL pipeline as T-SQL stored procedures (bronze.load_bronze, silver.load_silver), with TRY/CATCH error handling and per-table load-time logging
• Data cleansing and standardization logic in the Silver layer: trimming, deduplication (keep-latest-by-create-date), code-to-label normalization (e.g. 'S' → 'Single'), and derived/corrected columns
• Gold-layer star schema exposed as views, with surrogate keys generated via ROW_NUMBER()
• Dedicated data quality check scripts for both the Silver and Gold layers (null/duplicate primary keys, invalid date ranges, referential integrity between fact and dimension tables)
• Documented naming conventions and a full data catalog for the Gold layer
• Architecture, data flow, and data model diagrams (both rendered PNGs and editable .drawio source files)

Demo

High-level architecture — sources, Bronze/Silver/Gold, and consumption:

Gold layer data model — star schema:

Data flow through the warehouse:

Source system integration:

Architecture

CRM CSVs ─┐                                              ┌─ BI & Reporting
          ├─▶ Bronze (raw, as-is) ─▶ Silver (cleaned) ─▶ Gold (star schema) ─┼─ Ad-Hoc SQL
ERP CSVs ─┘                                              └─ (ML-ready)

• Bronze: bronze.load_bronze truncates and BULK INSERTs each CRM/ERP CSV into its own raw table — no transformation, object type matches source as-is.
• Silver: silver.load_silver truncates and reloads from Bronze with cleansing applied — trimming whitespace, standardizing codes into readable labels, resolving duplicate customer records by keeping the latest by create date, and correcting invalid values.
• Gold: SQL views join and reshape Silver tables into a star schema — gold.dim_customers, gold.dim_products, and gold.fact_sales — ready to query directly.

Full naming conventions are in docs/naming_conventions.md; the complete Gold-layer field-by-field catalog is in docs/data_catalog.md.

Tech stack

• SQL Server (T-SQL) — schemas, stored procedures, views, BULK INSERT
• Draw.io — architecture, data flow, and data model diagrams

Getting started

Prerequisites

• SQL Server (Developer or Express edition is fine) with SSMS or Azure Data Studio
• This repository cloned locally

Setup

-- 1. Create the database and bronze/silver/gold schemas
-- Run: scripts/init_database.sql

⚠️ init_database.sql drops and recreates a database named DataWarehouse if one already exists. Don't run it against a database you care about.

-- 2. Create the Bronze layer tables
-- Run: scripts/bronze/ddl_bronze.sql

-- 3. Update the file paths in scripts/bronze/proc_load_bronze.sql to point at
--    your local copy of datasets/source_crm/ and datasets/source_erp/, then run it
--    to create the load procedure.

-- 4. Load the Bronze layer
EXEC bronze.load_bronze;

-- 5. Create the Silver layer tables and load procedure
-- Run: scripts/silver/ddl_silver.sql
-- Run: scripts/silver/proc_load_silver.sql
EXEC silver.load_silver;

-- 6. (Optional but recommended) Run the Silver layer quality checks
-- Run: scripts/silver/quality_checks_silver.sql

-- 7. Create the Gold layer views
-- Run: scripts/gold/ddl_gold.sql

-- 8. (Optional but recommended) Run the Gold layer quality checks
-- Run: scripts/gold/quality_checks_gold.sql

Once the Gold views exist, query them directly — e.g. SELECT * FROM gold.fact_sales; — or move on to the analytics project, which is built entirely on top of these three Gold views.

Project structure

sql-data-warehouse-project/
├── datasets/
│   ├── source_crm/          # Raw CRM CSV exports (customer, product, sales)
│   └── source_erp/          # Raw ERP CSV exports (customer, location, category)
├── docs/
│   ├── SQLDWH_Architecture.png / .drawio
│   ├── SQLDWH_DataFlow.drawio
│   ├── data_flow.png
│   ├── data_integration.png
│   ├── data_model.png
│   ├── data_catalog.md      # Field-by-field catalog of the Gold layer
│   └── naming_conventions.md
├── scripts/
│   ├── init_database.sql
│   ├── bronze/              # DDL + load procedure for the raw layer
│   ├── silver/              # DDL + load procedure + quality checks
│   └── gold/                # DDL (views) + quality checks
├── LICENSE
└── README.md

Data

The source data is Microsoft's public AdventureWorks sample dataset (synthetic/fictional CRM and ERP exports — no real customer data), split across datasets/source_crm/ and datasets/source_erp/ as CSV files. It's small enough (~5 MB total) to commit directly, so no separate download step is needed.

Testing

Data quality isn't checked by a separate test suite — it's built into the pipeline itself as dedicated SQL scripts: scripts/silver/quality_checks_silver.sql and scripts/gold/quality_checks_gold.sql. These check for null/duplicate primary keys, unwanted whitespace, inconsistent category codes, invalid date ordering (e.g. ship date before order date), and referential integrity between the Gold fact and dimension tables. Run them after loading each layer and investigate anything they flag.

What I learned / skills gained

• ETL pipeline design — structuring a pipeline into distinct raw/cleaned/business-ready stages (Medallion architecture) instead of one monolithic transformation, and why that separation makes debugging and re-running each stage independently possible.
• T-SQL stored procedures — writing procedures with TRY/CATCH error handling, execution-time logging per table and per batch, and TRUNCATE + reload patterns for repeatable, idempotent loads.
• Data cleansing in SQL — trimming, deduplicating records by business rule (keep the latest by create date), normalizing inconsistent source codes into readable values, and validating results rather than assuming a transformation worked.
• Dimensional modeling — designing a star schema from normalized source data: surrogate keys via ROW_NUMBER(), separating dimensions from facts, and documenting the result in a data catalog so it's usable by someone who didn't build it.
• Data quality as code — writing quality checks as runnable SQL scripts (not manual spot-checks), covering primary key integrity, referential integrity, and business-rule validation.
• Technical documentation habits — naming conventions, data catalogs, and architecture diagrams as first-class project artifacts, not an afterthought.

Roadmap

• Automate the full Bronze → Silver → Gold load as a single orchestrated job (e.g. SQL Server Agent or a scheduler) instead of running each step manually
• Add incremental/historized loading (this project intentionally scopes to latest-snapshot-only, per the original assignment)
• Parameterize the Bronze load paths instead of hardcoded file paths in proc_load_bronze.sql

My role

I built this by following Baraa Khatib Salkini's "Data Warehouse and Analytics Project" tutorial series hands-on in SQL Server — writing and running every script in this repository myself to learn ETL pipeline design, T-SQL, and dimensional modeling. The project structure, dataset, and medallion-architecture approach follow that tutorial; see Acknowledgments for the original source.

Acknowledgments

This project follows the structure and curriculum of DataWithBaraa/sql-data-warehouse-project by Baraa Khatib Salkini, used under its MIT License. I built this repository by completing that tutorial end-to-end as a learning project.

License and contact

Licensed under the MIT License (see license file for the original tutorial's attribution notice).

Contact: github.com/VS-2K01