SQL Data Warehouse

A data warehouse built with PostgreSQL using the Medallion Architecture (Bronze → Silver → Gold). Raw data from two source systems (CRM and ERP) is ingested, cleaned, and transformed into a star schema ready for analytics.

---

Architecture Overview

Source Systems
    ├── CRM (3 CSV files)
    └── ERP (3 CSV files)
            │
            ▼
        [Bronze]          Raw data — loaded as-is from CSVs
            │
            ▼
        [Silver]          Cleaned & standardized — deduplication, type fixes, lookups
            │
            ▼
         [Gold]           Star schema — dimension & fact materialized views

---

Source Data

Source	File	Contents
CRM	cust_info.csv	Customer records
CRM	prd_info.csv	Product catalogue
CRM	sales_details.csv	Sales transactions
ERP	CUST_AZ12.csv	Customer demographics (birth date, gender)
ERP	LOC_A101.csv	Customer country/location
ERP	PX_CAT_G1V2.csv	Product categories & subcategories

---

Layer Details

Bronze — Raw Ingestion

Tables mirror the source files exactly. Data is loaded using PostgreSQL COPY inside a stored procedure (bronze.load_bronze()). Each load truncates and reloads the table.

Tables: bronze.customer_info, bronze.product_info, bronze.sales_info, bronze.erp_cust_az12, bronze.erp_loc_a101, bronze.erp_px_cat_g1v2

---

Silver — Cleaned & Transformed

Data quality issues found in Bronze are fixed here via silver.load_silver(). Each table also gets a dwh_create_date audit timestamp.

Key transformations applied:

Table	What was fixed
customer_info	Trimmed names, decoded gender (M/F → Male/Female) and marital status (M/S → Married/Single), deduplicated by keeping the latest record per customer
product_info	Split prd_key to extract a cat_id, decoded product line codes, derived prd_end_date using LEAD(), defaulted null costs to 0
sales_info	Fixed invalid date strings, recalculated sales_amount where it didn't match qty × unit_price, handled null/negative prices
erp_cust_az12	Stripped NAS prefix from customer IDs, nulled out future birth dates, standardised gender values
erp_loc_a101	Removed hyphens from IDs, normalised country codes (DE → Germany, US/USA → United States)
erp_px_cat_g1v2	Passed through as-is (already clean)

---

Gold — Star Schema

Business-ready layer built as materialized views via gold.load_gold().

gold.dim_customers — Customer dimension

• Joins silver.customer_info + silver.erp_loc_a101 (country) + silver.erp_cust_az12 (birth date, gender)
• Adds a surrogate customer_key

gold.dim_products — Product dimension

• Joins silver.product_info + silver.erp_px_cat_g1v2 (category/subcategory)
• Filters to current products only (prd_end_date IS NULL)
• Adds a surrogate product_key

gold.fact_orders — Sales fact table

• Joins silver.sales_info with dim_customers and dim_products via surrogate keys
• Contains order dates, amounts, quantities, and unit prices

---

Project Structure

sql-data-warehouse/
├── dataset/
│   ├── source_crm/         # CRM CSV files
│   └── source_erp/         # ERP CSV files
├── docs/
│   ├── data_flow.png
│   ├── data_integration.jpg
│   └── data_model.png
├── scripts/
│   ├── init.sql             # Create database, schemas, and run all procedures
│   ├── tests-eda.sql        # Data quality checks & EDA queries
│   ├── 01_bronze/
│   │   ├── bronze_ddl.sql   # Table definitions
│   │   └── load_bronze.sql  # Stored procedure to ingest CSVs
│   ├── 02_silver/
│   │   ├── silver_ddl.sql   # Table definitions
│   │   └── load_silver.sql  # Stored procedure to clean & transform
│   └── 03_gold/
│       └── load_gold.sql    # Stored procedure to build materialized views
└── README.md

---

How to Run

1. Create the database and schemas

CREATE DATABASE DataWarehouse;

-- Connect to DataWarehouse, then:
CREATE SCHEMA IF NOT EXISTS bronze;
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;

2. Create tables — run bronze_ddl.sql and silver_ddl.sql

3. Load the procedures — run load_bronze.sql, load_silver.sql, load_gold.sql

4. Execute the pipeline

CALL bronze.load_bronze();
CALL silver.load_silver();
CALL gold.load_gold();

Note: The COPY commands in load_bronze.sql use absolute local paths. Update these paths to match where the dataset/ folder lives on your machine before running.

---

Data Quality Tests

scripts/tests-eda.sql contains exploratory queries used to identify issues before writing the Silver transformations. Checks include:

• Orphaned sales records (product keys not in Silver)
• Invalid or malformed date strings in sales
• Null or inconsistent sales_amount vs qty × unit_price
• Duplicate IDs in ERP tables
• Future birth dates
• Non-standard country codes and gender values
• Whitespace in categorical columns