VFlash: Fast, Accurate Visual Planning via Flash Primitive Decoding
u_Unrolled
D1: CS & Systems Foundations · SQL for ML — Point-in-Time Training Sets, A/B Test Analysis and Vector Search in Postgres
Phase 1/Domain 1/Lesson 28

SQL for ML — Point-in-Time Training Sets, A/B Test Analysis and Vector Search in Postgres

15 min read·Foundational·Free lesson
a timeline per student with feature events and a label date; a point-in-time join takes only events before each prediction time (an "as of" join), while a naive join leaks future events; beside it, an A/B results table computed in SQL with a confidence interval, and a Postgres table with an embedding column queried by vector distance and keyword match.
Diagram— a timeline per student with feature events and a label date; a point-in-time join takes only events before each prediction time (an "as of" join), while a naive join leaks future events; beside it, an A/B results table computed in SQL with a confidence interval, and a Postgres table with an embedding column queried by vector distance and keyword match.

The incident. The dropout model's top feature was "lessons completed in total". For students in the training set, that count included lessons completed after the date the model was supposed to be predicting from, so students who stayed obviously had more lessons. In production, the feature only had the past, and the model's edge vanished.

You already know: window functions and CTEs (previous lessons); data leakage and feature stores (D3 Feature engineering); confidence intervals and A/B tests (D2); embeddings and vector search (D8).


1. Point-in-time correct training sets

The question. How do you build features that only use information available at prediction time?

Start from nothing. Each training example has a prediction time (the moment the model would have been asked) and a label from after it. Every feature must be computed from events strictly before that time: an as-of join. In SQL, join events to the label table with e.ts < l.prediction_ts (and often >= l.prediction_ts - window) and aggregate. Feature stores implement exactly this; writing it once by hand makes the failure mode unforgettable.

python
import random, sqlite3
import numpy as np

random.seed(0)
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE events (student INT, ts INT)")
db.execute("CREATE TABLE labels (student INT, prediction_ts INT, dropped INT)")
events, labels = [], []
for s in range(2000):
    engagement = random.random()
    dropped = int(random.random() < 0.6 - 0.4 * engagement)
    labels.append((s, 30, dropped))
    for day in range(60):
        active_after = day >= 30 and dropped
        if not active_after and random.random() < 0.1 + 0.3 * engagement:
            events.append((s, day))
db.executemany("INSERT INTO events VALUES (?, ?)", events)
db.executemany("INSERT INTO labels VALUES (?, ?, ?)", labels)

leaky = """SELECT l.dropped, COUNT(e.ts) FROM labels l LEFT JOIN events e ON e.student = l.student GROUP BY l.student"""
correct = """SELECT l.dropped, COUNT(e.ts) FROM labels l LEFT JOIN events e
             ON e.student = l.student AND e.ts < l.prediction_ts AND e.ts >= l.prediction_ts - 30 GROUP BY l.student"""

def auc(rows):
    y = np.array([r[0] for r in rows]); x = -np.array([r[1] for r in rows], float)   # fewer events -> more likely to drop
    pos, neg = x[y == 1], x[y == 0]
    return np.mean([(p > neg).mean() + 0.5 * (p == neg).mean() for p in pos])

print(f"AUC of 'event count' feature: leaky join {auc(db.execute(leaky).fetchall()):.2f}, point-in-time join {auc(db.execute(correct).fetchall()):.2f}")

The leaky feature counts activity after day 30, which the label itself causes, so it predicts dropout almost perfectly offline; the point-in-time feature reflects what the model can actually know. Make the prediction timestamp an explicit column in every training table, and make every feature query take it as a parameter.

On these 2,000 simulated students, the same "event count" feature scores an AUC of 0.90 with the leaky join and 0.60 with the point-in-time join. The 0.90 is the number that would have shipped, and production would have delivered something close to 0.60.

a student's event timeline over 60 days with the prediction time at day 30; the point-in-time window covers the 30 days before, while the leaky join also counts events after day 30 that the label causes; a bar chart shows AUC 0.90 for the leaky feature vs 0.60 for the point-in-time feature, beside the SQL shape of the fix.
Diagram— a student's event timeline over 60 days with the prediction time at day 30; the point-in-time window covers the 30 days before, while the leaky join also counts events after day 30 that the label causes; a bar chart shows AUC 0.90 for the leaky feature vs 0.60 for the point-in-time feature, beside the SQL shape of the fix.

