ITC dbt/Airflow deployment package

Portable production deployment of the transit analytics ELT: Oracle (BOMS) source → Azure Database for PostgreSQL (staging + storage), orchestrated by Airflow 3 (Cosmos) with all transforms in dbt (pure SQL, pgrouting for the in-database distance matrix).

Everything needed to build and run ships in this folder — no sample data, no development artifacts, no credentials (fill in .env.prod).

Companion docs (docs/):

• docs/deployment.md — full step-by-step deployment & upgrade procedure
• docs/airflow-operations-manual.md — UI user manual: trigger patterns, backfills, failure handling
• docs/architecture-evolution.md — database continuity when dbt models change

ITC_dbt_deploy/
├── Dockerfile                     # Astro Runtime 3 + dbt-postgres + oracledb; copies the project in
├── docker-compose.prod.yml        # api-server / scheduler / dag-processor / triggerer (LocalExecutor)
├── .env.prod.example              # Azure + Oracle + admin credentials template
├── requirements.txt, packages.txt
├── dags/
│   ├── boms_ingest_dag.py         # Oracle -> Postgres raw, date-range params, idempotent
│   └── afc_elt_dag.py             # dbt build via Cosmos (staging -> intermediate -> marts + tests)
├── dbt/                           # the dbt project "transit" (dual-target; postgres is prod)
│   └── models/<layer>/{afc,avm,avm_network,platform}/   # organized by layer, then domain
└── include/
    ├── scripts/postgres/init_db.sql            # extensions + schemas (run by bootstrap_pg.py)
    ├── scripts/postgres/bootstrap_pg.py        # idempotent first-time DB init (runs in airflow-init)
    ├── scripts/postgres/run_sql.py             # ad-hoc .sql runner (servers without psql)
    └── data/skim_precomputed/                  # duckdb-dev-only AVM matrix parquet (not shipped)

0. Air-gapped / restricted-network deployment (image transfer)

The production server needs no outbound internet — only its internal routes to Oracle (1521) and Azure PostgreSQL (5432), plus inbound AIRFLOW_WEB_PORT (default 8080) for the Airflow UI. Everything that downloads anything (base image, pip packages, dbt deps) happens at build time on a connected machine; the runtime image is fully self-contained (dbt_utils baked in, DBT_INSTALL_DEPS=false).

On a machine with Docker + internet:

cd ITC_dbt_deploy
docker build -t itc-transit-airflow:1.0 .
docker save -o itc-transit-airflow_1.0.tar itc-transit-airflow:1.0

Or let GitHub build it (.github/workflows/build-image.yml): push this folder to a private GitHub repository and either push a tag (git tag v1.0 && git push --tags) — the compressed tarball lands on the GitHub Release (split into parts if > 2 GB: reassemble with cat *.tar.gz.part-* > image.tar.gz) — or use "Run workflow" for a 14-day workflow artifact. The workflow smoke-tests the image offline (--network none dbt parse) before publishing, and also pushes to GHCR for environments that can reach a registry. Load with:

gunzip itc-transit-airflow_1.0.tar.gz
docker load -i itc-transit-airflow_1.0.tar

Transfer to the server: the image tar + docker-compose.prod.yml + .env.prod.example (+ this README). On the server:

docker load -i itc-transit-airflow_1.0.tar
cp .env.prod.example .env.prod          # fill in; AIRFLOW_IMAGE=itc-transit-airflow:1.0
docker compose -f docker-compose.prod.yml --env-file .env.prod up -d   # NO --build

