A rebuild of the Mexico air quality monitoring pipeline on a different stack — swapping Bruin’s declarative CLI for a dlt + dbt + Dagster combination, built as the capstone for the DataTalks Data Engineering Zoomcamp 2026. Same problem — centralizing OpenAQ readings from 306 stations across 121 Mexican cities into analytics-ready tables — solved with asset-based orchestration and dbt-native testing instead.
Architecture & Stack
- Ingestion → dlt 1.24 pipelines: batch loading from the OpenAQ S3 archive (
locationid/year/month-partitioned CSV.gz) plus the OpenAQ v3 API for station metadata, archiving each raw CSV.gz to GCS before it’s parsed into BigQuery; a Kafka-compatible Redpanda producer/consumer streams near-real-time measurements straight to BigQuery - Storage → Google BigQuery: 3-layer warehouse (
air_quality_raw→air_quality_staging→air_quality_marts), with a GCS bucket holding the raw archive - Transformation → dbt 1.11 (dbt-bigquery): staging model deduplicates via
ROW_NUMBER()and filters outliers, mart models apply ppm→µg/m³ unit conversion and WHO AQI categorization for PM2.5, enforced by 15 dbt tests (not_null,unique,accepted_values) - Orchestration → Dagster 1.13 software-defined assets (
dagster-dbt,dagster-dlt) wiring dlt ingestion straight into dbt lineage, with monthly partitions from2024-01-01to present and a scheduled run on the 1st of every month - Infrastructure → Terraform (GCS bucket + BigQuery datasets, shared with the Bruin sibling project), Docker Compose (Redpanda broker + console)
- CI/CD → GitHub Actions: dbt tests run on every push, dbt docs (lineage graph, model + test docs) auto-publish to GitHub Pages
Key Technical Achievements
End-to-end asset lineage in Dagster — openaq_locations and openaq_measurements (dlt ingestion assets) feed directly into stg_measurements, dim_stations, and fct_city_daily_aqi (dbt assets) as a single Dagster asset graph, so the whole batch pipeline runs and is observable from one scheduled job instead of stitched-together scripts.
Unit-normalized, WHO-classified mart layer — Pollutant-specific ppm→µg/m³ conversion factors (CO ×1145, NO2 ×1880, SO2 ×2620, O3 ×1960, NO ×1230, NOx ×1880) and WHO AQI thresholds for PM2.5 (Good/Moderate/Unhealthy for Sensitive Groups/Unhealthy/Very Unhealthy/Hazardous) are computed in dbt and backed by 15 passing schema tests.
Resilient batch + streaming ingestion — S3 retry logic and a Dagster retry policy on the batch side, combined with OpenAQ API rate-limit handling and a dead-letter queue on the Redpanda streaming consumer, so transient upstream failures don’t drop data or halt the pipeline.
CI-published documentation — GitHub Actions runs the full dbt test suite on every push and publishes the generated dbt docs site (interactive lineage graph, model and column descriptions) to GitHub Pages, keeping documentation in sync with the models automatically. Query results feed a Power BI dashboard tracking city-level AQI trends and WHO guideline compliance.