Divvy Bikeshare Data Engineering Project 🚴‍♀️

This is an end to end data engineering project, This project uses Chicago's Divvy bikeshare dataset, Divvy is the bicycle sharing system in the Chicago metropolitan area, currently serving the cities of Chicago and Evanston. The system is owned by the Chicago Department of Transportation and has been operated by Lyft since 2019.

Problem Description

The purpose of this project is to make an end to end data pipeline which extracts the divvy data from the web https://divvy-tripdata.s3.amazonaws.com/index.html and Load this data in Google Cloud storage and Big Query , apply Kimbal Dimensional Modeling(Facts and Dimensions tables) to the data using dbt and build a Looker dashboard to vizualize

• Daily riders activity
• Trips per start stations
• Average trip duration
• Bike type distribution and Membership status distribution
• Rides per month and year

Technology Stack

The following technologies are used to build this project

• Google Cloud Storage (GCS) - as Data Lake
  
• Google BigQuery - for Data Warehouse
  
• Terraform - as Infrastructure-as-Code (IaC) tool
  
• Prefect - for orchestration
  
• dbt - for transformation and data modeling
  
• Google Looker studio - for visualizations
  

Data Pipeline Architecture

Data Dictionary

Column	Description
r_id	Unique surrogate built using ride_id and started_at
ride_id	Unique ID Assigned to Each Divvy Trip
rideable_type	Type of bikes user can take out (Docked, Classic, Electric)
started_at	Start of Trip Date and Time
ended_at	End of Trip Date and Time
start_station_name	Name of start station
start_station_id	Unique Identification Number of Station the Trip Started at
end_station_name	Name Assigned to Station the Trip Ended at
end_station_id	Unique Identification Number of Station the Trip Ended at
start_lat	Latitude of the Start Station
start_lng	Longitude of the Start Station
end_lat	Latitude of the End Station
end_lng	Longitude of the End Station
member_casual	Field with Two Values Indicating Whether the Rider has a Divvy Membership or Paid with Credit Card(Casual)

Dashboard

Check out the interactive dashboard here

Reproduce it yourself

1. First fork this repo, and clone it to your local environment.

git clone https://github.com/Fozan-Talat/divvy-bikeshare-de-project.git

2. Setup your Google Cloud environment

• Create a Google Cloud Platform project
• Configure Identity and Access Management (IAM) for the service account, giving it the following privileges:
  • Viewer
  • Storage Admin
  • Storage Object Admin
  • BigQuery Admin
• Download the JSON credentials and save it, e.g. to ~/.gc/<credentials>
• Install the Google Cloud SDK
• Let the environment variable point to your GCP key, authenticate it and refresh the session token

export GOOGLE_APPLICATION_CREDENTIALS=<path_to_your_credentials>.json
gcloud auth activate-service-account --key-file $GOOGLE_APPLICATION_CREDENTIALS
gcloud auth application-default login

Check out this link for a video walkthrough.

3. Install all required dependencies into your environment

conda create -n divvy_project python=3.10
conda activate divvy_project
pip install -r requirements.txt

4. Setup your infrastructure

• Run the following commands to install Terraform - if you are using a different OS please choose the correct version here and exchange the download link and zip file name

sudo apt-get install unzip
cd ~/bin
wget https://releases.hashicorp.com/terraform/1.4.1/terraform_1.4.1_linux_amd64.zip
unzip terraform_1.4.1_linux_amd64.zip
rm terraform_1.4.1_linux_amd64.zip

• change the variables.tf file with your corresponding variables, I would recommend to leave the name of the dataset, table and bucket as they are; otherwise you need to change them in the prefect flows and dbt.
• To initiate, plan and apply the infrastructure, adjust and run the following Terraform commands

cd terraform/
terraform init
terraform plan -var="project=<your-gcp-project-id>"
terraform apply -var="project=<your-gcp-project-id>"

• Type 'yes' when prompted.

5. Setup your orchestration

• If you do not have a prefect workspace, sign-up for the prefect cloud and create a workspace here
• Create the prefect blocks via the cloud UI or adjust the variables in /prefect/prefect_blocks.py and run

python prefect/prefect_blocks.py

• To execute the flow, run the following commands in two different terminals

prefect agent start -q 'default'

python prefect/web_to_gcs_to_bq.py

• After running the flow web_to_gcs_to_bq.py you will find the data at divvy_data_raw.divvy_default_data, the flow will take around 45 mins to complete.

6. Data tranformation and modeling using dbt

cd into the dbt/divvy_project directory and run the following command

dbt build --var 'is_test_run: false'

You will get 4 tables in divvy_data_dbt data set

• dim_neighbourhoods
• divvy_stations_lookup
• facts_divvy
• stg_divvy_data

7. Data Vizualization and Dashboarding

• You can now query the data and connect it to looker to visualize the data, when connecting to data source use facts_divvy table to build the data source in looker, dont use partitioned table, as you wont get any data in your report.
• go to Looker Studio → create → BigQuery → choose your project, dataset & transformed table.