05 - Airflow on Compute Engine

20 min read

Airflow on Compute Engine

Archived demo boundary

The end-to-end Airflow demo described here was validated on 2026-04-13, not rerun during this 2026-04-15 refresh. Current read-only checks against the replacement workspace returned Listed 0 items. for both gcloud run jobs list --project=dagflow-poc --region=europe-west1 and gcloud run services list --project=dagflow-poc --region=europe-west1, so there is no comparable live Cloud Run estate available to replay the pipeline honestly.

Treat the commands, outputs, and DAG chronology below as a historically verified demo record. This refresh focused on preserving the operator knowledge, clarifying boundaries, and keeping the note explicit about what is no longer live.

Conceptual Model

flowchart TD
    L["Local workstation<br/>gcloud + IAP SSH"] --> VM["Compute Engine VM<br/>stoxx-airflow<br/>Ubuntu 22.04<br/>10.132.0.9"]
    VM --> AF["Airflow 3.2.0<br/>Docker Compose"]
    AF --> DAG["DAG: stoxx_stage_yfinance"]
    DAG --> CR["Cloud Run Job<br/>stoxx-stage-fetch"]
    CR --> YF["yfinance API"]
    CR --> GCS["GCS bucket<br/>gs://stoxx-stage-bucket"]
    GCS --> OBJ1["stage/*.json"]
    GCS --> OBJ2["dimensions/*.json"]
    GCS --> OBJ3["pulse/*.json"]
    GCS --> OBJ4["manifests/stoxx-stage-fetch/*.json"]

Provision the Stage Bucket

The first shared dependency is the raw stage bucket. Uniform bucket-level access and public access prevention keep the bucket private and predictable for the demo.

Create the regional stage bucket in europe-west1.

gcloud storage buckets create gs://stoxx-stage-bucket \
  --project=bq-wh-nb \
  --location=europe-west1 \
  --uniform-bucket-level-access \
  --public-access-prevention

Validate the bucket configuration after creation.

gcloud storage buckets describe gs://stoxx-stage-bucket
creation_time: 2026-04-13T13:39:24+0000
location: EUROPE-WEST1
name: stoxx-stage-bucket
public_access_prevention: enforced
storage_url: gs://stoxx-stage-bucket/
uniform_bucket_level_access: true

Create the Airflow VM

The VM runs privately behind IAP only. It reuses the existing project service account bq-wh-sa@bq-wh-nb.iam.gserviceaccount.com and relies on Cloud NAT for outbound package installs and Docker image pulls.

Create the private Ubuntu VM for Airflow.

gcloud compute instances create stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --machine-type=e2-standard-2 \
  --image-family=ubuntu-2204-lts \
  --image-project=ubuntu-os-cloud \
  --boot-disk-size=30GB \
  --boot-disk-type=pd-balanced \
  --no-address \
  --service-account=bq-wh-sa@bq-wh-nb.iam.gserviceaccount.com \
  --scopes=https://www.googleapis.com/auth/cloud-platform \
  --metadata="enable-oslogin=TRUE" \
  --tags="airflow,iap-ssh" \
  --labels="app=stoxx-airflow,env=dev"

Confirm the final VM shape.

gcloud compute instances describe stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --format="yaml(name,status,zone,machineType,networkInterfaces,serviceAccounts,tags,labels)"
labels:
  app: stoxx-airflow
  env: dev
machineType: .../machineTypes/e2-standard-2
name: stoxx-airflow
networkInterfaces:
- networkIP: 10.132.0.9
serviceAccounts:
- email: bq-wh-sa@bq-wh-nb.iam.gserviceaccount.com
status: RUNNING
tags:
  items:
  - airflow
  - iap-ssh
zone: .../zones/europe-west1-b

Open the first IAP SSH session and verify the guest OS.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="hostname && lsb_release -ds"
stoxx-airflow
Ubuntu 22.04.5 LTS

Install Docker Engine and Compose

Airflow runs entirely in containers on the VM. Docker Engine and the Compose plugin are installed directly on Ubuntu and the OS Login user is added to the docker group.

