Data Extraction, Cleanup and Transformation Tools (ETL)

Q: What is ETL?

Extract data from heterogeneous sources, Transform it (clean, convert, aggregate) and Load it into the warehouse.

Q: What is change data capture (CDC)?

Capturing only the changes made to source data, typically by reading database logs, so that only new and modified rows are processed.

Q: What is binning?

Sorting data and distributing it into bins, then smoothing each value using its bin mean, median or boundaries to remove noise.

Q: Differentiate full refresh, incremental load and merge.

Full refresh reloads everything, incremental only appends new rows, and merge updates existing keys and inserts new ones.

Q: Why are rejected rows stored in an error table?

So bad records can be reviewed, corrected and reprocessed without stopping the load or losing data.

Source systems differ in DBMS, data model, coding schemes, units and quality. ETL converts this heterogeneous, dirty data into a single clean, consistent, integrated warehouse format.

Important

Know the ETL architecture diagram, the extraction techniques, data cleaning with a binning example, and a few ETL tools. The binning numerical is a popular 5-mark question.

1. ETL Architecture

MermaidDiagram

Rendering diagram…

2. Extraction Techniques

Technique	Mechanism	Pros	Cons
Full extraction	Copy the entire source table each run	Simple, always consistent	Very heavy for large tables
Timestamp-based delta	`WHERE last_modified > last_run`	Cheap and simple	Needs a reliable timestamp; misses hard deletes
Log-based / CDC	Read the DBMS transaction or redo logs	Fast, near real time, low source impact	Tool and DBMS specific
Trigger-based	Triggers fill a shadow table	Captures every change immediately	Overhead on OLTP, brittle
File comparison	Compare today's extract file with the previous one	No source load	Needs full file transfer

3. Data Cleaning

(a) Missing values

Ignore the tuple (when the class label is missing).
Fill with a global constant, or the attribute mean, median or mode.
Fill with the most probable value predicted by Bayes, a decision tree or regression.

\bar{x} = \frac{1}{n}\sum_{i=1}^{n} x_i \quad \text{(mean imputation)}

(b) Noisy data

Noise is random error or variance in a measured value.

Binning: sort, split into bins, then smooth by bin means, medians or boundaries.
Clustering: detect and remove outliers that fall outside clusters.
Regression: fit $y = a + bx$ and use the fitted value.
Manual inspection with domain rules.

Pro TipBinning example (equal-frequency, depth 4)

Sorted data: 4, 8, 15, 21, 21, 24, 25, 28, 34

Bin 1: 4, 8, 15, 21 gives mean 12
Bin 2: 21, 24, 25, 28 gives mean 24.5
Bin 3: 34 (remainder)

Smoothing by bin means replaces every value in a bin with that bin's mean.

(c) Duplicates and entity resolution

Exact-match de-duplication, fuzzy matching (edit distance, Jaro-Winkler), and merge-purge with survivorship rules.

(d) Inconsistencies

Unify codes: M, m, Male all become Male; 0/1 becomes Female/Male.

4. Transformation Activities

Standardize units, currency and date formats (DD-MM-YYYY to YYYY-MM-DD).
Encoding and surrogate key generation for dimensions.
Derived attributes: age = current_date - DOB, amount = qty * price.
Aggregation at the chosen grain.
SCD handling (type 1, 2, 3) while loading dimensions.
Validation rules; rejected rows go to an error table with reason codes.

NetAmount = Qty \times UnitPrice \times (1 - DiscountRate) + Tax

5. Loading Strategies

Strategy	Action	Use when
Full refresh	Truncate, then reload the target	Small tables, no history needed
Incremental append	Insert only new rows	Fact tables (facts are immutable)
Merge (upsert)	Update matching keys, insert new keys	Dimension tables (SCD)

Performance tricks: bulk loaders (SQL*Loader, bcp, LOAD DATA), disable indexes and constraints during load, partition switching, parallel jobs, and an audit row per run (job id, start and end time, rows read, written, rejected).

LoadQuality = \frac{R_{written}}{R_{read}} \times 100\%, \qquad RejectRate = \frac{R_{rejected}}{R_{read}} \times 100\%

6. Popular ETL Tools

Tool	Type	Notes
Informatica PowerCenter	Commercial	Market leader, rich transformations
IBM DataStage	Commercial	Parallel ETL for big volumes
Oracle Data Integrator	Commercial	ELT style, uses the database engine
SSIS	Microsoft, bundled	GUI packages, works well with SQL Server
Talend Open Studio	Open source	Code generation, big data support
Apache NiFi	Open source	Flow-based data movement
Apache Airflow	Open source	Orchestration of scheduled DAGs
Apache Spark / PySpark	Open source	Large-scale transformation and cleaning
Pentaho Data Integration	Open source	Spoon GUI

Exam TrapTypical questions

ETL architecture diagram with extraction, cleaning, transformation and loading (10 marks).
Data cleaning techniques with a binning numerical example (5-7 marks).
List any five ETL tools (2-3 marks).
Difference between full and incremental extraction.

Frequently Asked Questions

QWhat is ETL?

Extract data from heterogeneous sources, Transform it (clean, convert, aggregate) and Load it into the warehouse.

QWhat is change data capture (CDC)?

Capturing only the changes made to source data, typically by reading database logs, so that only new and modified rows are processed.

QHow are missing values handled?

Ignore the tuple, fill with a constant, fill with mean, median or mode, or fill with the most probable value predicted by a model.

QWhat is binning?

Sorting data and distributing it into bins, then smoothing each value using its bin mean, median or boundaries to remove noise.

QDifferentiate full refresh, incremental load and merge.

Full refresh reloads everything, incremental only appends new rows, and merge updates existing keys and inserts new ones.

QWhy are rejected rows stored in an error table?

So bad records can be reviewed, corrected and reprocessed without stopping the load or losing data.

Data Extraction, Cleanup, and Transformation Tools