Cloud Data Warehouse

CASE STUDY SNAPSHOT

Designing a warehouse that makes listening behavior queryable.

This project follows the full path from semi-structured S3 logs to a Redshift star schema, showing how raw music-streaming events become a reliable analytics layer for product and business questions.

Project flow

A quick read of how the work moves from raw material to useful insight.

S3 events

Start with song metadata and user activity JSON stored in cloud storage.

Redshift staging

Load raw files into staging tables and validate the shape of the incoming data.

Star schema

Build fact and dimension tables for users, songs, artists, time, and songplays.

Analytics

Enable faster questions about engagement, popularity, and listening patterns.

Architecture: Amazon S3 feeds staged data into Amazon Redshift, where SQL transformations shape an analytics-ready star schema.

Key Decisions: Separate staging from analytics tables, use a fact table with dimensions, and load semi-structured events with Redshift COPY commands.

Data Quality: Validate incoming records during staging, filter listening events, and derive consistent time-based dimensions before analysis.

Outcome: A reliable AWS warehouse for querying listening behavior, song popularity, and engagement trends.