SQL Data Warehouse on PostgreSQL

Context

Before dbt and lakehouses, most analytics ran on exactly this: a relational warehouse loaded by SQL procedures. It is still everywhere, and the fundamentals it teaches (layered loading, conformed dimensions, surrogate keys, quality gates) transfer directly to any modern stack. This project builds that classic warehouse end-to-end in pure SQL, and proves every claim it makes with a reproducible run.

Business Problem

Sales data lives in two disconnected systems: a CRM (customers, products, transactions) and an ERP (demographics, locations, product categories). Nobody can answer "which product categories drive revenue, by customer segment and geography?" without manually stitching CSV exports. The warehouse integrates both sources into one dimensional model that answers those questions in a single query.

Architecture

Three PostgreSQL schemas in one database. Bronze is a faithful, auditable copy of the extracts: sales dates stay as raw 8-digit integers because raw is raw. Silver is where every cleansing rule lives, each commented with the data problem it fixes. Gold exposes dim_customers, dim_products and fact_sales as views, grain: one row per order line, declared before any SQL was written. Between the layers sit two quality gates that RAISE EXCEPTION on violation: the load is not "done" until they pass.

The Source Data Is Deliberately Dirty

• Customers re-inserted on every CRM update: 18,494 raw rows for 18,484 real customers; silver keeps the latest per id.
• Sales dates as YYYYMMDD integers, with zeros and malformed values, decoded to real dates, invalid becomes NULL, never a fake date.
• sales ≠ quantity × price on some lines, recomputed from the trustworthy pair, then enforced by a gate.
• ERP customer ids with a legacy NAS prefix and location ids with dashes, normalized so the systems actually join.
• Product versions with end dates before their start dates. The end date is derived instead: a version ends the day before the next one starts.

Challenges

Rebuilt from the rule, not transcribed from the code

The warehouse design comes from a well-known T-SQL project (credited below). Every procedure here was rewritten in PostgreSQL from the cleansing rule it implements: BULK INSERT became a data-driven COPY loop in dynamic SQL, GETDATE() became now(), integer-date casts became to_date(int::text,'YYYYMMDD'). Translating dialect by patching syntax until it runs teaches nothing; translating from the rule proves you understood it.

A gate that cannot fail is not a gate

Inspection queries that SELECT problems and return zero rows prove little: nobody reads them on the hundredth run. Every check here raises an exception, and the runner uses ON_ERROR_STOP, so a violation halts the pipeline. Proven by sabotage: injecting one duplicate customer id aborts the gate with ERROR: S01 FAIL: 1 duplicate/null cst_id.

The shell miscounted; the database didn't

Validating bronze against the CSVs, wc -l reported one row fewer than COPY loaded, on exactly the files with no trailing newline. The database was right. Lesson: even your verification needs verification, and row counts belong to query output, not to eyeballed console lines.

Idempotency is proven, not assumed

Every load is truncate-and-load and every DDL drops before creating. Claim tested two ways: the full pipeline run twice yields identical counts on all 12 tables, and a clean-room rebuild after docker compose down -v ends with both gates green.

Results

Row lineage: nothing lost by accident, everything lost by rule.

18 quality checks green, including revenue reconciliation across layers: gold Σsales_amount = silver Σsls_sales = 29,356,250. And the warehouse answers real questions: Bikes are 96.5% of revenue while Accessories move 2× the units; the US leads total revenue (9.16M), but Australia monetizes each customer 2× better (2,523 vs 1,224); 2013 alone holds more than half of all revenue.

Tech Stack

Architecture and source datasets from the SQL Data Warehouse Project by Baraa Khatib Salkini (MIT). This is an independent rebuild on PostgreSQL (own code, own gates, own decisions), not a fork.

View the code on GitHub ↗