Break
Kickoff: three teardowns
Nano quiz ↗
press b or esc to carry on
M0

Course Logistics and Policies

5 figures
course-logistics

Four eras of data systems, compared on the same four questions

Four eras of data systems, compared on the same four questions A four-column grid. The columns are Banks and retail in the 1970s to 1980s, the web from 1995 to 2010, the cloud era from 2004 onwards, and AI agents from 2025 onwards, the last one filled blue as the era the course is built for. Four rows compare them: the products of each era, who is on the other end of the query, the hard question each era had to answer, and what the industry invested in during it. The middle row escalates from hundreds of clerks, to millions reading, to billions writing at human pace, to machines at machine pace. The investments row runs from transactions and the relational model, through replicas and NoSQL and MapReduce, to LSM trees and object storage and columnar scans, to vector indexes and KV caches and lineage and Iceberg, the table format that lets any engine read the same storage. PRODUCTS WHO IS ON THE OTHER END THE HARD QUESTION INVESTMENTS Banks and retail 1970s TO 1980s Ledgers, inventory, airline seats Hundreds of clerks Is it correct? Transactions, ACID, the relational model, locking, write-ahead logs The web 1995 TO 2010 Google, Amazon Millions reading Can everyone read it at once? Replicas, caches, NoSQL, inverted indexes, the cluster, MapReduce The cloud 2004 ONWARDS Gmail, YouTube, Instagram Billions writing, at human pace Can everyone write and store forever? LSM trees, sharding, consistent hashing, object storage, quorums, columnar scans AI agents 2025 ONWARDS Copilots, agents, assistants Machines, at machine pace Machines read and write nonstop. Is all of it correct, and what did it cost? Vector indexes, KV caches, eval sets, lineage, Iceberg, lakehouses Each era stacks on the one before it. This course is built for the column on the right.
Each era shifted the party issuing the query. CS145 targets the agent era.
Notes ↗
course-logistics · Before You Start

What you need on day one, and what helps a lot

What you need on day one, and what helps a lot Three panels. The first, with an ink top strip marking it as required, is Python and Colab: read Python and run a notebook, needed from day one. The next two, with grey strips marking them as helpful rather than required, are CS 161 Algorithms, giving sort and hash and complexity, and CS 111 Systems, giving filesystems and the memory hierarchy and scheduling. A closing line says CS145 runs those same concepts on data 100 to 1,000,000 times bigger, and points at the ten-question concept check that opens PSET 0. WHAT YOU NEED REQUIRED Python and Colab Read Python, run a notebook, open a Colab and execute a cell. df.head() → [10 rows] HELPS A LOT CS 161 · Algorithms Sort, hash, complexity. How an operation scales when the input grows. Modules 2 and 3 lean on it. HELPS A LOT CS 111 · Systems Filesystems, the memory hierarchy, scheduling. Where the bytes actually live. Module 2 leans on it. CS145 takes those same concepts and runs them on data 100x to 1,000,000x bigger. PSET 0 opens with ten questions on exactly these concepts. Start there, and scan ahead when one finds a gap.
The course leans on the concepts from CS161 and CS111 more than the coursework. Use PSET 0 to check where you stand.
Notes ↗
course-logistics · How It Works

One week in CS145, and the eleven weeks it repeats across

One week in CS145, and the eleven weeks it repeats across Two blocks in one picture. The top block is one week in three lanes, read top to bottom in the order you do them. LECTURES holds the two lectures, Tuesday and Thursday. PRACTICE holds one section band spanning Wednesday to Friday, marked one of three, running the Colab walkthrough. READING, tagged own time, holds the same page used in lecture with its three to five minute video, and the week's industry case study. A dotted rail down the left carries each lane into the next and continues into the second block. Between them, a line says section and the reading are the rehearsal and the PSET is the same material on your own, graded. The bottom block is the quarter: eleven week columns with the module each covers, M1 SQL through M5 Distributed, a Thanksgiving gap at week ten and industry lessons at week eleven, then two rows of real deadlines. Six PSETs: M0 Sep 25, M1 Oct 5, M2 Oct 12, M3 Oct 19, M4 Nov 9, M5 Dec 4, each landing the week after its module ends. Three project milestones: Project 1 Oct 16, the Project 2 proposal Oct 30, and Project 2 Nov 20. ONE WEEK TUE WED THU FRI 1 LECTURES 2 PRACTICE 3 READING OWN TIME Lecture new material, live Lecture new material, live Section · pick any one Colab walkthrough, hands on keyboard 1 OF 3 The lecture page, in Notes plus a 3 to 5 minute video The week's reading list required pages, plus a case study Section and the reading are your prep for the PSET. It is the same material, on your own, graded. ELEVEN WEEKS The module underneath changes. The rhythm stays the same. W1W2W3 W4W5W6 W7W8W9 W10W11 M1 M1 M2 · M3 M3 M4 M4 M4 M5 M5 break Industry PSETS PSET M0Sep 25 PSET M1Oct 5 PSET M2Oct 12 PSET M3Oct 19 PSET M4Nov 9 PSET M5Dec 4 PROJECTS Project 1Oct 16 P2 proposalOct 30 Project 2Nov 20
Lectures are Tuesday and Thursday. Practice is section: the same Colab walkthrough runs Wednesday, Thursday and Friday across three slots, and you attend one. Reading is the lecture page in Notes with its three to five minute video, plus the week's reading list: the required pages and a case study, in ten of the eleven weeks.
Notes ↗
course-logistics · How Grading Works

