DW on Redshift with ETL

This project aims to build OLAP Data Warehouse for reporting and analytical purposes.

Motivation

The following data warehouse is built for imaginary music streaming application called "Sparkify". The startup aims to establish consistent reporting service to analyze the activity of their users. Their data resides in S3 bucket in a directory of JSON logs as well as the directory of JSON metadata on their songs in the app.

Objective

The main objective of the project is to build ETL pipeline to extract data from S3 and stage them in Redshift. Afterwards, the data in Redshift must be transformed into a set of dimensional tables that work best for read-intensive purposes.

Datasets

There two basic log files in S3 bucket:

• song_data (song metadata)
• log_data (event log)

OLAP Database schema is a star schema optimized for queries on song play analysis. It consists of the following entities

1. songplays - fact table, records in event data associated with song plays
2. users - users in the app
3. songs - songs in the music database
4. artists - artists in the music database
5. time - timestamps of records in songplays broken down into specific units: start_time, hour, day, week, month, year, weekday

Project files

create_table.py - where fact table and dimension tables are created for the star schema in the Redshift etl.py - where the data load from S3 to Redshift staging tables takes place. Then the data is further processed into analytics tables on Redshift sql_queries.py - where all SQL queries for creating needed tables, dropping tables and loading data are created. README.md - readme file with instructions

Sample queries

Run the following queries to check the information in tables:

SELECT * FROM column_name;

Run the following query to check table_name, column_name and data_type:

SELECT
table_name, column_name, data_type
FROM
information_schema.columns
WHERE
table_name = 'table_name'