Keep AZ_PG_PASSWORD as the raw PostgreSQL password for dbt and the database bootstrap. Set AZ_PG_PASSWORD_URLENCODED to the percent-encoded form used only inside Airflow connection URIs (for example, @ becomes %40 and # becomes %23). Generate it without placing the raw password in shell history:

python -c "import getpass,urllib.parse; print(urllib.parse.quote(getpass.getpass('PostgreSQL password: '), safe=''))"

Treat both values as secrets and validate the completed file with python check_compose_env.py .env.prod before starting the stack.

The one-time server preparation below also needs no local tooling — psql and pip are not required; the database bootstrap runs inside the loaded image as part of the normal stack start.

1. Prepare the PostgreSQL server (once)

Database creation and warehouse bootstrap are automated: the one-shot airflow-init compose service runs include/scripts/postgres/bootstrap_pg.py on every up -d, before airflow db migrate. It creates the airflow metadata and transit warehouse databases if missing (connecting through the postgres maintenance DB — override with PG_MAINTENANCE_DB if needed), then enables postgis + pgrouting and creates the raw/analytics schemas in the warehouse DB. Everything is idempotent, so re-runs are no-ops; a failure aborts the stack start (see docker compose logs airflow-init). Only two things remain manual:

1. Allow-list extensions (Azure Flexible Server only): add POSTGIS,PGROUTING to the azure.extensions server parameter (Portal → Server parameters, or az postgres flexible-server parameter set --name azure.extensions --value POSTGIS,PGROUTING ...), then apply/restart if prompted — do this before the first up -d, otherwise the bootstrap fails at CREATE EXTENSION and prints this remediation. On plain PostgreSQL just install the postgis/pgrouting server packages.
2. Networking: the Docker host must reach the server on 5432 (VNet rule or firewall entry). TLS is enforced by Azure — everything in this package already connects with sslmode=require.

The configured PG user needs CREATEDB for the first run (Azure server admin has it). Once the databases exist, bootstrap also works with a less privileged user that owns the warehouse DB.

Leg distances come solely from the date-aware AVM stop-distance matrix, computed in-database with pgRouting from the NOM pattern tables — no static distance lookups are loaded (the legacy skim fallback tables were dropped).

(For ad-hoc SQL on servers without psql, run_sql.py remains available: docker compose -f docker-compose.prod.yml --env-file .env.prod run --rm scheduler python /usr/local/airflow/include/scripts/postgres/run_sql.py <file.sql>.)

2. Bring up Airflow

cp .env.prod.example .env.prod    # fill in Azure + Oracle credentials
docker compose -f docker-compose.prod.yml --env-file .env.prod up -d --build

Airflow UI: http://<host>:8080 — or the port set as AIRFLOW_WEB_PORT in .env.prod (login = AIRFLOW_ADMIN_USER/AIRFLOW_ADMIN_PASSWORD). Auth uses Airflow 3's SimpleAuthManager (dev/test-grade): the api-server writes the passwords file from those env vars at startup, so the documented credentials survive container recreation. For a production deployment, switch AIRFLOW__CORE__AUTH_MANAGER to a production-capable manager (e.g. FAB) and set a strong AIRFLOW_JWT_SECRET (it must be identical across all components; a loud insecure default is only there so test deploys work out of the box). Also set the required AIRFLOW_API_SECRET_KEY; this separate shared key signs task-log requests between the api-server and scheduler log server. If it differs between components, the UI cannot display task logs. Newly discovered DAGs start unpaused; an existing metadata database keeps each DAG's current pause state. The UI is the scheduling console: pause/resume the DAGs, trigger ad-hoc windows, inspect logs.

3. Operating model

1. boms_ingest (monthly; or "Trigger DAG w/ config" with {"start_date":"YYYY-MM-DD","end_date":"YYYY-MM-DD"} for backfills) copies the Oracle BOMS tables + NOM_PATTERN_POINT into the raw schema for the window [start_date, end_date). Idempotent: date/month-windowed delete+insert, reference truncate+reload. Windows must be month-aligned (both bounds the 1st of a month) in the default mode=full_month — the month-keyed tables (avm_line_summary, avm_rev_km) always load whole months, so a mid-month window would mix full-month MI data with partial DI data; the DAG and CLI fail fast on misaligned windows. For a partial DI reload (e.g. a few bad days), pick mode=di_only in the trigger form (or CLI --skip avm_line_summary avm_rev_km): month-keyed tables are skipped and partial windows are allowed.
2. It then triggers afc_elt, passing the window explicitly via trigger conf (snapped outward to whole months — a di_only patch of a few days still rebuilds the full month: the marts are accumulating incremental tables whose period batch must be replaced atomically). afc_elt validates the raw source, pre-creates every dbt schema once, then runs the full dbt build (all models first, one aggregated test task afterward); the conf window becomes the dbt reporting_period_start/end vars, falling back to the data interval for scheduled runs. The same window drives ingestion, the AVM distance-matrix validity windows, and fare apportionment.
3. Results land in analytics_marts (leg facts fct_trip_legs*, AVM dims/facts, and the platform tables ads_* / t_plan_* — including the transfer suite ads_transfer_{network,stop,route,route_pair,chain}_di derived from int_transfer_legs over the linked AFC legs, with stop names/coordinates/regions from raw.nom_point via int_stop_lookup).

Marts accumulation (since 2026-08-13). Marts are incremental models (delete+insert on a per-period unique_key): a run replaces only its own reporting period's rows and previous months are kept. Re-running a window is idempotent; --full-refresh resets a mart to the current-period snapshot (use it if an upstream correction shrank a period — delete+insert alone won't remove stale keys). The dims accumulate too (dim_route/dim_stop/ dim_vehicle mean "ever observed"), so fact→dim relationships hold for all months present.