In production. Feature stores exist largely to make this join impossible to get wrong. Feast's get_historical_features, for example, takes an "entity dataframe" of IDs and event timestamps and performs a point-in-time join against each feature view. Two checks catch leakage before training: a feature with suspiciously high importance, and an offline metric far above anything the business has seen. When either happens, ask "could this feature have been known at prediction time?" before celebrating.

From production

A churn model's top feature is support_tickets_total, and offline AUC is 0.94. After launch, performance drops to roughly chance on that feature. The label is 'cancelled within 30 days after the snapshot date'.

What's the most likely cause?

  1. The production database is slower
  2. The model needs more trees
  3. Support tickets are noisy in general
  4. Label leakage: the training feature counted tickets filed after the snapshot date, including the 'please cancel my account' tickets that the outcome itself generates; recompute it with e.ts < snapshot_ts
Show the answer

D. Label leakage: the training feature counted tickets filed after the snapshot date, including the 'please cancel my account' tickets that the outcome itself generates; recompute it with e.ts < snapshot_ts — A feature that includes post-prediction events encodes the label. A point-in-time join (events strictly before the prediction timestamp, within a window) gives the model only what it will have in production.

2. Analysing an A/B test in SQL

The question. Did the new recommendation module increase lesson completion?

Start from nothing. Compute per-variant metrics at the unit of randomization (students, not events), then the difference and its confidence interval. SQL produces the sufficient statistics (counts, sums, sums of squares) and a few lines of arithmetic finish the job. Check the sample ratio first: if a 50/50 split produced 52/48, the assignment or logging is broken and the result isn't trustworthy (D10).

python
import math, random, sqlite3

random.seed(3)
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE assignment (student INT, variant TEXT)")
db.execute("CREATE TABLE completions (student INT, lesson INT)")
for s in range(20_000):
    v = "treatment" if random.random() < 0.5 else "control"
    db.execute("INSERT INTO assignment VALUES (?, ?)", (s, v))
    rate = 1.30 if v == "treatment" else 1.20
    for lesson in range(int(random.expovariate(1 / rate) + 0.5)):
        db.execute("INSERT INTO completions VALUES (?, ?)", (s, lesson))
q = """
WITH per_student AS (
    SELECT a.student, a.variant, COUNT(c.lesson) AS completed
    FROM assignment a LEFT JOIN completions c ON c.student = a.student GROUP BY a.student, a.variant
)
SELECT variant, COUNT(*), AVG(completed), SUM(completed * completed) FROM per_student GROUP BY variant ORDER BY variant
"""
stats = {}
for variant, n, mean, sumsq in db.execute(q):
    var = sumsq / n - mean ** 2
    stats[variant] = (n, mean, var)
    print(f"{variant:<10} n={n:>5}  mean completions {mean:.3f}")
(nc, mc, vc), (nt, mt, vt) = stats["control"], stats["treatment"]
diff, se = mt - mc, math.sqrt(vc / nc + vt / nt)
print(f"sample ratio {nt / (nt + nc):.3f} (expect 0.500)")
print(f"lift {diff:+.3f} completions per student ({diff / mc:+.1%}), 95% CI [{diff - 1.96 * se:+.3f}, {diff + 1.96 * se:+.3f}]")

The left join keeps students who completed nothing (zeros matter for the mean), aggregation at the student level respects the randomization unit, and the interval says whether the lift is distinguishable from zero. Doing this in SQL against the warehouse avoids exporting millions of rows.

In this simulation, control averages 1.184 completions per student (n = 9,981) and treatment 1.264 (n = 10,019). The sample ratio is 0.501, healthy. The lift is +0.081 completions per student (+6.8%), with a 95% interval of [+0.044, +0.117], which excludes zero.

completions per student for control (1.184, n = 9,981) and treatment (1.264, n = 10,019), with the readout steps: sample ratio 0.501 (healthy), lift +0.081 (+6.8%), and a 95% confidence interval from +0.044 to +0.117 that excludes 0.
Chart— completions per student for control (1.184, n = 9,981) and treatment (1.264, n = 10,019), with the readout steps: sample ratio 0.501 (healthy), lift +0.081 (+6.8%), and a 95% confidence interval from +0.044 to +0.117 that excludes 0.

