Thailand Air Quality Insights

Problem Statement

Thailand faces a recurring and severe public health challenge from high levels of PM2.5 air pollution, particularly during the dry "burning season" (January–April). This project builds an automated, end-to-end data pipeline to harvest official measurements from the Air4Thai API, providing a robust foundation for long-term air quality analysis, hotspot detection, and seasonal trend tracking.

Tech Stack

• Language: Python 3.12+, SQL
• Orchestration: Apache Airflow 3 (with Astronomer Cosmos)
• Data Transformation: dbt (Fused for BigQuery)
• Infrastructure as Code: Terraform
• Containerization: Docker & Docker Compose
• Cloud Provider: Google Cloud Platform (GCP)

Cloud

We utilize Google Cloud Platform for reliable and scalable data storage and analysis:

• Google Cloud Storage (GCS): Acts as our Data Lake for landing raw landing/processed PM2.5 payloads.
• BigQuery: Our primary Data Warehouse for high-performance SQL analytics.
• IAM: Managed service accounts with granular permissions (BigQuery Job User, Data Editor) for automated pipelines.

Data Ingestion

Orchestrated by Airflow, the ingestion pipeline:

1. Harvests PM2.5 and station metadata from the Air4Thai API.
2. Processes the JSON payloads into cleaned, partition-ready formats using Python.
3. Automatically uploads the data to synchronized GCS buckets for downstream consumption.
4. Manages BigQuery external tables to ensure zero-copy data availability.

Data Warehouse

The project follows a modern analytical warehouse architecture in BigQuery:

• Staging Layer: Raw data is ingested as external tables and standardized in terms of naming (e.g., measurement_date) and casing.
• Transformation Layer: dbt provides a modular transformation process, implementing robust surrogate keys (hashing date + station) to handle non-unique daily station dumps.

Transformation

Built with dbt, the transformation logic includes:

• Surrogate Keys: Ensure row-level uniqueness in analytical fact tables.
• AQI Categorization: Custom SQL macros to categorize PM2.5 levels (e.g., 'Good', 'Moderate', 'Hazardous') based on national standards.
• Data Quality: Automated tests for not_null, unique, and accepted_range (0–1000 µg/m³) for PM2.5 concentrations.

Dashboard

The marts layer provides pre-aggregated high-level metrics ready for BI tools:

• Top 10 Worst Provinces: Ranking regions by year-to-date average pollution.
• Monthly PM2.5 Trend: Longitudinal analysis of country-wide air quality.
• Seasonal Analysis: Comparative performance during "Burning" vs. "Green" seasons.

Link to Dashboard

Reproducibility

The entire stack is designed for portability:

1. Infrastructure: Navigate to 01_terraform/ and run terraform init / apply to provision GCP resources.
2. Orchestration: Use docker compose up --build in 02_airflow/ to launch the local Airflow/dbt environment.
3. dbt Integration: Astronomer Cosmos automatically maps the dbt project in 03_dbt/ to Airflow tasks without manual profile management.

Acknowledgement

This project was developed as the capstone for the Data Engineering Zoomcamp. I would like to express my gratitude to Alexey Grigorev and the entire DataTalks.Club community for their invaluable guidance and support throughout the course.