Chapter 6 · Practice and Capstone
Capstone Projects, Cheat Sheet and What's Next
- Page 22 of 22
- 11 min read
You have covered the whole stack of working with data in AI systems: advanced queries, sound design, transactions and performance, Python and pandas around the database, clean and validated data, pipelines, ML-ready datasets, the data layer of LLM apps, and the analytical systems around them. Knowledge turns into skill when you build something end to end, with real decisions and real mistakes. This last page gives you four capstone projects to do exactly that, a one-page cheat sheet of the advanced tutorial, and the road ahead on the AI path.
What you will learn
- Four capstone briefs — data cleaning, ETL pipeline, ML dataset builder, analytics warehouse — with requirements, milestones and a checklist
- What makes a data project portfolio-ready
- A cheat sheet of the advanced tutorial's syntax and patterns
- Where SQL and data handling lead next: APIs, prompt engineering, embeddings, vector databases and RAG
How to approach the capstones
Pick one or two; each takes a few evenings. Work in a Git repository with a README.md, keep secrets in environment variables, and make every run reproducible: one command rebuilds everything from the raw input. Use SQLite to start; move to PostgreSQL (Docker) when a project needs it. Each brief lists the pages to revisit. (Project 1 was the sales analytics report in Project: A Sales Analytics Report for BitByte Shop at the end of the beginner tutorial, so the numbering continues from 2.)
Project 2: The messy-data cleaner
Goal: turn a messy export into a clean, validated table and a data-quality report. Revisit: Data Handling Fundamentals: Formats, Quality and Lineage, Data Cleaning with pandas, Importing and Exporting: CSV, JSON, Excel and Parquet.
- Input: a CSV of 1,000+ orders that you generate with a seed and then deliberately damage: mixed date formats, prices as text with "৳" and commas, duplicate rows, inconsistent city spellings ("Dhaka", "dhaka ", "DHK"), missing values, impossible quantities, Bangla names in UTF-8.
- Requirements: a reusable
clean(df)function; a rules file (allowed cities, value ranges); rejected rows written to a quarantine file with a reason; output to Parquet and a SQLite table with constraints; a report of counts before/after per rule. - Milestones: (1) generator and profiling, (2) cleaning rules with tests, (3) quarantine and report, (4) load into the database and prove the constraints hold.
Project 3: An idempotent ETL pipeline
Goal: move data from an operational database and an "API" into an analytics database, safely and repeatably. Revisit: ETL and ELT: Building a Reliable Data Pipeline, Python and Databases: Connections, Parameters and Transactions, Transactions, ACID, Isolation and Locking.
- Input: the shop database (or a generated larger one) plus a folder of JSON files standing in for a payments API.
- Requirements: extract, transform and load as separate functions; incremental loads with a watermark table; upserts so a second run changes nothing; each load in one transaction; logging with row counts; retries with backoff around the "API"; a reconciliation step (source vs target counts and sums) that fails the run on mismatch.
- Milestones: (1) full load, (2) incremental load with a watermark, (3) idempotency test: run twice, compare, (4) failure test: kill it halfway, rerun, nothing duplicated.
Project 4: An ML dataset builder
Goal: a command that builds a leak-free, versioned training set and trains a baseline model. Revisit: Preparing ML-Ready Data with SQL and pandas, Window Functions II: LAG, LEAD, Running Totals and Frames, pandas and SQLAlchemy: DataFrames In and Out of SQL.
- Input: a generated customer/activity database (customers, orders, support tickets, app sessions) with a seed.
- Requirements: feature SQL parameterised by a cutoff date; a clear population and label window; at least ten features from two or more sources; snapshots for several cutoffs saved to Parquet with a manifest (seed, query version, row count, content hash); automated point-in-time checks; a time-based split; a scikit-learn pipeline with imputation, scaling and encoding; a metrics report against a majority baseline; a deliberate leak experiment documented in the README.
- Milestones: (1) generator, (2) one correct snapshot with tests, (3) multiple snapshots and manifest, (4) model and report.
Project 5: A small analytics warehouse
Goal: a star schema with history, loaded by a pipeline, answering business questions fast. Revisit: Warehouses, Lakes, Streams and Scale, Indexes: How They Work and When They Help, EXPLAIN and Query Optimization, Views, Stored Procedures, Functions and Triggers.
- Input: the output of Project 3, or the shop plus a generated year of orders.
- Requirements: a declared grain; a generated date dimension; a type 2 customer dimension with a point-in-time fact load; at least eight report queries (monthly revenue and growth, cohort retention, top-N per category, payment mix…) stored as views; indexes justified by
EXPLAINplans; on PostgreSQL, a partitioned fact table and a materialized view for the dashboard; a read-only role for analysts. - Milestones: (1) dimensional model diagram, (2) loading with SCD type 2, (3) report views with checks against the source, (4) performance and security pass.
The portfolio checklist
| Area | Done when… |
|---|---|
| Reproducible | One command rebuilds outputs from raw input; random generators are seeded; versions are pinned (requirements.txt) |
| Correct | Totals reconcile with the source; tests cover NULLs, duplicates, empty inputs and reruns |
| Safe | Parameters everywhere (no string-built SQL); credentials only in environment variables; PII masked or excluded |
| Explained | README with the problem, a schema diagram, how to run, results and trade-offs; comments say why, not what |
| Measured | Row counts and timings logged; slow queries checked with EXPLAIN |
Advanced cheat sheet
Queries
| Pattern | Syntax |
|---|---|
| CTE | WITH t AS (SELECT …) SELECT … FROM t |
| Recursive CTE | WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 10) … |
| Ranking | ROW_NUMBER() / RANK() / DENSE_RANK() OVER (PARTITION BY g ORDER BY v DESC) |
| Top-N per group | rank in a CTE, then WHERE rn <= N outside it |
| Previous / next row | LAG(v) OVER (ORDER BY d), LEAD(v, 1, 0) OVER (…) |
| Running total / moving average | SUM(v) OVER (ORDER BY d), AVG(v) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) |
| Share of total | 100.0 * v / SUM(v) OVER (PARTITION BY g) |
| Gaps and islands | julianday(d) - ROW_NUMBER() OVER (PARTITION BY u ORDER BY d) as the group key |
| Anti-join | WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.k = a.k) |
| Avoid fan-out | aggregate each child table per key in a CTE, then join |
Design, integrity and performance
| Task | Syntax |
|---|---|
| Transaction | BEGIN; … COMMIT; / ROLLBACK;, SAVEPOINT s; ROLLBACK TO s; |
| Safe decrement | UPDATE products SET stock = stock - 1 WHERE product_id = ? AND stock > 0 |
| Row lock | SELECT … FOR UPDATE (PostgreSQL, MySQL) |
| Upsert | INSERT … ON CONFLICT (k) DO UPDATE SET v = excluded.v; MySQL: ON DUPLICATE KEY UPDATE |
| Index | CREATE INDEX idx ON t (a, b) — leftmost columns first; UNIQUE for rules |
| Plan | EXPLAIN QUERY PLAN … (SQLite), EXPLAIN ANALYZE … (PostgreSQL, MySQL 8) |
| Sargable date filter | d >= '2026-03-01' AND d < '2026-04-01', never a function on the column |
| View / materialized view | CREATE VIEW v AS …; CREATE MATERIALIZED VIEW mv AS …; REFRESH MATERIALIZED VIEW mv; |
| Trigger | CREATE TRIGGER t AFTER UPDATE ON x FOR EACH ROW BEGIN … END; (SQLite) |
| Least privilege | GRANT SELECT ON v TO analyst;, REVOKE … |
| Partitioning | CREATE TABLE t (…) PARTITION BY RANGE (d); + PARTITION OF t FOR VALUES FROM … TO … |
Python, data and AI
| Task | Syntax |
|---|---|
| Parameters | cur.execute("… WHERE id = ?", (x,)) (sqlite3), %s (psycopg2, psycopg 3, PyMySQL) |
| Batch insert | cur.executemany(sql, rows); PostgreSQL COPY, MySQL LOAD DATA |
| pandas | pd.read_sql(sql, con, params=…), df.to_sql("t", con, if_exists="append", index=False) |
| SQLAlchemy | engine = create_engine(url); with engine.begin() as c: c.execute(text(sql), {"k": v}) |
| Point-in-time features | every source filtered with < :cutoff; label from [cutoff, cutoff + window) |
| SCD type 2 join | ON d.id = f.id AND f.date >= d.valid_from AND f.date < d.valid_to |
| JSON | SQLite json_extract(j, '$.k'), json_each; PostgreSQL j ->> 'k', j @> '{"k": 1}' |
| Full-text search | SQLite CREATE VIRTUAL TABLE f USING fts5(body); WHERE f MATCH 'word' ORDER BY rank (BM25, lower = better); PostgreSQL to_tsvector('english', body) @@ plainto_tsquery('english', 'word'), ts_rank, GIN index |
| pgvector | CREATE EXTENSION vector; embedding vector(768); ORDER BY embedding <=> :q LIMIT 5 (cosine; <-> L2, <#> negative inner product); USING hnsw (embedding vector_cosine_ops); SET hnsw.ef_search for recall |
| Text-to-SQL guard | read-only role, allow-listed tables/views, one statement, row limit, statement_timeout, logging |
What's next on the AI path
With this topic you finish the data foundations of Level 0. What comes next builds directly on it:
Level 0 (now) next Path 1: AI Engineer
How AI works · Python · ──▶ APIs: designing and calling ──▶ embeddings → vector databases
Maths · SQL & Data Prompt Engineering (pgvector and friends) → RAG
→ evaluation and monitoring- APIs: your SQL and pandas work becomes a service (FastAPI over a database), with the parameters, transactions and security you learned here.
- Prompt Engineering: the conversation and cost logs from SQL for AI Apps: Embeddings, pgvector and RAG Metadata become your evaluation sets for comparing prompts.
- Path 1: embeddings, vector databases and RAG take the
documents/chunkstables and hybrid search much further — chunking strategies, rerankers, evaluation — on exactly the PostgreSQL and pgvector foundations you used.
Keep practising SQL weekly: re-solve the interview problems in PostgreSQL or MySQL, try public datasets (open government data, Kaggle) in DuckDB or PostgreSQL, and read the query plans of everything slow.
Common mistakes
- A project that only runs on your laptop. Hard-coded paths and passwords, an unseeded generator. Fix: environment variables, relative paths, seeds and a one-command run script.
- Only the happy path. A pipeline tested once on clean data. Fix: tests for empty input, duplicates, NULLs, reruns and a crash halfway.
- Results nobody can check. A dashboard number without a reconciliation. Fix: compare every output with a simple count or sum from the source, and print it.
- Too big too soon. Spark and Kafka for 10,000 rows. Fix: SQLite or DuckDB until a measured limit forces you to move.
Try it yourself
- Easy: Show each customer's most recent order (ID and date) using
ROW_NUMBER. - Medium: Write the reconciliation check every capstone needs: compare the total of
order_itemsfor delivered and shipped orders with the total of their payments, and printOKor the difference. - Hard: Write
manifest_for(df, name, seed): it returns a dictionary with the name, row count, column names, the seed and the first 16 characters of the SHA-256 of the DataFrame's CSV text. Test it on the delivered orders read withpd.read_sqland print it as JSON.
Answers
-- 1.
WITH ranked AS (
SELECT customer_id, order_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn
FROM orders
)
SELECT customer_id, order_id, order_date
FROM ranked
WHERE rn = 1
ORDER BY customer_id;+-------------+----------+------------+
| customer_id | order_id | order_date |
+-------------+----------+------------+
| 1 | 10 | 2026-04-19 |
| 2 | 12 | 2026-05-21 |
| 3 | 14 | 2026-06-09 |
| 4 | 5 | 2026-02-25 |
| 5 | 7 | 2026-03-15 |
| 6 | 13 | 2026-06-02 |
| 7 | 11 | 2026-05-06 |
+-------------+----------+------------+# 2. and 3.
import hashlib
import json
import pandas as pd
from sqlhelp import con
items, paid = con.execute("""
SELECT (SELECT SUM(oi.quantity * oi.unit_price)
FROM order_items AS oi JOIN orders AS o ON o.order_id = oi.order_id
WHERE o.status IN ('delivered', 'shipped')),
(SELECT SUM(p.amount)
FROM payments AS p JOIN orders AS o ON o.order_id = p.order_id
WHERE o.status IN ('delivered', 'shipped'))
""").fetchone()
print("OK" if items == paid else f"difference: {items - paid}")
def manifest_for(df, name, seed):
digest = hashlib.sha256(df.to_csv(index=False).encode()).hexdigest()[:16]
return {"name": name, "rows": len(df), "columns": list(df.columns), "seed": seed, "sha256_16": digest}
delivered = pd.read_sql("SELECT * FROM orders WHERE status = 'delivered' ORDER BY order_id", con)
print(json.dumps(manifest_for(delivered, "delivered_orders", seed=None)))OK
{"name": "delivered_orders", "rows": 11, "columns": ["order_id", "customer_id", "order_date", "status"], "seed": null, "sha256_16": "af73a7877a5c2153"}Summary
- Four capstones — cleaner, ETL pipeline, ML dataset builder, warehouse — turn the advanced tutorial into portfolio work.
- Portfolio-ready means reproducible, correct (reconciled and tested), safe, explained and measured.
- The cheat sheet collects the patterns: CTEs and windows, transactions and indexes, parameters and pandas, point-in-time features, JSON, full-text and vector search.
- The AI path continues with APIs and Prompt Engineering, then embeddings, vector databases and RAG — all standing on the data skills from this topic.
Next: this is the end of SQL and Data Handling for AI: Advanced. Take the course exam to check yourself, pick a capstone, and continue on the AI path with APIs and Prompt Engineering.