Production Data Pipeline

Incremental ETL pipeline for API ingestion with checkpointing, PostgreSQL bronze landing, dbt transformations, and Apache Airflow orchestration — a reference implementation for data engineers building reliable cloud data platforms.

A production-style data engineering portfolio project demonstrating incremental API ingestion, idempotent loads, medallion-style layering (bronze → silver → gold), and operational monitoring — patterns used in modern ETL/ELT pipelines on cloud data platforms.

Problem

Operational analytics breaks when pipelines silently drop records, re-process duplicates, or push schema drift into dashboards. Teams need a reference pattern that shows:

• How to resume ingestion after interruption without skipping pages or double-counting events
• How to separate raw landing, validated staging, and curated aggregates so each layer is testable
• How to fail loudly when quality regresses — not after bad data reaches executives

This repository is a production-style reference implementation for those patterns, not a tutorial CSV load.

Architecture

External API
    ↓
Ingestion connector (incremental, idempotent)
    ↓
Raw / bronze layer (PostgreSQL landing)
    ↓
Validated / silver layer (dbt `stg_events`)
    ↓
Curated / gold layer (`fct_daily_event_metrics`)
    ↓
Downstream consumers

See docs/architecture.md for component boundaries and failure modes.

Key decisions

Decision	Alternatives considered	Why this choice
Cursor + processed-ID checkpoints	Timestamp-only watermarks	APIs can return out-of-order events; stable IDs give safer idempotency
PostgreSQL bronze + metadata tables	JSON checkpoints only, SQLite	Matches warehouse/lakehouse landing patterns; DB constraints enforce idempotency
dbt for silver/gold transforms	Pure Python transforms	Declarative models, built-in tests, and clearer analytics ownership boundaries
Airflow orchestration	Cron, Prefect-only	Widely adopted scheduling model; demonstrates retry/backoff and task dependencies
Quality gates before bronze landing	Validate only in dbt	Blocks bad records at the ingestion boundary; reduces polluted raw history
File checkpoints for local dev	Require Postgres everywhere	Lowers onboarding friction while keeping a production upgrade path

Full rationale: docs/adr/.

Failure modes

Failure	Mitigation
Duplicate delivery	processed_ids set + ON CONFLICT DO NOTHING on event_id
Partial page read	Checkpoint advances only after successful page processing
API timeout	Airflow retry (2×, 5-min backoff); manual rerun in docs/operations.md
Schema drift	Python quality gate blocks landing; dbt tests catch transform issues
dbt test failure	DAG stops before downstream consumers see broken data
Zero-record “success”	Ingestion metrics + webhook alerts for empty runs

Trade-offs

Area	Choice	Cost accepted
Checkpoint store	PostgreSQL metadata tables	Requires Docker/local Postgres for integration tests
Processed-ID tracking	In-database set per pipeline	Will need pruning/archival at high event volume
Landing layer	PostgreSQL bronze	Not object-storage lakehouse scale — intentional scope for clarity
Orchestration	Local airflow dags test	Production Airflow deployment not included yet

Results

Reference implementation metrics (synthetic sample source, CI-validated):

Signal	Outcome
Idempotent re-runs	Duplicate events suppressed via processed-ID set and DB constraint
Recovery	Checkpoint replay from last successful cursor after process restart
Test coverage	pytest across ingestion, quality gates, dbt transforms, and DAG integrity
CI	GitHub Actions with PostgreSQL service container on every push and PR
Operational docs	Architecture, ADRs, operations runbook, and demo script included

Current capabilities

• Incremental ingestion with JSON-file or PostgreSQL checkpoint store
• Idempotent record handling via stable event IDs
• PostgreSQL bronze landing layer with Docker Compose
• Data-quality checks: schema, required fields, uniqueness, freshness
• dbt silver (stg_events) and gold (fct_daily_event_metrics) models
• Airflow DAG production_sample_ingestion with retries
• Ingestion summary metrics and webhook alerts for zero-record runs
• Operations runbook and demo script
• Unit and integration tests with CI PostgreSQL service
• CI workflow on push and pull request
• Production Airflow deployment (local airflow dags test supported)

Technology stack

Area	Selection
Language	Python 3.12
Storage	PostgreSQL bronze + metadata, optional JSON checkpoints
Orchestration	Apache Airflow (production_sample_ingestion)
Transformation	dbt (silver + gold models)
Testing	pytest
Deployment	Docker Compose (local), CI via GitHub Actions

Quick start

git clone https://github.com/br413/production-data-pipeline.git
cd production-data-pipeline
python -m venv .venv

Windows:

.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt
pytest

Linux/macOS:

source .venv/bin/activate
pip install -r requirements.txt
pytest

Run the sample ingestion (file checkpoints):

python -m src.pipeline.ingestion --source sample --checkpoint .checkpoints/sample.json

Run with PostgreSQL landing and DB checkpoints:

docker compose up -d
python -m src.pipeline.ingestion --source sample --storage postgres --pipeline-name sample-ingestion
python -m src.pipeline.run_dbt --target dev

Run the full demo script (Windows):

.\scripts\run_demo.ps1

Project structure

.
├── .github/
│   ├── ISSUE_TEMPLATE/
│   ├── workflows/ci.yml
│   └── pull_request_template.md
├── dags/
│   └── production_pipeline_dag.py
├── dbt/
│   ├── models/
│   └── profiles.yml
├── docs/
│   ├── architecture.md
│   ├── demo.md
│   ├── operations.md
│   └── adr/
├── scripts/
│   └── run_demo.ps1
├── src/
│   └── pipeline/
├── tests/
├── docker-compose.yml
├── README.md
├── CONTRIBUTING.md
├── SECURITY.md
└── requirements.txt

Engineering decisions

Architectural Decision Records are stored in docs/adr/.

Testing

pytest -v

Coverage includes checkpoint persistence, duplicate suppression, incremental cursor advancement, PostgreSQL landing writes, data-quality validation, dbt transforms, and Airflow DAG integrity.

Operations

Concern	Approach
Scheduling	Airflow DAG @daily with 2 retries
Monitoring	Ingestion metrics + webhook alerts — see docs/operations.md
Retries	Airflow 5-minute backoff; manual rerun documented
Backfills	Checkpoint reset with documented procedure
Recovery	Checkpoint replay from last successful cursor
Secrets	Environment variables; never committed
Demo	docs/demo.md walkthrough script

Related projects

Project	Focus
data-quality-observability	Contract-driven data quality checks with history and alert routing
cloud-lakehouse-blueprint	Medallion lakehouse architecture with Terraform IaC
Portfolio & writing	Senior DE portfolio and technical articles
@br413	Senior Data Engineer & Data Architect profile

Roadmap

Tracked via GitHub Issues and milestones. Week 4 career materials:

• docs/career/resume-snippets.md
• docs/career/linkedin-setup.md
• docs/career/outreach-tracker.md

Topics

data-engineering · etl · elt · data-pipeline · incremental-loading · airflow · dbt · python · postgresql · data-platform · bronze-silver-gold

Attribution

Built as a public portfolio project by @br413 — Senior Data Engineer & Data Architect. Sample data is synthetic for demonstration.

License

MIT — see LICENSE.