It all started with a real-world problem: a customer drowning in messy PostgreSQL tables—NULL values lurking like potholes, duplicate records multiplying like rabbits, and business teams begging for clean data. But when it came time to share the solution with the world, I needed something lighter, faster, and portable. Enter DuckDB, the plucky little database that could. Unlike its heavyweight cousins, DuckDB runs locally with zero setup—no servers, no cloud costs, just pure analytical power on your laptop. Perfect for showing off pipeline magic without the infrastructure headaches.
The Blueprint: A Three-Story Data House
I built a medallion architecture. A data neighborhood where information grows wiser as it climbs from floor to floor.
The Basement (Raw Layer)
Here, data arrives exactly as it's born: unfiltered, unapologetic, and occasionally chaotic. We kept it untouched, like a time capsule, because you never know when you'll need to revisit the original recipe.
The Ground Floor (Staging Views)
This is where the scrubbing bubbles came out. Using dbt, I crafted lightweight views (not tables!) to clean data on-the-fly: standardizing NULLs, fixing typos, and adding unit tests (hidden in schema.yml like secret quality-control traps). Views meant no storage and transformed data every time someone peeked in.
The Penthouse (Datamart Tables)
For the business teams who just wanted answers (not SQL lessons). I served up dimension tables (clean, descriptive reference data) and fact tables (the measurable "actions" like sales or invoices). These golden datasets were pre-joined, pre-aggregated, and ready for drag-and-drop dashboarding. Even better? They became building blocks for future datamarts (like LEGO pieces for data teams).