One hundred points: fifty fixed, and fifty you can earn two different ways

One hundred points: fifty fixed, and fifty you can earn two different ways A hundred-point scale. The first fifty points are fixed for everyone: Project 1 worth ten, PSets worth fifteen, Project 2 worth twenty-five. From the fifty-point mark the bar splits into two alternatives for the remaining fifty. Path 1 accrues fifteen points from nano quizzes and puts thirty-five on the final. Path 2 is a final worth fifty. A brace on the right marks that you are given whichever of the two scores higher, automatically. YOUR GRADE, OUT OF 100 0 50 100 FIXED FOR EVERYONE Project 1 10 PSets 15 Project 2 25 PATH 1 · ACCRUE POINTS FROM NANO QUIZZES Nano quizzes 15 Final 35 Final 50 PATH 2 · BET ON THE FINAL You get the higher score.
Everyone earns 50 fixed points: Project 1 for 10, PSets for 15, Project 2 for 25. The remaining 50 points come from either Path 1 or Path 2, and the course uses the higher score. Path 1 accrues 15 points from nano quizzes across the term and puts 35 on the final. Path 2 assigns all 50 to the final. Nano quizzes run live on Poll Everywhere, one in every lecture and one in every section.
Notes ↗
course-logistics · AI Policy: Learn Like You'll Work

Where AI is allowed in CS145, and what is graded in each place

Where AI is allowed in CS145, and what is graded in each place Two panels. On the left, the projects: every AI tool is allowed, credited, and the grade is on four things you do after the model answers, namely verify the answer, fix the semantics, cut the cost, and defend the design. On the right, the paper test: no AI, standing in for a whiteboard interview and measuring the systems thinking you carry yourself. SHIP WITH A I, OWN THE RESULT On the projects Use any tool you like. Credit it. GPT, Claude, Gemini, whatever you prefer. The model writing the first draft is assumed, so the grade is on what you do after it answers: Verify the answer is correct Fix the semantics Cut the cost Defend the design On the paper test No A I. This is the stand-in for the whiteboard job interview, so it measures the part that is now scarce: systems thinking you carry yourself. Query semantics, what the SQL does Performance debugging, why queries are slow Index strategy, when it helps or hurts Join algorithms, which one fits your data Tradeoffs: memory vs disk, latency vs throughput
The model drafting your first query is assumed. What gets graded is the four things you do after it answers, and the paper test checks that you can do them without it.
Notes ↗
M0

Why Memory Matters

CS145 is about how data persists over time.

2 figures
why-memory-matters · What this course teaches

Five you learn, then the modern systems you build

Five you learn, then the modern systems you build Top row: the five capability modules you learn, left to right, one machine to many. M1 SQL (blue): a query in, rows out; runs Postgres and BigQuery. M2 Storage (orange): a columnar cylinder over a disk; runs Chrome and Uber. M3 Indexing (emerald): a B+Tree on the cylinder, one billion rows to milliseconds; runs Spotify and OpenAI. M4 Transactions (violet): a lock on the cylinder, two users converging; runs Stripe and GitHub. M5 Distributed (pink): three shard cylinders with replication arrows; runs Netflix and Kafka. A green arrow leads down to the payoff: learn these five, build any new system, correct and scalable. Below, the destination panel, M6 Modern systems (slate): a database inside a phone, an AI agent, and a server stack, the systems running now, and your next app, agent, or system. Color key: each module is colored for consistency; the cylinder is the same throughout because the primitive does not change, only what surrounds it. Every system is the same few ideas. You can build new ones. The five you learn, then the modern systems you build with them. ONE MACHINE → MANY MACHINES M1 · SQL SELECT genre, COUNT(*) FROM listens GROUP BY ... Declarative Postgres · BigQuery M2 · Storage disk pages On disk Chrome · Uber M3 · Indexing 1B rows → ms Spotify · OpenAI M4 · Transactions all-or-nothing Stripe · GitHub M5 · Distributed shard 1 · shard 2 · shard 3 Many machines Netflix · Kafka Learn these five. Build any new system: correct and scalable. phone agent server M6 · Modern systems: the ones running now Build your next { app, agent, system }
Each module adds one capability, and each one supports the next scale.
Notes ↗
why-memory-matters · Where we start: three teardowns

