Whether you want to test, build or analyse data pipelines, the same handful of ETL concepts come up again and again, in projects and in interviews. This guide explains each one with a small example and the SQL behind it. For the warehouse side (facts, dimensions, schemas), see data warehouse concepts.
The ETL Flow in One Picture
Sources (DB, files, APIs) | Extract (full / incremental / CDC) Staging (raw copy, audit columns) | Transform (clean, standardise, derive, lookup, aggregate) Target (insert / upsert / SCD) -> rejects & audit logs | Orchestration: schedule, dependencies, retries
New to ETL itself? Start with ETL full form and what is an ETL pipeline.
Extraction Concepts
| Concept | What it means | Example |
|---|---|---|
| Full extraction | Read the whole source every run | Small reference tables such as country codes |
| Incremental extraction | Read only rows changed since the last run, using a timestamp or ID watermark | WHERE updated_at > last_run_time |
| Change data capture (CDC) | Read inserts, updates and deletes from the database log | Capturing deleted orders that a timestamp would miss |
| Watermark / control table | Stores where the last run stopped | ctl.watermark(table_name, last_loaded_ts) |
| File and API extraction | Reading CSV/JSON files or paginated APIs | Daily bank statement files |
-- Incremental extraction with a watermark SELECT * FROM src.orders WHERE updated_at > (SELECT last_loaded_ts FROM ctl.watermark WHERE table_name = 'orders');
Transformation Types
| Type | Example |
|---|---|
| Cleansing | TRIM spaces, fix invalid dates, remove junk characters |
| Standardisation | Upper-case state codes; one date format; "Bengaluru" vs "Bangalore" mapped to one value |
| Deduplication | Keep the latest record per customer |
| Derivation | total_amount = quantity × unit_price − discount |
| Lookup / enrichment | Replace product_code with product_key from the dimension |
| Filtering | Exclude test accounts and cancelled orders |
| Aggregation | Daily sales per store |
| Joining and splitting | Join orders with customers; split full_name into first and last name |
| Type conversion | Text "01-10-2026" to DATE |
-- Deduplication: keep the latest version of each customer SELECT * FROM ( SELECT c.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM stg.customers c ) t WHERE rn = 1;
Load Strategies
| Strategy | How it works | Use when |
|---|---|---|
| Truncate and load (full refresh) | Empty the target, reload everything | Small tables; simple and safe to re-run |
| Append (insert-only) | Add new rows only | Event logs, transactions that never change |
| Upsert / MERGE | Update existing keys, insert new ones | Master data such as customers and products |
| SCD Type 2 | Expire the old row, insert a new version | When history matters. See SCD testing |
-- Upsert with MERGE (syntax varies slightly by database) MERGE INTO dw.dim_product t USING stg.products s ON t.product_code = s.product_code WHEN MATCHED THEN UPDATE SET t.product_name = s.product_name, t.category = s.category WHEN NOT MATCHED THEN INSERT (product_code, product_name, category) VALUES (s.product_code, s.product_name, s.category);
Staging and Layers
A staging area holds raw extracted data so transformations never touch source systems and failed runs can restart from staging. Modern lakehouses use the same idea with bronze (raw), silver (cleaned) and gold (business-ready) layers. See data lake testing.
Errors, Rejects and Audit Columns
- Reject (error) table: rows that fail rules are written here with a reason, not silently dropped. Testers reconcile source = target + rejects.
- Audit columns: load_id, load_ts, source_file and created_by on every row make defects traceable.
- Job logs: rows read, written and rejected per run.
- Data quality checks: nulls, ranges and duplicates checked during or after the load. See data quality checks.
Re-runs and Idempotency
A pipeline is idempotent when running it twice for the same period gives the same result as running it once. Without that, a retried job creates duplicates. Common techniques: delete-and-reload the batch period, MERGE on keys instead of plain INSERT, and unique constraints on business keys. Testing tip: run the same load twice and check for duplicates.
-- Re-run safety check after loading the same day twice SELECT order_id, COUNT(*) FROM dw.fact_orders WHERE order_date = '2026-10-01' GROUP BY order_id HAVING COUNT(*) > 1; -- expect 0 rows
Scheduling and Dependencies
ETL jobs run on schedules (nightly, hourly) or events (file arrival), and in order: dimensions before facts, so every fact can find its keys. Orchestrators such as Apache Airflow, Azure Data Factory or Informatica workflows handle dependencies, retries and alerts. Late-arriving facts are usually loaded with an "unknown" dimension key and fixed later. Tools overview: ETL tools list.
ETL Concepts Interview Questions
1. What is the difference between full and incremental load?
A full load reloads all data each run; an incremental load processes only new or changed data since the last run, usually tracked with a watermark.
2. What is CDC?
Change data capture: reading inserts, updates and deletes from source logs so downstream systems receive every change, including deletes.
3. Why use a staging area?
To isolate sources from transformation work, allow restarts from staged data, and make validation easier.
4. What is an upsert?
Update the row if the key exists, otherwise insert it, typically with MERGE.
5. What does idempotent mean in ETL?
Running the same load twice gives the same result as once, so retries do not create duplicates.
6. Why load dimensions before facts?
So fact rows can find their dimension keys; otherwise they become orphans or need an unknown member.
More: ETL interview questions and answers and the free mock test.
Data warehouse basics: SCD types · data warehouse concepts · star vs snowflake schema · fact and dimension tables · data reconciliation · data warehouse testing · data warehouse interview questions
More guides for Indian students: ETL testing jobs in India · ETL full form · ETL testing with example · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · SQL for testers · QA to data engineer · ETL testing kya hai (Hindi) · ETL testing projects for practice · ETL testing notes · work from home ETL jobs · ETL course in Chennai & Bangalore
Frequently Asked Questions
What are the basic ETL concepts?
Extraction (full, incremental, CDC), transformation types (cleansing, standardisation, deduplication, derivation, lookups, aggregation), load strategies (truncate-load, append, upsert, SCD), staging, error handling, audit columns, idempotent re-runs and scheduling.
What are ETL concepts in a data warehouse?
They are the steps that move data from source systems into warehouse fact and dimension tables: extracting changes, transforming them to business rules and loading them with the right strategy, usually dimensions before facts.
What ETL concepts are asked in interviews?
Full vs incremental load, CDC, staging, transformation types, upsert and SCD loading, reject handling, idempotency and job dependencies are among the most common.
What is the difference between ETL and ELT?
ETL transforms data before loading it into the target; ELT loads raw data first and transforms it inside the warehouse. See the ETL vs ELT guide on this site.
Do ETL testers need to know ETL concepts?
Yes. Testers design checks around these concepts, for example re-running loads to test idempotency or reconciling source with target plus rejects.
Asim Noaman Lodhi
Certified Google Partner · QA Consultant · 12+ Years IT
QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.