PostgreSQL Medallion Data Warehouse

A data engineering project that integrates CRM and ERP CSV exports into a PostgreSQL analytics warehouse. The pipeline follows a medallion architecture, moving data through raw ingestion, quality-focused transformation, and a business-ready dimensional model.

The project demonstrates practical warehouse engineering with PostgreSQL, including full-refresh ingestion, SQL-based data cleansing, source-system integration, dimensional modeling, surrogate keys, and data quality validation.

Data Warehouse and Analytics Project Description

The source systems contain customer, product, sales, location, and product-category data from separate CRM and ERP exports. The warehouse standardizes these datasets and makes them available for customer, product, and sales analysis.

Analytical model

The gold layer exposes a star schema through three views:

• gold.dim_customers: Customer identity, demographics, country, and creation date. CRM gender is preferred, with ERP gender used as a fallback.
• gold.dim_products: Current products enriched with category, subcategory, maintenance, cost, and product-line attributes.
• gold.fact_sales: Sales transactions joined to customer and product surrogate keys, with order, shipping, and due dates plus sales measures.

The resulting model supports questions such as:

• How much revenue and quantity were generated by product, category, or product line?
• Which customer segments and countries contribute to sales?
• How do product versions and current product attributes relate to sales activity?
• Are sales transactions connected to valid customer and product dimensions?

---

Data Architecture Overview

Table-level data flow

flowchart LR
	subgraph SRC[Source CSV files]
		CRM_CUST[cust_info.csv]
		CRM_PRD[prd_info.csv]
		CRM_SALES[sales_details.csv]
		ERP_CUST[CUST_AZ12.csv]
		ERP_LOC[LOC_A101.csv]
		ERP_CAT[PX_CAT_G1V2.csv]
	end

	subgraph BRONZE[Bronze layer: 6 raw tables]
		B_CUST[bronze.crm_cust_info]
		B_PRD[bronze.crm_prd_info]
		B_SALES[bronze.crm_sales_details]
		B_ERP_CUST[bronze.erp_cust_az12]
		B_ERP_LOC[bronze.erp_loc_a101]
		B_ERP_CAT[bronze.erp_px_cat_g1v2]
	end

	subgraph SILVER[Silver layer: 6 cleansed tables]
		S_CUST[silver.crm_cust_info]
		S_PRD[silver.crm_prd_info]
		S_SALES[silver.crm_sales_details]
		S_ERP_CUST[silver.erp_cust_az12]
		S_ERP_LOC[silver.erp_loc_a101]
		S_ERP_CAT[silver.erp_px_cat_g1v2]
	end

	subgraph GOLD[Gold layer: 3 views]
		G_CUSTOMERS[gold.dim_customers]
		G_PRODUCTS[gold.dim_products]
		G_SALES[gold.fact_sales]
	end

	CRM_CUST --> B_CUST --> S_CUST
	CRM_PRD --> B_PRD --> S_PRD
	CRM_SALES --> B_SALES --> S_SALES
	ERP_CUST --> B_ERP_CUST --> S_ERP_CUST
	ERP_LOC --> B_ERP_LOC --> S_ERP_LOC
	ERP_CAT --> B_ERP_CAT --> S_ERP_CAT

	S_CUST --> G_CUSTOMERS
	S_ERP_CUST --> G_CUSTOMERS
	S_ERP_LOC --> G_CUSTOMERS

	S_PRD --> G_PRODUCTS
	S_ERP_CAT --> G_PRODUCTS

	S_SALES --> G_SALES
	G_CUSTOMERS --> G_SALES
	G_PRODUCTS --> G_SALES

Loading

The pipeline creates six raw bronze tables from six CSV files, transforms them into six silver tables, and then builds three gold views. The gold customer and product views combine related silver tables; the sales fact view combines silver sales with the two gold dimensions.

Bronze: Raw Data Ingestion

The bronze layer mirrors the six source files in six staging tables. bronze.load_bronze() truncates each table and uses PostgreSQL COPY to load the CSV files. Load notices include row counts and elapsed time.

Silver: Data Cleaning and Integration

The silver layer creates a consistent analytical foundation by:

• Deduplicating customers and retaining the latest record by customer ID.
• Trimming text and standardizing marital status, gender, country, and product-line values.
• Normalizing CRM and ERP customer identifiers for joins.
• Converting integer YYYYMMDD values into PostgreSQL dates.
• Reconstructing invalid sales and price values from quantity and price relationships.
• Deriving product validity dates with LEAD().
• Adding dwh_create_date metadata to each silver table.

Gold: Analytics Layer

The gold layer is implemented as views over silver tables. ROW_NUMBER() produces customer and product surrogate keys at query time. Current products are selected by filtering out historical product versions, and sales are connected to both dimensions with left joins so unmatched references remain visible for data-quality investigation.

Final gold star schema