Single-VM Docker Compose is a lab pattern

This layout is intentionally optimized for demo bring-up and operator visibility, not for production-grade Airflow availability. Docker Compose on one VM keeps the stack understandable, but it also keeps the scheduler, worker, metadata database, and broker in one failure domain.

Install Docker Engine, Compose, and add the VM user to the docker group.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="
    sudo apt-get update &&
    sudo apt-get install -y ca-certificates curl gnupg lsb-release &&
    sudo install -m 0755 -d /etc/apt/keyrings &&
    curl -fsSL https://download.docker.com/linux/ubuntu/gpg |
      sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg &&
    sudo chmod a+r /etc/apt/keyrings/docker.gpg &&
    echo 'deb [arch='\"'\"'$(dpkg --print-architecture)'\"'\"' signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu '\"'\"'$(. /etc/os-release && echo $VERSION_CODENAME)'\"'\"' stable' |
      sudo tee /etc/apt/sources.list.d/docker.list > /dev/null &&
    sudo apt-get update &&
    sudo apt-get install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin &&
    sudo systemctl enable --now docker &&
    sudo usermod -aG docker $USER
  "

Verify Docker from a fresh SSH session.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="id && docker --version && docker compose version"
uid=1137701540(alexper_recovery_gmail_com) ... groups=...,999(docker)
Docker version 29.4.0, build 9d7ad9f
Docker Compose version v5.1.2

Build the Airflow App

The Airflow app lives on the VM under /home/alexper_recovery_gmail_com/app and is synced from the local working copy in .codex-temp/airflow-vm. The final app includes:

  • a custom image built from apache/airflow:3.2.0
  • apache-airflow-providers-google added with Airflow’s official constraints
  • a single DAG, stoxx_stage_yfinance, which calls CloudRunExecuteJobOperator
  • environment variables GCP_PROJECT_ID, GCP_REGION, STAGE_BUCKET, and STAGE_FETCH_JOB

The custom image installs the Google provider under the Airflow 3.2 constraints set.

FROM apache/airflow:3.2.0
 
COPY requirements.txt /tmp/requirements.txt
 
RUN AIRFLOW_VERSION=$(python -c "from airflow import __version__; print(__version__)") \
    && PYTHON_VERSION=$(python -c "import sys; print(f'{sys.version_info.major}.{sys.version_info.minor}')") \
    && CONSTRAINT_URL="https://raw.githubusercontent.com/apache/airflow/constraints-${AIRFLOW_VERSION}/constraints-${PYTHON_VERSION}.txt" \
    && pip install --no-cache-dir "apache-airflow==${AIRFLOW_VERSION}" -r /tmp/requirements.txt --constraint "${CONSTRAINT_URL}"

The demo DAG is intentionally small and manual-trigger only.

with DAG(
    dag_id="stoxx_stage_yfinance",
    description="Fetch STOXX bronze-stage JSON from yfinance into GCS via Cloud Run",
    schedule=None,
    catchup=False,
    max_active_runs=1,
) as dag:
    CloudRunExecuteJobOperator(
        task_id="fetch_bronze_stage_into_gcs",
        project_id=PROJECT_ID,
        region=REGION,
        job_name=JOB_NAME,
        deferrable=False,
    )

First compose boot changes ownership of app/dags

After the initial Airflow bring-up, the host-mounted dags/ directory was owned by UID 50000 (the Airflow container user), which caused the next gcloud compute scp of the DAG file to fail with permission denied.

Fix: sudo chown -R alexper_recovery_gmail_com:alexper_recovery_gmail_com /home/alexper_recovery_gmail_com/app/dags before copying the DAG file.

Sync the Airflow app files to the VM and rebuild the stack.

gcloud compute scp \
  "C:\Users\aperi\My Drive\VAULT\.codex-temp\airflow-vm\Dockerfile" \
  "C:\Users\aperi\My Drive\VAULT\.codex-temp\airflow-vm\requirements.txt" \
  "C:\Users\aperi\My Drive\VAULT\.codex-temp\airflow-vm\docker-compose.yaml" \
  stoxx-airflow:/home/alexper_recovery_gmail_com/app/ \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap
gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="
    sudo chown -R alexper_recovery_gmail_com:alexper_recovery_gmail_com \
      /home/alexper_recovery_gmail_com/app/dags
  "
gcloud compute scp \
  "C:\Users\aperi\My Drive\VAULT\.codex-temp\airflow-vm\dags\stoxx_stage_yfinance.py" \
  stoxx-airflow:/home/alexper_recovery_gmail_com/app/dags/ \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap
gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="
    cd /home/alexper_recovery_gmail_com/app &&
    docker compose down &&
    docker compose build &&
    docker compose up airflow-init &&
    docker compose up -d &&
    docker compose ps
  "
app-airflow-apiserver-1       stoxx-airflow:3.2.0   Up ... (healthy)
app-airflow-dag-processor-1   stoxx-airflow:3.2.0   Up ... (healthy)
app-airflow-scheduler-1       stoxx-airflow:3.2.0   Up ... (healthy)
app-airflow-triggerer-1       stoxx-airflow:3.2.0   Up ... (healthy)
app-airflow-worker-1          stoxx-airflow:3.2.0   Up ... (healthy)
app-postgres-1                postgres:16           Up ... (healthy)
app-redis-1                   redis:7.2-bookworm    Up ... (healthy)

Create Artifact Registry and Build the Fetch Image

The yfinance fetcher is packaged separately as a Cloud Run job image. The implementation lives in .codex-temp/stoxx-stage-fetch and writes the following object families into GCS:

  • dimensions/{prefix}_dim.json
  • stage/{prefix}_ohlcv.json
  • stage/{prefix}_signals_daily.json
  • stage/{prefix}_signals_quarterly.json
  • pulse/{prefix}_tickers.json
  • pulse/{prefix}_pulse.json
  • manifests/stoxx-stage-fetch/{timestamp}.json
  • manifests/stoxx-stage-fetch/latest.json

The fetcher intentionally deduplicates the symbol fetch workload across the three retained STOXX index definitions. On 2026-04-13, the live manifest reports symbol_count: 150, which matches the three 50-name index universes after oil_20 was removed from scope.

Enable the required service APIs.

gcloud services enable \
  run.googleapis.com \
  cloudbuild.googleapis.com \
  artifactregistry.googleapis.com \
  --project=bq-wh-nb
Operation "...acf.p2-348557092514-..." finished successfully.

Create a Docker repository for demo images.

gcloud artifacts repositories create stoxx-demo \
  --repository-format=docker \
  --location=europe-west1 \
  --description="STOXX demo containers" \
  --project=bq-wh-nb
Created repository [stoxx-demo].

Build and push the Cloud Run image with Cloud Build.

gcloud builds submit \
  "C:\Users\aperi\My Drive\VAULT\.codex-temp\stoxx-stage-fetch" \
  --project=bq-wh-nb \
  --tag europe-west1-docker.pkg.dev/bq-wh-nb/stoxx-demo/stoxx-stage-fetch:20260413-1
Successfully tagged europe-west1-docker.pkg.dev/bq-wh-nb/stoxx-demo/stoxx-stage-fetch:20260413-1
...
STATUS: SUCCESS

Deploy the Cloud Run Job

The job runs with the same project service account as the VM. A 30-minute timeout is excessive for the current payload size, but it leaves room for yfinance variability during the live demo.

Deploy the Cloud Run job.

