SQL Data Warehouse & Business Analytics — Central Superstore (Medallion Architecture)

Designed and built a full SQL data warehouse from a single retail dataset (Central Superstore), following the Medallion Architecture (Bronze → Silver → Gold), as Mini Project 2 for the DEPI Data Analyst Trainee program.

What I built:

- Bronze layer — raw ingestion via BULK INSERT from CSV, kept untouched as a source-of-truth copy

- Silver layer — cleaned/standardized data with a derived total_price column, using a truncate-and-reload pattern

- Gold layer — a star schema (3 dimensions + 1 fact table: dim_customers, dim_products, dim_order_details, fact_sales) implemented as views, designed and documented in ERDPlus

- Stored procedures (load_bronze, load_silver) to automate layer refreshes

- A Python script to auto-convert the source Excel file to CSV for BULK INSERT

- Advanced SQL analysis using CTEs, window functions (RANK, LAG, running/moving totals), and CASE logic — covering magnitude, ranking, change-over-time, cumulative, YoY performance, and part-to-whole analysis

- Two KPI reporting views:

- report_customers — per-customer total orders, sales, quantity, distinct products, lifespan, recency, average order value, and average monthly spend

- report_products — segments products into High/Mid/Low performers by revenue, with recency, total orders/sales/customers, lifespan, average selling price, average order revenue, and average monthly revenue

- Full documentation: README, gold-layer data catalog, and naming conventions guide

- Data quality checks across all three layers (nulls, duplicates, whitespace, standardization, invalid dates, referential integrity)

Tools: SQL Server / SSMS, Python, ERDPlus, Git/GitHub

This was my first end-to-end SQL project, built while transitioning from an accounting background into data analytics.