🏢 SQL Data Warehouse & Analytics Project

An end-to-end SQL Server Data Warehouse solution built using the Medallion (Bronze → Silver → Gold) Architecture. This project integrates disparate data from CRM and ERP source systems, cleanses and standardizes raw inputs, models data into an optimized Star Schema, and generates analytical views for business decision-making.

---

🏗️ Architecture Overview

       CRM & ERP CSV Files
                │
                ▼
        🥉 Bronze Layer
       (Raw Data Ingestion)
                │
                ▼
        🥈 Silver Layer
  (Cleansing & Standardization)
                │
                ▼
        🥇 Gold Layer
  (Dimensional Modeling / Star Schema)
                │
                ▼
   📊 Customer & Product Analytics

---

🛠️ Tech Stack

• Database Engine: Microsoft SQL Server
• Language: T-SQL
• Interface / IDE: SQL Server Management Studio (SSMS)
• Data Modeling: Star Schema (Dimensional Modeling)
• Database Objects: Stored Procedures, Views, DDL/DML Scripts
• Data Ingestion: BULK INSERT / CSV Handling

---

📂 Project Directory Structure

├── data/
│   ├── source_crm/               # Raw CRM source files
│   └── source_erp/               # Raw ERP source files
│
└── scripts/
    ├── init_database.sql         # Database & schema initialization
    ├── script_bronze/            # Bronze layer DDL & ingestion procedures
    │   ├── ddl_bronze.sql
    │   └── proc_load_bronze.sql
    ├── script_silver/            # Silver layer DDL & transformation procedures
    │   ├── ddl_silver.sql
    │   └── proc_load_silver.sql
    ├── script_gold/              # Gold layer dimensional views
    │   └── ddl_gold.sql
    └── script_analytical/        # Business analytics & KPI reports
        └── basic_data_analysis.sql
        └── advance_data_analysis.sql
        └── reports_data_analysis.sql

---

🧱 Data Warehouse Layers

🥉 Bronze Layer (Raw Ingestion)

• Purpose: Ingests raw data from source systems (CRM & ERP) without altering data types or applying business rules.
• Method: Automated loading via BULK INSERT through stored procedures.
• Key Files:
  • script_bronze/ddl_bronze.sql
  • script_bronze/proc_load_bronze.sql

🥈 Silver Layer (Cleansing & Standardization)

• Purpose: Transforms raw data into clean, structured, and consistent tables.
• Operations Performed:
  • Trimming whitespace and standardizing casing.
  • Handling missing, null, and duplicate values.
  • Date format parsing and data type validation.
  • Normalizing customer, product, and transactional attributes.
• Key Files:
  • script_silver/ddl_silver.sql
  • script_silver/proc_load_silver.sql

🥇 Gold Layer (Dimensional Modeling)

• Purpose: Exposes analytics-ready reporting views structured into a Star Schema.
• Schema Design:
  • Dimensions:
    • gold.dim_customers
    • gold.dim_products
  • Fact:
    • gold.fact_sales
• Key Files:
  • script_gold/ddl_gold.sql

---

📊 Analytics & Key Business Metrics

The reporting layer computes high-level executive KPIs and deep customer/product intelligence:

Category	Key Metrics & Reports
Sales Performance	Total Revenue, Total Orders, Units Sold, Average Order Value (AOV)
Customer Insights	Customer Lifespan, Recency/Frequency, Average Monthly Spend, Customer Segmentation
Product Analytics	Product Performance, Top/Bottom Selling Items, Category Revenue Contribution

---

🚀 How to Run

1. Initialize Database:
   • Run scripts/init_database.sql to create the warehouse database and required schemas (bronze, silver, gold).
2. Setup Bronze Layer:
   • Execute script_bronze/ddl_bronze.sql to create raw staging tables.
   • Execute script_bronze/proc_load_bronze.sql and run the procedure to ingest raw CSV data.
3. Setup Silver Layer:
   • Execute script_silver/ddl_silver.sql to create cleansed tables.
   • Execute script_silver/proc_load_silver.sql to transform, standardize, and load clean data.
4. Setup Gold Layer:
   • Execute script_gold/ddl_gold.sql to create dimension and fact views.
5. Run Analytics:
   • Execute the scripts in script_analytical/ to generate business reports and analytical insights.

---

👨‍💻 Author

Chethan Prabhas
Data Engineering | SQL | Python | Azure | Databricks