• This repo contains a data pipeline project built to load cv data from an aws-s3 bucket to a redshift database cluster. The project was done in two ways.

  • Using only python to build an etl pipeline that takes the csv files from s3 to redshift.
  • Using airflow to create the etl pipeline.

  Data Pipeline with Airflow

• I built an etl pipeline with airflow using python. I defined a star schema data model that consists of 4 dimensional models and a fact table focused on a particular analytical need. The etl loads data from a s3 bucket into a redshift data warehouse for analytics.

I used the popular pagila dataset for this project.

1. Dates
2. Customer
3. Movie
4. Store

1. Sales Fact

• The project contains 2 folders and 3 files
  1. dags folder: This contains the dag file.
  2. plugins folder: this contains the helper folders and operators used in building the airflow data pipeline
  3. create_tables.py: this file builds the tables in the redshift data warehouse.
  4. create_tables.sql: this contains the sql queries to create the tables.
  5. dwh.cfg: contains the configuration codes for our aws infrastructures.

1. Create an IAM user and take note of the secret key and access key.
2. Create aws redshift role: Create a redshift role under IAM.
3. Create Redshift clusters: First you need to create a redshift cluster, with you preferred configuration details and connects the cluster to the redshift IAM role.
4. Airflow connection: Create a new airflow connection on airflow containing details of your redshift connections including the database info.
5. Create a new variable containing your bucket name.
6. Add configuration details to the dwh.cfg file.

1. Run create_tables.py to create the data warehouse tables.
2. Go to your airflow UI and run the dag.