Explain the ETL Process of a Data Warehouse with a block diagram. How is it different from ELT?

Data is pulled from multiple heterogeneous source systems (OLTP databases, flat files, APIs, spreadsheets). Only the relevant data needed for the warehouse is extracted. Two types: Full extraction (entire source) and Incremental extraction (only changed records).

The core stage. Raw data is cleaned (remove nulls, duplicates), validated (enforce business rules), converted (data type mapping, format standardisation), and integrated (resolve schema conflicts, de-duplicate across sources). Aggregations and surrogate key generation also happen here.

Transformed, clean data is loaded into the target Data Warehouse. Two approaches: Full Load (replaces all data — used initially) and Incremental Load (appends only new/changed records — used for regular updates). Bulk loading tools are used for efficiency.

Transformation happens BEFORE loading, in a separate staging server or ETL engine. Best for structured data and when the target warehouse has limited processing power. Traditional approach.

Data is loaded into the target system FIRST, then transformed INSIDE the warehouse using its own processing power. Best for cloud data warehouses (e.g. Snowflake, BigQuery) that have massive compute. More modern approach.