Three teardowns, one primitive

Three teardowns, one primitive Three blue cards left to right: 0.1 small analytics, Pandas and Polars on a laptop, fast until it runs out of about sixteen gigabytes of RAM, the on-ramp; 0.2 agent memory, no memory then local then global, from one user to eight hundred million, the whole course in one example; and 0.3 a big service, UberEats with one database, three sides and the offline edge, billions of orders planet-wide. A grey scale axis underneath runs from one machine to many machines. A colour key notes that blue marks a teardown, the same database at growing scale. Three teardowns, one primitive The same SQL database, three settings. This course is the bridge. 0.1 · Small analytics Pandas and Polars on a laptop. Fast, until it runs out of RAM. ~16 GB on a laptop the on-ramp 0.2 · Agent memory No memory, then local, then global. 1 user → 800 M users the whole course in one example 0.3 · A big service UberEats: one database, three sides, plus the edge. billions of orders, planet-wide planet scale and the edge one machine many machines Color key  blue = a teardown, the same database at growing scale
Three environments run the same database, from one machine to many. Modules 1 through 6 build it.
Notes ↗
M0

Pandas: Deconstructing Small Analytics

Pandas and Polars load your whole dataset into your machine's memory and work on it there.

3 figures
case-study-pandas

What a DataFrame actually is

What a DataFrame actually is On the left, one file sits on disk, listens.csv, written row after row. An arrow labelled read into memory carries it to the right, where it becomes a DataFrame: a table with named, typed columns user_id, song_id and rating, and rows of values. Below, the same DataFrame is shown the way it is really held, split into one contiguous typed array per column. A closing line notes that every column of every row is in memory at once, which is why it is fast on small data and why it fails on big data. What a DataFrame actually is One file, read into memory, then held one column at a time. ON DISK listens.csv a CSV, or a Parquet file read IN MEMORY · A DataFrame user_id song_id rating int64 int64 int64 1 12 5 1 45 4 Held column by column Each column is one contiguous typed array, which is why summing one column is fast. user_id 1 1 song_id 12 45 rating 5 4 Every column of every row is in memory at once. That is why it is fast on small data, and why it fails on big data.
A CSV on disk or a Parquet file reads into a DataFrame with named, typed columns. The system stores each column as one contiguous array of a single type, which makes column aggregation fast. The DataFrame keeps every column of every row in memory at once, so it runs fast on small data and fails when the data exceeds memory.
Notes ↗
case-study-pandas · A familiar example

Pandas: the whole dataset has to fit in RAM

Pandas: the whole dataset has to fit in RAM A blue-stripped box is the single machine, RAM about 16 gigabytes, holding a DataFrame that is grouped, sorted, and joined entirely in memory; grey shows the de-emphasized CSV or Parquet on disk that is read in. On the right, the red-stripped box is the failure state: when data exceeds RAM the program crashes or thrashes. A visible color key runs along the bottom. Color key: blue is the one machine in focus, red is the out-of-RAM failure, grey is out of focus. Pandas: the whole dataset has to fit in RAM One process, one machine. Everything lives in memory, until it does not fit. CSV / Parquet on disk read ONE MACHINE · RAM ≈ 16 GB DataFrame every row, one process, in memory group / sort / join all in RAM result handed back fast RAM limit data > RAM: crash or thrash Color key  blue = the one machine in focus  ·  red = out of RAM, the failure  ·  grey = out of focus
Group, sort, and join all execute in memory in one process, which is fast while the dataset fits. Past that, the process does not slow down. It crashes, because nothing runs the same computation from disk.
Notes ↗
case-study-pandas · Pandas, Polars, and a database

How far each engine gets on a data-size axis, and where one machine ends

