Enterprise Retail Sales Data Warehouse

Architect: Data With Rishal Engine: MySQL 8.0 Architecture: 3-Tier Medallion (Bronze → Silver → Gold) Presentation Model: Dimensional Star Schema

---

Overview

A production-grade data warehouse built to consolidate fragmented CRM and ERP operational systems into a single, audited analytical platform. The pipeline ingests raw transactional data, applies enterprise-grade cleansing, and delivers a conformed Star Schema optimized for BI tooling.

Validated output: $43,518,903.00 in reconciled global sales revenue across 60,398 fact transactions.

---

Architecture

Bronze — Landing Zone

Raw source data lands here untouched. No transformations, no business logic. The objective is a faithful 1:1 mirror of the source systems so that any upstream issue can be traced back to its exact origin without ambiguity.

Silver — Cleansing & Conformity

Standardization and validation layer. Key engineering decisions made here:

• Carriage return stripping — Windows-originated flat files carried hidden \r characters that broke string comparisons silently. Fixed with REPLACE(TRIM(col), '\r', '') applied consistently across all affected columns before any join logic ran.
• Date integrity — Source systems contained 0000-00-00 sentinel dates and future-dated records. Session mode was overridden to NO_ENGINE_SUBSTITUTION during load to prevent strict mode from aborting the entire batch on individual bad rows. Affected records were isolated and corrected rather than dropped.
• Financial imputation — Pre-aggregated sales_amount values in the source drifted from their atomic components over time. Gold derives sales_amount = ROUND(quantity * price, 2) directly, making the source pre-calculation irrelevant.
• Gender and marital status standardization — Mixed code formats (M, Male, MALE) across CRM and ERP were resolved into display-ready labels via CASE with UPPER(CAST(... AS CHAR)) to survive strict mode without data loss.

Gold — Presentation Layer

Conformed dimensional model optimized for analytical query patterns. Source business keys replaced with surrogate keys. Dimensions built before the fact table. Referential integrity enforced at load time via INNER JOIN — unresolvable transactions are audited and documented, not silently carried as NULLs.

---

Data Model

          ┌─────────────────────────────────┐
          │          dim_customers          │
          ├─────────────────────────────────┤
          │ customer_key  PK (surrogate)    │
          │ customer_id   business key      │
          │ first_name, last_name           │
          │ gender, marital_status          │
          │ country, birthdate              │
          └────────────────┬────────────────┘
                           │ 1:N
                           ▼
     ┌─────────────────────────────────────────────┐
     │                  fact_sales                 │
     ├─────────────────────────────────────────────┤
     │ sales_key     PK (surrogate)                │
     │ order_number  degenerate dimension          │
     │ customer_key  FK → dim_customers            │
     │ product_key   FK → dim_products             │
     │ order_date, ship_date, due_date             │
     │ sales_amount, quantity, price               │
     └─────────────────────────────────────────────┘
                           ▲
                           │ 1:N
          ┌────────────────┴────────────────┐
          │          dim_products           │
          ├─────────────────────────────────┤
          │ product_key   PK (surrogate)    │
          │ product_id    business key      │
          │ product_number, product_name    │
          │ category, subcategory           │
          │ cost, product_line              │
          └─────────────────────────────────┘

Surrogate keys decouple warehouse history from upstream ID changes. Foreign key indexes on customer_key, product_key, and order_date support sub-second aggregation on the full 60K row fact set.

---

Warehouse Reconciliation

Every load script ships with post-load quality checks. Nothing is considered done until these pass.

Metric	Value	Status
Fact transactions loaded	60,398	✅ Parity confirmed
Distinct customers	18,484	✅ Grain validated
Distinct products	397	✅ Hierarchy resolved
Validated revenue	$43,518,903.00	✅ Gold vs Silver reconciled
Data horizon	2010-12-29 → 2014-01-28	✅ No date anomalies in Gold
Null surrogate keys in fact	0	✅ Full referential integrity

---

Deployment Order

Execute scripts sequentially from a clean environment. Each script is idempotent — safe to re-run.

scripts/
├── 01_init_database.sql
├── 02_create_bronze_tables.sql
├── 03_load_bronze_data.sql
├── 04_init_silver_layer.sql
├── 05_load_silver_crm_cust.sql
├── 06_load_silver_crm_prd.sql
├── 07_load_silver_erp_loc.sql
├── 08_load_silver_erp_cat.sql
├── 09_load_silver_erp_cust_az12.sql
├── 10_load_silver_crm_sales.sql
├── 11_load_gold_dim_customers.sql
├── 12_load_gold_dim_products.sql
└── 13_load_gold_fact_sales.sql

---

Commit Convention

feat:     new object or pipeline stage
fix:      data quality or load issue resolution
refactor: structural improvement to existing logic
docs:     documentation updates
audit:    quality check additions or reconciliation updates

---

Built end-to-end by Data With Rishal — schema design, pipeline engineering, data quality framework, and reconciliation suite.