Aayush Yagol

This project showcases a complete end-to-end solution for building a modern data warehouse using SQL Server, transforming raw data into business-ready insights. Designed as a portfolio project, it demonstrates my hands-on skills in data engineering, ETL pipeline development, and analytics.

📌 Overview

This repository presents a structured approach to data warehousing, adhering to the Medallion Architecture:

High Level Architecture

🟤 Bronze Layer – Stores raw data as-is from the source systems. Data is ingested from CSV Files into SQL Server Database.
⚪ Silver Layer – This layer includes data cleansing, standardization, and normalization processes to prepare data for analysis.
🟡 Gold Layer – Houses business-ready data modeled into a star schema required for reporting and analytics.

With data sourced from ERP and CRM systems (CSV format), I implemented ETL pipelines, created fact and dimension tables, and generated insights on customer behavior, product performance, and sales trends.

🧱 Features

🏗️ Medallion Architecture applied in SQL Server
🔄 ETL Pipelines for ingestion, cleaning, transformation, and modeling
🌐 Star Schema Design (Fact & Dimension tables)
📊 Analytics-ready tables supporting reporting needs
📚 Documentation for architecture, flow, and schema
💡 SQL-based reporting for key business metrics

📂 Project Structure

sql-data-warehouse-project/ │ ├── datasets/ # Raw CSV files from ERP and CRM │ ├── docs/ # All documentation and visuals │ ├── data_architecture_diagram.png # Visual representation of architecture │ ├── data_catalog.md # Gold Layer: Tables and column descriptions │ ├── etl.drawio # ETL flow diagram │ ├── data_models.drawio # Star schema model │ ├── naming-conventions.md # Standards for naming │ ├── scripts/ │ ├── bronze/ # Ingest raw data │ ├── silver/ # Transform & clean data │ ├── gold/ # Create business-level models │ ├── tests/ # SQL validation and quality checks │ ├── README.md └── requirements.txt # Tools & dependencies

🧪 Tech Stack & Tools

SQL Server Express – Backend DBMS
SSMS (SQL Server Management Studio) – GUI for development
Draw.io – Architecture and data modeling diagrams
Git & GitHub – Version control and collaboration
Notion – Project planning and documentation

📊 Key Insights Delivered

📈 Customer Behavior Analysis
🛒 Product Performance Metrics
💰 Sales Trend Insights

Each of these was enabled through optimised SQL queries over the Gold Layer, empowering stakeholders with actionable intelligence.

✅ What I Learned

Real-world implementation of Medallion Data Architecture
Building efficient and reusable ETL processes
Hands-on practice with dimensional modeling
Writing performant and readable SQL queries for analytics
Importance of clear documentation and naming conventions

Diagrams

Data Flow Diagram

Data Integration

Data Integration Diagram

Data Model

ETL Methods

View the files and Project Here: LINK

Data Warehouse and Analytics Project