Skip to content

About

Portfolio project: event-driven ESG air quality pipeline on GCP. Ingests OpenWeather API data via Cloud Scheduler, Pub/Sub, Eventarc and Cloud Run into GCS and BigQuery, with Dataform models and tests. Infrastructure as code with Terraform and CI/CD with Cloud Build.

Topics

Resources

Stars

1 star

Watchers

0 watching

Forks

Repository files navigation

ESG Air Quality Ingestion: GCP Pipeline

Study project, actively evolving.

A study project that builds a simple ESG data ingestion pipeline (air quality) on Google Cloud, focused on infrastructure as code, CI/CD and cloud best practices.


Overview

Goal: periodically fetch air quality data from the public OpenWeather Air Pollution API, store the raw JSON in Cloud Storage, register the ingestion event in BigQuery (raw layer), and transform the data with Dataform (staging/mart + tests), orchestrated by:

  • Cloud Scheduler → triggers a job on a defined schedule
  • Pub/Sub → receives the trigger message (ingestion-trigger-<env>)
  • Eventarc → listens to the topic and calls the HTTP service
  • Cloud Run → runs the ingestion container (Flask/Python)
  • GCS (raw bucket) → stores the raw JSON
  • BigQuery (raw) → records the ingestion (dataset <env>_raw)
  • Dataform (ELT) → builds staging/mart tables and runs data quality assertions
  • Cloud Build (CI/CD) → automatically runs Dataform on pushes to the dev branch

All infrastructure is provisioned with Terraform, and automations are triggered through Cloud Build.


Main components

1. Ingestion app (ingestion/)

  • main.py Flask service that:

    • Receives the Eventarc event (Pub/Sub message).

    • Calls the OpenWeather Air Pollution API using:

      • AIR_POLLUTION_API_KEY
      • AIR_LAT / AIR_LON (default: São Paulo)
    • Saves the raw JSON to the bucket:

      • gs://elt-project-esg-raw-data-<env>/air_pollution/<env>/<timestamp>.json
    • Inserts a row into BigQuery:

      • Dataset: <env>_raw
      • Table: air_pollution_raw (name configurable via environment variable)
  • Dockerfile Python 3.10 container that:

    • Installs dependencies from requirements.txt
    • Runs gunicorn main:app (ready for Cloud Run)
  • requirements.txt Main dependencies:

    • Flask
    • gunicorn
    • requests
    • google-cloud-storage
    • google-cloud-bigquery
  • scripts/test_air_pollution.py Simple script to test the OpenWeather API locally using the AIR_POLLUTION_API_KEY environment variable.


2. Infrastructure with Terraform (terraform/)

  • backend.tf Configures the remote backend on Google Cloud Storage to store terraform.tfstate (e.g. bucket elt-project-esg-tfstate).

  • variables.tf Generic variables to support multiple environments:

    • project_id
    • region
    • env (e.g. dev, prod)
    • container_image (Docker image used by Cloud Run)
  • envs/dev.tfvars and envs/prod.tfvars Per-environment configuration (project, region and environment name).

  • main.tf Provisions the core resources:

    • Enables the APIs: Run, Pub/Sub, Cloud Build, BigQuery, Dataform, Storage, Artifact Registry, Eventarc.

    • Raw bucket: elt-project-esg-raw-data-<env>.

    • BigQuery:

      • Dataset <env>_raw
      • Dataset <env>_staging
    • Pub/Sub:

      • Topic ingestion-trigger-<env>.
    • Artifact Registry:

      • Docker repository ingestion-repo-<env>.
    • Dedicated service account for Cloud Run:

      • Permissions for GCS, BigQuery, Pub/Sub and Cloud Run invocation.
    • Cloud Run:

      • Service ingestion-service-<env> using container_image.
    • Eventarc trigger:

      • Connects the Pub/Sub topic to the Cloud Run service.

3. ELT transformations with Dataform (definitions/ + workflow_settings.yaml)

The project uses Dataform (BigQuery-native) to transform raw data into analysis-ready tables and to run data quality tests.

  • Config (workflow_settings.yaml)

    • defaultProject: elt-project-esg
    • defaultLocation: southamerica-east1
    • defaultDataset: dev_staging
    • defaultAssertionDataset: dev_assertions
    • vars.env: dev
  • Models

    • definitions/stg_api_raw.sqlx: reads dev_raw.air_pollution_raw and exposes ingested_at + payload
    • definitions/mart_air_quality.sqlx: parses the JSON into analytical columns (AQI, pollutant components, etc.)
    • definitions/assert_air_quality_measure_ts_not_null.sqlx: assertion that validates a critical field (returns failing rows, if any)

Datasets (dev):

  • Raw: dev_raw
  • Staging: dev_staging
  • Mart: dev_mart
  • Assertions: dev_assertions

4. CI/CD with Cloud Build (automatic Dataform execution)

  • Trigger

    • Runs on pushes to the dev branch using cloudbuild.yaml.
  • cloudbuild.yaml

    • Triggers Dataform through its REST API (no dependency on gcloud dataform).

    • Flow:

      1. Creates a compilationResult pointing to gitCommitish=dev

      2. Creates a workflowInvocation using a dedicated Dataform service account

        • sa-dataform-dev@elt-project-esg.iam.gserviceaccount.com
        • format expected by Dataform: projects/-/serviceAccounts/<email>

Note: in Cloud Build, shell variables must be escaped with $$ so they are not treated as Cloud Build substitutions.


5. Dataform service account (dev)

To run Dataform with "strict act-as" enabled, a dedicated service account was created with minimal permissions:

  • SA: sa-dataform-dev@elt-project-esg.iam.gserviceaccount.com

  • Project-level roles:

    • roles/bigquery.jobUser
    • roles/bigquery.dataEditor
  • Impersonation permissions (actAs/token) for the Dataform service agent:

    • roles/iam.serviceAccountUser
    • roles/iam.serviceAccountTokenCreator

6. Cloud Scheduler (dev)

Job created manually for the dev environment, scheduling the trigger through Pub/Sub:

gcloud scheduler jobs create pubsub ingest-job-dev \
  --location="southamerica-east1" \
  --schedule="0 7 * * 1" \
  --topic="ingestion-trigger-dev" \
  --message-body="Start ingestion" \
  --time-zone="America/Sao_Paulo"

About

Portfolio project: event-driven ESG air quality pipeline on GCP. Ingests OpenWeather API data via Cloud Scheduler, Pub/Sub, Eventarc and Cloud Run into GCS and BigQuery, with Dataform models and tests. Infrastructure as code with Terraform and CI/CD with Cloud Build.

Topics

Resources

Stars

1 star

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages