NovaTech Retail
Data Warehouse
A 3-layer SQL Server warehouse that transforms raw retail store data into a structured performance system — covering employee results, coaching targets, department comparisons, and store-level trends.
Problem & Data Sources
Retail data is messy by default — files from different systems, inconsistent columns, duplicates, and raw numbers that don't tell a story. This project transforms three raw source tables into a clean warehouse that answers real management questions about people, products, and store performance.
Source Tables: raw_employees, raw_products, raw_pos_transactions
Who is performing well and who needs coaching?
Is employee performance improving over time?
Which department is leading and which is lagging?
How is the store performing month by month?
Medallion Pipeline
Bronze
Raw CSV files loaded into SQL Server as-is using stored procedures. No business logic applied — exact copy of source structure.
Silver
Names cleaned, data types fixed, duplicates removed, department values standardized, and invalid rows filtered out.
Gold
Seven reporting views built using window functions, aggregations, and KPI logic — ready for analysis and decision-making.
Business Views
vw_employee_monthly
Employee Performance
Measures each employee monthly across total sales, sales per hour, protection attach, membership attach, service attach, and return rate.
vw_product_performance
Product Performance
Shows which products are selling, attaching protection plans, and being returned — to identify what drives and what drags results.
vw_coaching_targets
Coaching Targets
Flags high-volume sellers with weak attach performance so managers can identify and prioritize coaching conversations faster.
vw_employee_trend
Employee Trend
Compares performance month over month using LAG and window functions to show whether an employee is improving, declining, or flat.
vw_employee_scorecard
Employee Scorecard
One-row summary per employee with latest performance, historical averages, and an overall trend direction — a single view for reviews.
vw_department_summary
Department Summary
Rolls individual performance up to department level so leadership can compare monthly team results across the store.
vw_store_monthly
Store Summary
One row per month covering total sales, attach rates, and overall store health — the top-level view for store management reporting.
From Raw Store Data to a Performance System
Three source files. One structured warehouse. Seven business views. The system covers the full pipeline — ingestion, cleaning, and reporting — and delivers clear answers on employee performance, coaching needs, department results, and store-level trends.
Medallion warehouse: Bronze → Silver → Gold
SQL cleaning: deduplication, type fixes, ROW_NUMBER()
Window functions: LAG, PERCENT_RANK, ROW_NUMBER
7 Gold views covering employees, products, and store