ETL: Extract, Transform, Load
ETL moves data through three phases from sources to containers. It handles one or more inputs and outputs via software that automates the process on recurring schedules or in batches. The footgun is defaulting to manual runs when automation is typical.
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.
Interview question
Which of the following describes a proper ETL implementation?
- a.A system operator manually extracts data, reformats it, and loads it into a report each week.
- b.A scheduled process automatically extracts data from several sources, transforms it, and loads it into a central database.Correct
- c.A script automatically copies raw backup files to a secondary server every night without modifying them.
- d.A database administrator migrates a single table to a new system as a one-time task.
Why? this is the answer
ETL is intended as an automated, three-phase pipeline run on recurring schedules, which option B illustrates. Option A represents the manual-run footgun the card warns against, while B omits the required transformation phase and D describes a one-time task lacking recurring automation.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #etl
- #data-pipeline
- #automation
- #batch-jobs
- #data-engineering
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on etl — each one lists the topics its interview covers.
See open roles