Data Warehouse

A music streaming startup, Sparkify, has grown their user base and song database and want to move their processes and data onto the cloud. Their data resides in S3, in a directory of JSON logs on user activity on the app, as well as a directory with JSON metadata on the songs in their app. Therefore, The task is to:

• build an ETL pipeline that extracts their data from S3,
• stages it in Redshift,
• and transforms the data into a set of dimensional tables.

That is to be able to have better analytical processes by building a Data Warehouse using AWS Redshift and to continue finding insights into what songs their users are listening to.

Dataset

You'll be working with two datasets that reside in S3 (their links are reside in the configuration file):

Song Dataset

The first 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.

Log Dataset

The second 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. The log files in the dataset you'll be working with are partitioned by year and month.

Methodology

In this project, we ordered the work by:

• creating the staging tables, which consists of the two mentioned datasets, Song and Log datasets.
• building and ETL pipeline that extracts the data from S3 buckets and copy it to the staging tables.
• then, transforming the staging tables to into a set of dimensional tables to complete the data warehouse structure and to prepare the data for further analytics and usage of business intelligence tools.

We transformed the song and log staging tables to several dimensional tables by creating a database with Star schema optimized for queries on song play analysis. This includes the following tables:

Fact Table

1. songplays - records in log data associated with song plays i e. records with page NextSong:
   • songplay_id, start_time, user_id, level, song_id, artist_id, session_id, location, user_agent

Dimension Tables

2. users - users in the app

   • user_id, first_name, last_name, gender, level

3. songs - songs in music database

   • song_id, title, artist_id, year, duration

4. artists - artists in music database

   • artist_id, name, location, latitude, longitude

5. time - timestamps of records in songplays broken down into specific units

   • start_time, hour, day, week, month, year, weekday

The above Star schema satisfies the needs of simpler queries and faster aggregation, Which make it a suitable choice to the project problem.

Project Structure

The project structure is shown as the following:

• create_tables.py: is where you'll create your fact and dimension tables for the star schema in Redshift.
• etl.py: is where you'll load data from S3 into staging tables on Redshift and then process that data into your analytics tables on Redshift.
• sql_queries.py: is where you'll define you SQL statements, which will be imported into the two other files above.
• README.md: provides discussion about the project.

Usage

To work on this project, you need first to install Python in your machine. Then, install the following dependencies:

• psycopg2

Steps

To complete the project, you need to follow these steps:

1. Write all your needed SQL statements (Drop, Create, Copy, Insert, etc.) in sql_queries.py.

2. Launch a redshift cluster and create an IAM role that has read access to S3, if you don't have one.

3. Add redshift database and IAM role info in dwh.cfg.

4. Run create_tables.py from terminal to set up the database and tables:

   python create_tables.py

5. Run etl.py from terminal to load data from S3 to staging tables on Redshift, then to analytics tables on Redshift:

   python etl.py