Back to Projects
Data Warehousing & Business Intelligence • 2025The Vision & Architecture
Reconciling ERP & CRM data silos into
a single, high-performance Star Schema.
End-to-end data warehouse engineering project integrating siloed CRM and ERP source systems into an optimized Kimball dimensional star schema. Implements Medallion staging, automated data quality cleansing, SCD Type 2 dimension historization, and complex analytical CTE reporting.
Core Capabilities
Master Data Integration (CRM + ERP)
Reconciled disparate customer demographics, product catalogs, and transaction records across siloed enterprise business systems.
Data Quality & Cleansing Automation
Resolved duplicate customer records, out-of-bounds dates, compound SKU key prefixes, and formatting inconsistencies using advanced T-SQL.
Kimball Dimensional Modeling
Constructed a high-performance Gold Star Schema with central fact tables and denormalized dimension tables for rapid BI aggregation.
SCD Type 2 Analytical Historization
Dynamically generated dimension validity windows (start_date, end_date) using SQL window functions (LEAD/LAG) to track attribute drift.
Advanced BI Reporting Queries
Authored multi-CTE queries calculating Customer Lifetime Value (CLV), RFM customer segmentation cohorts, and product margin analyses.
Technology Stack
Architectural Decisions
Star schema over normalized 3NF for reporting marts
Denormalized dimensions eliminate complex multi-table joins and significantly accelerate analytical dashboard queries.
Database-generated surrogate keys
Surrogate keys decouple the warehouse from source ERP/CRM identifier changes, ensuring durable analytical integrity.