ETL with PostgreSQL

A hypothetical startup called Sparkify wants to analyze the data they've been collecting on songs and user activity on their new music streaming app. The company is interested in understanding what songs users are listening to. At present, Sparkify has the data stored in a directory of JSON logs which are not useful for analysis in that format.

The purpose of this project is to create a PostgreSQL database schema and ETL pipeline so that analysis can be performed easily.

Datasets Available

The song dataset is a subset of real data from the Million Song Dataset. Each file is in JSON format and contains metadata about a song and the artist of that song. The files are partitioned by the first three letters of each song's track ID.

The log dataset consists of log files in JSON format generated by this event simulator based on the songs in the dataset above. These simulate activity logs from a music streaming app based on specified configurations.

Setup Instructions

• Install requirements with pip3 install -r requirements.txt.
• Set up a local PostgreSQL instance on port 5432. Please see detailed instructions in the PostgreSQL documentation.

Program Execution

• Execute the script to generate the database and its tables by executing python3 create_tables.py.
• Load the data and insert it to the database by executing python3 etl.py.

Schema Design

• The fact table songplays stores the records in log data associated with song plays i.e. records with page.
• The dimension table users stores the users in the app.
• The dimension table song stores the songs in the music database.
• The dimension table artists stores the artists the in music database.
• The dimension table time stores the timestamps of records in songplays broken down into specific units.

Purpose of this Database

This database allows you to aggregate all songs, artists, users and songs played in a single database. In this way, the company disposes of the needed data stored in a unique structure and can thus analyze different scenarios easily. For instance, you can analyze the popularity of different songs or artists. Also, it is possible to perform analysis with geographic information. You can for instance determine which song is popular in which country and region.