Air Quality Monitor

Automatic synchronization of air quality data — performs collection, transformation and update directly in TiDB/MySQL.

Comment

Inspiration Project created to automate the ingestion of public air quality data from Montreal and facilitate analysis and observability in MySQL/TiDB-compatible databases.

What it does Periodically fetches open data, transforms it into a tabular table (Polars), and upserts it into the target database, recording audit artifacts (discarded rows, summary of the last run).

How we built it Implemented in Python with Polars for transformation, SQLAlchemy for persistence, APScheduler for scheduling, and Docker for packaging; configuration is done via environment variables (.env).

Challenges we ran into Handling differences in SQL dialects (TiDBvs. SQLite), missing DB drivers in minimal images, and volume mounting/permissions behavior on Windows/WSL.

Accomplishments we're proud of Creating a lightweight, testable pipeline with SQLite for smoke tests, dialect-aware upsert, health check via docker-compose, and execution summary (last_run_.json) for observability.

What we learned Container environments require careful handling of paths, drivers, and permissions; local testing with an alternative (SQLite) speeds up iteration without relying on the remote DB.

What's next for Air Quality Monitor Improving observability with HTTP endpoints and metrics, integrating secure secrets management (Docker/K8s secrets or Vault), and adding CI that builds the image and runs the smoke test automatically.

Built With

  • .dockerignore-healthcheck-via-docker-compose-(checks-artifacts/last-run-*.json)-artifacts-dir-for-audit-files-(discarded-rows
  • datetime
  • docker
  • docker-compose.yml
  • http
  • logging
  • mysql
  • mysql-connector-python-(fallbacks-handled)-scheduling-&-orchestration-apscheduler-(daily-job-scheduling)-docker-/-docker-compose-(containerization-&-deployment)-entrypoint-script-+-.env-(python-dotenv)-for-configuration-dev-/-ops-dockerfile
  • os)-persistence-/-db-sqlalchemy-(orm-/-engine)-target-db:-tidb-/-mysql-compatible-local-smoke-test:-sqlite-(pysqlite)-db-drivers-supported:-pymysql
  • polar
  • python
  • sqlalchemy
  • sqlite
Share this project:

Updates