wq1973 / udacity-de-projects

Data Engineering Nanodegree

Projects done in the Data Engineering Nanodegree by Udacity.com

Project 1: Relational Databases - Data Modeling with PostgreSQL

Developed a relational database using PostgreSQL to model user activity data for a music streaming app. Skills include:

• Created a relational database using PostgreSQL
• Developed a Star Schema database using optimized definitions of Fact and Dimension tables. Normalization of tables.
• Built an ETL pipeline to optimize queries in order to understand what songs users listen to.

Datasets:

• 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. For example, here are filepaths to two files in this dataset.

Here is an example of what a single song file looks like:

{"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 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.

Here is an example of what a single log file looks like:

{"artist":"Survivor","auth":"Logged In","firstName":"Jayden","gender":"M","itemInSession":0,"lastName":"Fox","length":245.36771,"level":"free","location":"New Orleans-Metairie, LA","method":"PUT","page":"NextSong","registration":1541033612796.0,"sessionId":100,"song":"Eye Of The Tiger","status":200,"ts":1541110994796,"userAgent":"\"Mozilla\/5.0 (Windows NT 6.3; WOW64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/36.0.1985.143 Safari\/537.36\"","userId":"101"}