A daily reporting pipeline may need to move data from several sources into a warehouse. Extract, transform, load (ETL) does this in three stages: collect the data, prepare it in a staging area, then load it into the target system. It suits reporting and historical analysis when immediate results are unnecessary. section 8.1.1 section 5.1.4
The placement and timing of transformation distinguish three approaches:
- ETL cleanses, filters, aggregates, or converts data before loading it. It typically runs in periodic batches. section 8.1.1
- Extract, load, transform (ELT) loads raw data first and transforms it in the target system. section 8.1.1
- Stream processing transforms arriving events in real time. Batch ETL can be simpler and more efficient when immediate analysis is not needed. section 8.1.1 section 5.1.4
Migrating Kafka event data is a related but distinct task. An incompatible schema change cannot be handled automatically by Kafka: producers, consumers, and existing messages may need a coordinated transition. For a non-compacted topic, one option is to drain old messages, register the new schema, upgrade clients, and resume. Separate versioned topics are another option; maintaining availability may require temporary dual reads or writes. section 6.5.2
A compacted topic stores state, so an incompatible change requires migrating all messages. A dedicated component transforms them into a new topic while preserving keys and timestamps; producers and then consumers move to that topic. This is a data migration, not simply a scheduled ETL load. section 6.5.3
Moving production data into UAT
A controlled pipeline can mask sensitive fields, transform records to the UAT schema, and transfer them periodically. Direct real-time copying risks exposing confidential data and may fail when UAT and production use different schema versions. section 10.2.4
Figure 8.2 An example of the ETL process. Data is loaded from various data sources into the staging area, transformed there, and then loaded into the target system.
The diagram locates ETL’s transformation step between source extraction and loading into the target system. section 8.1.1
