A startup called Sparkify wants to analyze the data they've been collecting on songs and user activity on their new music streaming app. The analytics team is particularly interested in understanding what songs users are listening to. Currently, they don't have an easy way to query their data, which resides 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.
Creating a Postgres database with tables designed to optimize queries on song play analysis. Creating a database schema and ETL pipeline for this analysis and test the database and ETL pipeline by running queries given to you by the analytics team from Sparkify and compare the results with their expected results.

This project implements a data modeling and ETL pipeline solution using PostgreSQL. It features a star schema design with clearly defined fact and dimension tables optimized for specific analytical purposes. The Python-based ETL pipeline efficiently processes and transfers data from JSON files stored in two local directories into the PostgreSQL database, combining Python's processing capabilities with SQL for seamless data integration. This complete solution transforms raw JSON data into an analysis-ready database structure.

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.
---Metadata is data that provides information about other data.For example, in a database, metadata could describe the schema (table names, column names, data types)

song_data/A/B/C/TRABCEI128F424C983.json
song_data/A/A/B/TRAABJL12903CDCF1A.json

And below is an example of what a single song file, TRAABJL12903CDCF1A.json, 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
}

The log files are from a music streaming app based on specified configurations. For example, here are filepaths to two files in this dataset.

log_data/2018/11/2018-11-12-events.json
log_data/2018/11/2018-11-13-events.json

{
  "artist": "Pavement",
  "auth": "Logged In",
  "firstName": "Sylvie",
  "gender": "F",
  "itemInSession": 0,
  "lastName": "Cruz",
  "length": 99.16036,
  "level": "free",
  "location": "Washington-Arlington-Alexandria, DC-VA-MD-WV",
  "method": "PUT",
  "page": "NextSong",
  "registration": 1540266185796.0,
  "sessionId": 345,
  "song": "Mercy:The Laundromat",
  "status": 200,
  "ts": 1541990258796,
  "userAgent": "\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.77.4 (KHTML, like Gecko) Version/7.0.5 Safari/537.77.4\"",
  "userId": "10"
}

Using the Star Schema: one fact table consist of the measures associated with each event songplays, and referencing four dimensional tables songs, artists, users and time, each with a primary key that is being referenced from the fact table.

The data files, the project includes seven files:
create_tables.py: drops and creates your tables. You run this file to reset your tables before each time you run your ETL scripts.
etl.ipynb: reads and processes a single file from song_data and log_data and loads the data into your tables. This notebook contains detailed instructions on the ETL process for each of the tables.
etl.py: reads and processes files from song_data and log_data and loads them into your tables. You can fill this out based on your work in the ETL notebook.
README.md: provides discussion on this project.
ER_Diagram.png: ERD for star schema of this project
sql_queries.py: contains all your sql queries, and is imported into the first three files above.
test.ipynb: displays the first few rows of each table to let us check on the database.

Run create_tables.py to create the database and tables.
Run etl.py to build ETL pipeline for loading, extracting and inserting the data.
Run test.ipynb to confirm the creation of database and columns.