Production Data Pipelines in Python and SQL: What Actually Ships

Most pipeline advice stops at 'use Airflow.' This is how we build data pipelines in Python and SQL that run on schedule, survive failures, and stay maintainable: layering, testing, idempotency, and observability from practitioners who deploy daily.

Most pipeline tutorials stop at "hello Airflow." Enterprise slide decks stop at reference architecture. Neither helps when your job failed at 3 a.m. and finance is asking why yesterday's numbers moved.

This is the checklist and layering we use on real engagements: growth-stage warehouse stacks, Postgres shops, and federal-scale volume. Your orchestrator might differ. The habits don't.

SQL vs Python: split at the warehouse wall

The maintainable default: SQL does set math where the data already lives. Python does everything else.

SQL wins here

Joins, aggregates, window functions
Incremental merges in BigQuery, Snowflake, Postgres
dbt models analysts can read and PR
Large scans without shipping rows to a laptop

Python wins here

API pulls and pagination
Parsing PDFs, JSON blobs, odd file formats
ML feature logic that doesn't fit cleanly in SQL
Orchestration glue and custom validation

Pulling ten million rows into pandas to compute a daily sum is a bill and a timeout waiting to happen. An 800-line SQL string with regex nested three levels deep is a maintenance trap. Pick the fight that matches the tool.

The five layers we expect on every serious pipeline

Standard pipeline anatomy

01
Extract
Incremental keys (updated_at, CDC, cursor). Log row counts. Quarantine bad records instead of dropping them silently.
02
Stage raw
Land upstream data lightly typed. When the vendor adds a column, you replay transforms without re-pulling history.
03
Transform
Business logic in SQL models or Python with explicit deps. Write down grain: one row per what?
04
Validate
dbt tests, Great Expectations, or custom SQL checks. Fail or alert before bad data hits the dashboard.
05
Publish
Marts, feature tables, exports. Snapshot when someone needs reproducible point-in-time numbers.

Idempotency (the 2 a.m. topic)

If re-running yesterday's job duplicates rows or double-charges customers, you don't have a pipeline yet. Patterns we use constantly:

Partition by date. Jobs take a run_date. Backfill means rerun that partition, not the whole history.
Merge on business key. Upsert or delete+insert for the keys you care about.
No sneaky clocks. Business logic shouldn't depend on now() unless you mean it.
Side effects get their own step. Emails and API writes carry idempotency keys. Analytics transforms stay pure.

Orchestration: minimum viable, not Netflix cosplay

Airflow, Dagster, Prefect, disciplined cron. Five daily jobs don't need Kubernetes. Fifty cron entries nobody documented do need structure.

Minimum orchestration bar

Dependency graph: step B knows it waits on A
Retries with backoff on transient failures
Alert on failure (Slack, PagerDuty, email)
Parameterized backfill for a date range
One place to see last success time

Observability ops actually uses

Green checkmark in Airflow is not enough. Track row counts stage to stage, runtime trend, freshness of max(timestamp) in the mart, and quality test pass rate over time.

Most "production gaps" we audit start here: pipelines exist, nobody watches them. See the production gap.

When ETL becomes an ML pipeline

Feature tables inherit everything above, plus one hard rule: training and serving must share the same definition. Different Python offline vs different SQL online equals a model that looks great in a notebook and wrong in prod.

Full staged architecture: ML and AI pipeline architecture.

How we sequence a greenfield build

Map sources, consumers, SLAs, current failure modes
Draw raw → mart layers and pick incremental strategy
Ship critical path with tests + one schedule
Add validation, monitoring, backfill runbook
Hand off in a paired deploy session. Your team merges the next PR.