Unit 4.2 · about 3 h
258 / 324Aggregations, windows, nulls and point-in-time snapshots
Assume Programming's relational-sql-queries and transactions-isolation units. A consistent engine snapshot is not the same as a historically available feature set; this unit makes the two contracts explicit.
- implement
You can now
- Test aggregation and window frames against ties, nulls, empty inputs and cutoff boundaries.
- Construct feature rows using both event time and availability time without future leakage.
- Preserve an immutable, ordered query result and its provenance for a point-in-time dataset.
Covers
- Aggregations, windows, null behavior and boundary tests
- Point-in-time snapshots and feature availability
Study, in this order
3 steps
- PostgreSQL 18: Window FunctionsPostgreSQL - EXPLAIN and transaction isolation lab
Section 9.22, Table 9.67 and the paragraphs on peers, default frames and whole-partition aggregation
- Point-in-time joinsPoint-in-time joins
Point-in-time joins, including the TTL rule, then Retrieving features as of the event time
Depth · Read the semantics only. Do not install Feast; express event and availability predicates explicitly in PostgreSQL.
Focus on
GROUP BYcollapses rows; a window preserves them.COUNT(*)counts rows,COUNT(value)excludes nulls, andSUMover no non-null values returnsNULL.- An ordered window's default frame includes peers through the current peer group. Use an explicit
ROWSframe and a unique tie-breaker for a row-by-row prefix. - For an as-served feature, require
event_at <= cutoffandavailable_at <= cutoffbefore choosing the newest eligible row. Availability must mean when serving could actually use it. - An MVCC snapshot gives a consistent current extraction. Persist its ordered result, query, cutoff and content digest; a snapshot identifier alone cannot reproduce it indefinitely.
Reproduce closed-book
Use the section PostgreSQL setup. Create
feature(id int PRIMARY KEY, entity int, event_at int, available_at int, value int) and insert (1,1,8,9,10),(2,1,9,12,99). At cutoff 10, filter entity=1, event_at<=10 and available_at<=10, then ORDER BY event_at DESC, available_at DESC, id DESC LIMIT 1: get 10, not 99. Separately use VALUES (1,8,10),(2,8,20) as (id,event_at,value): SUM(value) OVER(ORDER BY event_at) returns 30,30, while ORDER BY event_at,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW returns 10,30. On one NULL::int row, COUNT(*),COUNT(value),SUM(value) is 1,0,NULL. Export the eligible rows with an explicit ORDER BY inside one read-only REPEATABLE READ transaction; record the SQL, cutoff and SHA-256 of the export. Recheck the saved bytes after changing the live table.Traps
- Ranking before filtering availability. A late newer row can win and then remove an entity whose older eligible feature should have been used.
- Treating event-time correctness as availability correctness. A later backfill can have an old event timestamp.
- Using a timestamp alone to break ties, or expecting
last_valuewith the default frame to return the partition's final value.
Practice
The section mini-project covers this: null, tie and exact-cutoff tests, late backfills, and an immutable point-in-time export with query and digest provenance.
Self-test
-
Why are both default-frame prefix sums 30 in the tie fixture?
Both rows are peers onevent_at. Each default frame includes the entire peer group. -
Does a row at
event_at=9,available_at=12belong to an as-served snapshot at 10?No. It occurred before the cutoff but was unavailable then. -
Does a stable MVCC snapshot prevent historical leakage?
No. It stabilizes which current row versions are read, not when their feature values became available historically.
Completion criterion
Stop when
The three fixtures match the stated results and the saved export remains verifiable after a live-table backfill.
Mark passed, then open 4.3 Only mark this when you can do it without notes.Leads to
- 5.6 Consumer lag, watermarks and state rebuild Carry availability and late-arrival rules into streaming state.
Resources for this unit
- CMU 15-445/645 - database and storage core Use Fall 2025 Lectures 1-9, 11-18 and 20-22 with their notes: relational model, SQL, storage, buffer pools, layouts, indexes, execution, planning, concurrency, MVCC, logging and recovery. Complete the matching SQL, Storage, Indexes and Filters, Transactions and Recovery homeworks before considering a BusTub project.
- PostgreSQL - EXPLAIN and transaction isolation lab Create one normalized schema, joins, an aggregation and a window query. Compare EXPLAIN ANALYZE before and after one justified index. In two sessions, reproduce one documented isolation anomaly and prevent it with a stated database or application invariant.
- Point-in-time joins Read Point-in-time joins and Retrieving features as of the event time. Separate event-time eligibility from actual feature availability; implement the predicates in SQL, not a Feast installation.
Also in this section
- Designing Data-Intensive Applications, 2nd edition - storage and transactions Read only the sections on data models and query languages, storage and retrieval, encoding and evolution, and transactions that answer a live design question from the CMU route. Write one comparison table covering layout, index, isolation and recovery trade-offs.
- Operating Systems: Three Easy Pieces - persistence and integrity Use Chapters 36, 39-45 to trace devices, filesystem structures, journaling, log-structured storage, flash and integrity. Apply the crash sequence to your own append-only store rather than repeating the SYS-1 file experiment.
- CMU 15-445 - one reduced BusTub project slice If a C++ database implementation is the chosen depth lane, complete Project 1 Buffer Pool Manager or Project 2 Database Index, not both during the first pass. Follow the course publication rules and keep code private. The blank-file append-only store remains the core checkpoint.
- CMU Lecture 19: Timestamp Ordering Concurrency Control Section 1 only, p. 1: timestamp order as a chosen serialization order. The section pool omits Lecture 19; this fills the timestamp-ordering vocabulary gap without assigning all of OCC.
- SQLite Write-Ahead Logging Sections 1 and 3 only: rollback journaling remains the default and WAL is an opt-in mode. Correct the dated SQLite statement in CMU Lecture 21; inspect the bundled engine without running concurrent WAL writers.