In production. Experimentation platforms compute exactly these per-unit sufficient statistics in the warehouse, often adding variance-reduction methods such as CUPED (which uses each user's pre-experiment metric to shrink the interval). The sample-ratio check is automated, because a mismatch usually means a logging or assignment bug, and no statistics can rescue a broken split. For LLM features, the same readout applies to online metrics like task completion and thumbs-up rate, with offline eval scores as a pre-launch gate (Evaluation path).

From production

An analyst computes an A/B test's completion rate as completed events / all events, per variant, by grouping the events table.

What's wrong?

  1. Nothing
  2. Events should be sampled first
  3. SQL can't compute ratios
  4. The unit of analysis doesn't match the unit of randomization: heavy users contribute many events and their correlated behaviour makes the variance estimate wrong. Aggregate per student first, then compare student-level metrics
Show the answer

D. The unit of analysis doesn't match the unit of randomization: heavy users contribute many events and their correlated behaviour makes the variance estimate wrong. Aggregate per student first, then compare student-level metrics — Students were randomized, so students are the independent units. Event-level analysis overweights heavy users and understates uncertainty, producing false positives.

3. Vector and hybrid search in Postgres

The question. Can Atlas keep embeddings next to the course data instead of a separate vector database?

Start from nothing. Yes, often. The pgvector extension adds a vector column type, distance operators (<-> L2, <=> cosine distance, <#> negative inner product) and approximate indexes (HNSW, IVFFlat; D8). The advantage is that filters and joins run in the same query, with the same permissions and transactions: "the 5 nearest chunks from the student's enrolled courses". Hybrid search combines a full-text rank (ts_rank over a tsvector) with vector similarity, for example by reciprocal rank fusion. A dedicated vector database earns its place at very large scale or with specialized needs.

sql
-- schema and query in PostgreSQL with pgvector (not executed here)
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
  id bigserial PRIMARY KEY, course_id int NOT NULL, body text NOT NULL,
  embedding vector(1024) NOT NULL, tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON chunks USING gin (tsv);
CREATE INDEX ON chunks (course_id);

-- hybrid search, permission-filtered, fused by reciprocal rank
WITH allowed AS (SELECT course_id FROM enrolments WHERE student_id = $1),
semantic AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $2) AS r FROM chunks
  WHERE course_id IN (SELECT course_id FROM allowed) ORDER BY embedding <=> $2 LIMIT 50),
lexical AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(tsv, q) DESC) AS r
  FROM chunks, plainto_tsquery('english', $3) q
  WHERE tsv @@ q AND course_id IN (SELECT course_id FROM allowed) LIMIT 50)
SELECT c.id, c.body, COALESCE(1.0 / (60 + s.r), 0) + COALESCE(1.0 / (60 + l.r), 0) AS score
FROM chunks c LEFT JOIN semantic s USING (id) LEFT JOIN lexical l USING (id)
WHERE s.id IS NOT NULL OR l.id IS NOT NULL
ORDER BY score DESC LIMIT 10;

The permission filter sits inside both retrievals (D22 LLM platforms), and reciprocal rank fusion combines the two rankings without having to calibrate their scores against each other. The same logic can be prototyped in SQLite with plain tables and NumPy for the distances, which is how the exercise checks it.

