In this project, I built a cloud-based data warehouse for the Sparkify music streaming platform using Amazon S3, Amazon Redshift, and SQL. The objective is to design an ETL pipeline that extracts semi-structured JSON data from cloud storage, stages it in Redshift, and transforms it into an analytics-ready dimensional model.
The workflow begins by loading song metadata and user activity logs from Amazon S3 into staging tables within Amazon Redshift using high-performance COPY commands. Separating raw data into staging tables provides a reliable foundation for data validation and simplifies downstream transformations.
After staging, I transform the raw datasets into a star schema with one fact table and multiple dimension tables, optimized for analytical workloads. The ETL process joins song and event data, filters user listening events, derives time-based dimensions, and structures the data to support efficient business intelligence queries.
Beyond building the ETL pipeline, the project demonstrates key data engineering concepts, including cloud-native data warehousing, dimensional modeling, bulk data loading, SQL-based transformations, and scalable analytics architecture on AWS. The resulting warehouse enables analysts to efficiently explore user listening behavior, song popularity, and engagement trends using an optimized Redshift database.
