Editing Extract, transform, load (section)

{{Short description|Procedure in computing}}
{{More citations needed|date=September 2024}}
[[File:Extract, Transform, Load Data Flow Diagram.svg|alt=Conventional ETL architecture|thumb|upright=2|Conventional ETL architecture]]
{{Data transformation}}

'''Extract, transform, load''' ('''ETL''') is a three-phase [[computing]] process where data is [[Data extraction|''extracted'']] from an input source, [[Data transformation|''transformed'']] (including [[Data cleaning|cleaning]]), and [[Data loading|''loaded'']] into an output data container. The data can be collected from one or more sources and it can also be output to one or more destinations. ETL processing is typically executed using [[software application]]s but it can also be done manually by system operators. ETL software typically automates the entire process and can be run manually or on recurring schedules either as single jobs or aggregated into a batch of jobs.

A properly designed ETL system extracts data from source systems and enforces data type and data validity standards and ensures it conforms structurally to the requirements of the output. Some ETL systems can also deliver data in a presentation-ready format so that application developers can build applications and end users can make decisions.<ref name="Kimball 2004">{{Cite book|title=The data warehouse ETL toolkit : practical techniques for extracting, cleaning, conforming, and delivering data|last=Ralph.|first=Kimball|date=2004|publisher=Wiley|others=Caserta, Joe, 1965-|isbn=978-0764579233|location=Indianapolis, IN|oclc=57301227}}</ref>

The ETL process is often used in [[data warehouse|data warehousing]].<ref>{{Cite journal|last=Denney|first=MJ|date=2016|title=Validating the extract, transform, load process used to populate a large clinical research database|journal=International Journal of Medical Informatics|volume=94|pages=271–4|doi=10.1016/j.ijmedinf.2016.07.009|pmc=5556907|pmid=27506144}}</ref> ETL systems commonly integrate data from multiple applications (systems), typically developed and supported by different [[vendor]]s or hosted on separate computer hardware. The separate systems containing the original data are frequently managed and operated by different [[stakeholder (corporate)|stakeholder]]s. For example, a cost accounting system may combine data from payroll, sales, and purchasing.

Data extraction involves extracting data from homogeneous or heterogeneous sources; data transformation processes data by data cleaning and transforming it into a proper storage format/structure for the purposes of querying and analysis; finally, data loading describes the insertion of data into the final target database such as an [[operational data store]], a [[data mart]], [[data lake]] or a data warehouse.<ref>{{Cite web|url=https://www.edq.com/blog/what-is-etl-extract-transform-load/|title=What is ETL? (Extract, Transform, Load) {{!}} Experian|last=Zhao|first=Shirley|date=2017-10-20|website=Experian Data Quality|access-date=2018-12-12}}</ref><ref>{{Cite web|url=https://www.theregister.co.uk/2018/06/04/data_integration_is_hard/|title=Extract, transform, load? More like extremely tough to load, amirite?|first=Trevor|last=Pott|date=4 June 2018|website=[[The Register]]|access-date=2018-12-12}}</ref>