gcloud run jobs deploy stoxx-stage-fetch \
  --project=bq-wh-nb \
  --region=europe-west1 \
  --image=europe-west1-docker.pkg.dev/bq-wh-nb/stoxx-demo/stoxx-stage-fetch:20260413-1 \
  --service-account=bq-wh-sa@bq-wh-nb.iam.gserviceaccount.com \
  --tasks=1 \
  --max-retries=0 \
  --task-timeout=1800 \
  --cpu=2 \
  --memory=2Gi \
  --set-env-vars "GCP_PROJECT_ID=bq-wh-nb" \
  --set-env-vars "STAGE_BUCKET=stoxx-stage-bucket" \
  --set-env-vars "OHLCV_LOOKBACK_DAYS=10" \
  --set-env-vars "JOB_NAME=stoxx-stage-fetch" \
  --set-env-vars "INFO_WORKERS=6" \
  --set-env-vars "HISTORY_WORKERS=4" \
  --set-env-vars "YF_RETRIES=5"
Job [stoxx-stage-fetch] has successfully been deployed.

Describe the final job configuration.

gcloud run jobs describe stoxx-stage-fetch \
  --project=bq-wh-nb \
  --region=europe-west1
+ Job stoxx-stage-fetch in region europe-west1
Executed 4 times
Last executed 2026-04-13T14:28:27.099685Z with execution stoxx-stage-fetch-s2b48
Container Image: europe-west1-docker.pkg.dev/bq-wh-nb/stoxx-demo/stoxx-stage-fetch:20260413-1
Memory: 2Gi
CPU: 2
Env vars:
  GCP_PROJECT_ID   bq-wh-nb
  HISTORY_WORKERS  4
  INFO_WORKERS     6
  JOB_NAME         stoxx-stage-fetch
  OHLCV_LOOKBACK_DAYS 10
  STAGE_BUCKET     stoxx-stage-bucket
  YF_RETRIES       5
Service account: bq-wh-sa@bq-wh-nb.iam.gserviceaccount.com

Configure Airflow to Invoke Cloud Run

Two issues surfaced during the first Airflow task runs:

  1. google_cloud_default did not exist in the Airflow metadata DB.
  2. The VM service account lacked run.jobs.run on the Cloud Run job.

Both fixes are required on a fresh Airflow 3.2 deployment.

Add the Google connection

Create the default Google connection and rely on Application Default Credentials from the VM service account.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="
    cd /home/alexper_recovery_gmail_com/app &&
    docker compose exec -T airflow-worker \
      airflow connections add google_cloud_default \
      --conn-uri 'google-cloud-platform://'
  "
Successfully added `conn_id`=google_cloud_default : google-cloud-platform://

Verify the stored connection.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="
    cd /home/alexper_recovery_gmail_com/app &&
    docker compose exec -T airflow-worker airflow connections get google_cloud_default
  "
id | conn_id              | conn_type             | ... | extra_dejson | get_uri
1  | google_cloud_default | google_cloud_platform | ... | {}           | google-cloud-platform://

Grant Cloud Run execution permission

The first operator run failed with:

google.api_core.exceptions.PermissionDenied:
403 Permission 'run.jobs.run' denied on resource
'projects/bq-wh-nb/locations/europe-west1/jobs/stoxx-stage-fetch'

The VM service account already had broad storage and BigQuery permissions, but not the Cloud Run job execution permissions required by CloudRunExecuteJobOperator.

Grant roles/run.developer to the VM service account.

gcloud projects add-iam-policy-binding bq-wh-nb \
  --member="serviceAccount:bq-wh-sa@bq-wh-nb.iam.gserviceaccount.com" \
  --role="roles/run.developer" \
  --condition=None
Updated IAM policy for project [bq-wh-nb].

Persist the Stack Across Reboots

Airflow must survive VM restarts. A systemd unit wraps docker compose up -d and restores the stack at boot.

Install and enable the systemd unit.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="
    cat <<'EOF' | sudo tee /etc/systemd/system/stoxx-airflow.service >/dev/null
[Unit]
Description=STOXX Airflow Docker Compose Stack
Requires=docker.service
After=docker.service network-online.target
Wants=network-online.target
 
[Service]
Type=oneshot
RemainAfterExit=yes
User=alexper_recovery_gmail_com
Group=docker
WorkingDirectory=/home/alexper_recovery_gmail_com/app
Environment=HOME=/home/alexper_recovery_gmail_com
ExecStart=/usr/bin/docker compose up -d --remove-orphans
ExecStop=/usr/bin/docker compose down
TimeoutStartSec=0
 
