ETL takes data that is heterogeneous and makes it homogeneous. It would be great if data from all these sources had a compatible schema from the outset, but this is rarely the case. When creating a data warehouse, it is common for data from disparate sources to be brought together in one place so that it can be analyzed for patterns and insights. Once loaded, the ETL process is complete, although in many organizations ETL is performed regularly in order to keep the data warehouse updated with the latest data.

Load-The load phase moves the transformed data into the permanent, target database.

The goal of transformation is to make all the data conform to a uniform schema.

Typical transformations include things like date formatting, resorting rows or columns of data, joining data from two values into one, or, conversely, splitting data from one value into two. Transform-In the transformation phase, the data is processed to make values and structure consistent across all data.Data that fails the validation is rejected and further processed to discover why it failed validation and remediate if possible. During extraction, validation rules are applied to test whether data has expected values essential to the data warehouse. Extract-The extraction process is the first phase of ETL, in which data is collected from one or more data sources and held in temporary storage where the subsequent two phases can be executed.The three words in Extract Transform Load each describe a process in the moving of data from its source to a formal data storage system (most often a data warehouse).

ETL also describes the commercial software category that automates the three processes. What do I need to know about ETL?ĭata must be properly formatted and normalized in order to be loaded into these types of data storage systems, and ETL is used as shorthand to describe the three stages of preparing data. Extract Transform Load refers to a trio of processes that are performed when moving raw data from its source to a data warehouse, data mart, or relational database.

Load extract transform