Manage schema migrations at scale | Dataford Interview Questions

How do you manage database schema migrations in a high-scale, production environment?

Using the provided migration metadata, write a query that reports the current production migration health for every service, including services without a production migration.

Output

One row per service with service_name, migration_version, latest_status, failed_step_count, total_step_count, and duration_seconds
Include services without a production run, using NULL migration details and zero step counts
Return the latest production run per service, ordered by service_name ascending

Schema

services

Column	Type	Description
service_idPK	INT	Unique service identifier
service_name	VARCHAR(100)	Name of the service

migration_runs

Column	Type	Description
run_idPK	INT	Unique migration run identifier
service_id	INT	Service executing the migration
environment	VARCHAR(20)	Deployment environment
migration_version	VARCHAR(50)	Migration version identifier
status	VARCHAR(20)	Overall migration status
started_at	TIMESTAMP	Migration start timestamp
completed_at	TIMESTAMP	Migration completion timestamp

migration_steps

Column	Type	Description
step_idPK	INT	Unique migration step identifier
run_id	INT	Migration run containing the step
step_name	VARCHAR(100)	Migration step name
status	VARCHAR(20)	Step execution status

Your question is Manage schema migrations at scale. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem