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
Top comments (0)