Data Pipelines & ETL

Workflow

1. Design Airflow DAGsAirflow schedules and monitors pipeline execution. DAGs (Directed Acyclic Graphs) define tasks and their dependencies - Airflow ensures correct execution order, retries on failure, and alerting.
2. Write dbt transformation modelsdbt transforms raw data in the warehouse using SQL with version control, testing, and documentation. Layered models (staging → intermediate → mart) keep transformations organized and reusable.
3. Test data qualityBad data silently corrupts downstream decisions. Automated data quality checks catch issues at ingestion - before dashboards show wrong numbers.
4. Implement incremental loadingFull table reloads are slow and expensive at scale. Incremental patterns (watermark, CDC) load only new or changed records.
5. Monitor pipeline healthSilent pipeline failures cause stale dashboards that nobody notices until a business decision goes wrong. Proactive monitoring catches issues before stakeholders notice.

Output Format

Data pipelines: Airflow DAG with default_args+retries+SLA, dbt staging→mart layers with incremental merge, Great Expectations data quality, watermark-based incremental extraction, and data freshness monitoring.

Airflow: schedule="0 6 * * *" + catchup=False + retries=3 + email_on_failure
dbt: staging(view) → intermediate → mart(incremental, unique_key)
schema.yml: unique + not_null + relationships + accepted_range tests
Watermark: SELECT WHERE updated_at >= last_watermark AND < now()
Freshness: TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(event_at), MINUTE) > 120 → STALE

Examples

Edge Cases & Gotchas

• Late-arriving data - Events often arrive hours or days late (mobile apps with offline mode, delayed webhooks). Process a lookback window (last 3 days) on each incremental run to catch late arrivals, not just since the last watermark.
• Airflow XCom size limits - Airflow XCom (cross-task communication) stores values in the metadata database. Storing large DataFrames in XCom causes performance issues. Pass S3/GCS paths between tasks instead of data.
• dbt model fanout - Avoid dozens of models all depending on one base model - changes cascade unpredictably. Build clear lineage layers: staging (1:1 with source) → intermediate (business logic) → mart (consumer-facing).
• Timezone handling in pipelines - Store all timestamps in UTC. Convert to local timezone only at presentation layer. Mixing timezones in pipeline logic causes off-by-one errors at DST transitions.

When to Break These Rules

• Use Prefect instead of Airflow for simpler deployments - Prefect has a better local development experience and doesn't require a separate metadata database for small pipelines.
• Use dlt (data load tool) instead of custom ETL scripts for extracting from SaaS APIs - it handles pagination, incremental loading, and schema inference automatically.

Reference Files

• https://airflow.apache.org/docs/
• https://docs.getdbt.com/