ETL: The Three-Phase Data Pipeline

WHY IT EXISTS: Data originates in input sources and must ultimately reside in output containers. ETL exists to formalize that movement as a disciplined three-phase pipeline, extracting the data, transforming it, and loading it, rather than leaving the work as an ad-hoc manual procedure.

THE MENTAL MODEL: Imagine a factory conveyor belt with three distinct stations. At station one, raw materials are gathered from one or more input sources. At station two, those materials are modified. At station three, the finished goods are placed into one or more output containers. The belt is driven by software that automates the entire line, and it can be started by an operator or set to run on a recurring schedule.

HOW IT WORKS: The first phase is extraction, where data is pulled from input sources. The second phase is transformation, where that data is altered. The third phase is loading, where the transformed data is placed into output data containers. ETL software automates the entire process end to end. The process can be executed manually by a system operator, or it can be scheduled to run automatically. Individual jobs can run alone, or multiple jobs can be aggregated into a batch of jobs that execute together.

WHEN TO USE IT: Use ETL whenever data needs to move from input sources to output containers and must pass through a transformation step along the way. It is the right choice when the workflow is recurring, when data originates from multiple sources, when it must be delivered to multiple destinations, or when the operation should be handled by automated software rather than repeated manual effort.

WHEN NOT TO USE IT: Do not use ETL when data does not need to move between containers, or when the task is a genuine one-time transfer that a system operator can handle manually without automation. In those cases, the three-phase structure adds unnecessary overhead.

ONE CANONICAL EXAMPLE: Consider a scenario where data is collected from several distinct input sources and must be unified in a single output container. The ETL process extracts the data from each source, transforms it, and loads it into the output container. The software automates the entire sequence and runs it as a batch job on a recurring schedule, eliminating the need for a system operator to perform the work manually every time.