PostgreSQL Medallion Data Warehouse

A complete SQL data warehouse project built in PostgreSQL using a simple Medallion architecture:

• Bronze: raw CSV ingestion
• Silver: cleaned and standardized data
• Gold: analytics-ready star schema views

This repository is designed as a portfolio project by Rameez Shafat to demonstrate practical data engineering fundamentals with readable SQL and minimal complexity.

Architecture

The warehouse is organized into three schemas:

1. bronze: raw source data loaded from CSV files
2. silver: cleaned and transformed data
3. gold: dimensional views for reporting

Tech Stack

• PostgreSQL
• SQL (DDL, DML, Views, Procedures)
• CSV source files

Project Structure

postgres-medallion-data-warehouse/
├── datasets/
│   ├── source_crm/
│   └── source_erp/
├── scripts/
│   ├── init_database.sql
│   ├── bronze/
│   │   ├── ddl_bronze.sql
│   │   └── proc_load_bronze.sql
│   ├── silver/
│   │   ├── ddl_silver.sql
│   │   └── proc_load_silver.sql
│   └── gold/
│       └── ddl_gold.sql
├── tests/
│   ├── quality_checks_silver.sql
│   └── quality_checks_gold.sql
└── README.md

How To Run

Run scripts in this order in PostgreSQL:

1. scripts/init_database.sql
2. scripts/bronze/ddl_bronze.sql
3. scripts/silver/ddl_silver.sql
4. scripts/gold/ddl_gold.sql
5. scripts/bronze/proc_load_bronze.sql
6. scripts/silver/proc_load_silver.sql

Then execute:

CALL bronze.load_bronze('/absolute/path/to/postgres-medallion-data-warehouse');
CALL silver.load_silver();

Data Quality Checks

Run these validation scripts after loading data:

• tests/quality_checks_silver.sql
• tests/quality_checks_gold.sql

What This Project Demonstrates

• Schema-based Medallion layering in PostgreSQL
• Repeatable CSV ingestion workflow
• Simple, maintainable SQL transformations
• Star-schema style reporting layer
• SQL-based quality checks

Author

Rameez Shafat

If you want collaboration or feedback on this project, open an issue in this repository.

License

Licensed under the MIT License. See LICENSE for details.