Companies collect data in many places: app databases, payment gateways, CRM tools, spreadsheets, website events. Analysts and ML models need it together, clean and up to date. A data pipeline moves data from sources to a destination where it can be analysed. Building reliable pipelines is the core job of a data engineer.
ETL: Extract, Transform, Load
- Extract data from sources.
- Transform it on a separate processing server: clean, join, aggregate.
- Load the final, clean result into the warehouse.
ELT: Extract, Load, Transform
- Extract data from sources.
- Load the raw data straight into a cloud warehouse.
- Transform it inside the warehouse using SQL (often with a tool like dbt).
ETL vs ELT
| ETL | ELT | |
|---|---|---|
| Where transforms run | Separate processing engine | Inside the warehouse |
| Raw data kept? | Usually not | Yes, so you can re-transform later |
| Best for | Strict compliance, on-premises systems, heavy pre-processing | Cloud warehouses (Snowflake, BigQuery, Redshift, Databricks) |
| Flexibility | Changing logic means re-running extraction | Change the SQL and rebuild |
Modern cloud teams mostly use ELT, because warehouse compute is cheap and scalable and keeping raw data makes debugging and new analyses easier. ETL remains common where sensitive data must be masked before it is stored.
Batch vs streaming
- Batch: process data on a schedule (every hour, every night). Simple and cost-effective; fine for most reports.
- Streaming: process events continuously within seconds (Kafka, Spark Structured Streaming, Flink). Needed for fraud detection, live dashboards and real-time alerts.
Warehouse vs lake vs lakehouse
| Data warehouse | Data lake | Lakehouse | |
|---|---|---|---|
| Data | Structured, modelled tables | Any format (files, JSON, images) | Files with table features |
| Users | Analysts, BI tools | Data scientists, engineers | Both |
| Examples | Snowflake, BigQuery, Redshift | Amazon S3, Azure Data Lake | Databricks, Apache Iceberg / Delta Lake |
A tiny ETL job in Python
Extract orders from a CSV export, transform them, and load a daily summary into a database.
import pandas as pd
from sqlalchemy import create_engine
# Extract
orders = pd.read_csv("orders_export.csv", parse_dates=["created_at"])
# Transform
orders = orders.drop_duplicates(subset="order_id")
orders = orders[orders["status"] == "paid"]
orders["order_date"] = orders["created_at"].dt.date
daily = (
orders.groupby(["order_date", "course"], as_index=False)
.agg(orders=("order_id", "count"), revenue=("amount", "sum"))
)
# Load
engine = create_engine("postgresql+psycopg://user:password@localhost:5432/analytics")
daily.to_sql("daily_course_revenue", engine, if_exists="replace", index=False)
print(f"Loaded {len(daily)} rows")In production, read the database password from an environment variable or a secrets manager, never hard-code it.
What makes a pipeline production-ready
- Idempotent: running it twice gives the same result (no duplicated rows).
- Incremental: processes only new or changed data instead of everything each time.
- Scheduled and orchestrated: tools such as Apache Airflow, Dagster or cloud schedulers run jobs in order and retry failures.
- Tested: checks for nulls, uniqueness, row counts and accepted values (dbt tests, Great Expectations).
- Monitored: alerts when a job fails or data arrives late.
- Documented: data lineage shows where every column came from.
Interview questions
- What is idempotency in pipelines? Re-running a job for the same period produces the same output, typically by overwriting partitions or using merge/upsert.
- What is a slowly changing dimension (SCD)? A technique for tracking changes in dimension data over time; Type 2 keeps history with valid-from and valid-to dates.
- What is CDC? Change Data Capture: reading inserts, updates and deletes from a database log to load only changes.
Next steps
Schedule the Python job above to run daily and add a row-count check. Learn Spark, Airflow, cloud warehouses and AI data pipelines in the Data Engineering + AI course.