Building a Medallion Data Warehouse with Airflow, dbt, and PostgreSQL — and the 3 Bugs That Taught Me the Most

elkanah karera — Wed, 11 Nov 2026 01:01:48 +0000

I had nine Olist e-commerce CSVs — 1.55 million rows — and no pipeline. I wanted a production-shaped batch pipeline, not another tutorial notebook.

This is what I built, and the three bugs that taught me more than the code did.

Medallion pattern on PostgreSQL:

9 CSVs → Bronze (raw + SHA-256 audit) → Silver (typed + flagged) → Gold (star schema)

Bronze keeps every column as TEXT — no casting. Each file is SHA-256 hashed, and if the hash is already loaded, it skips. Malformed rows go to an audit table, never dropped silently.

Silver applies real types, PK/FK constraints, and dedup. Key decision: retain and flag anomalies instead of deleting them — 1,382 delivery anomalies, 1,076 payment mismatches, 610 uncategorized products. Dropping rows silently changes business totals.

Gold builds 6 dimensions, 4 facts, 9 views. fact_orders pre-aggregates items, payments, and reviews independently before joining to avoid fan-out.

The orchestration

Five Airflow tasks, two blocking gates:

bronze_load → 0.86s (hash-skip)
bronze_quality_gate → 0.34s
silver_rebuild → 45.01s
silver_quality_gate → 3.53s
gold_build → 7.70s

Green run: 57 seconds. The 0.86s on Bronze isn't a bug — it's SHA-256 idempotency working.

The DAG does not pass DataFrames between tasks. XComs are for metadata, not datasets.

dbt runs in parallel: 33 models, 162 tests, isolated dbt_dev_* schemas with a fail-closed macro.

Bug 1: Airflow couldn't reach the database

PostgreSQL was running, but the container got "Connection refused."

The fix: PostgreSQL was bound only to loopback. Changed listen_addresses in postgresql.conf to include 172.17.0.1 (Docker bridge) and added a pg_hba.conf rule for 172.17.0.0/16.

Container-to-host networking is a host config problem, not a container problem.

Bug 2: Relative paths resolved wrong

The DAG failed with FileNotFoundError on data/raw/olist_customers_dataset.csv.

.env said RAW_DIR=./data/raw — correct on the host, wrong inside the container where CWD is /opt/airflow.

The fix: override DATA_DIR, RAW_DIR, PROCESSED_DIR, WAREHOUSE_DIR in the container environment, not in .env. Changing .env would break host-side runs.

Container overrides belong in the container. Not shared config.

Bug 3: pandas version conflict

Airflow 2.9.3's constraints file pins pandas==2.1.4. My Dockerfile asked for pandas>=2.2.

The fix: one line — pandas==2.1.4.

The --constraint file is not a suggestion. It's the tested version set.

What I deliberately did NOT build

Not streaming — the source is static CSVs, Kafka would be theatre.

Not incremental — full rebuilds (TRUNCATE + DROP) every run. Fine for 1.55M rows, documented as an ADR with the migration path.

Not production-scale — 1.55M rows is small. I say "production-shaped" because one exaggeration makes reviewers distrust everything.

The numbers

Raw rows: 1,550,922
Gold objects: 6 dims, 4 facts, 9 views
dbt models / tests: 33 / 162
Green run: 57.4 seconds
Rejected rows: 0

Zero rejects means malformed rows went to audit tables — not that the data is pristine.

The real lesson

The pipeline was the easy part. The hard part is the operating discipline: idempotent ingestion, quality gates that block, documenting trade-offs, and admitting what you didn't build.

A data engineering portfolio isn't a list of technologies. It's evidence you can ship something and run it.

Repo: https://github.com/Elikanah890/Enterprise-E-Commerce-Data-Engineering-Platform

Stack: PostgreSQL 16 · Airflow 2.9.3 · dbt 1.9.4 · Python 3.11 · Docker Compose

DEV Community: elkanah karera