19. Learning path and hands-on projects

Fluency comes from building small systems, breaking them on purpose, and fixing them. The path below moves from foundations to AI-specific scale; each phase ends with a gate you can test yourself against.

The path

Phase	Sections	Build	You are done when you can
1. Foundations	2, 3, 4, 5	Project 1	Explain row versus columnar from memory, write window functions and as-of joins unaided, read an EXPLAIN plan
2. Reliable pipelines	6, 10, 11	Project 2	Show your pipeline surviving a double run, late data and an upstream schema change
3. AI data	12, 14	Projects 3 and 5	Show a retrieval metric moving because of a data change, and demonstrate and then fix leakage
4. Scale	7, 8, 9, 13, 15	Projects 4, 6 and 7	Estimate time and cost of a 1 TB job before running it, and explain every number in a dedup report
5. Operate	16, 17	Extend earlier projects	Delete one person’s data end to end, and state the cost per document or prediction

Project 1: a local lake with DuckDB

• Download a public dataset with timestamps, for example the NYC taxi trip records or a Wikipedia dump.
• Convert CSV or JSON to Parquet. Compare file sizes and query times, with and without compression.
• Partition by month, sort by a filter column, and use EXPLAIN ANALYZE to watch partition and row-group pruning happen.
• Write ten analytical queries using joins, window functions and CTEs.

Project 2: an idempotent, incremental pipeline

• Ingest a public API daily (a weather service or GitHub events) into bronze JSONL, append-only.
• Build silver Parquet: typed, validated with Pandera, deduplicated by a natural key.
• Build gold aggregates, orchestrated with Dagster or Prefect, partitioned by date.
• Break it on purpose: trigger the same run twice, inject late-arriving events, rename a field upstream. Fix each until the output is correct and identical across runs.
• Add a write-audit-publish gate and a backfill command for any date range.

Project 3: a RAG ingestion and evaluation pipeline

• Pick a corpus you know well enough to judge answers, such as your own expat.tips articles or public German government guidance pages, mixing HTML and PDF.
• Parse, clean and chunk it with two different strategies. Embed with a multilingual model and store in PostgreSQL with pgvector, using the schema from section 5.
• Write 50 real questions with their relevant chunks. Measure recall@5 and MRR for each chunking strategy.
• Add BM25 hybrid search and a reranker; measure again.
• Make indexing incremental by content hash, propagate deletions, and trace every query with Langfuse.

Project 4: a mini LLM data curation pipeline

• Take a sample of web text, for example a few Common Crawl WET files or a slice of a public web dataset, ideally including German.
• Run language identification, a handful of heuristic filters, exact deduplication, and MinHash near-deduplication (the datasketch library implements MinHash and LSH).
• Decontaminate against a small public benchmark.
• Produce a report of how many documents and tokens each step removed, per language. Tokenize the survivors and write shards with a manifest.

Project 5: a point-in-time correct ML dataset

• From event data, build a customer dimension with Type 2 history, a spine of prediction dates, and labels from a future window.
• Build the training set twice: naively with current values, and correctly with as-of joins on availability time. Train the same model on both and compare offline and time-split results.
• Version the dataset (DVC or a table snapshot ID) and log the run in MLflow, so the model points back to its exact data.

Project 6: change data capture into a vector index

• With Docker Compose, run PostgreSQL, Debezium and Kafka or Redpanda.
• Write a consumer that re-chunks and re-embeds a document when it changes, and removes it when deleted.
• Measure the freshness lag from an edit in the database to a changed search result.

Project 7: feeding a GPU

• Pack an image dataset into WebDataset shards on MinIO, and keep a copy as individual small files.
• Benchmark DataLoader throughput while varying num_workers, prefetching and storage layout.
• Profile one training loop and show whether the GPU waits on data before and after your changes.

Reading list

• Martin Kleppmann, Designing Data-Intensive Applications (the second edition if available): the single best book for sections 3, 4, 6 and 8.
• Joe Reis and Matt Housley, Fundamentals of Data Engineering: the modern data engineering lifecycle.
• Ralph Kimball and Margy Ross, The Data Warehouse Toolkit: dimensional modeling.
• Tyler Akidau, Slava Chernyak and Reuven Lax, Streaming Systems: event time, windows and watermarks.
• Chip Huyen, Designing Machine Learning Systems and AI Engineering: ML and LLM systems from the data side.
• Jure Leskovec, Anand Rajaraman and Jeffrey Ullman, Mining of Massive Datasets: the chapter on similarity search covers MinHash and LSH.
• Jay Kreps’ essay The Log: why the append-only log unifies data systems.
• Timnit Gebru and colleagues, Datasheets for Datasets: dataset documentation.
• Dataset write-ups such as FineWeb and Dolma: real LLM data pipelines with their ablations.

Study habits

• Keep a lab notebook: every surprise, every number you measured, every bug and its cause.
• Give each project a README that states its design decisions and trade-offs. It doubles as portfolio evidence.
• After each section, explain its core idea aloud in two minutes without notes. Where you stall, re-read.