ETL: Extract, Transform, Load

WHY IT EXISTS: Data resides in input sources and must reach output data containers. Without a defined process, moving that data from origin to destination lacks structure. ETL exists to provide a three-phase computing process that bridges input sources and output containers in a repeatable sequence.

THE MENTAL MODEL: Imagine a pipeline with three stations. At the first station, data is drawn from one or more input sources. At the second station, the data is transformed. At the third station, the data is loaded into one or more output data containers. The pipeline moves information from origin to destination in a fixed order.

HOW IT WORKS: The first phase is extraction, where data is extracted from an input source. The second phase is transformation, where the data is transformed. The third phase is loading, where the data is loaded into an output data container. The process can collect data from one or more sources. It can also output data to one or more destinations. ETL processing is typically executed using software applications. It can also be done manually by system operators. ETL software typically automates the entire process. This automation can be run manually or on recurring schedules. The work can be structured as single jobs. It can also be aggregated into a batch of jobs.

WHEN TO USE IT: Use ETL when you need a three-phase computing process to move data from input sources into output data containers. It applies when data must be collected from one or more sources and delivered to one or more destinations. It is appropriate when the work should be executed using software applications that automate the entire process rather than performed manually by system operators.

WHEN NOT TO USE IT: Avoid ETL when data does not need to pass through a three-phase sequence from input source to output container. If the work does not require extraction, transformation, and loading, the structure is unnecessary. Likewise, if there is no need for recurring schedules or batching of single jobs, the full process may be more than the task demands.

ONE CANONICAL EXAMPLE: An organization uses ETL software to move data. The software extracts data from multiple input sources. It transforms the data. It loads the data into an output data container. The organization runs the process on recurring schedules. The schedules aggregate the work into a batch of jobs. The software automates the entire process instead of requiring system operators to perform the steps manually.