Skip to content

Project 3 Data Warehouse

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

Project 3 Data Warehouse

README.md


Project 3 Data Warehouse

Udacity Nanodegree
Explore the repository»

About The Project

In this project, I learned how to build a data warehouse on AWS and and use it together with an ETL pipeline for a database hosted on Redshift.

I applied what I've learned in the previous lessons to build a pipeline that loads data from S3 buckets to staging tables on Redshift and model into fact and dimensions tables to perform analytics and obtain meaningful insights.

Project Description

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.

As their data engineer, you are tasked with building an ETL pipeline that extracts their data from S3, stages them in Redshift, and transforms data into a set of dimensional tables for their analytics team to continue finding insights into what songs their users are listening to. You'll be able to test your database and ETL pipeline by running queries given to you by the analytics team from Sparkify and compare your results with their expected results.

Tools Used

  • Python
  • PostgreSQL
  • Jupyter notebooks
  • AWS

Datasets

Song Dataset

This first dataset is a subset of Million Song Dataset. Each file in the dataset is in JSON format and contains meta-data about a song and the artist of that song. The dataset is hosted at S3 bucket s3://udacity-dend/song_data.

Sample:

{"num_songs": 1, "artist_id": "ARJIE2Y1187B994AB7", "artist_latitude": null, "artist_longitude": null, "artist_location": "", "artist_name": "Line Renaud", "song_id": "SOUPIRU12A6D4FA1E1", "title": "Der Kleine Dompfaff", "duration": 152.92036, "year": 0}

Log Dataset

The second dataset is generated by Event Simulator. These log files in JSON format simulate activity logs from a music streaming application based on specified configurations. The dataset is hosted at S3 bucket s3://udacity-dend/log_data.

Sample:

{"artist": null, "auth": "Logged In", "firstName": "Walter", "gender": "M", "itemInSession": 0, "lastName": "Frye", "length": null, "level": "free", "location": "San Francisco-Oakland-Hayward, CA", "method": "GET","page": "Home", "registration": 1540919166796.0, "sessionId": 38, "song": null, "status": 200, "ts": 1541105830796, "userAgent": "\"Mozilla\/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/36.0.1985.143 Safari\/537.36\"", "userId": "39"}

Data Modeling

ERD

In this case I knew before-hand that the data types are structured (JSONs) and the amount of data we need to analyze is not big enough to require big data related solutions.

For that reason I used the Star Schema for data modeling in this ETL pipeline, there is two stging_tbles containing all the files information, one fact table containing all the metrics associated to each event songplays, and four dimensions tables, containing complementary informations about songs, artists, users and time, each with a primary key that is being referenced from the fact table.

The data stored on S3 buckets is extracted to staging tables staging_events and staging_songs on Redshift. Then the data from these tables are transformed and inserted into the fact and dimensional tables.

This structure will allow the analysts to do joins and aggregations of the data efficiently.

You can see an Entity Relationship Diagram (ERD) of the built data model below:

database

Project structure

Files:

File / Folder Description
sql_queries.py Contains the SQL queries for staging, schema definition and ETL
create_tables.py Drops and creates all tables (Reset the tables)
etl.py Stages and transforms the data from S3 buckets and loads them into tables
cluster_configuration.ipynb Step by step to create and set up a Redshift cluster on AWS with proper configurations
tests.ipynb Basic querying from all tables created to ensure their validity
delete_resources.ipynb Destorys the Redshift cluster and the roles on AWS, if exists
dwh.cfg Sample configuration file for AWS
images Folder at the root of the project, where ERD image is stored
README Readme file

How to Run

Clone the repository into a local machine using

git clone https://github.com/BinariesGoalls/Udacity-Data-Engineering-Nanodegree

Prerequisites

These are the tools necessaries to run the program.

  • python 3.7
  • PostgreSQL
  • AWS account
  • psycopg2 python library
  • boto3 python library

Steps

Follow the steps to extract and load the data into the data model.

  1. Navigate to Project 3 Data Warehouse folder

  2. Follow the steps on the notebook cluster_configuration.ipynb to create the clusters on AWS and configure the dwh.cfg file

  3. Run create_tables.py to create/reset the tables by

    python create_tables.py

  4. Run ETL process and load data into database by

    python etl.py

    This will execute SQL queries corresponding to staging data from S3 on Redshift and to transform and insert into the Postgres tables on Redshift.

  5. Run queries on the notebook tests.ipynb to validate the entry of data into tables

    This runs some sample queries from all the tables created.

  6. Run the steps on delete_resources.ipynb to destroy the clusters and roles on AWS

Contact

Alisson lima - ali2slima10@gmail.com

Linkedin: https://www.linkedin.com/in/binariesgoalls/