🏗️ CRM + ERP SQL Data Warehouse Project

A comprehensive Data Warehouse built using T-SQL to integrate and transform CRM and ERP datasets into clean, analysis-ready reporting views for business intelligence.

---

📌 Data Warehouse Architecture

Layers:

• Source: Raw CSV files from CRM & ERP systems
• Bronze: Raw ingested tables
• Silver: Cleaned and conformed data
• Gold: Final dimensional views for reporting

---

Data Flow

✅ Key Features

• Ingested 6 structured CRM/ERP datasets
• Cleaned and normalized raw data in Silver layer
• Built 3 dimensional views in Gold:
  • dim_customers
  • dim_products
  • fact_sales
• Ready for integration with BI tools (Power BI/Tableau)

---

🔧 ETL Process

🥉 Bronze Layer

• Loaded CSVs into:
  • crm_cust_info, crm_prd_info, crm_sales_details
  • erp_cust_az12, erp_loc_a101, erp_px_cat_g1v2

🥈 Silver Layer

• Standardized country names, product categories
• Cleaned duplicates and nulls
• Derived product categories, cost bands

• 🧱 Transformations done

🥇 Gold Layer

• dim_customers: customer demographics + segmentation
• dim_products: product details + categories
• fact_sales: sales facts joined with dimensions

---

📊 Business Outcomes

• Segment customers by region, gender, and lifecycle value
• Analyze product performance by category and sales
• Track YoY trends and monthly revenue movements

---

🧰 Tools & Techniques

• SQL Server (SSMS)
• T-SQL: Joins, CTEs, Window Functions, Views
• Data Modeling: Star Schema (Facts + Dimensions)
• Analytics-ready schema for BI visualization

---

📎 Sample Output: fact_sales

sls_ord_num	product_key	customer_key	sls_order_dt	sls_sales	sls_quantity	sls_price
SO43697	20	11005	2010-12-29	3578	1	3578
SO43698	9	11003	2010-12-29	3400	1	3400
SO43699	9	11003	2010-12-29	3400	1	3400
SO43700	47	14501	2010-12-29	699	1	699

---

👤 Author

Hi, I'm Akkala Sai Krishna — a data engineering enthusiast passionate about building scalable data solutions with SQL and modern warehouse design.

🔗 LinkedIn

---

🏷️ Tags

#SQL #DataWarehouse #ETL #CRM #ERP #FactDimensionModel #TSQL #BI