Schema for Simulation Datasets

HardSQL · PostgreSQL

Problem

How would you design a schema for a new simulation dataset that needs to be queried by multiple ML teams at Waymo?

Define a normalized PostgreSQL schema for teams, datasets, simulation runs, and team access. Then write a query that returns the latest completed run for every team and dataset access record, while retaining accessible datasets with no completed run.

Output

One row per team and accessible dataset.
Columns: team_name, dataset_name, access_level, latest_run_id, latest_completed_at, and scenarios_simulated.
Include datasets without a completed run with NULL run fields. Order by team_name, then dataset_name.

Schema

ml_teams

Column	Type	Description
team_idPK	INT	Unique ML team identifier
team_name	VARCHAR(100)	ML team name

simulation_datasets

Column	Type	Description
dataset_idPK	INT	Unique simulation dataset identifier
dataset_name	VARCHAR(150)	Human-readable dataset name
owner_team_id	INT	Team responsible for the dataset
modality	VARCHAR(50)	Primary simulated sensor or data modality
created_at	TIMESTAMPTZ	Dataset creation timestamp

simulation_runs

Column	Type	Description
run_idPK	INT	Unique simulation run identifier
dataset_id	INT	Dataset produced by the run
run_status	VARCHAR(20)	Run lifecycle status
started_at	TIMESTAMPTZ	Run start timestamp
completed_at	TIMESTAMPTZ	Run completion timestamp
scenarios_simulated	INT	Number of scenarios produced by the run

dataset_team_access

Column	Type	Description
dataset_idPK	INT	Accessible dataset identifier
team_idPK	INT	Team granted access
access_level	VARCHAR(20)	Permission level granted to the team
granted_at	TIMESTAMPTZ	Access grant timestamp

Tablesml_teamssimulation_datasetssimulation_runsdataset_team_access