Since the sparkify a music streaming company is growing rapidly, the company wants to introduce more automation and monitoring to their data warehouse ETL pipelines.
The songs data & log data are in JSON format that are stored in Amazon S3
- Python
- Apache Airflow
- Postgres
- AWS redshift
- Notion for project management
- Github for Version control
- Create connection "aws_credentials" & "redshift" for storing aws credentials & redshift connection string, respectively.
- Create variable named s3_bucket, s3_prefix, region and json_format, which will be used in the program
- Run the airflow server using "airflow standalone command"
- Go to localhost:8080 in browser
- Run dag named sparkify_analysis to perform following task in sequence
- Create staging, facts and dimension tables
- Load data from s3 to staging tables
- Load data from staging tables to facts and dimension tables
- Check the quality of data
The project consists of following files:
This is the major file of the project in which all of the tasks and their ordering are defined.
This file consists of all queries required for creating staging, fact, and dimension tables.
This operator allows tasks in dag to load data from s3 to redshift staging tables
This file consists of all sql queries that are required to load data on the facts and dimension table.
This operator loads data from the staging table in redshift to the fact table
This operator loads data from the staging table to the dimension tables
This operator validates the data loaded to the facts and dimension table