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.
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
devbranch
All infrastructure is provisioned with Terraform, and automations are triggered through Cloud Build.
-
main.pyFlask service that:-
Receives the Eventarc event (Pub/Sub message).
-
Calls the OpenWeather Air Pollution API using:
AIR_POLLUTION_API_KEYAIR_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)
- Dataset:
-
-
DockerfilePython 3.10 container that:- Installs dependencies from
requirements.txt - Runs
gunicorn main:app(ready for Cloud Run)
- Installs dependencies from
-
requirements.txtMain dependencies:- Flask
- gunicorn
- requests
- google-cloud-storage
- google-cloud-bigquery
-
scripts/test_air_pollution.pySimple script to test the OpenWeather API locally using theAIR_POLLUTION_API_KEYenvironment variable.
-
backend.tfConfigures the remote backend on Google Cloud Storage to storeterraform.tfstate(e.g. bucketelt-project-esg-tfstate). -
variables.tfGeneric variables to support multiple environments:project_idregionenv(e.g.dev,prod)container_image(Docker image used by Cloud Run)
-
envs/dev.tfvarsandenvs/prod.tfvarsPer-environment configuration (project, region and environment name). -
main.tfProvisions 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
- Dataset
-
Pub/Sub:
- Topic
ingestion-trigger-<env>.
- Topic
-
Artifact Registry:
- Docker repository
ingestion-repo-<env>.
- Docker repository
-
Dedicated service account for Cloud Run:
- Permissions for GCS, BigQuery, Pub/Sub and Cloud Run invocation.
-
Cloud Run:
- Service
ingestion-service-<env>usingcontainer_image.
- Service
-
Eventarc trigger:
- Connects the Pub/Sub topic to the Cloud Run service.
-
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-esgdefaultLocation:southamerica-east1defaultDataset:dev_stagingdefaultAssertionDataset:dev_assertionsvars.env:dev
-
Models
definitions/stg_api_raw.sqlx: readsdev_raw.air_pollution_rawand exposesingested_at+payloaddefinitions/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
-
Trigger
- Runs on pushes to the
devbranch usingcloudbuild.yaml.
- Runs on pushes to the
-
cloudbuild.yaml-
Triggers Dataform through its REST API (no dependency on
gcloud dataform). -
Flow:
-
Creates a
compilationResultpointing togitCommitish=dev -
Creates a
workflowInvocationusing a dedicated Dataform service accountsa-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.
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.jobUserroles/bigquery.dataEditor
-
Impersonation permissions (actAs/token) for the Dataform service agent:
roles/iam.serviceAccountUserroles/iam.serviceAccountTokenCreator
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"