How far each engine gets on a data-size axis, and where one machine ends A horizontal data-size axis running from one gigabyte to one petabyte, each tick ten times the last. Landmarks along it: the small Spotify we built at a few gigabytes, a laptop's RAM at about sixteen gigabytes, one big server's RAM at hundreds of gigabytes, one big server's disk at tens of terabytes, and the real Spotify out past the end. A dashed boundary near the right divides the region where one machine is enough, tinted blue, from the region that needs many machines, tinted green. Four bars show how far each engine reaches. Pandas, writing DataFrame code, ends at a hard red wall while the data is still smaller than the laptop's RAM, because it copies at every step. Polars, also DataFrame code, runs solid to about one terabyte and fades out through tens of terabytes. A distributed database, also SQL, runs the whole axis and continues past it. Underneath, the tradeoff: one machine runs on hardware you already own, many machines needs a cluster you rent. Same query. The data keeps growing. How far each one gets before it needs another machine. Each tick is ten times more data. One machine is enough Needs many machines small Spotify laptop RAM one server's RAM one server's disk the real Spotify 1 GB 10 GB 100 GB 1 TB 10 TB 100 TB 1 PB Pandas DataFrame code out of RAM Polars DataFrame code Distributed database SQL Hardware you already own A cluster you rent
Each tick is ten times the last. Pandas copies the data at each step and holds every row in memory, so it hits a wall while the file is still several times smaller than the laptop's RAM. Polars keeps almost the same API but builds a lazy plan and streams from disk on every core, so it fades instead of crashing rather than stopping at a fixed size. Where exactly it gives out depends on the query, which is why its bar is not drawn with an end. Only the distributed database crosses the line, running the query where the data lives. Everything left of that line runs on hardware you already own; everything right of it is a cluster you rent.
Notes ↗
M0

Claude & OpenAI: Deconstructing Agent Memory

An AI coding agent with no memory hallucinates.

6 figures
case-study-agent-memory

Real facts: store and retrieve, local then global

Real facts: store and retrieve, local then global Three panels left to right. Panel one, red: a grey de-emphasized model circle with only a context window; it hallucinates. Panel two, green: the same model plus a local database, SQLite running claude-mem, a file on disk on one machine, queried with SQL; it looks the answer up. Panel three, green: the same model plus the same database scaled out, ChatGPT's user data, Postgres on disk with read replicas, also queried with SQL, one private memory per user across 800 million users. The model talks to each database using SQL. Models keep upgrading and databases keep scaling; the database is the core infrastructure that holds their memory and context, and that is what is in focus. Color key: red is no memory and the ephemeral context window, grey is the model which is a separate topic out of focus, green is persistent memory. Real facts: store and retrieve, local then global Models keep upgrading and databases keep scaling. The database holds their memory and context. 1 · Model alone Model context window Short-term working memory. Wiped when the session ends. ✗ Hallucinates a function 2 · + Local memory Model SQL SQLite · claude-mem On disk, one machine. Survives every session. ✓ Looks it up · per laptop 3 · + Global memory Model SQL Postgres ChatGPT's user database. On disk, many machines · × 800M ✓ One per user · ×800M RAM forgets · disk remembers · one machine, then many Color key  red = no memory, the ephemeral context window  ·  grey = the model, a separate topic, out of focus  ·  green = persistent memory
Two steps: add a database so the model (grey) can store and retrieve real facts instead of inventing them, then scale that database across machines for one memory per user across millions of users. Local memory maps to Modules 1 to 4. Global memory maps to Modules 5 and 6.
Notes ↗
case-study-agent-memory · No memory, and the hallucination

A plausible answer is still wrong

A plausible answer is still wrong On Tuesday you ask the agent to extend the auth module you built with it on Monday. On the left, with no memory to consult, the agent predicts and calls a function named verifyJWTWithScope, a name that sounds exactly right. On the right, your codebase contains no such function; it never existed, and nothing errored until the code ran. A closing line notes that the agent looked nothing up, it predicted, and a plausible wrong answer is the hardest kind to catch. A plausible answer is still wrong With nothing to look up, the agent predicts. Tuesday: "extend the auth module we built on Monday." THE AGENT GUESSES Monday is gone, so it predicts a name. verifyJWTWithScope() A name that sounds exactly right. IN YOUR CODEBASE Every function you actually wrote: no such function It never existed anywhere. It looked nothing up. It predicted. A confident wrong answer is the hardest kind to catch, and the fix is having somewhere to look.
Monday produced a working module. Tuesday starts without the Monday context. The agent generates verifyJWTWithScope() from patterns in its training distribution, and the name reads like real code. A persistent place to store Monday's decisions gives Tuesday something to query.
Notes ↗
case-study-agent-memory · The context window is the agent's working-memory limit

The context window is working memory

