Data rarely comes ready for analysis. It arrives from APIs, transactional databases, spreadsheets and logs, each with its own format and its own mess. The work of taking this raw material to a clean, trustworthy table is what we call a data pipeline — and understanding ETL is central to the work of any mid-level professional.

ETL: Extract, Transform, Load

The classic pattern has three steps:

• Extract — pull the data from the source systems.
• Transform — clean, standardize, join, calculate. This is where most of the complexity lives.
• Load — load the result into the destination (a data warehouse, for example).

In traditional ETL, the transformation happens before loading, on an intermediate server.

ELT: the inversion the modern warehouse brought

In ELT, you flip the order: load the raw data first and transform it inside the data warehouse. Why did this take off? Because modern warehouses (BigQuery, Snowflake, Redshift) have massive, cheap processing power. Instead of maintaining a separate server to transform, you dump the raw data in and use SQL to transform it with the full power of the warehouse.

The stages and the raw → staging → analytics flow

A mature pipeline organizes data in layers, each with its own purpose:

 Sources       raw            staging         analytics
+-------+    +-------+      +----------+    +-----------+
| API   |───▶| raw   |─────▶| cleaned, |───▶| business  |
| DB    |    | data  |      | standard.|    | models    |
| CSV   |    | (exact|      | typed    |    | (fact/dim)|
+-------+    | copy) |      +----------+    +-----------+
             +-------+

The raw layer keeps an exact copy of the source (useful for reprocessing); staging cleans and standardizes; analytics delivers tables ready for consumption, often in the dimensional model.

Orchestration: who says what runs, and in what order

Pipelines have dependencies: you can't transform before extracting. An orchestrator coordinates this by modeling tasks as a DAG (directed acyclic graph) — a web of "this task only runs after that one". Airflow is the best-known tool for defining and scheduling these DAGs.

Idempotency and reprocessing

Pipelines fail — the network goes down, a source becomes unavailable. That's why a task should be idempotent: running it twice produces the same result as running it once, without duplicating data. Think of a light switch: pressing "on" three times still leaves the light on, not "three times on". In practice, this means overwriting a partition instead of just inserting — so you can safely reprocess a day.

Data quality: trust in what comes out

A pipeline that delivers wrong data is worse than none, because nobody notices. That's why we build in quality checks: does the key column have nulls? Did the row count suddenly drop? Are the values within the expected range? Failing the pipeline early is better than polluting the director's report.

A good pipeline isn't the one that runs when everything goes right — it's the one that fails safely, can be reprocessed without duplicating anything, and warns you when the data looks wrong.

In interviews it's common to be asked the difference between ETL and ELT and why ELT became the standard — and also what makes a task idempotent. Day to day, this shows up when a pipeline fails at 3 a.m. and you need to reprocess it in the morning without fear of messing up the data.

Conclusion

ETL and ELT describe how to move and transform data; the raw/staging/analytics layers organize that journey; DAG orchestration guarantees the order; and idempotency plus data quality guarantee the result is trustworthy and reprocessable. These principles hold for any tool — understanding them is what lets you build pipelines that don't break silently.