[Install]
WantedBy=multi-user.target
EOF
    sudo systemctl daemon-reload
    sudo systemctl enable --now stoxx-airflow.service
    systemctl status stoxx-airflow.service --no-pager --full
  "
Loaded: loaded (/etc/systemd/system/stoxx-airflow.service; enabled; vendor preset: enabled)
Active: active (exited) since Mon 2026-04-13 14:31:09 UTC
ExecStart=/usr/bin/docker compose up -d --remove-orphans (code=exited, status=0/SUCCESS)

Validate the Final End-to-End Flow

Validation was done in two stages: first by running the Cloud Run job directly, then by running the Airflow task that triggers the same job.

Direct Cloud Run test

Execute the job once directly from gcloud.

gcloud run jobs execute stoxx-stage-fetch \
  --project=bq-wh-nb \
  --region=europe-west1 \
  --wait
Execution [stoxx-stage-fetch-rb465] has successfully completed.

Airflow-triggered test

The first airflow dags test surfaced the missing connection and IAM issues above. After both fixes, the clean operator validation was a direct task test from the Airflow worker container.

Run the Airflow task that triggers Cloud Run.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="
    cd /home/alexper_recovery_gmail_com/app &&
    docker compose exec -T airflow-worker \
      airflow tasks test stoxx_stage_yfinance fetch_bronze_stage_into_gcs 2026-04-13T15:00:00+00:00
  "
Getting connection using `google.auth.default()` since no explicit credentials are provided.
...
Task instance state updated ... new_state=success

Confirm that the operator-created Cloud Run execution exists and was run by the VM service account.

gcloud run jobs executions list \
  --job=stoxx-stage-fetch \
  --project=bq-wh-nb \
  --region=europe-west1 \
  --limit=3
JOB                EXECUTION                REGION        COMPLETE  CREATED                  RUN BY
stoxx-stage-fetch  stoxx-stage-fetch-s2b48  europe-west1  1 / 1     2026-04-13 14:28:27 UTC  bq-wh-sa@bq-wh-nb.iam.gserviceaccount.com
stoxx-stage-fetch  stoxx-stage-fetch-xtrwd  europe-west1  1 / 1     2026-04-13 14:26:01 UTC  bq-wh-sa@bq-wh-nb.iam.gserviceaccount.com
stoxx-stage-fetch  stoxx-stage-fetch-rb465  europe-west1  1 / 1     2026-04-13 14:06:27 UTC  alexper.recovery@gmail.com

GCS landing validation

The final successful Airflow-triggered run updated the manifest at 2026-04-13T17:31:42Z and produced a clean errors: [] result.

Read the latest manifest written by Cloud Run.

gcloud storage cat gs://stoxx-stage-bucket/manifests/stoxx-stage-fetch/latest.json
{
    "job_name": "stoxx-stage-fetch",
    "generated_at": "2026-04-13T17:31:42Z",
    "bucket": "stoxx-stage-bucket",
    "project_id": "bq-wh-nb",
    "gcs_prefix": "",
    "lookback_days": 10,
    "index_count": 3,
    "symbol_count": 150,
    "errors": []
}

List the stage JSON objects and timestamps.

gcloud storage ls -l gs://stoxx-stage-bucket/stage/*.json
136332  2026-04-13T17:31:42Z  gs://stoxx-stage-bucket/stage/eurostoxx50_ohlcv.json
42635   2026-04-13T17:31:43Z  gs://stoxx-stage-bucket/stage/eurostoxx50_signals_daily.json
45500   2026-04-13T17:31:43Z  gs://stoxx-stage-bucket/stage/eurostoxx50_signals_quarterly.json
137842  2026-04-13T17:31:43Z  gs://stoxx-stage-bucket/stage/stoxxasia50_ohlcv.json
42904   2026-04-13T17:31:43Z  gs://stoxx-stage-bucket/stage/stoxxasia50_signals_daily.json
45447   2026-04-13T17:31:43Z  gs://stoxx-stage-bucket/stage/stoxxasia50_signals_quarterly.json
136833  2026-04-13T17:31:43Z  gs://stoxx-stage-bucket/stage/stoxxusa50_ohlcv.json
42736   2026-04-13T17:31:43Z  gs://stoxx-stage-bucket/stage/stoxxusa50_signals_daily.json
45476   2026-04-13T17:31:43Z  gs://stoxx-stage-bucket/stage/stoxxusa50_signals_quarterly.json
TOTAL: 9 objects, 675705 bytes (659.87kiB)