The context window is working memory Three sessions run left to right. In session one the context window fills with messages until it is full, and the oldest message falls out of the top, greyed and struck through, because the window holds only so much. Sessions two and three start completely empty, because nothing carries over when a session ends. Underneath all three sessions runs one continuous database bar, still holding everything. The closing line notes that working memory is bounded and temporary, while durable memory is a database. The context window is working memory Bounded, and gone when the session ends. CONTEXT working memory SESSION 1 Full. It holds 200K to 1M tokens. A real project runs to tens of millions. SESSION 2 empty SESSION 3 empty Every new session starts with nothing. Monday's decisions are gone by Tuesday. DATABASE durable One database, underneath all three sessions, still holding everything. Working memory is bounded and temporary. Durable memory is a database.
The context window has two limits. It stays bounded at roughly 200,000 to 1,000,000 tokens, while the project state can reach tens of millions, so older content falls out. It stays temporary because each session opens empty. A database persists across both limits.
Notes ↗
case-study-agent-memory · Local memory: a database on your machine

Local memory: write and read are just SQL

Local memory: write and read are just SQL A grey, de-emphasized model on the left (an LLM plus context extraction, for example pulling function signatures from code, a separate topic that is out of focus) talks to one green embedded database on your machine using SQL. A top arrow is SQL write, the model into the database. A bottom arrow is SQL read, the database back out through a small rerank box and into the model, a closed loop. The green database, claude-mem, is the focus: it holds two cylinders, SQLite with FTS5 for keyword and structured lookup, what you said, and a sqlite-vec vector index for semantic lookup, what you meant. Color key: grey is the model and your machine, a separate topic out of focus; green is the persistent embedded database, what we care about. Local memory: write and read are just SQL claude-mem on your machine: SQLite for what you said, vectors for what you meant. Model LLM + context extraction e.g. code → signatures SQL write SQL read rerank YOUR MACHINE One embedded database · claude-mem SQLite · FTS5 keyword · what you said Vector · sqlite-vec semantic · what you meant Color key  grey = the model and your machine, out of focus  ·  green = the persistent database
Local memory uses one embedded database file. The model writes observations with SQL. Retrieval reads rows back through a rerank step. SQLite stores a keyword index with FTS5 and a vector index with sqlite-vec. The deployment uses one file and no server.
Notes ↗
case-study-agent-memory · Global memory: the same need, at scale

Global memory: one Postgres primary, many read replicas, 800 million users

Global memory: one Postgres primary, many read replicas, 800 million users On the left, two grey de-emphasized boxes that are a separate topic out of focus: 800 million ChatGPT users, and the GPT-5 model, OpenAI's LLM. They send SQL writes to one green Postgres primary inside a green shared-database region; the primary streams replication to about fifty read replicas that serve the SQL reads. Same SQL write and SQL read pattern as the local database, one machine versus many. Color key: grey is the users and the GPT-5 model, a separate topic out of focus; green is the shared Postgres database, what we care about. Global memory: one logical database, many machines One primary takes every write; ~50 replicas fan the reads out to 800 million users. ChatGPT users × 800M writes and reads GPT-5 model OpenAI's LLM a separate topic Shared database · Postgres / SQL Primary takes all writes ~50 read replicas serve all reads replicate SQL write SQL read · fanned out from ~50 replicas Color key  grey = the users and the GPT-5 model, out of focus  ·  green = the shared Postgres database
Global memory keeps the SQL interface (grey) and changes only the deployment (green). One Postgres primary accepts every write. It streams replication to about fifty read replicas, which serve 800 million users.
Notes ↗
case-study-agent-memory · Going deeper

Three ways to rank the same rows

Three ways to rank the same rows Three cards side by side, each a different ranking signal over the same observations table. The first is BM25, keyword matching, which catches the words you actually typed: a search for auth returns rows containing auth. The second is vector similarity, which embeds the question and finds rows pointing the same way, so a search for login also finds auth. The third is PageRank over the code graph, an importance signal: a file that forty others import outranks one that nothing points at. A closing line notes all three are just an ORDER BY, so mixing them is a one-line query edit. Three ways to rank the same rows Each one catches something the other two miss. 1 · WHAT YOU SAID BM25 Keyword match on the text, the words you actually typed. search "auth" rows containing auth 2 · WHAT YOU MEANT vector similarity Embeds the question, finds rows pointing the same way. search "login" also finds auth 3 · WHAT MATTERS NOW PageRank An importance signal over your code: what points at it. auth.ts 40 files import it: ranks high All three are just an ORDER BY over the same table. Mixing them is a one-line query edit, which is the point of keeping memory in SQL.
One set of rows produces three orderings. BM25 ranks by term matches. Vector similarity ranks by embedding distance, so "login" can match "auth." PageRank ranks by importance in a code graph, so a file with forty importers outranks a file with none. Each strategy produces an ORDER BY, so a query can mix signals by editing the ordering expression.
Notes ↗
M0

