Lesson 2 — ETL, ELT, Mapping and Staging
ETL is a controlled change of representation
Extract selects and acquires the required source data. Good extraction avoids unnecessary source workload and may use a physical staging area or memory.
Transform changes the data so the target can use it correctly. Transformation includes format changes, structural changes, semantic conversion, de-duplication, re-ordering, calculations and other business-rule-driven changes.
Load stores or presents the transformed result to the target. A successful load does not automatically mean the data is fully integrated, governed or ready for every consumer.
Why transformation is dangerous when meaning is unmanaged
A technically successful job can be semantically wrong. If source 1 = current customer is mapped to target ACTIVE ACCOUNT without confirming equivalence, the integration can run perfectly while corrupting business meaning.
That is why transformation rules are both business-governed rules and Metadata.
ETL vs ELT
- ETL: Extract → Transform → Load. Transform happens before target load.
- ELT: Extract → Load raw → Transform in target. Useful when the target has strong transformation capability or retaining raw data in the target is useful, such as some data-lake designs.
Neither is automatically newer, better or more “modern.” The deciding clue is where transformation happens relative to load.
Physical staging vs in-memory flow
Physical staging can:
- preserve intermediate states;
- create audit/restart points;
- support recovery from a known step after failure.
But it adds storage, write/read work and latency. Very-low-latency designs may keep intermediate results in memory.
Trade-off: recoverability/auditability/restartability versus latency/resource overhead.
Mapping vs orchestration
Mapping answers: How does this source element become that target element? It specifies source and target structures, extraction rules, lookups, calculations, updates and transformation logic.
Orchestration answers: What runs when? It defines order, dependencies, triggers, frequency, retries and recovery.
CUSTOMER_STATUS 1 → ACTIVE is mapping. LOAD_CUSTOMER must finish before LOAD_ORDER is orchestration.
Stop and check
A ten-step job fails at step 7 and must restart without repeating the entire run. Persisted staging can provide a recovery point, but it costs storage and extra I/O/latency.
Source: pp. 260–263.