ETL and ELT pipelines

ETL extracts data from sources, transforms in a middle tier, then loads into the warehouse. ELT loads raw landing zone first, transforms inside the warehouse with SQL/dbt — favored when the warehouse engine is powerful and schemas evolve often.

Batch jobs: scheduled Airflow/Dagster/cron with partitioned increments, watermarks, and retryable tasks. Streaming: Kafka/Flink for lower latency with windowed aggregations and state checkpoints.

| Property | Why it matters | |----------|----------------| | Idempotency | Safe replays after failure | | Observability | Row counts, duration, data quality checks | | Ownership | Clear team for schema breaks | | SLAs | Freshness vs cost of frequent runs |

On interviews: sketch a pipeline from app DB to dashboard; explain how you prevent duplicate facts on retry; contrast batch nightly vs hourly vs streaming for the same metric.

Common pitfalls: silent partial loads; transforms without tests; shared credentials; no alerting on row count drift; coupling pipeline deploy to app deploy without contract tests.

The trade-off is transformation flexibility and warehouse compute cost (ELT) versus pre-shipped clean data and middle-tier complexity (ETL).

Checklist:

• Define ETL vs ELT for your stack.
• Design idempotent loads and watermarks.
• Add data quality checks in the DAG.
• Monitor freshness SLA and failures.
• Document schema contracts with producers.