Definition
- ETL – stands for Extract, Transform, Load: the pattern of pulling data out of sources, reshaping it, and writing the prepared result into a target system.
- Extract – reliably reading data from one or more source systems like databases, APIs, streams, or files.
- Transform – cleaning nulls, filtering, deduplicating, joining, enriching, converting formats, and computing derived fields.
- Load – writing the transformed result into a target like a data warehouse or data lake.
- ELT – the sibling pattern that loads raw data first, then transforms inside the target using the target engine's compute.
- Idempotent load – using upserts keyed by a stable id so re-running never creates duplicates.
Picture the National Bank of India preparing its monthly statements. Extract is a clerk going to each department and copying out the raw records: card swipes from the switch, transfers from core banking, loans from the loan system. Transform is the back office cleaning those records: removing duplicates, converting every amount to rupees, matching each transaction to Rajesh's or Priya's account. Load is filing the finished, clean statements into the central archive room where managers can pull them instantly. ETL is that assembly line from messy source to tidy shelf.
Understanding the Concepts
What is ETL?
ETL stands for Extract, Transform, Load. It is the most common task in data engineering. You pull data out of one or more source systems, reshape it by cleaning, filtering, joining, enriching, and converting formats, and then write the prepared result into a target system like a data warehouse or data lake. Every data pipeline at its core is some form of ETL.
package com.bank.etl;
import java.math.BigDecimal;
import java.util.List;
public class ETLOverview {
public static void main(String[] args) {
System.out.println("E - Extract: pull raw data from sources");
System.out.println("T - Transform: clean, join, convert");
System.out.println("L - Load: write to warehouse or lake");
}
}
E - Extract: pull raw data from sources
T - Transform: clean, join, convert
L - Load: write to warehouse or lake
How does Extract work?
Extract is about reliably reading data from sources without disturbing them. Sources vary: a production database read via JDBC or change-data-capture, a REST API, a Kafka message stream, or flat files. A key senior concern is extracting incrementally rather than re-reading everything each run. You only pull transactions newer than the last watermark, which keeps the extract cheap and avoids re-processing old rows.
package com.bank.etl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
public class IncrementalExtract {
static final String SQL = """
SELECT txn_id, account_id, amount, created_at
FROM transactions
WHERE created_at > ?
ORDER BY created_at
""";
public static void main(String[] args) throws Exception {
Timestamp lastWatermark =
Timestamp.valueOf("2024-10-29 00:00:00");
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost/bankdb",
"etl_user", "secret");
PreparedStatement ps = conn.prepareStatement(SQL)) {
ps.setTimestamp(1, lastWatermark);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(
rs.getString("txn_id") + " | "
+ rs.getBigDecimal("amount"));
}
}
}
}
T10045 | 15000.00
T10046 | 5000.00
T10047 | 250000.00
How does Transform work?
Transform is where most of the real logic lives. You clean nulls and bad values, filter out irrelevant rows, deduplicate, join to reference data such as attaching the account holder's name to each transaction, convert types and formats, standardize currency and time zones, and compute derived fields and aggregates. All money math uses BigDecimal because floating-point arithmetic would corrupt financial totals.
package com.bank.etl;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
public class TransformStage {
record RawTxn(String txnId, String accountId,
BigDecimal amount, String currency) {}
record CleanTxn(String txnId, String accountId,
String holderName, BigDecimal amountInr) {}
static Map<String, String> customers = Map.of(
"SAV2001", "Rajesh Kumar",
"CUR3002", "Priya Sharma");
static BigDecimal USD_TO_INR = new BigDecimal("83.50");
public static void main(String[] args) {
List<RawTxn> raw = List.of(
new RawTxn("T1", "SAV2001",
new BigDecimal("15000"), "INR"),
new RawTxn("T2", "SAV2001", null, "INR"),
new RawTxn("T3", "CUR3002",
new BigDecimal("100"), "USD"));
List<CleanTxn> clean = raw.stream()
.filter(t -> t.amount() != null)
.filter(t -> t.amount().compareTo(BigDecimal.ZERO) > 0)
.map(t -> {
BigDecimal inr = t.currency().equals("USD")
? t.amount().multiply(USD_TO_INR)
: t.amount();
return new CleanTxn(
t.txnId(), t.accountId(),
customers.getOrDefault(
t.accountId(), "UNKNOWN"), inr);
})
.collect(Collectors.toList());
clean.forEach(t -> System.out.println(
t.txnId() + " | " + t.holderName()
+ " | " + t.amountInr() + " INR"));
}
}
T1 | Rajesh Kumar | 15000 INR
T3 | Priya Sharma | 8350.00 INR
How does Load work?
Load writes the transformed data into the target. Senior concerns here are idempotency and load strategy. A full load replaces everything, an incremental append adds new rows, and an upsert (merge) inserts new keys and updates existing ones so re-running does not create duplicates. You also decide batch size and transaction boundaries so a failure does not leave the target half-written.
package com.bank.etl;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class IdempotentLoad {
static final String UPSERT = """
INSERT INTO warehouse.transactions
(txn_id, account_id, holder_name, amount_inr)
VALUES (?, ?, ?, ?)
ON CONFLICT (txn_id)
DO UPDATE SET account_id = EXCLUDED.account_id,
holder_name = EXCLUDED.holder_name,
amount_inr = EXCLUDED.amount_inr
""";
public static void main(String[] args) throws Exception {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost/warehouse",
"etl_user", "secret");
PreparedStatement ps = conn.prepareStatement(UPSERT)) {
ps.setString(1, "T1");
ps.setString(2, "SAV2001");
ps.setString(3, "Rajesh Kumar");
ps.setBigDecimal(4, new BigDecimal("15000.00"));
ps.executeUpdate();
System.out.println("Upserted T1: safe to re-run");
}
}
}
Upserted T1: safe to re-run
What is the difference between ETL and ELT?
Classic ETL transforms in a separate processing layer before loading, which suits strict schemas and when the target has expensive compute. ELT loads raw data into a powerful warehouse or lake first, then transforms using the target's engine, which suits modern cloud warehouses with cheap storage and elastic compute.
| Aspect | ETL | ELT |
|---|---|---|
| Transform location | Separate engine (Spark, custom) | Inside the target (warehouse SQL) |
| Best for | Strict schemas, regulated | Cloud warehouses (Snowflake, BigQuery) |
| Storage cost | Lower (only clean data loaded) | Higher (raw loaded first) |
| Flexibility | Less (schema decided before load) | More (raw available for re-transform) |
| Example | Spark cleans, then loads Redshift | Load raw to Snowflake, dbt transforms |
-- ELT: transform INSIDE the warehouse after raw load
CREATE TABLE curated.daily_summary AS
SELECT account_id,
SUM(amount_inr) AS total_spend,
COUNT(*) AS txn_count
FROM raw.transactions
WHERE txn_date = '2024-10-29'
AND amount_inr > 0
GROUP BY account_id;
What are common ETL mistakes?
The most dangerous mistakes in banking ETL involve correctness and duplicates.
Wrong: using double for money
// WRONG: floating point corrupts financial totals
double a = 0.1 + 0.2;
System.out.println(a); // 0.30000000000000004
Correct: using BigDecimal for money
import java.math.BigDecimal;
BigDecimal a = new BigDecimal("0.1")
.add(new BigDecimal("0.2"));
System.out.println(a); // 0.3
Wrong: append-only load without dedup
-- WRONG: re-running creates duplicates
INSERT INTO warehouse.transactions (txn_id, amount)
VALUES ('T1', 15000);
-- Run again: now T1 appears twice!
Correct: upsert for idempotency
-- CORRECT: re-running is safe
INSERT INTO warehouse.transactions (txn_id, amount)
VALUES ('T1', 15000)
ON CONFLICT (txn_id) DO UPDATE SET amount = EXCLUDED.amount;
-- Run again: still one row for T1
What are data-quality gates?
A senior data engineer places validation checks between stages so a bad extract never silently poisons the warehouse. These gates check row counts, null rates, value ranges, and schema conformance.
package com.bank.etl;
import java.math.BigDecimal;
import java.util.List;
public class DataQualityGate {
record Transaction(String txnId, BigDecimal amount) {}
public static void main(String[] args) {
List<Transaction> batch = List.of(
new Transaction("T1", new BigDecimal("15000")),
new Transaction("T2", null),
new Transaction("T3", new BigDecimal("-500")));
long total = batch.size();
long nulls = batch.stream()
.filter(t -> t.amount() == null).count();
long invalid = batch.stream()
.filter(t -> t.amount() != null
&& t.amount().compareTo(BigDecimal.ZERO) <= 0)
.count();
double nullRate = (double) nulls / total * 100;
System.out.println("Total: " + total);
System.out.println("Nulls: " + nulls
+ " (" + nullRate + "%)");
System.out.println("Invalid: " + invalid);
if (nullRate > 10.0) {
System.out.println("GATE FAILED: null rate too high."
+ " Quarantining batch.");
} else {
System.out.println("GATE PASSED: proceeding to load.");
}
}
}
Total: 3
Nulls: 1 (33.333333333333336%)
Invalid: 1
GATE FAILED: null rate too high. Quarantining batch.
When should you use ETL vs ELT?
Use ETL when you need strict control over what enters the target, when the target has limited compute, or when regulatory requirements demand that only validated data reaches the warehouse. Use ELT when the target is a powerful cloud warehouse with cheap storage and elastic compute, or when you want raw data available for re-transformation as business needs change.
ETL flow: Source ──▶ [Spark: clean + join] ──▶ Warehouse (clean only) ELT flow: Source ──▶ Warehouse (raw zone) ──▶ [dbt: clean + join] ──▶ Warehouse (curated zone)
What are common interview follow-up questions?
- How do you handle late-arriving data in an ETL pipeline?
- What is the difference between a full load and an incremental load?
- How do you test an ETL pipeline?
- What happens when the source schema changes mid-pipeline?
- How do you monitor ETL jobs in production?
Visual Overview
ETL Pipeline Flow
─────────────────
┌──────────┐ ┌──────────────┐ ┌──────────────┐
│ EXTRACT │────▶│ TRANSFORM │────▶│ LOAD │
│ │ │ │ │ │
│ Database │ │ Clean nulls │ │ Upsert into │
│ Kafka │ │ Deduplicate │ │ warehouse │
│ API │ │ Join ref data│ │ (idempotent) │
│ Files │ │ Convert types│ │ │
└──────────┘ └──────────────┘ └──────────────┘
│ │ │
▼ ▼ ▼
[watermark] [quality gate] [conflict key]
ETL vs ELT
──────────
ETL: Source ──▶ Spark (T) ──▶ Warehouse (clean)
^^^^^^^^
Transform before load
ELT: Source ──▶ Warehouse (raw) ──▶ dbt (T) ──▶ Warehouse (curated)
^^^^^^^^
Transform after load
Key Takeaway
ETL stands for Extract, Transform, Load, and it is the most common task in data engineering. Extract is reliably reading data from source systems like databases, APIs, streams, or files without disturbing them. Transform is where most of the logic lives: cleaning nulls, filtering, deduplicating, joining to reference data, converting types and currencies, and computing derived fields. Load is writing the prepared result into a target like a data warehouse or data lake. A sibling pattern called ELT flips the order, loading raw data first and transforming inside a powerful target, which suits modern cloud warehouses.
There are several senior concerns that separate production ETL from toy scripts. For extract, always pull incrementally using a watermark rather than re-reading the whole source every run. For transform, always do money math in BigDecimal because floating point would corrupt financial totals, and enrich each transaction by joining to reference data. For load, use upserts keyed by a stable id so that re-running a failed job never creates duplicates. Around all of it, add orchestration with Airflow for scheduling, dependencies, and retries, and data-quality gates between stages so a bad extract is quarantined rather than silently poisoning the warehouse.
In a banking context at the National Bank of India, the nightly job pulled raw transactions that carried only an account id, filtered out dirty rows like a null amount, enriched each surviving row by joining to the account reference table to attach Rajesh's or Priya's name, and upserted the result into the warehouse keyed by transaction id. The job was deliberately made safe to re-run, so even if it ran twice the warehouse row count stayed stable.
That idempotency is critical in banking, because a duplicated transaction in a report is not a cosmetic bug: it can mislead a regulator or a customer about real money.
Remember one sentence: ETL extracts raw data, transforms it into clean enriched records, and loads it idempotently so the warehouse is always trustworthy and safely re-runnable.
---