The live bucket now exposes the three retained STOXX indices only:

  • 9 under stage/
  • 3 under dimensions/
  • 6 under pulse/
  • manifest history under manifests/stoxx-stage-fetch/

No oil20 objects remain anywhere under gs://stoxx-stage-bucket.

Extend the DAG to SQL Server, BigQuery, and Firestore

After the initial fetch-only validation, the Airflow deployment was extended into the full STOXX-only production demo pipeline. The final runtime on 2026-04-13 uses three additional Cloud Run jobs:

  • stoxx-bronze-load reads the stage bucket and loads the SQL Server bronze schema on stoxx-vm.
  • stoxx-transforms executes the SQL medallion transforms into silver and gold.
  • stoxx-serving syncs SQL gold into BigQuery replica tables, builds BigQuery marts, and publishes Firestore serving documents.

Final DAG topology

The final Airflow DAG contains 11 tasks and runs end to end without manual intervention after trigger:

OrderTask IDTarget
1fetch_bronze_stage_into_gcsCloud Run job stoxx-stage-fetch
2load_bronze_into_sqlCloud Run job stoxx-bronze-load
3transform_ohlcv_to_silverCloud Run job stoxx-transforms --step=3
4transform_signals_daily_to_silverCloud Run job stoxx-transforms --step=8
5transform_signals_quarterly_to_silverCloud Run job stoxx-transforms --step=9
6build_gold_scoresCloud Run job stoxx-transforms --from=14 --to=15
7build_gold_index_performanceCloud Run job stoxx-transforms --step=16
8sync_gold_to_bigqueryCloud Run job stoxx-serving --mode=sync-replica
9build_bigquery_martsCloud Run job stoxx-serving --mode=build-marts
10publish_serving_to_firestoreCloud Run job stoxx-serving --mode=publish-firestore
11validate_serving_layerCloud Run job stoxx-serving --mode=validate

Serving-layer target objects

The serving job publishes two downstream layers above SQL Server:

  • BigQuery replica datasets: stoxx_silver and stoxx_gold
  • BigQuery marts dataset: stoxx_marts
  • Firestore database: main
  • Firestore root collection: stoxx_indices

The final BigQuery mart tables are:

  • mart_constituent_screener_latest
  • mart_sector_heatmap_latest
  • mart_index_compare_history
  • mart_index_factsheet_latest

Troubleshooting chronology

The serving-layer extension surfaced seven concrete issues during bring-up:

IssueSymptomFix
Cloud Run network modeServing job failed before container start when deployed with --vpc-connector=default.Recreated stoxx-serving with --network=default --subnet=default --vpc-egress=private-ranges-only.
Container arg handlingCloud Run treated --mode=... as the executable instead of a script argument.Changed the image from CMD ["python", "stoxx_serving.py"] to ENTRYPOINT ["python", "stoxx_serving.py"].
Legacy oil cleanup SQLsync-replica failed with Unrecognized name: _index against stoxx_bronze.dim_index.Changed the cleanup statement to use index_key on the legacy bronze table.
BigQuery factsheet martbuild-marts failed because correlated subqueries could not be decorrelated.Rewrote mart_index_factsheet_latest to pre-aggregate arrays by _index in separate CTEs.
Firestore database selectionpublish-firestore failed with The database (default) does not exist.Added FIRESTORE_DATABASE=main and created the Firestore client with database='main'.
Airflow compose warningdocker compose warned that SERVING_JOB was unset on the VM.Changed the compose env reference to ${SERVING_JOB:-stoxx-serving}.
DAG remained queuedThe first triggered DAG run stayed queued after redeploy.Unpaused stoxx_stage_yfinance on the VM and reran the scheduler checks.

