Retail Analytics Pipeline

SQL and Python ETL feeding a reproducible KPI dashboard, replacing hand-built spreadsheet reporting

---

Why this exists

Modelled on a real reporting problem: a monthly pack assembled by hand in Excel. It took most of a day, the numbers moved depending on who built it, and when a figure was questioned nobody could reconstruct how it had been derived.

Replacing it with a script is the easy half. The half that matters is making the output defensible.

Approach

The pipeline is deliberately linear and boring:

extract -> validate -> transform -> aggregate -> publish

Four properties matter more than any individual step.

It fails before it publishes. Validation sits between extract and transform, and raises. A pipeline that produces a confident wrong report is worse than one that stops — the wrong report gets circulated.

Every KPI is defined exactly once. metrics.py holds the definitions. Two teams quoting different revenue figures is almost always two definitions, not two datasets, which is why revenue is never a column name here — only gross_revenue and net_revenue.

Runs are idempotent and dated. Re-running a past period reproduces it, so a disputed number can be regenerated rather than argued about.

Every run leaves a manifest. Row counts per stage, a content fingerprint of the input, timings and warnings. "Where did this number come from" has an answer.

Design notes

• orders counts distinct order_id, not rows. A four-line order is one order. Counting rows inflates order volume and deflates average order value — the most common error in this kind of report.
• Deduplication keeps the last row per (order_id, product_id). Corrections are appended in source systems, so the last row is the corrected one; keeping the first silently reports pre-correction figures.
• The input is fingerprinted with a content hash, so a rerun on unchanged input is provably unchanged.
• strict=False degrades to warnings rather than raising, for the backfill case where historical data is known to be imperfect and stopping is not an option.

Installation

git clone https://github.com/shehan2020/retail-analytics-pipeline.git
cd retail-analytics-pipeline
python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt

Usage

Run the whole thing:

python src/pipeline.py

Or against your own extract:

from src.pipeline import run_pipeline

manifest = run_pipeline("data/raw/orders.csv", output_dir="outputs")
print(manifest.render())

Headline numbers for the top of the pack:

import pandas as pd
from src.metrics import headline

tables = {name: pd.read_csv(info["path"]) for name, info in manifest.outputs.items()}
print(headline(tables))

Layout

retail-analytics-pipeline/
├── src/
│   ├── pipeline.py     extract, validate, transform, aggregate, publish
│   └── metrics.py      every KPI definition, in one place
├── tests/
├── examples/
└── requirements.txt

Author

Md Sazzad Hossain Shehan GitHub · LinkedIn

License

MIT — see LICENSE.