Pipeline to Dashboardnihalsulaiman.com

Week 7 · Run it in production › Day 48

Day 48. Capstone build: daily-data-platform

⏱ 4h · build 210m · review 30m🏷 all four sectors in one platformBefore: Days 1 to 47

By the end of today you can

  • Combine the six proofs into one platform that runs end to end from one command
  • Demonstrate a full morning, including a late file and a bad file
  • Have every acceptance criterion evidenced in the repo

1. The brief

You are the first data hire at a fictional holding company that owns a 12-store grocery chain, an 18-restaurant QSR group with three brands, a 5-hotel group, and an in-house marketing agency running ads for all three. Every morning data arrives by email, shared drive, SFTP and API. Managers want their reports by 08:00. The owner wants to know what it costs.

2. Architecture (target)

generate_data.py ──► landing/ (email, shared drive, SFTP, API)
                       │
Dagster ─ sensors ─► land_* assets ─► raw zone (Parquet + manifest, MinIO or folder)
         schedules     │
                       ├─► clean_* assets (contracts, quarantine, DQ checks)
                       ├─► load to warehouse (Postgres | Azure SQL | DuckDB via resource)
                       ├─► dbt models (staging → marts; tests as asset checks; partitions)
                       ├─► reports: email + PDF, Power BI refresh trigger
                       └─► ops: load_audit, dq_results, report_sent, SLOs
BI: Power BI (PBIP) + Metabase on Postgres · Health: Grafana/Metabase ops board · CI: GitHub Actions

3. Acceptance criteria

# Criterion Evidence
1 just demo generates data, starts the stack (Docker Compose) and materialises everything README, video
2 Four sources land idempotently with a manifest and completeness report screenshot, test
3 Contracts, quarantine, five DQ families, robust volume checks DQ report
4 Dagster: partitions, sensors, retries, blocking checks, backfill screenshots
5 Warehouse resource with ≥2 targets; merge/delete-insert patterns; audit tests, audit query
6 dbt star schemas for all four sectors; SCD2; tests; docs dbt docs
7 Metric dictionary; menu engineering; hotel pace; ROAS; retail basket docs + dashboards
8 Power BI PBIP + Metabase dashboards; RLS; freshness banner screenshots
9 Idempotent daily email with partial-data rule screenshot
10 SLOs, health dashboard, 2 triage reports, 1 incident review docs
11 CI green; data diff; Dockerfile; deploy + rollback docs Actions run
12 Cost model, security/ops plan, business case, ADRs docs

4. The demo morning

Script a "morning" you can replay in the video:

  1. 04:00 hotel files land, one is still .partial.
  2. 06:00 retail emails arrive; R107 is missing; one is sent twice; one has renamed columns.
  3. 06:15 schedule runs; the drifted file is quarantined with a blocker for that store only; R107 shows as missing, not zero.
  4. 07:40 the late hotel file completes; the sensor reprocesses that partition.
  5. 07:55 the email goes out with a banner listing R107 and the drifted store; the dashboards show "data as of 07:52".
Maths and principle

Principle: an end-to-end check closes the loop: the number on the report must be derivable from the files that arrived.

Equation: for each source \(s\) and date \(d\): \(\text{report}_{s,d} = \sum \text{raw}_{s,d} - \text{dups}_{s,d} - \text{quarantine}_{s,d} \pm \text{explained transforms}\). Automate it as the final asset check before the email.

Analogy: closing a thermodynamic cycle: if you don't come back to the starting state, something leaked. Where it breaks: sources with no control totals. Then compare to the source's own row counts or file sums.

Try it: retail raw $120,400 for a day, duplicates $3,100, quarantine $0, refunds split out $900. Expected report net sales if refunds are shown separately? ($120,400 − $3,100 = $117,300 gross sales; refunds $900 as their own line.)

The 5-year lens

Scope ruthlessly. If you're behind, ship the core path (retail + QSR end to end, Azure SQL or Postgres, Power BI) with excellent docs, and list hotels and ads as "next" with links to Proofs 3 and 4. A finished, honest, well-documented platform beats an unfinished ambitious one every time.

Today

Work through the acceptance table in order, ticking items in docs/acceptance.md with links to evidence. Record the demo morning video at the end of the day.

Check yourself

1. You're behind on day 48. Best move?

2. In the demo, R107 sent nothing. What should the email show?

My notes