Data Warehouse & Analytics Platform
Designed and implemented a modern SQL Server data warehouse using Medallion Architechture to integrate CRM and ERP systems into scalable analytical model optimzed for reporting and analytics.
3 Layers
Bronze, Silver, Gold
6 Source Tables
CRM, ERP Systems
1 Star Schema
Fact & Dimension Tables
100% SQL-Based ETL
Stored Procedure Orchestration
Architechture Design
Built using Medallion Architechture Principles
Bronze Layer
Stores raw CRM and ERP data ingested directly from CSV sources into SQL server without transformation.
- Raw ingestion
- No transformations
- Stored Procedure for load
Silver Layer
Applies data cleansing, normalization, type connections and derived column creation to standardize and prepare data for integration.
- Data cleaning
- Standardization
- Key normalization
- Derived Columns
Gold Layer
Business-ready analytical model structured as a star schema to enable fast reporting and BI queries.
- fact_sales
- dim_customers
- dim_products
- Optimized aggregations
Data Integration Strategy
The project integrates transactional CRM sales data with ERP-based product and customer metadata. Product and customer keys are normalized and onriched in the Silver layer before being transformed into the Gold layer.
- Cross-system key mapping
- Dedupliation logic
- Schema-based organization
- Stored procedure orchestration
Sample SQL Transformations
Core SQL logic used to clean source data, standardize structures and prepare business-ready models for reporting.
Key Outcomes & Deliverables
Successfully designed and implemented a robust SQL server data warehouse that provides valuable business insights through efficient ETL pipelines.
4- Stage ETL Pipelines
Developed SQL-based ETL pipelines across Bronze, Silver, and Gold layers.
Data Warehouse Infrastructure
Built a scalable SQL Server data warehouse following Medallion Architecture.
Analytics Ready Data Models
Implemented fact and dimension tables structured as a star schema.
Comprehensive Documentation
Delivered architecture diagrams, data models, and structured SQL workflows.