Data7 min read

ETL Pipelines

Build reliable data pipelines that extract from sources, transform to business logic, and load to warehouses - using Airflow, dbt, and modern ELT patterns.

ETLData PipelineApache AirflowData EngineeringPython

skill: ETL Pipelines category: Data tags: [ETL, Data Pipeline, Apache Airflow, Data Engineering, Python] readingTime: 7

Modern data engineering prefers ELT (Extract, Load, Transform) over ETL - load raw data into the warehouse first, then transform using SQL (dbt). This is cheaper, more debuggable, and enables re-transformation without re-extracting. ETL is still relevant for high-volume streaming or when raw data can't be stored (PII).

Workflow

Design idempotent extractionsPipelines fail and retry. An idempotent pipeline produces the same result whether it runs once or 100 times - no duplicate records.
Transform and validate dataGarbage in, garbage out. Data validation at the transformation step prevents corrupt data from reaching the warehouse and poisoning downstream reports.
Orchestrate with Apache AirflowAirflow schedules, monitors, and retries pipeline steps - providing visibility into DAG (Directed Acyclic Graph) execution history and failure alerts.
Load with upsert (MERGE) semanticsAppending instead of upserting creates duplicates for slowly-changing dimensions. MERGE (or INSERT ON CONFLICT) handles both inserts and updates atomically.
Monitor pipeline healthSilent failures (schema drift, row count drops) corrupt the data warehouse without triggering errors. Data observability tools catch these.

Output Format

ETL pipeline: idempotent extract, validated transform, upsert load, Airflow orchestration, and data quality checks.

Pipeline DAG:
  extract_task → transform_task → validate_task → load_task
  Schedule: daily, catchup=False
  Retries: 3 × 5min backoff
  Alerts: Slack + email on failure
  Data quality: row count ±20%, schema checks, not-null on keys

Examples

Input

Output

Edge Cases & Gotchas

Schema drift - Source systems add or rename columns without notice. Validate schema on every extraction run and fail loudly rather than silently loading null columns.
Late-arriving data - Events timestamped yesterday arrive today (mobile offline sync). Use updated_at (not created_at) for incremental extraction and re-process the last 3 days to catch late data.
Timezone handling - Source systems in different timezones cause ambiguous timestamps. Normalize all timestamps to UTC at extraction time.
Large batch memory pressure - Extracting millions of rows into a pandas DataFrame crashes on low-memory machines. Use chunked iteration: cursor.fetchmany(1000) in a loop.

When to Break These Rules

Use dbt (data build tool) instead of Python ETL for transformations that run inside a data warehouse - dbt SQL is simpler, version-controlled, and has built-in testing.
Use Fivetran or Airbyte instead of custom extraction code for standard SaaS sources (Salesforce, Stripe, Google Analytics) - they maintain connectors so you don't have to.

Reference Files

https://airflow.apache.org/docs/
https://docs.greatexpectations.io/