🏗️ SQL Data Warehouse Project

An end-to-end data warehouse built on SQL Server, consolidating two source systems (CRM and ERP) into a single analytics-ready star schema using medallion architecture — covering ingestion, data cleansing, dimensional modelling and data quality validation.

---

📖 Overview

Six CSV extracts (~116,000 rows across a CRM and an ERP system) are ingested raw, cleansed and standardised, then modelled into fact and dimension objects for reporting.

The pipeline follows an ELT pattern: data is loaded raw into the warehouse first, and every transformation is executed as SQL inside the warehouse. This keeps an auditable copy of the source, allows transformations to be re-run without touching the source system, and isolates ingestion failures from transformation failures.

🏛️ Data Architecture

Layer	Purpose	Object type
Bronze	Raw, unmodified data ingested directly from source CSV files via BULK INSERT. No transformation, loose data types so ingestion never fails on data quality.	Tables
Silver	Cleansed and standardised — deduplication, trimming, coded-value normalisation, invalid-date handling, derived product validity windows, repaired sales figures. Carries a dwh_create_date audit column.	Tables
Gold	Business-ready star schema — dimensions and fact with surrogate keys, ready for BI and analytics.	Views

CSV (CRM + ERP)  →  bronze.*  →  silver.*  →  gold.dim_customers
                                              gold.dim_products
                                              gold.fact_sales

See docs/data_architecture.md for the flow and star schema diagrams.

📊 Source Data

Source	File	Rows
CRM	cust_info.csv	18,494
CRM	prd_info.csv	397
CRM	sales_details.csv	60,398
ERP	CUST_AZ12.csv	18,484
ERP	LOC_A101.csv	18,484
ERP	PX_CAT_G1V2.csv	36

The 60,398 sales rows span 27,659 distinct orders — the fact grain is one product line on one sales order.

🧹 Data Quality Issues Handled

Real defects found in the source data and resolved in the Silver layer:

Issue	Scale	Handling
Duplicate customer records	5 customer IDs, 15 rows	ROW_NUMBER() window — keep the most recent record per customer
NULL customer IDs	4 rows	Excluded (a record with no business key cannot be a dimension member)
Invalid integer dates (0 or not 8 digits)	36 rows	Set to NULL rather than defaulted
sales ≠ quantity × price	35 rows	Sales recalculated from quantity × absolute price
NULL / non-positive sales and price	25 rows	Recalculated; divide-by-zero guarded with NULLIF
Broken product end dates (end < start)	200 rows	Discarded and re-derived using LEAD over product start dates
Cross-system key mismatch	11,042 rows	ERP NAS prefix stripped; hyphens removed to match CRM keys
Future birthdates	16 rows	Set to NULL
Inconsistent coded values	Gender in 8 forms; country as US/USA/United States, DE/Germany	Standardised via UPPER(TRIM(...)) + CASE
Leading/trailing whitespace	Names, product line codes	TRIM()

🌟 Data Model

Star schema, with dimensions denormalised one join away from the fact:

• gold.dim_customers — surrogate customer_key, integrating CRM and ERP customer attributes (CRM is the system of record for gender, ERP is the fallback)
• gold.dim_products — surrogate product_key, with ERP category, subcategory and maintenance flattened in
• gold.fact_sales — order lines with surrogate key lookups to both dimensions; additive measures sales_amount and quantity, plus non-additive price

⚙️ Tech Stack

• Database: Microsoft SQL Server 2019+
• Language: T-SQL
• Concepts applied: Medallion architecture, ELT, stored procedures with TRY...CATCH and per-table duration logging, window functions (ROW_NUMBER, LEAD), surrogate keys, star vs. snowflake schema design, fact grain and measure additivity, data quality validation

📂 Repository Structure

sql-data-warehouse-project/
│
├── datasets/         # Raw source data (CRM and ERP extracts)
├── docs/             # Architecture and data model diagrams
├── scripts/          # SQL scripts organised by layer
│   ├── init_database.sql
│   ├── bronze/       # Create and raw-load bronze tables
│   ├── silver/       # Create and cleanse into silver tables
│   └── gold/         # Star schema views (facts & dimensions)
├── tests/            # Data quality and validation scripts
├── LICENSE
└── README.md

🚀 Getting Started

1. Install SQL Server (2019+) and SSMS.
2. Clone this repository.
3. Run scripts/init_database.sql to create the database and the three schemas.
4. Run scripts/bronze/ddl_bronze.sql, then scripts/bronze/proc_load_bronze.sql, then EXEC bronze.load_bronze; (update the file paths inside the procedure to match where you cloned the repo — BULK INSERT resolves paths from the SQL Server service account, not the client)
5. Run scripts/silver/ddl_silver.sql, then scripts/silver/proc_load_silver.sql, then EXEC silver.load_silver;
6. Run scripts/gold/ddl_gold.sql to create the star schema views.
7. Run the scripts in tests/ to validate data quality across layers.

✅ Data Quality Checks

tests/quality_checks_silver.sql validates primary key uniqueness and non-nullness, unwanted whitespace, standardisation of coded values, valid date ranges, logical date ordering, and the sales = quantity × price business rule.

tests/quality_checks_gold.sql validates surrogate key uniqueness in both dimensions and referential integrity between the fact and its dimensions.

Every check is written to return zero rows when healthy — no output means the check passed.

⚠️ Known Limitations

Documented deliberately, along with the production fix for each:

Limitation	Production approach
Full truncate-and-load only; no incremental loading	Watermark control table or CDC on the source; MERGE upserts into Silver and Gold
Gold dim_products filters to current versions, discarding the history computed in Silver — effectively SCD Type 1 at the consumption layer	Full SCD Type 2: expose all versions with valid_from / valid_to / is_current, join the fact within the validity window
Surrogate keys generated by ROW_NUMBER() inside a view, so they are recomputed on every query and are not stable	Materialise dimensions as tables with IDENTITY or SEQUENCE keys, assigned via MERGE
Sales of discontinued products produce orphan facts (NULL product_key)	SCD Type 2 dimension plus an "Unknown" member (key -1) so totals still reconcile
Quality checks are diagnostic SELECTs; they neither log results nor fail the pipeline	Write results to a dq_results audit table with severity; raise and stop the pipeline on critical failures
TRY...CATCH prints errors but does not re-raise, so a failed load returns success to the caller	Log to a persistent etl_log table and THROW after logging; wrap loads in explicit transactions
No primary keys, foreign keys or indexes on Silver and Gold	Primary keys on business keys in Silver; clustered columnstore on the fact, clustered index on dimension surrogate keys
No dim_date	Add a full date dimension with fiscal calendar, weekday and holiday attributes
Hardcoded local file paths in the Bronze load	Parameterise the path, or ingest via an orchestrated copy activity from cloud storage
No orchestration, scheduling or alerting	Pipeline orchestration with dependencies, retries and failure notification

🎯 Purpose

Built to practise and demonstrate core data engineering fundamentals — ELT design, medallion architecture, dimensional modelling, slowly changing dimensions and data quality validation — on a platform where each step is fully visible, before applying the same patterns on a distributed cloud stack.

📜 License

Licensed under the MIT License.

🙋 Author

Yash Kute GitHub · LinkedIn