Sales Data Warehouse

The problem

Company data usually lives in separate systems. Here, customer and sales data comes from a CRM, and product categories and customer details come from an ERP, all as raw CSV files with inconsistent formats, missing values and duplicates. You can’t run reliable analysis on that directly.

This project turns those raw files into one clean, business-ready warehouse that analysts can query easily.

Architecture

The warehouse follows the Medallion Architecture, with three layers:

1. Bronze: raw data loaded from the CSV files, with no changes
2. Silver: cleaned, validated and standardized data
3. Gold: a business-ready star schema for analytics

Data flow

How each source table moves through the layers:

Integrating CRM and ERP

How the tables from the two source systems connect:

What happens in each layer

Bronze: loads raw customer, product and sales data from the CSVs. No transformations, just ingestion.

Silver:

• fixes inconsistent gender values
• standardizes date formats
• handles missing values
• normalizes customer, product and location data

Gold (star schema):

• Dimension, Customers: cleaned customer data enriched with ERP attributes (country, gender, birthdate), with a surrogate key
• Dimension, Products: CRM products combined with ERP category mappings (category, product line, maintenance type)
• Fact, Sales: order dates, product and customer keys, sales amount, quantity and price

Data quality checks

Tests make sure there are:

• no duplicate IDs
• no null keys
• only valid dates
• correct links between the fact table and the dimensions

What you can analyze with it

• Top customers by revenue
• Performance by product category
• Revenue trends by month
• High-value product segments
• Order-to-shipping time

Next steps

• Slowly Changing Dimensions (SCD Type 2)
• Automate the loads with Airflow
• Incremental loads instead of full reloads
• Dashboards on top of the Gold layer