erDiagram
	dim_customers ||--o{ fact_sales : "customer_key"
	dim_products ||--o{ fact_sales : "product_key"

	dim_customers {
		bigint customer_key
		int customer_id
		varchar customer_number
		varchar first_name
		varchar last_name
		varchar country
		varchar marital_status
		varchar gender
		date birthdate
		date create_date
	}

	dim_products {
		bigint product_key
		int product_id
		varchar product_number
		varchar product_name
		varchar category_id
		varchar category
		varchar subcategory
		varchar maintenance
		int cost
		varchar product_line
		date start_date
	}

	fact_sales {
		varchar order_number
		bigint product_key
		bigint customer_key
		date order_date
		date shipping_date
		date due_date
		int sales_amount
		int quantity
		int price
	}

Loading

fact_sales is the central transaction view. dim_customers and dim_products provide descriptive context for slicing sales by customer, geography, product, category, and product line. customer_key and product_key are logical join columns: the dimension views generate them with ROW_NUMBER(), and the fact view selects the matching values through its joins. The gold layer contains views only, so no primary-key or foreign-key constraints are physically declared in PostgreSQL.

---

Repository Structure

medallion-pipeline-postgres/
├── datasets/
│   ├── source_crm/
│   │   ├── cust_info.csv
│   │   ├── prd_info.csv
│   │   └── sales_details.csv
│   └── source_erp/
│       ├── CUST_AZ12.csv
│       ├── LOC_A101.csv
│       └── PX_CAT_G1V2.csv
├── scripts/
│   ├── init_database.sql          # Database and schema bootstrap
│   ├── bronze/
│   │   ├── bronze.sql             # Raw table definitions
│   │   ├── proc_load_bronze.sql   # CSV ingestion procedure
│   │   └── output.txt             # Recorded bronze load output
│   ├── silver/
│   │   ├── ddl_silver.sql         # Cleansed table definitions
│   │   ├── proc_load_silver.sql   # Transformation procedure
│   │   └── output.txt             # Recorded silver load output
│   └── gold/
│       └── ddl_gold.sql           # Customer, product, and sales views
├── tests/
│   ├── quality_checks_silver.sql  # Silver validation queries
│   └── quality_checks_gold.sql    # Gold model validation queries
├── docs/                          # Reserved for project documentation
├── LICENSE
└── README.md

---

Project Overview

Technology

• PostgreSQL
• PL/pgSQL stored procedures
• SQL DDL, transformations, and validation queries
• CSV source data

Source data

Source	File	Business content
CRM	cust_info.csv	Customer identity, names, status, gender, creation date
CRM	prd_info.csv	Product attributes, cost, product line, validity dates
CRM	sales_details.csv	Orders, products, customers, dates, sales, quantity, price
ERP	CUST_AZ12.csv	Customer birthdate and gender
ERP	LOC_A101.csv	Customer country
ERP	PX_CAT_G1V2.csv	Product category, subcategory, and maintenance classification

The checked-in load output records the following historical row counts:

Dataset	Bronze rows	Silver rows
CRM customers	18,494	18,484
CRM products	397	397
CRM sales	60,398	60,398
ERP customer attributes	18,484	18,484
ERP locations	18,484	18,484
ERP product categories	37	37

The ten-row reduction in CRM customers is the result of null-key filtering and latest-record deduplication in the silver load.

Data quality and validation

The validation scripts are query-based checks intended to return no unexpected rows. They cover:

• Null and duplicate business keys.
• Whitespace and standardization issues.
• Negative or missing product costs.
• Invalid date ranges and order-date relationships.
• Sales consistency against quantity and price.
• Customer birthdate ranges.
• Gold dimension-key uniqueness.
• Fact-to-dimension referential connectivity.

---

Running the Project

Prerequisites

• PostgreSQL with permission to create and drop databases.
• A SQL client such as psql, DBeaver, or pgAdmin.
• Access for the PostgreSQL server process to read the repository CSV files.

Execution order

Run the scripts in this order:

1. Run scripts/init_database.sql from an administrative connection.
2. Connect to the newly created data_warehouse database.
3. Run scripts/bronze/bronze.sql.
4. Run scripts/silver/ddl_silver.sql.
5. Run scripts/bronze/proc_load_bronze.sql. This creates and calls bronze.load_bronze().
6. Run scripts/silver/proc_load_silver.sql, then call silver.load_silver().
7. Run scripts/gold/ddl_gold.sql.
8. Run tests/quality_checks_silver.sql and tests/quality_checks_gold.sql.

For a repeatable rebuild, recreate the gold views before dropping silver tables, because the silver DDL uses DROP TABLE IF EXISTS without CASCADE.

Important execution notes

• Update base_path in scripts/bronze/proc_load_bronze.sql to the absolute path of the repository's datasets/ directory, configured for your local environment, before running the bronze load procedure.
• PostgreSQL server-side COPY reads from the database server filesystem, not necessarily the SQL client machine.
• Bronze and silver loads truncate and reload their targets; this is a full-refresh demonstration, not an incremental pipeline.
• The load procedures catch errors and emit notices without re-raising them. Review the notices and row counts after each run.
• The repository stores ERP filenames in uppercase, while the procedure references lowercase names. This works on case-insensitive Windows filesystems but should be aligned before running on a case-sensitive system.
• The quality-check scripts are currently written as manual queries. Before running them on PostgreSQL, replace SQL Server functions LEN() and GETDATE() in tests/quality_checks_silver.sql with PostgreSQL equivalents such as LENGTH() and CURRENT_DATE.

---

License

This project is available under the MIT License.