CS 145 · Modern Data Systems

Projects

The project path: Project 1, then one of two Project 2 options Everyone does Project 1 (BigQuery's First Heist, solo, Sep 25 to Oct 16, 16 to 20 hours, blue) and builds decision-worthy SQL and scalable cloud analytics, then picks one Project 2 (Oct 16 to Nov 20, pairs by default with solo optional, about 40 hours per person for a Tier 93 project (about 8 hours a week over five weeks), with the Tier 100 capstone adding about 8 more): Science (BigQuery Part Deux, violet, Spark ELT, vector embeddings, and a BigQuery ML model, a warehouse plus model to headline a portfolio) or Systems (NanoMem, green, a search engine giving AI coding agents persistent memory that races real production tools). Each card lists focus, skills you build, and the portfolio story. Blue is the SQL foundation, violet is data science, green is systems. PROJECT 1 · SOLO Sep 25 – Oct 16 BigQuery's First Heist FOCUS Write decision-worthy SQL on a 500 MB+ public dataset. SKILLS YOU BUILD CTEs, subqueries, window functions, debug tables, cloud analytics PORTFOLIO 3 decision-worthy SQL answers on real cloud-scale data. 16–20 hrs THEN PICK ONE P2 · SCIENCE Oct 16 – Nov 20 BigQuery Part Deux FOCUS Enrich with new data, visualize, and train a BigQuery ML model. SKILLS YOU BUILD Spark ELT, vector embeddings, BigQuery ML, query-plan analysis PORTFOLIO A warehouse + ML model to headline your portfolio. ≈40 hrs/person (Tier 93) or solo OR P2 · SYSTEMS Oct 16 – Nov 20 NanoMem FOCUS Build a search engine that gives AI coding agents persistent memory. SKILLS YOU BUILD Inverted index + BM25, LSH vectors, hybrid retrieval, query planner PORTFOLIO A memory engine that races real production tools. ≈40 hrs/person (Tier 93) or solo

Everyone does Project 1, then picks one Project 2 (Science or Systems). Same credit either way; pick by interest, not difficulty.

What you'll build

The big picture for each project. Click a title for the full spec.

Project 1 · BigQuery's First Heist →

Project 1 architecture: a read-only public BigQuery dataset queried with SQL (CTEs, a RANK window function, joins) into decision-worthy answers, verified with paper debug tables.

The whole project in one flow: pick a big public dataset, ask three decision-worthy questions, answer them with real SQL (joins, CTEs, subqueries, window functions), and hand-trace two debug tables to prove you know how it runs.

Project 2 · Science · BigQuery Part Deux →

Project 2 Science architecture: Wikipedia (new) and your Project 1 warehouse feed a Spark ELT funnel, a partitioned BigQuery warehouse, vector embeddings, and a BigQuery ML model with a decision boundary and a calibrated 0.87 prediction. A gold crown box marks the Tier 100 capstone: the Analyst's Agent, a governed text-to-SQL agent on this stack.

Project 1 was SQL on one warehouse. Project 2 adds the three layers a real data team owns: a Spark job to ingest outside data, embeddings for semantic search, and a BigQuery ML model that makes a calibrated business call.

Project 2 · Systems · NanoMem →

Project 2 Systems architecture: two datasets indexed by BM25 posting lists and LSH buckets, fused by hybrid retrieval and a query planner into memory an AI coding agent queries, benchmarked for parity against SQLite FTS5 and sqlite-vec. A gold crown box marks the Tier 100 capstone: web-scale memory, 22 GB of Wikipedia in a 1 GB working set, still keeping pace with the tools in a billion phones.

You build the whole engine from scratch: an inverted index with BM25 for keywords, LSH over vectors for meaning, a hybrid layer that fuses them, and a planner that picks the plan, then race it for parity against the production tools shipping in a billion phones.

AI Policy

The full AI Policy lives on the course logistics page. Read it here.