ETL Framework

ETL stands for Extract, Transform, Load. It is the process that moves data from many source systems into the data warehouse in a clean, consistent form.

Flow

Sources (DBs, files, apps) -> EXTRACT -> Staging area -> TRANSFORM -> LOAD -> Data warehouse

Stages

Stage	What happens
Extract	Read the required data from heterogeneous sources (databases, flat files, APIs). Can be full extraction or incremental (only new or changed data, using timestamps or change data capture), without affecting the operational systems
Transform	Done in a staging area. Includes cleaning (fix errors, missing values, duplicates), standardization (formats, units, codes), integration (merge sources, key mapping), derivation (calculated fields), aggregation and applying business rules
Load	Write the transformed data into the warehouse: initial full load first, then periodic incremental/refresh loads; also update indexes, aggregates and data marts

Supporting elements

• Staging area: temporary storage where raw data is held and transformed.
• Metadata: records mappings, rules and lineage.
• Scheduling, logging and error handling: to run loads on time and track rejected records.

ETL vs ELT

In ELT the raw data is loaded first and transformed inside the target system (common with cloud and big data platforms).

Tools

Informatica, Talend, Microsoft SSIS, Apache NiFi, Pentaho.

Importance and challenges

• ETL gives decision-makers accurate, integrated and consistent data.
• Challenges: data quality, large volumes, load-time windows, changing source schemas and keeping history.