Data Modeling with Python and Postgres

Introduction

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.

Project Structure

ETL Pipeline

etl.py

ETL pipeline builder

process_data

process_song_file

process_log_file

create_tables.py

Creating Fact and Dimension table schema

create_database

drop_tables

create_tables

sql_queries.py

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

*_table_drop

*_table_create

*_table_insert

song_select

Database Schema

Fact table

Dimension table