Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: Normalize for writes; denormalize for analytics. **OLTP**: users (user_id PK, email, created_at); artists (artist_id PK, name); tracks (track_id PK, artist_id FK, title, duration); playlists (playlist_id PK, user_id FK, name); playlist_tracks...
Architectural Logic: Normalize for writes; denormalize for analytics. OLTP: users (user_id PK, email, created_at); artists (artist_id PK, name); tracks (track_id PK, artist_id FK, title, duration); playlists (playlist_id PK, user_id FK, name); playlist_tracks (playlist_id, track_id, position). Analytics: fact_listening (user_id, track_id, played_at, duration_played)—partition by played_at. dim_user, dim_artist, dim_track, dim_date. Why Partition: Time-series queries on listening history; conformed dimensions for cross-mart analysis. Scalability: Support many-to-many (collabs) via track_artists; surrogate keys for SCD. Cost: Separate hot (recent listening) from cold (historical) via partitioning.
Red Flag: Single table for tracks and artists without supporting collaborations—common oversight. Pro-Move: Use track_artists bridge table; dim_artist as conformed dimension across listening and playlist analytics.
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.