Pipeline to Dashboardnihalsulaiman.com

Week 2 · Clean and trust the data › Day 14

Day 14. Proof 2: qsr-clean, a trustworthy cleaning pipeline

⏱ 4h · build 150m · README 60m · review 30m🏷 QSR (three brands, 18 restaurants)Before: Days 8 to 13

By the end of today you can

  • Ship a cleaning pipeline with contracts, quarantine, DQ report and tests
  • Show a first analytical dashboard on clean data
  • Explain the business-day and dedup decisions in an interview

1. What you're shipping

qsr-clean: reads raw QSR POS files (from Proof 1's raw zone or the landing folder), applies contracts and rules, writes staging Parquet and quarantine, runs DQ checks, and produces two outputs: the DQ report and a small sales dashboard.

Acceptance criteria

# Requirement Proof
1 Contract for POS lines (pandera), strict schema test with a drifted file fails
2 Business date with 05:00 cut-off test at 04:59/05:00
3 cp1252 files decode correctly test with ñ
4 Re-sent batches removed; genuine repeat lines kept two tests
5 Bad rows quarantined with reasons; counts in = good + quarantined conservation test
6 DQ report with five families, unavailable ≠ pass screenshot
7 Dashboard: revenue by brand × business day, channel mix, hour-of-day heatmap, top items screenshot + how to run
8 docs/ai-log.md showing how Claude Code was used and what you rejected file
Maths and principle

Principle: conservation of rows. Every row that enters a transform must leave as a good row, a quarantined row or a counted duplicate.

Equation: \(N_{\text{in}} = N_{\text{good}} + N_{\text{quarantine}} + N_{\text{dup removed}}\). Test it on every run; a mismatch means a transform silently dropped data.

Analogy: Kirchhoff's current law: what flows into a node flows out. Where it breaks: aggregation steps change the unit (lines → orders), so conserve a sum (quantity, revenue) instead of rows there.

Try it: in 48,210, good 47,905, quarantined 112. How many duplicates should the log show? (193.)

2. The dashboard

Use Streamlit (or Evidence, or a Jupyter notebook exported to HTML) reading the staging Parquet through DuckDB. Four visuals, each answering one question a restaurant ops manager has:

  1. How did each brand do yesterday vs the same weekday last week? KPI tiles with % change.
  2. Which channels are growing? Stacked bar of channel share by week.
  3. When are we busy? Heatmap: hour of business day × weekday, order count.
  4. What sells? Top 10 items by revenue with food-cost %.

Use the dataviz principles: one message per chart, labelled axes, no 3D, consistent colours per brand. Power BI comes in Week 6; this is the lightweight version.

import duckdb, streamlit as st
con = duckdb.connect()
df = con.sql("""select brand, business_date, sum(qty*price) revenue
                from 'data/staging/qsr_pos/**/*.parquet' p join 'data/reference/restaurants.csv' r using (restaurant_id)
                group by all order by 2""").df()
st.line_chart(df, x="business_date", y="revenue", color="brand")

What to say about this project

"POS exports from 18 restaurants arrived with re-sent batches, a Windows encoding on one brand, and a business day that ends at 5 a.m. I wrote a contract per source, quarantined bad rows with reasons instead of dropping them, and test that rows in equal rows out plus quarantine plus duplicates on every run. Calendar-day reporting had been putting [N]% of revenue on the wrong day." Fill [N] from your Day 10 chart; never quote a number you did not measure.

Build checklist

  1. One command: uv run qsr-clean --business-date 2026-09-14 (and --since).
  2. Conservation test passes on 60 days of generated data.
  3. DQ report and dashboard screenshots in docs/img/.
  4. README sections: summary + screenshot, problem, architecture, run in 5 minutes, decisions (contract, business day, dedup, quarantine), tests, AI usage, next steps.
  5. Ask Claude Code to act as a senior reviewer: "What would break if this ran unattended every day for a year?" Add the top three answers to Next steps; you'll fix them in Week 3.

Check yourself

1. Rows in 10,000; good 9,800; quarantine 150; duplicates removed 30. What does this mean?

2. Which dashboard question fits a QSR operations manager best?

My notes