Pipeline to Dashboardnihalsulaiman.com

Week 1 · Ingest anything › Day 1

Day 1. The shape of a data platform

⏱ 4h · learn 75m · lab 120m · ship 30m · review 15m🏷 all four sectors

By the end of today you can

  • Draw the six stages of the pipeline and name a tool for each
  • Explain ETL vs ELT and when each wins
  • Explain raw, staging and mart layers and why raw is immutable
  • Generate the course data and describe what arrives each morning

1. Why this matters

When an interviewer says "walk me through a pipeline you built", they are not asking for a tool list. They want to hear that you understand where data comes from, what can go wrong at each hop, and how you'd know. Today you build that mental map. Everything in the next 48 days hangs on it.

2. The six stages

Stage Question it answers Core tool in this course Alternatives you'll meet
Ingest What arrived, from where, when? Python (imaplib / Microsoft Graph, pathlib, paramiko, requests) dlt, Airbyte, Meltano, rclone
Clean Is it typed, valid, de-duplicated? Polars, DuckDB, pandera, Claude Code pandas, Great Expectations, Soda
Orchestrate What runs when, in what order, and what happens on failure? Dagster Airflow, Prefect, Mage, Kestra, cron
Load Where does it live permanently? Azure SQL Database Postgres, DuckDB/MotherDuck, ClickHouse, Snowflake, BigQuery
Model What shape makes questions easy and answers consistent? SQL + dbt SQLMesh, plain views
Deliver Who sees what, how, and how often? Power BI Metabase, Superset, Evidence, emailed PDFs

Two things run across all six: observability (logs, freshness, row counts, lineage) and governance (who can see what, where secrets live, how changes are reviewed).

3. ETL vs ELT

ETL transforms before loading: Python cleans the file, then writes finished tables. ELT loads raw data first, then transforms inside the warehouse with SQL. Modern teams mostly do ELT with a Python edge: Python handles what SQL is bad at (parsing emails, Excel, encodings, APIs), loads lightly-typed raw tables, and SQL/dbt does the business logic where it's visible, testable and fast.

Maths and principle

Principle: a pipeline is a composition of functions, and you want each function to be pure (same input, same output) so you can rerun any step safely.

Equation: \(\text{mart} = M(S(R(\text{source}, t)))\) where \(R\) = land raw at time \(t\), \(S\) = standardise, \(M\) = model. If \(R\) is stored immutably, you can recompute \(S\) and \(M\) any time without going back to the source.

Analogy: in physics you keep the raw detector readings and re-run calibration when you find a better constant. You never overwrite the readings. Where it breaks: sources that only give you "current state" (a product price table) lose history unless you snapshot it.

Try it: a bug in \(S\) was live for 10 days. With immutable raw, how many source re-pulls do you need to fix history? (Zero: rerun S and M over the 10 raw partitions.)

4. Layers: raw → staging → marts

  • Raw (bronze / landing): an exact copy of what arrived, plus metadata (source, received time, file hash). Append-only. Never edited.
  • Staging (silver): one cleaned, typed table per source entity. Column names standardised, duplicates removed, bad rows quarantined.
  • Marts (gold): star schemas and aggregates shaped for questions: fct_sales, dim_store, dim_date.

The 5-year lens

The raw layer is your alibi. When finance says "your dashboard said $41,200 for store R107 but the store says $43,900", you need to show exactly which files you received, when, and what was in them. Teams that skip raw end up arguing from memory. Also: storage is cheap, re-requesting data from a vendor is not, and some sources (emails, API windows) can't be re-pulled at all after 30–90 days.

5. What a morning looks like

At 06:00 the sales emails start arriving. At 06:30 the POS exports sync to the shared drive. The hotel PMS pushes its extract to SFTP around 04:00, but sometimes the upload is still running at 06:00. The ad API's numbers for yesterday keep changing until about noon (late conversions). Reports must be in managers' inboxes by 08:00.

That timeline is the real design brief. It decides your schedules, sensors, freshness checks and the "data as of" label on every report.

Lab: generate and explore the course data (about 2 hours)

  1. Create a folder ~/p2d and copy starter/generate_data.py into it.
  2. Make a virtual environment and install basics: 
    python -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install pandas numpy openpyxl
python generate_data.py --start 2026-09-01 --days 30 --out ./data
  3. Explore each landing area and fill in this table in a file notes/sources.md:
Source Path Format Grain (one row = ?) Arrives Faults you can spot
Retail sales landing/email_inbox .eml with CSV/XLSX ? ? ?
QSR POS landing/shared_drive/qsr/<date> CSV ? ? ?
Hotel PMS landing/sftp/hotel pipe-delimited ? ? ?
Ads landing/api/ads paginated JSON ? ? ?
  1. Open one .eml in a text editor. Find the headers, the MIME boundary and the base64 attachment.
  2. Draw the six-stage diagram for this company on paper or in excalidraw.com. Photograph it into notes/.

Ship

git init, commit notes/sources.md and your diagram: docs: map sources and pipeline stages.

Go deeper

  • Joe Reis & Matt Housley, Fundamentals of Data Engineering (O'Reilly), chapters 1–3: the lifecycle model this course follows.
  • Dagster docs, "What is an asset?" (skim now; Week 3 goes deep).

Check yourself

1. A vendor API only keeps 30 days of history. Your cleaning code had a bug for 45 days. What design would have saved you?

2. Which job belongs in Python rather than SQL in a typical ELT design?

3. What is the "grain" of the hotel PMS extract?

My notes