Concurrency / backfills. To backfill several windows, trigger several boms_ingest runs — ingest runs for disjoint windows may overlap in time (each table loads with DELETE <window> + INSERT in one transaction), but never trigger overlapping windows (both runs delete the shared rows and double-insert). The afc_elt runs they trigger are serialized (max_active_runs=1): each dbt run rebuilds the shared analytics_* staging/intermediate schemas in place, so concurrent builds would corrupt each other — queued runs execute one at a time, in order. (The marts themselves are accumulating incremental tables — see "Marts accumulation" below.)

4. First-run verification checklist

• boms_ingest smoke run on one small window: watch the mapped ingest_table tasks. Most likely first hurdles are (a) Oracle thin-mode compatibility — if the server enforces native network encryption, switch to thick mode (instant client + oracledb.init_oracle_client()), and (b) the ingest account's visibility of dotdot_reports.NOM_PATTERN_POINT@AVM_STAT — adjust the NOM_SOURCE_PREFIX/NOM_DBLINK env vars (see §5) if needed.
• afc_elt: check_raw_source verifies the window has events; then the dbt build runs analytics_staging → analytics_intermediate → analytics_marts.
• Reconcile one month against the validated dev run before sign-off (the SQL pipeline is parity-validated: the trip linker produced field-identical legs on DuckDB and PostgreSQL 16 for the full 240,627-event reference corpus).

5. Test-replica adaptation (validated June 2026, plain PostgreSQL 16)

The package was validated end-to-end against a replica Oracle source (all BOMS objects are local plain tables — no AVM_STAT DB link) and a plain PostgreSQL 16.4 server instead of Azure PG. A ready-made .env.test captures the re-pointing (plain PG, NOM_SOURCE_PREFIX=BOMS, NOM_DBLINK=NONE, replica Oracle, DBT_THREADS=1–2). Deploy the test configuration with:

docker compose -f docker-compose.prod.yml --env-file .env.test up -d --build

(There is deliberately no env_file: line in the compose file — every container variable comes from the environment: block interpolated from the --env-file you pass, so a test deploy can never pick up .env.prod values.) The .env.prod overrides behind .env.test:

• AZ_PG_SSLMODE=prefer (no TLS on plain PG), NOM_SOURCE_PREFIX=BOMS, NOM_DBLINK=NONE (NOM tables are local BOMS tables on the replica). The AVM marts window env vars are gone (2026-08-09): one unified reporting period is passed per run via the boms_ingest → afc_elt handoff (half-open [start, end)) and drives dim_date, the AVM staging filter, fct scoping and fare proration.
• Small PostgreSQL servers (near-default shared_buffers=128MB, work_mem=4MB): the un-chunked pgrouting many-to-many call in int_avm_stop_distance_matrix buffers its whole n×n result in backend memory and OOM-crashes the server. The model now chunks source vids (~100 per call) — keep that change; also use DBT_THREADS=1–2 (now interpolated from the --env-file, like every other container variable) and consider ALTER DATABASE transit SET work_mem = '32MB' for the recursive linker.
• The ingest specs/logic live in include/scripts/boms_ingest_lib.py (the DAG imports it). It doubles as a standalone CLI for hosts without Docker: python include/scripts/boms_ingest_lib.py --start 2026-06-01 --end 2026-07-01 (connections from the same env vars as .env.prod).

A full configuration guide, a one-day run trace, and a full-month profiling report from that validation live in ../deliverables/ (outside this repo).

Notes

• The recursive trip linker on Postgres uses four helper functions (afc_slice, afc_match_pos, afc_live_pos, afc_frames_to_legs) created automatically by dbt's on-run-start in the profile schema.
• Heavy months: trigger boms_ingest in smaller windows rather than one giant range — every table task streams in 50k-row batches, but AVM_TRIPS months are large.
• Scale-out later: swap LocalExecutor for Celery/K8s executor, or deploy the identical image via Astronomer.