Eventual Consistency | Dataford Interview Questions

Spotify's catalog pipeline writes updates to a source-of-truth table first, then propagates them to the serving layer with a delay. Write a SQL query to find catalog items where the serving layer is still stale.

Requirements

Return items whose source version is newer than the serving version, or that do not exist in the serving layer yet.
Show the item id, item type, source version, serving version, and a status label of pending_sync or missing_in_serving.
Order the result by item_id.

Schema

catalog_source

Column	Type	Description
item_idPK	INT	Source system item id
item_type	VARCHAR(20)	Item type, such as track or playlist
source_version	INT	Latest version in the source system
updated_at	TIMESTAMP	Last source update time

catalog_serving

Column	Type	Description
item_idPK	INT	Serving layer item id
item_type	VARCHAR(20)	Item type in serving
serving_version	INT	Version currently visible in serving
last_synced_at	TIMESTAMP	Last sync time into serving

Your question is Eventual Consistency. Start with the requirements and the two 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