UberEats: Deconstructing a Big Service

UberEats keeps diners, drivers, and restaurants in a single relational database so each order updates all three records atomically, while the same SQL engine, embedded, runs offline on the driver's phone.

3 figures
case-study-ubereats

UberEats: one database, three sides

UberEats: one database, three sides Three red-stripped client apps, Diner, Driver, and Restaurant, each hold volatile in-memory state and all feed one green-stripped relational database, the persistent shared truth, where placing an order charges the diner, assigns one driver, and decrements inventory atomically. A box with an ink bottom strip and an ink target mark is the takeaway: two drivers can never claim the same order. Color key: red is volatile or ephemeral client state, green is the persistent shared database, ink is the takeaway. UberEats: one database, three sides A burrito order has three sides. They all have to agree the moment it is placed. Diner app volatile in-memory state Driver app volatile in-memory state Restaurant volatile in-memory state Relational database · the persistent shared truth TRANSACTION: place order → charge diner, assign one driver, decrement inventory. All of it, or none of it. ⊙ Two drivers can never claim the same order Color key  red = volatile client state  ·  green = the persistent shared database  ·  ink bottom strip = the takeaway
Three apps write volatile client state. One relational database stores shared state. One order runs as one atomic transaction that charges the diner, assigns one driver, and decrements inventory.
Notes ↗
case-study-ubereats · One order, three sides

One order. Three records. All or none.

One order. Three records. All or none. Placing one order sits at the top. Below it, three records change inside a single transaction: the diner's balance is charged, exactly one driver is assigned, and the restaurant's inventory is decremented. Two outcomes follow. On commit, all three apply together. On abort, none of them do, and the greyed struck-through records show that nothing partial is left behind. A closing line notes that the database holds this guarantee even if the server dies mid-write, with no coordination code of your own. One order. Three records. All or none. Atomicity, the A in ACID. One diner places one order ONE TRANSACTION DINER balance charged DRIVER exactly one assigned RESTAURANT inventory decremented COMMIT All three apply together. ANY ONE FAILS None of them do. The database holds that even if the server dies mid-write. You write no coordination code.
Atomicity in ACID. One transaction updates three records so the system never stores a charged diner with no order, and never stores one order with two drivers. The database commits or aborts even if the server dies between writes, so the application does not implement its own coordination protocol.
Notes ↗
case-study-ubereats · The disconnected edge

The disconnected edge: a database on the phone

The disconnected edge: a database on the phone A blue-outlined phone, the system in focus, runs the driver app over an embedded SQLite database and stays transactional with no signal. On reconnect, a green arrow pushes local changes to a green cloud database, the shared source of truth, and a grey arrow reconciles back. Color key: blue is the phone in focus, green is the cloud source of truth. The disconnected edge: a database on the phone Same SQL, offline. The driver app does not stall waiting for the network. DRIVER'S PHONE · offline Driver app SQLite, embedded transactional even if the phone dies on reconnect: push local changes reconcile against the truth Cloud database the shared source of truth Color key  blue = the phone, the system in focus  ·  green = the cloud source of truth
The driver app stores state in an embedded SQLite database and executes transactions offline. The app syncs local changes to the cloud database on reconnect and reconciles cloud state back to the device.
Notes ↗
M0

Projects

4 figures
projects

The project path: Project 1, then one of two Project 2 options

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
Notes ↗
projects · What you'll build

Project 1 architecture: query a public dataset with SQL to answer questions

Project 1 architecture: query a public dataset with SQL to answer questions Left to right: a read-only public dataset in BigQuery (three or more tables such as posts, users, and comments, at least 500 MB, grey) is queried by the SQL you write in a Colab notebook (a code card shows a CTE with a RANK window function and a join). It produces a result table of decision-worthy answers, one row starred as the insight. Below, you hand-trace two paper debug tables to verify how the SQL executes. Blue is what you build; grey is the read-only data you query. The big picture: turn a 500 MB dataset into a decision A read-only public dataset, real SQL, one insight worth acting on. THE DATA read-only, in BigQuery postsid · user_id · score 41 · u12 · 8842 · u07 · 15 usersid · name · country u12 · Ada · USu07 · Lin · SG commentsid · post_id · body 91 · 41 · "nice" 3+ tables · ≥ 500 MB StackOverflow, NYC Taxi, GitHub… query YOUR SQL 10+ queries, written in a Colab notebook top_posters.sql WITH ranked AS ( SELECT user_id, COUNT(*) AS posts, RANK() OVER (ORDER BY COUNT(*) DESC) rk FROM posts GROUP BY user_id ) SELECT u.name, r.posts, r.rk FROM ranked r JOIN users u USING(user_id) WHERE r.rk <= 10; 3 JOINs 2 CTEs 2 correlated subqueries 3 window fns answer THE PAYOFF "Who are our power users?" namepostsrk Ada4,2101 ★ Lin3,8802 Ravi3,1023 ★ Insert your insight: why are they ranked #1? (+5) 3+ decision-worthy answers, stated in English, backed by SQL VERIFY ON PAPER postsrktie? 42101no Hand-trace how RANK breaks ties, and a correlated subquery, row by row. 2 debug tables blue = what you build · grey = the read-only data you query
Notes ↗
projects · What you'll build

Project 2 Science architecture: a production data and ML pipeline

Project 2 Science architecture: a production data and ML pipeline Left to right: two data sources, Wikipedia (about 22 GB, new in Project 2, violet) and your Project 1 warehouse (grey). A Spark ELT job funnels the 22 GB down to a filtered 1 GB slice; a partitioned, clustered BigQuery warehouse stores it; vector embeddings turn text into vectors so a nearest-neighbor scatter finds meaning; and a BigQuery ML model separates two classes with a decision boundary and makes a calibrated prediction (0.87) tied to a decision. Violet marks what is new in Project 2 (Project 1 was just SQL): Wikipedia, Spark ELT, embeddings, and the ML model. Grey is the Project 1 data you start from. Below the pipeline, a gold crown box marks the Tier 100 capstone: the Analyst's Agent, a governed text-to-SQL agent built on this stack, taking a bare model from 21% to about 95% accurate. The big picture: the data + ML stack a team ships before lunch Ingest outside data, embed it, model it, ship a calibrated decision. WIKIPEDIA NEW ~22 GB6.7M articles YOUR P1 WAREHOUSE ≥ 500 MB SPARK ELT NEW 22 GB in filter 1 GB slice Filter to your domain, load it into BigQuery. PySpark on Colab WAREHOUSE partition by date Partitioned + clustered so scans stay cheap. fact + wiki layers EMBEDDINGS NEW "token refresh" → [.12 -.4 .9] near in space = near in meaning Find neighbors keyword joins miss. ML.GENERATE_EMBEDDING BIGQUERY ML NEW 0.87 stayact a boundary + a calibrated probability Predict something compelling you’ll tell your team. CREATE MODEL violet = new in Project 2 (Project 1 was just SQL) · grey = the data you start from TIER 100 · THE CROWN The Analyst's Agent · a governed text-to-SQL agent
Notes ↗
projects · What you'll build

Project 2 Systems architecture: a hybrid search engine for AI-agent memory

Project 2 Systems architecture: a hybrid search engine for AI-agent memory Two data sources (about 2 million code functions from CodeSearchNet and about 8.8 million web passages from MS MARCO, grey) are indexed two ways you build from scratch (green): an inverted index with BM25 shows posting lists mapping each term to documents (keyword search, "what you said"); LSH hashes vectors into buckets so similar vectors share a bucket (semantic search, "what you meant"). A hybrid layer fuses the two ranked lists with RRF and your own fusion, a query planner picks the plan, and the result is memory an AI coding agent queries in a terminal before it answers. A benchmark scoreboard races your engine for parity against SQLite FTS5 (BM25) and sqlite-vec (vectors). Green is what you build; grey is the data you index. A gold crown box marks the Tier 100 capstone: web-scale memory, streaming retrieval over 22 GB of Wikipedia in a 1 GB working set, still keeping pace with the tools in a billion phones. The big picture: a search engine that gives an AI agent memory Two data sources, two kinds of search, fused, then raced against production tools. CODESEARCHNET ~2M functions6 languages MS MARCO ~8.8M passagesweb + Bing labels INVERTED INDEX + BM25 keyword search · "what you said" "cache"→ d3:2 d7:1 d9:3 "index"→ d1:1 d3:4 term → posting list (doc:tf), scored by BM25 LSH VECTORS semantic search · "what you meant" query b0 b1 b2 similar vectors share a bucket HYBRID RETRIEVAL BM25 d7 d3 d9 LSH d3 d5 d7 FUSED d3 d7 d5 → RRF + your own fusion. a query planner picks the plan AI CODING AGENT $ recall("session") → token = lifetime Look it up before you make it up. THE BAR race the production tools for parity, same data, same eval metric your engine reference tool parity R@5 keyword0.71FTS5 0.72✓ R@5 vector0.68sqlite-vec 0.69✓ + R@1, MRR, p95 latency, peak memory green = what you build · grey = the data you index TIER 100 · THE CROWN Web-scale memory · 22 GB Wikipedia, 1 GB working set
Notes ↗
M0

