DEV Community

Bobby Ray
Bobby Ray

Posted on

Building a Production Data Pipeline with Incremental Loading and dbt

#dataengineering #python #dbt #airflow

Operational analytics breaks when pipelines silently drop records, re-process duplicates, or push schema drift into dashboards. This article walks through a production-style data pipeline pattern: incremental API ingestion, explicit checkpoints, medallion-style layering, and orchestration with Apache Airflow and dbt.

Full source code: production-data-pipeline

The problem

Most demo pipelines assume APIs are always available, schemas never change, and re-runs are harmless. Production systems need:

  • Incremental loading — fetch only new data since the last successful run
  • Idempotency — duplicate deliveries must not inflate metrics
  • Separable layers — raw landing, validated staging, and curated aggregates tested independently
  • Operational visibility — know when a run ingested zero records or failed mid-page

Architecture overview 

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
Enter fullscreen mode Exit fullscreen mode

Each boundary has a clear responsibility. Ingestion handles pagination and cursor advancement. Bronze stores append-only raw events. dbt owns transformation logic and data tests. Airflow schedules and retries the workflow.

Incremental ingestion with checkpoints

The ingestion connector tracks cursor position in a checkpoint store. Two options:

  1. File-based checkpoints — lightweight for local development
  2. PostgreSQL metadata tables — durable for shared environments 
# Local run with file checkpoint
python -m src.pipeline.ingestion \
  --source sample \
  --checkpoint .checkpoints/sample.json

# Production-style run with PostgreSQL
python -m src.pipeline.ingestion \
  --source sample \
  --storage postgres \
  --pipeline-name sample-ingestion
Enter fullscreen mode Exit fullscreen mode

Key design decisions:

  • Cursor advances only after successful page processing — a timeout mid-page does not skip data
  • Stable event IDs suppress duplicates — re-delivered events are ignored via a processed-ID set
  • Quality gates run before landing — schema validation, required fields, and freshness checks block bad records from bronze

Bronze, silver, and gold with dbt

After landing in bronze.raw_events, dbt builds two layers:

Layer Model Purpose
Silver stg_events Validated staging with typed columns
Gold fct_daily_event_metrics Daily aggregates for analytics 
python -m src.pipeline.run_dbt --target dev
Enter fullscreen mode Exit fullscreen mode

dbt tests enforce uniqueness, not-null constraints, and source freshness. When a test fails, the pipeline stops — downstream consumers never see broken data.

This is the medallion architecture in miniature: raw → validated → curated, each layer independently testable.

Orchestration with Airflow

The production_sample_ingestion DAG runs two tasks:

  1. ingest_sample_events — Python ingestion into bronze
  2. run_dbt_models — dbt run + test for silver and gold

Default retry policy: 2 retries with a 5-minute delay. For local testing:

airflow dags test production_sample_ingestion 2026-07-14
Enter fullscreen mode Exit fullscreen mode

Failure modes and mitigations

Failure Mitigation
Duplicate delivery Processed-ID set suppresses re-ingestion
Partial page read Checkpoint advances only after full page success
API timeout Airflow retry + documented manual rerun
Schema drift Python quality gate blocks landing; dbt tests catch transform issues
dbt test failure DAG stops before downstream use

Document these in an operations runbook. Pipelines that cannot be recovered at 2 AM are not production-ready.

Observability: metrics and alerts

Recent additions to the project include:

  • Structured ingestion summary metrics — records fetched, inserted, skipped per run
  • Webhook alerts for zero-record runs and Airflow task failures
  • Local validation script (scripts/check.ps1) for pre-commit confidence

Observability is not optional. A pipeline that runs successfully but ingests zero records is often worse than one that fails loudly.

What I would do differently at scale

This portfolio project targets a single API source and PostgreSQL landing. In a larger platform I would add:

  • Dead-letter queue for records that fail quality gates
  • Schema registry or contract versioning for API drift
  • Lineage tracking from source through gold (see my cloud-lakehouse-blueprint)
  • Separate quality observability layer (see data-quality-observability)

Try it yourself 

git clone https://github.com/br413/production-data-pipeline.git
cd production-data-pipeline
python -m venv .venv
source .venv/bin/activate  # Windows: .\.venv\Scripts\Activate.ps1
pip install -r requirements.txt
pytest
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
Enter fullscreen mode Exit fullscreen mode

The repo includes architecture docs, ADRs, CI with PostgreSQL service containers, and a v0.1.0 release.

Summary

Production data pipelines are defined by how they handle failure, not how they handle the happy path. Incremental checkpoints, idempotent loads, medallion layering, and testable transformation boundaries give you a foundation that scales from portfolio projects to real cloud data platforms.

Related projects

If you found this useful, star the repo or leave a comment — I am always interested in feedback from other data engineers and architects.

Top comments (0)