Online Retail ELT Pipeline
A self-contained pipeline that pulls over a million rows of raw retail sales into a database, cleans it, and reshapes it into a small set of well-organized tables a business analyst can query directly in plain SQL, with 174 data-quality tests gating every build.
Raw sales data is rarely usable as is. This project takes the UCI Online Retail II dataset, over 1 million rows spread across two overlapping Excel sheets, and turns it into a clean, tested set of tables built the way analysts actually query data: a handful of dimension tables (customers, products, dates, countries) connected to a couple of fact tables (sales lines, invoices). That structure is called a star schema, and the whole point of it is that a real business question, revenue by region last quarter, top products by units sold, becomes one straightforward join instead of a maze of raw tables.
The pipeline runs in three stages, each checked before the next begins. An extract-and-load step pulls the raw data in and lands it untouched, a cleaning stage trims, retypes and deduplicates it, and a modeling stage reshapes it into the star schema. Dagster runs and watches all three on a schedule and shows plainly which step succeeded or failed rather than leaving that buried in a log file. The loading step speaks Airbyte's data-movement protocol, the same message format real Airbyte connectors use to say "here is a record" or "here is how far I have got", built by hand rather than run through the Airbyte platform itself, since that now needs a small Kubernetes cluster to install, more setup than a project meant to start with one command should ask for.
The source data hides a genuinely tricky problem: the two yearly spreadsheet tabs overlap by nine days, so naively combining them double-counts 22,523 real transactions, while naively deduplicating them would erase thousands of genuinely repeated purchases that just happen to look identical. The fix numbers each row's appearance within its own sheet first, which turns "are these the same row?" from a guess into a fact. 174 automated tests run on every build to catch problems of that kind, and three of them were proven to work by deliberately breaking the pipeline, disabling the dedup logic, flipping a sign, corrupting a key, then confirming each test caught the exact right number of broken rows. Having tests and knowing they work are different things.
Because the historical dataset is frozen in time, a small simulated order system generates new, realistically messy sales every two hours, so the part of the pipeline that runs on a schedule has something new to process instead of running against the same static file forever. The whole thing starts from one command on a laptop, scheduler, warehouse and order feed included, which is deliberate: a data platform nobody can stand up locally is a data platform nobody contributes to.
- Built a 3-stage pipeline (load, clean, model) moving 1M+ rows into a tested star schema, verified end to end in under 2 minutes with zero manual SQL
- Found and fixed a real data problem: two overlapping source sheets that were double-counting 22,523 transactions, without erasing genuinely repeated purchases that looked identical
- Wrote 174 automated data-quality checks, then proved 3 of them actually catch mistakes by deliberately breaking the pipeline and confirming each test caught the exact right number of bad rows
- Added a simulated live order stream so the pipeline keeps processing new data every 2 hours instead of running once against a fixed file and going idle
- Traced a two-cent reconciliation error down to a single column storing money as floating point instead of a fixed-precision number, and fixed it so totals now match to the cent