an allowed-courses filter (from the student's enrolments) feeding both a semantic top-50 retrieval (embedding distance over an HNSW index) and a lexical top-50 retrieval (ts_rank over a GIN-indexed tsvector); reciprocal rank fusion sums 1 / (60 + rank) and returns the top 10 chunks.
Diagram— an allowed-courses filter (from the student's enrolments) feeding both a semantic top-50 retrieval (embedding distance over an HNSW index) and a lexical top-50 retrieval (ts_rank over a GIN-indexed tsvector); reciprocal rank fusion sums 1 / (60 + rank) and returns the top 10 chunks.

In production. The subtle failure is post-filtering. An approximate index returns, say, its 50 nearest neighbours first, and then a WHERE clause removes the ones the user may not see. If most of the nearest neighbours belong to other courses, the user gets 3 results, or none, while relevant allowed chunks sat just outside the top 50. Remedies include filtering inside the retrieval (as above), raising the candidate count for filtered queries, partitioning the index by tenant, or using a vector store with filter-aware search. Recent pgvector releases added iterative index scans for exactly this case. Whichever you choose, test filtered queries specifically: they're where retrieval quietly degrades.

From production

A multi-tenant RAG app runs SELECT … FROM chunks ORDER BY embedding <=> $q LIMIT 10 through an HNSW index and then drops chunks from other tenants in application code. Small tenants often get zero results.

What's happening, and what's a better design?

  1. Small tenants have bad embeddings
  2. The ANN search returns the global top 10, mostly other tenants' chunks, and post-filtering removes them; filter by tenant inside the query (and/or partition the index per tenant, or raise the candidate count), so search happens within the allowed set
  3. HNSW can't handle more than 10 results
  4. The cosine operator should be L2
Show the answer

B. The ANN search returns the global top 10, mostly other tenants' chunks, and post-filtering removes them; filter by tenant inside the query (and/or partition the index per tenant, or raise the candidate count), so search happens within the allowed set — Approximate nearest-neighbour search plus a later filter can't recover candidates it never returned. Filtering inside the retrieval (or per-tenant indexes) keeps the top-k within what the user may see, which is also safer: unauthorised rows never leave the database.


Check yourself

  1. What is a point-in-time (as-of) join, and what goes wrong without it?
  2. Why compute A/B metrics per student, and why check the sample ratio?
  3. Why keep zero-activity students via a left join in an A/B analysis?
  4. What does pgvector add to Postgres, and when is it a good choice over a separate vector database?
  5. Why can post-filtering an approximate nearest-neighbour result return too few (or zero) results?
  6. A feature has suspiciously high importance and offline AUC is far above anything seen in production. What should you check first?
Answers
  1. Joining each example only to events before its prediction time. Without it, features include future information (often caused by the label), so offline metrics are inflated and production performance collapses.
  2. Students are the randomized, independent units; event-level analysis misweights users and understates variance. A sample-ratio mismatch signals broken assignment or logging.
  3. Students with no completions are part of the metric (zeros); an inner join would drop them and bias both means upwards.
  4. A vector type, distance operators and ANN indexes. When filters, joins, permissions and transactions with relational data matter and scale is moderate.
  5. The index returns its top-k over all rows; if most of those fail the filter, few or none remain, even though matching rows exist just outside the top-k. Filter inside the search instead.
  6. Leakage: whether the feature could have been known at prediction time, i.e. whether its query uses only events strictly before the prediction timestamp.

Exercise

Prototype the hybrid search from section 3 in SQLite + NumPy: store chunks with a course ID, body text and an embedding (as a BLOB), retrieve the top 5 by cosine similarity among chunks the student may see, the top 5 by a simple keyword-overlap score, and fuse them with reciprocal rank fusion (k = 60). Show that a chunk from a course the student isn't enrolled in never appears.

Solution
python
import sqlite3
import numpy as np

rng = np.random.default_rng(0)
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE chunks (id INTEGER PRIMARY KEY, course INT, body TEXT, emb BLOB)")
db.execute("CREATE TABLE enrolments (student INT, course INT)")
db.executemany("INSERT INTO enrolments VALUES (?, ?)", [(1, 10), (1, 11)])
bodies = ["gradient descent updates weights", "attention weighs tokens", "the kv cache stores keys and values",
          "backpropagation applies the chain rule", "dropout regularizes networks", "secret exam answers for course 12"]
courses = [10, 10, 11, 11, 10, 12]
topic = rng.standard_normal(16)
for i, (b, c) in enumerate(zip(bodies, courses)):
    e = rng.standard_normal(16) + (2.0 * topic if "kv" in b or "secret" in b else 0)
    db.execute("INSERT INTO chunks VALUES (?, ?, ?, ?)", (i, c, b, (e / np.linalg.norm(e)).astype(np.float32).tobytes()))

def hybrid(student, query_text, query_emb, k=60):
    rows = db.execute("""SELECT id, body, emb FROM chunks WHERE course IN
                         (SELECT course FROM enrolments WHERE student = ?)""", (student,)).fetchall()
    ids = [r[0] for r in rows]
    embs = np.stack([np.frombuffer(r[2], dtype=np.float32) for r in rows])
    sem = [ids[i] for i in np.argsort(-(embs @ query_emb))[:5]]
    q_words = set(query_text.split())
    lex = [r[0] for r in sorted(rows, key=lambda r: -len(q_words & set(r[1].split())))[:5] if q_words & set(r[1].split())]
    score = {}
    for ranking in (sem, lex):
        for rank, cid in enumerate(ranking, 1):
            score[cid] = score.get(cid, 0) + 1 / (k + rank)
    return sorted(score.items(), key=lambda kv: -kv[1])

q_emb = topic / np.linalg.norm(topic)
for cid, sc in hybrid(1, "what does the kv cache store", q_emb):
    body, course = db.execute("SELECT body, course FROM chunks WHERE id = ?", (cid,)).fetchone()
    print(f"{sc:.4f}  course {course}  {body}")

The KV-cache chunk ranks first because both retrievers agree on it. The "secret exam answers" chunk is similar to the query's embedding, but it belongs to course 12, where the student isn't enrolled, so the filter inside retrieval excludes it before ranking ever happens.


This unlocks:

  • SQL interview patterns and capstone — the whole track in one analytics warehouse.