Modern Data Warehouse using SQL Server & Medallion Architecture

Project Domain: Data Engineering / Enterprise Data Warehousing
Tech Stack: SQL Server, T-SQL, CSV Data Ingestion, Medallion Architecture (Bronze, Silver, Gold)
Status: Production / Implemented

---

📋 Executive Summary & Specifications

This project establishes an enterprise-grade Modern Data Warehouse built on Microsoft SQL Server. The solution consolidates raw transactional sales data from disparate source systems (ERP and CRM) to empower business intelligence, analytical reporting, and executive decision-making.

Key Specifications & Requirements

• Data Sources: Ingestion of raw CSV extracts from two primary source systems:
  • CRM System: Customer demographics, leads, and sales interaction data.
  • ERP System: Product catalogs, transactional sales orders, and inventory data.
• Data Quality & Governance: Comprehensive cleansing, deduplication, standardization, and null-handling prior to downstream analytical consumption.
• Integration Strategy: Unification of ERP and CRM entities into a unified, user-friendly Star Schema dimensional model.
• Scope & Historization: Optimized for the latest snapshot analysis (full reload pattern for Bronze layer); historization strategy configured per layer requirements.
• Documentation & Data Lineage: Full technical and business documentation, end-to-end data lineage tracking, and standardized naming conventions.

---

🏗️ Architectural Framework: Medallion Architecture

The data warehouse follows the Medallion Architecture pattern (Bronze ➔ Silver ➔ Gold), guaranteeing progressive data quality improvement, traceability, and scalable processing. This architecture is data design pattern used in lakehouse environments to organize data into three distinct layers

       +-------------------+       +-------------------+       +-------------------+
       |   BRONZE LAYER    |       |   SILVER LAYER    |       |    GOLD LAYER     |
       |  (Raw Ingestion)  | ----> | (Cleansed & Filtered) | ----> | (Star Schema / BI)|
       +-------------------+       +-------------------+       +-------------------+
       | • Truncate/Load   |       | • Type Casting    |       | • Fact & Dim      |
       | • Exact Source    |       | • Data Cleansing  |       | • Business Logic  |
       |   Structure       |       | • Deduplication   |       | • Aggregations    |
       +-------------------+       +-------------------+       +-------------------+

Layer Breakdown

Layer	Purpose	Target Audience	Load Strategy	Transformations
Bronze	Raw land area; exact replica of source extracts	Data Engineers, System Auditing	Full Reload (TRUNCATE & INSERT)	Zero transformation (stored as-is)
Silver	Cleaned, validated, and standardized data	Data Analysts, Engineers	Incremental / Merge	Data typing, null handling, trimming, structural standardization
Gold	Star Schema dimensional model optimized for reporting	BI Developers, Business Analysts, Executives	Strategic Upsert / Full Load	Business aggregations, surrogate keys, dimension modeling

---

The architecture supports ELT (Extract, Load, Transform) workflows, allowing light transformations in the Silver layer and advanced business logic in the Gold layer.

🏷️ Naming Conventions & Design Standards

To ensure code maintainability, consistency, and readability across all database objects, strict snake_case naming standards are enforced.

General Principles

1. Case & Separators: All database object names use lowercase English letters with underscores (_) separating words.
2. Language: Exclusively English nomenclature.
3. Reserved Keywords: SQL reserved keywords are strictly forbidden as object identifiers.

Object Naming Reference

Category	Format / Convention	Example	Description
Bronze Tables	<sourcesystem>_<entity>	crm_customer_info	Matches raw source table name prefixed by source system
Silver Tables	<sourcesystem>_<entity>	erp_px_cat_g1v2	Cleansed source table with standardized data types
Gold Tables	<category>_<entity>	dim_customers, fact_sales	Categorized by role (dim_, fact_, report_, agg_)
Surrogate Keys	<entity>_key	customer_key	Primary key generated for dimensional integrity
Technical Columns	dwh_<column_name>	dwh_load_date	System-generated tracking columns
Stored Procedures	load_<layer>	load_bronze, load_silver	Automated ingestion and transformation procedures

---

🥉 Bronze Layer Implementation

Objectives & Strategy

• Goal: Capture raw data without business logic altering original records, ensuring 100% auditability and lineage tracking.
• Loading Technique: Bulk automated loading from CSV sources into staging tables via T-SQL BULK INSERT.
• Execution Summary: 18,493 raw records successfully ingested across CRM and ERP tables.

Key Discovery & Source Alignment Questions

Before building the Bronze layer pipeline, source data discovery was structured around three core pillars:

1. Business Context & Ownership

• Who is the business owner and technical custodian for each source dataset?
• Are data dictionaries and technical catalogs available?

2. Architecture & Tech Stack

• What are the native source database systems (e.g., SQL Server, Oracle, PostgreSQL)?
• What extract mechanisms are available (CSV dumps, API feeds, Change Data Capture)?

3. Extract & Load Dynamics

• Is the load strategy Incremental (CDC) or Full Load?
• What are the network, security, and authentication requirements (VPN, SSH, tokens)?
• How can ingestion be scheduled to prevent operational overhead on source systems?

---

🥈 Silver Layer Implementation

Objectives & Transformations

• Data Cleansing: Whitespace removal (TRIM), handling invalid string values, and converting missing/null codes into standard NULLs or default fallbacks.
• Type Safety: Explicit casting of raw strings into proper SQL data types (INT, DATE, DECIMAL, VARCHAR).
• Standardization: Normalizing gender codes, country names, and status codes across ERP and CRM entities.

---

🥇 Gold Layer Implementation & Data Modeling

Dimensional Modeling (Star Schema)

The Gold layer combines silver entities into business-centric dimension and fact tables, generating surrogate keys (<entity>_key) to isolate the data warehouse from upstream natural key changes.

                  +--------------------------+
                  |      dim_customers       |
                  +--------------------------+
                  | PK  customer_key         |
                  |     customer_id          |
                  |     full_name            |
                  |     gender               |
                  |     country              |
                  +--------------------------+
                               |
                               | 1:N
                               v
+-----------------------+   +--------------------------+   +-----------------------+
|     dim_products      |   |        fact_sales        |   |       dim_date        |
+-----------------------+   +--------------------------+   +-----------------------+
| PK  product_key       |   | PK  sales_key            |   | PK  date_key          |
|     product_id        |---| FK  product_key          |---|     full_date         |
|     product_name      |   | FK  customer_key         |   |     year, quarter     |
|     category, price   |   | FK  order_date_key       |   |     month_name        |
+-----------------------+   |     order_number         |   +-----------------------+
                            |     sales_amount         |
                            |     quantity             |
                            +--------------------------+

Final Analytical Value

• Provides reporting-ready star schemas for Power BI, Tableau, and ad-hoc SQL analysis.
• Delivers unified customer and product metrics across both sales (ERP) and customer interaction (CRM) channels.
• Optimizes query performance through targeted indexing, foreign key constraints, and pre-calculated measures