Airline Data Reliability Lab

An observable airline data pipeline that proves what loaded, what failed, and why.

The lab ingests airline flight records, enforces schema and business-quality gates, quarantines invalid rows with reason codes, loads accepted facts idempotently, and exposes evidence through an API, Prometheus metrics, and a portable quality report. PostgreSQL is the intended store; SQLite keeps the automated demo reproducible.

Why this project is different

Most portfolio pipelines stop at “CSV to database.” This one treats reliability as a product requirement:

• Fail-closed quality gates: a bad batch cannot silently enter the fact table.
• Row-level quarantine: invalid records retain source row numbers, payloads, and reason codes.
• Idempotent facts: deterministic business keys prevent duplicates across retries.
• Traceable lineage: every run records the source filename and SHA-256 digest.
• Observable operation: run history, check results, accepted/quarantined counts, and Prometheus metrics are queryable.
• Reproducible evidence: the CLI produces an auditable Markdown report on every run.

Architecture

flowchart LR
    S["Airline CSV"] --> V["Schema + business validation"]
    V --> G{"Quality gates"}
    G -->|pass| P["PostgreSQL facts"]
    G -->|invalid row| Q["Quarantine + reasons"]
    G --> E["Run evidence"]
    P --> A["FastAPI"]
    Q --> A
    E --> A
    A --> M["Prometheus metrics"]
    E --> R["Quality report"]

Loading

See the architecture notes for the data model and production evolution path.

Run the complete demo

Requirements: Python 3.11+

python -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"
airline-reliability data/sample_flights.csv \
  --database-url sqlite:///airline-reliability.db \
  --report artifacts/latest-quality-report.md
uvicorn reliability_lab.api:app --reload

The sample contains 20 records: 19 load and one invalid schedule time is quarantined. The 5% quarantine rate stays below the visible 10% gate. Open http://127.0.0.1:8000/docs, then inspect:

• GET /v1/quality/latest
• GET /v1/quality/history
• GET /metrics

PostgreSQL environment

docker compose up --build -d
docker compose exec api airline-reliability data/sample_flights.csv \
  --report artifacts/latest-quality-report.md

The API waits for a healthy PostgreSQL 16 instance before starting. Connection details are configurable through DATABASE_URL; .env.example documents the local value.

Quality contract

Gate	Default threshold	Behavior
Required schema	0 missing columns	Reject the file before a run is written
Row validity	At most 10% quarantined	Block all fact loading when exceeded
Duplicate business keys	0 duplicates	Preserve one row, quarantine duplicates, fail the batch

Validation covers ISO dates, airline and airport codes, HHMM schedule times, binary status flags, plausible distance, operated-flight arrival labels, and cancellation/diversion consistency.

Verification

python -m pytest

The tests prove normalization, invalid-row reasoning, quarantine behavior, idempotent reruns, API evidence, metrics, and report generation. GitHub Actions repeats the suite and a full CLI run on every push and pull request.

Honest scope

• The included rows are synthetic test fixtures shaped like BTS fields; they are not presented as airline production data.
• PostgreSQL is exercised through the documented Compose path; unit tests use SQLite for speed.
• A production deployment should add migrations, object storage, orchestration, alert delivery, retention policy, authentication, and partitioning.

Project integrity

This repository is original work. Its quality engine, pipeline, API, tests, and documentation were built for this project rather than cosmetically republishing another repository.

License

MIT License. See LICENSE.