Understand the sample data

The database is one connected ML platform, not a collection of unrelated interview tables. Follow a model from ownership and source data through training, deployment, serving, drift, and incidents.

The system in one line

project → dataset version → model version → deployment → prediction event
       ↘ feature materialization   ↘ approval      ↘ drift / incident
       ↘ experiment → training run → run metric
       ↘ pipeline → pipeline run → task attempt

Always say the grain first

Before joining, finish this sentence: “One output row represents …”. Most hard SQL mistakes in this lab are grain mistakes, not syntax mistakes.

Table grains and join keys

Area	Table	One row represents	Key joins
Ownership	projects	one ML product	project_id
Data	dataset_versions	one immutable dataset version	dataset_id
Features	feature_materializations	one feature built from one dataset version	feature_id, dataset_version_id
Training	training_runs	one physical training attempt	experiment_id, model_version_id
Metrics	run_metrics	one run/metric/split/step value	run_id
Registry	model_versions	one registered artifact	model_id, training_dataset_version_id
Serving	deployments	one release of one artifact	project_id, model_version_id
Rollout	deployment_traffic	one effective-dated allocation change	deployment_id
Events	prediction_events	one prediction request event	deployment_id, monthly predicted_at partition
Pipelines	pipeline_runs	one physical orchestration attempt	pipeline_id, retry_of
Reliability	drift_signals	one feature/window/method signal	deployment_id
Reliability	incidents	one operational incident	project_id, optional deployment_id

Look at representative rows

Projects: the ownership root

SELECT project_id, project_name, business_domain, criticality, pii_tier
FROM projects
ORDER BY project_id
LIMIT 3;

project_id	project_name	business_domain	criticality	pii_tier
1	ml_product_01	recommendations	2	none
2	ml_product_02	search	3	limited
3	ml_product_03	marketing	4	sensitive

Deployments: release history, not current state only

SELECT d.deployment_id, p.project_name, d.status, d.deployed_at, d.endpoint_name
FROM deployments d
JOIN projects p USING (project_id)
WHERE d.environment = 'production'
ORDER BY d.deployed_at
LIMIT 4;

deployment_id	project_name	status	deployed_at (UTC)	endpoint_name
673	ml_product_01	retired	2026-01-20 01:00	predict-project-1
674	ml_product_02	retired	2026-01-21 02:00	predict-project-2
457	ml_product_01	retired	2026-01-22 01:00	predict-project-1
675	ml_product_03	rolled_back	2026-01-22 03:00	predict-project-3

Project 1 appears twice. Joining projects to deployments changes the grain from one project to one deployment, so every project-level column repeats.

Traffic: effective-dated history

Deployment 673 ramps through four facts:

effective_at (UTC)	traffic_percent	reason
2026-01-20 01:00	5	canary
2026-01-20 07:00	25	ramp
2026-01-20 13:00	50	ramp
2026-01-20 19:00	100	full

To answer “traffic at 10:00,” filter effective_at <= 10:00, order descending, and keep one row. Never use the globally latest row for a historical question.

Prediction events: the large fact table

SELECT prediction_id, deployment_id, predicted_at, request_id,
       device_family, latency_ms, http_status, prediction, ground_truth
FROM prediction_events
ORDER BY predicted_at
LIMIT 3;

prediction_id	deployment_id	predicted_at (UTC)	device	latency_ms	status	ground_truth
349920	1	2026-01-01 00:00	iphone	18	200	NULL
443801	318	2026-01-01 00:01	ipad	247	200	0.337
93881	318	2026-01-01 00:01	ipad	247	200	0.905

ground_truth is intentionally delayed or missing. Filtering it in WHERE before calculating coverage changes the population and produces a misleading 100%.

Run metrics: a deliberate one-to-many join

One training run has several metrics and splits:

run_id	model_name	status	metric_name	split	value
1	model_1_1	succeeded	auc	test	0.6817
1	model_1_1	succeeded	latency_ms	test	21.0

Joining training_runs directly to run_metrics multiplies each run. Pivot or aggregate metrics back to one row per run before comparing experiments.

A repeatable query-building method

1. Declare grain

Write the output key and expected row count before SQL.

2. Build the base

Select only keys, timestamps, and row-level derived values.

3. Control joins

Ask whether every join is one-to-one, many-to-one, or multiplying.

4. Aggregate once

Group only after the base relation has the intended population.

5. Test edges

Check NULLs, ties, empty dates, zero denominators, and late facts.

6. Inspect the plan

Use EXPLAIN (ANALYZE, BUFFERS) after correctness.

Try these before an exercise:

\d+ prediction_events
SELECT count(*) FROM prediction_events;
SELECT * FROM deployments ORDER BY deployed_at DESC LIMIT 5;
SELECT status, count(*) FROM training_runs GROUP BY status;

The seeded database contains 500,000 prediction events, 15,000 training runs, 142,000 run metrics, 10,000 pipeline runs, and 40,000 task attempts.