ETL Pipelines — Cheat Sheet

Tags: etl, data-engineering, pipelines, extract, transform, load, airflow, sql

Level: intermediate

TL;DR: ETL pipelines automate the extraction of raw data from sources, transform it for business needs, and load it into a central repository for analytics.

Key concepts

Extract phase pulls data from APIs, relational databases, flat files, or event streams.

Transform phase cleans, filters, joins, aggregates, and standardizes data.

Load phase writes the processed data into a target data warehouse or data lake.

ELT shifts the transformation step after the load, leveraging cloud data warehouse compute power.

Idempotency ensures rerunning a failed pipeline does not duplicate data or cause inconsistencies.

Incremental loading uses watermarks or Change Data Capture (CDC) to only process new or updated rows.

Orchestrators like Airflow or Dagster manage dependencies using Directed Acyclic Graphs (DAGs).

Data lineage tracks data flow from origin to destination for debugging and compliance.

Typical questions and ready answers

Q: How do you handle pipeline failures mid-execution?R: I ensure tasks are idempotent, meaning I can safely restart the DAG from the point of failure. I also route bad data to a dead-letter queue so the pipeline doesn't crash entirely.

Q: When would you choose ELT instead of ETL?R: I choose ELT when using powerful modern warehouses like BigQuery or Snowflake. It lets me ingest data fast and write scalable SQL transformations directly in the database.

Pitfalls and common mistakes

Hardcoding configurations, credentials, or dates instead of using environment variables and logical execution dates.

Ignoring schema evolution, causing jobs to fail when an upstream source drops or renames a column.

Defaulting to full table snapshots instead of building incremental loads for large datasets.

Failing to set up data quality tests, resulting in silent failures where stakeholders consume bad data.

Snippets and commandsPython

```from airflow import DAG

from airflow.operators.python import PythonOperator

from datetime import datetime

def extract():

pass # fetch raw data

def transform():

pass # clean, standardize, and join

def load():

pass # push structured data to warehouse

with DAG('daily_etl_batch', start_date=datetime(2023, 1, 1), schedule_interval='@daily', catchup=False) as dag:

task_extract = PythonOperator(task_id='extract', python_callable=extract)

task_transform = PythonOperator(task_id='transform', python_callable=transform)

task_load = PythonOperator(task_id='load', python_callable=load)

task_extract >> task_transform >> task_load

```

To review

[ ] Change Data Capture (CDC) implementation patterns and log-based replication.

[ ] Differences between micro-batching and pure stream processing.