The full command-output chronology for the serving-layer bring-up is indexed in:

C:\Users\aperi\My Drive\VAULT\.codex-temp\bq-firestore-artifacts\20260413_184213\ARTIFACT_INDEX.md

Final validated run

Before the final Airflow run, the SQL demo window was reset with:

C:\Users\aperi\My Drive\VAULT\.codex-temp\stoxx-transforms\reset_recent_stoxx_demo_window.ps1

That reset deleted the last daily and quarterly windows from the STOXX-only bronze, silver, and gold tables so the DAG would repopulate meaningful data during the live run.

The final successful Airflow DAG run was:

  • run_id: manual__2026-04-13T17:28:30Z_serving
  • start_date: 2026-04-13 17:30:26 UTC
  • end_date: 2026-04-13 17:41:45 UTC
  • final DAG state: success

Every task in the final DAG run succeeded:

Task IDStateEnd time (UTC)
fetch_bronze_stage_into_gcssuccess2026-04-13 17:31:54
load_bronze_into_sqlsuccess2026-04-13 17:32:52
transform_ohlcv_to_silversuccess2026-04-13 17:34:07
transform_signals_daily_to_silversuccess2026-04-13 17:33:59
transform_signals_quarterly_to_silversuccess2026-04-13 17:34:04
build_gold_scoressuccess2026-04-13 17:35:19
build_gold_index_performancesuccess2026-04-13 17:36:29
sync_gold_to_bigquerysuccess2026-04-13 17:38:07
build_bigquery_martssuccess2026-04-13 17:39:31
publish_serving_to_firestoresuccess2026-04-13 17:40:40
validate_serving_layersuccess2026-04-13 17:41:44

The final serving-layer validation after the successful run was:

BigQuery tableRow count
mart_constituent_screener_latest150
mart_sector_heatmap_latest28
mart_index_compare_history4044
mart_index_factsheet_latest3
Firestore root documentConstituentsSectorsPerformance docs
euro_stoxx_5050101350
stoxx_asia_505091371
stoxx_usa_505091323

Final Runtime State

Check the running Airflow containers and the systemd unit.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  --command="
    cd /home/alexper_recovery_gmail_com/app &&
    docker compose ps &&
    systemctl status stoxx-airflow.service --no-pager --full | sed -n '1,12p'
  "
app-airflow-apiserver-1       ... Up ... (healthy)
app-airflow-dag-processor-1   ... Up ... (healthy)
app-airflow-scheduler-1       ... Up ... (healthy)
app-airflow-triggerer-1       ... Up ... (healthy)
app-airflow-worker-1          ... Up ... (healthy)
app-postgres-1                ... Up ... (healthy)
app-redis-1                   ... Up ... (healthy)
...
stoxx-airflow.service - STOXX Airflow Docker Compose Stack
Loaded: loaded (...; enabled)
Active: active (exited)

Access Pattern for the Demo

The VM has no public IP, so the Airflow UI is reached through IAP port forwarding.

Open a local SSH tunnel to the Airflow API server / UI.

gcloud compute ssh stoxx-airflow \
  --project=bq-wh-nb \
  --zone=europe-west1-b \
  --tunnel-through-iap \
  -- -L 8080:localhost:8080

Then open http://localhost:8080.

Result

The Airflow VM and downstream jobs described here were operational in the original 2026-04-13 validation window, where the full STOXX-only DAG completed successfully under run manual__2026-04-13T17:28:30Z_serving. The historically validated path was:

Airflow → stoxx-stage-fetch → stoxx-bronze-load → stoxx-transforms → stoxx-serving → BigQuery marts → Firestore main.

Graph View

Backlinks