CS145: Your SQL Journey

3 figures
sql-metro

CS145 career journey map

CS145 career journey map A left-to-right map. The two blue boxes are CS145 itself, the content in focus: the core curriculum with Project 1, then a Project 2 choice of the Science path or the Systems path, NanoMem. The grey box is what you keep learning beyond the course, out of focus here. The green box is where it leads, the careers the path opens. Color key: blue is CS145 content, green is where it leads, grey is beyond CS145. CS145: Your Data Journey Master the core, pick a technical path, build a career. CS 145 CS145 core curriculum Core: SQL (+ Pandas, NoSQL) the query languages for data Project 1: SQL Mastery BigQuery, tens of GB Core: Systems Storage, indexes, query opt Core: Transactions ACID, then distributed: replication, sharding, CAP Project 2: pick one Science path Enrich, visualize, train an ML model Systems path: NanoMem Build a search engine for AI agent memory Beyond CS145 keep learning Analyst / Scientist skills Engineer skills Where it leads Data Analyst Data Scientist Backend Eng Platform Eng Founder / CTO Color key  blue = CS145 content  ·  green = where it leads  ·  grey = beyond CS145
One map of the CS145 sequence: core database skills, then a project, then a project path, then typical industry roles that use that path.
Notes ↗
sql-metro · You'll read, debug, and write queries like this

One query, three parts

One query, three parts The power-users query broken into the three parts it is built from, each feeding the next. The first CTE, user_stats, rolls every listen up per user with counts of distinct songs and genres and a sum of minutes, grouped by user. The second CTE, power_users, keeps only users spanning at least three genres and ranks them into deciles by total minutes. The final SELECT joins back to user names and returns only the top decile. A closing line notes that an analyst, a scientist, an engineer and a founder all read the same query. One query, three parts The shape you will read, debug, and write. user_stats roll every listen up, per user COUNT(DISTINCT song_id), SUM(...) GROUP BY user_id power_users keep 3+ genres, then rank by minutes WHERE unique_genres >= 3 NTILE(10) OVER (ORDER BY ...) final SELECT join back to names, take the top JOIN users u ON p.user_id = ... WHERE p.decile = 1 Analyst, scientist, engineer, founder. Same language, same query.
A production SQL pattern: two CTEs compute per-user aggregates and a decile rank, then an outer SELECT joins names and filters to the top decile.
Notes ↗
sql-metro · Where This Journey Leads: Real-World Architectures

Spotify: one play event from click to dashboard

Spotify: one play event from click to dashboard One play event traced across three panels, revealed in the order they matter. First OLTP, the real-time write path that takes the click. Then OLAP at the far end, the columnar analytics store, and the dashboards it powers. Last the ELT hop in the middle, the bridge that carries rows from one to the other. Originally: one play event traced left to right across three panels. The blue panel is OLTP, the real-time write path that takes the click. The grey panel is the ELT batch hop: extract from OLTP, load raw rows into the warehouse, transform them in place. The green panel is OLAP, the analytics store that serves dashboards. Each panel lists the algorithms this course teaches. Color key: blue is OLTP the write path, grey is the ELT hop, green is OLAP analytics. Spotify: one play event, click to dashboard OLTP writes it. ELT moves it. OLAP serves it. One SQL backbone, three roles. OLTP: real-time writes User app, then API click "Anti-Hero", 100K requests / sec LSM write path MemTable, then flush to SSTable L0, L1, L2; background compaction INSERT INTO listens row store, under 10 ms, write-optimized, handles bursts real time ELT: the batch hop Extract + load Spark bulk-loads raw rows into the warehouse Transform in place aggregate at scale, the algorithms this course teaches batch load OLAP: analytics Columnar store star schema, compressed fact partitions Aggregation queries scan only the columns a query needs, read-optimized Dashboards Tableau, Snowflake, the warehouse every analyst reads Color key  blue = OLTP, the write path  ·  grey = ELT, the hop  ·  green = OLAP, analytics
One play event through OLTP for transactional writes, ELT for batch movement and transformation, and OLAP for analytics queries and dashboards.
Notes ↗
end of lecture 1~33 min of figures
M0 Kickoff: three teardowns 1 of 26
Notes ↗ Video ▶ Why ◎ Schedule ↗ □ keys

M0 Kickoff: three teardowns

Pandas on a laptop, agent memory, UberEats at planet scale · 26 figures · ~33 min · 1 lectures at this pace