DataTalks.Club's Data Engineering Zoomcamp Project

Table of Contents

Project Overview
Dataset
Technologies
Files and What They Do
Instruction on Running the Project
References

Project Overview

This project builds an automated end-to-end data pipeline that aims to get the livestream of train movement data and analyze the train operating company's performance. The source of streaming data comes from the UK's Network Rail company provided through an ActiveMQ interface. A train movement message is sent whenever a train arrives, passes or departs a location. It also records the time at which the event happens.

In this project, we first extract the live stream of train movement messages from the Network Rail's ActiveMQ endpoint and stream the messages into Kafka. We then consume and put them into a data lake (MinIO). After that we schedule a data pipeline (Airflow) to run daily to load the data to a data warehouse (Google BigQuery). Later on, we transform the data in the warehouse using dbt. Finally, once the data is cleaned and transformed, we can monitor and analyze the data on a dashboard (Google Data Studio).

Dataset

In this project, our dataset is the public data feed provided by Network Rail.

Technologies

Apache Airflow for orchestrating workflow
Apache Kafka for stream processing
MinIO for data lake storage
dbt for data transformation
Google BigQuery for data warehousing and analysis
Google Data Studio for dashboard
Terraform for provisioning BigQuery dataset
Docker for running services on local machine

Files and What They Do

Name	Description
`mnt/dags/load_networkrail_movement_data_to_bigquery.py`	An Airflow DAG file that runs the ETL data pipeline on Network Rail train movement data and load them to BigQuery
`networkrail/`	A dbt project used to clean and transform the train movement data
`playground/`	A folder that contains code for testing ideas
`terraform/`	A Terraform project used to provision the Google BigQuery dataset
`.env.example`	A environment example file that contains the environment variables we use in this project
`docker-compose.yaml`	A Docker Compose file that runs the Confluent platform (Kafka and friends), an Airflow instance, and MinIO
`Makefile`	A Makefile file for running commands
`get_networkrail_movements.py`	A Python script that get the live stream data through the Network Rail's ActiveMQ interface
`consume_networkrail_movements_to_data_lake.py`	A Python script that consumes the messages from Kafka and puts them into a data lake storage
`README.md`	README file that provides the discussion on this project
`requirements.txt`	A file that contains Python package dependencies used in this project
`secrets.json.example`	A secret file that contains the Network Rail's username and password

Instruction on Running the Project

Here is the list of services we use in this project:

Confluent Control Center: http://localhost:9021
Airflow UI: http://localhost:8080
MinIO Console: http://localhost:9001

We can start all services by running the commands below:

make setup
make up

To shutdown all services, run:

make down

Getting Started

Before we can get the Network Rail data feed, we'll need to register a new account on the Network Rail website first.

After we have the account, let's set up a virtual environment and install package dependencies:

python -m venv ENV
source ENV/bin/activate
pip install -r requirements.txt

Note: We need to install the Apache Kafka C/C++ Library named librdkafka first.

Once we've installed the package dependencies, we can run the following command to get the Network Rail livestream data and produce messages to the Kafka:

python get_networkrail_movements.py

Before we can consume the messages from Kafka, we need to set up a service account on MinIO first, so we can put the data into a data lake. Please see the Steps to Set Up a Service Account on MinIO section.

After we have the service account, we'll save the AWS access key ID and AWS secret access key from MinIO to the file .env. Here we have an example env file, so we can use it as a template.

cp env.example .env

To consume the messages from Kafka, run the following commands:

export $(cat .env)
python consume_networkrail_movements_to_data_lake.py

All the messages should be in the data lake (MinIO) by now.

We can go to the Airflow UI and manually trigger the data pipeline to load the data to the data warehouse (Google BigQuery) then wait for the data to show up on the dashboard (Google Data Studio). See the live dashboard here.

References

Kafka Topic on Confluent Control Center

The screenshot below shows the Kafka topic on Confluent control center.

Data Pipeline on Airflow

The screenshots below show the data pipeline on Airflow.

Airflow S3 Connection to MinIO

Connection Name: minio or any name you like
Connection Type: S3
Login: <replace_here_with_your_minio_access_key>
Password: <replace_here_with_your_minio_secret_key>
Extra: a JSON object with the following properties:
```
{
  "host": "http://minio:9000"
}
```

Note: If we were using AWS S3, we don't need to specify the host in the extra.

Data Models on Google BigQuery

The screenshot below shows the data models on Google BigQuery.

Network Rail TOC's Performance Dashboard

The screenshot below shows the dashboard to monitor the Network Rail train operating company (TOC)'s performance. View the live dashboard here: Network Rail Train Operating Company's Performance.

Steps to Set Up a Service Account on MinIO

The screenshots below show how to set up a service account on MinIO. Airflow needs it in order to get data from the MinIO storage.

Steps to Set Up a Service Account on Google Cloud Platform (GCP)

The screenshots belwo show how to set up a service account on GCP. This service account is required for Airflow to load data to the BigQuery as well as dbt to transform data in the BigQuery.