In this project, I analyzed the data collected by Sparkify – a startup company – to understand the song the users were listening to. Given a directory of JSON user activity on the music app as well as a directory with JSON metadata on the songs in their apps. I created a Postgres database with tables designed to optimize queries on the song play analysis. I thus started by creating database schema and ETL pipeline for this analysis. I was able to test the database and ETL pipeline by executing the queries given by the analytics teams from the company and was able to compare the results obtained.

This project models user activity data for a music streaming app called Sparkify to optimize queries for understanding what songs users are listening to by creating a Postgres relational database and ETL pipeline to build up Fact and Dimension tables and insert data into new tables.

ETL pipeline builder

process_data

process_song_file

process_log_file

Creating Fact and Dimension table schema

Helper SQL query statements for etl.py and create_tables.py