what does extraction transformation and loading mean

Looking up and validating the relevant data from tables or referential files.

Splitting a column into multiple columns ( e.g., converting a comma-separated list, specified as a string in one column, into individual values in different columns).Transposing or pivoting (turning multiple columns into multiple rows or vice versa).Aggregating (for example, rollup - summarizing multiple rows of data - total sales for each store, and for each region, etc.).Joining data from multiple sources ( e.g., lookup, merge) and deduplicating the data.Sorting or ordering the data based on a list of columns to improve search performance.Deriving a new calculated value: ( e.g., sale_amount = qty * unit_price).Encoding free-form values: ( e.g., mapping 'Male' to 'M').Translating coded values: ( e.g., if the source system codes male as '1' and female as '2', but the warehouse codes male as 'M' and female as 'F').Or, the selection mechanism may ignore all those records where salary is not present (salary = null). For example, if the source data has three columns (aka 'attributes'), roll_no, age, and salary, then the selection may take only roll_no and salary. Selecting only certain columns to load: (or selecting null columns not to load).

In other cases, one or more of the following transformation types may be required to meet the business and technical needs of the server or data warehouse: Character sets that may be available in one system may not be so in others. The challenge when different systems interact is in the relevant systems' interfacing and communicating. In the data transformation stage, a series of rules or functions are applied to the extracted data in order to prepare it for loading into the end target.Īn important function of transformation is data cleansing, which aims to pass only 'proper' data to the target. The rejected data is ideally reported back to the source system for further analysis to identify and to rectify incorrect records or perform data wrangling. If the data fails the validation rules, it is rejected entirely or in part. The streaming of the extracted data source and loading on-the-fly to the destination database is another way of performing ETL when no intermediate data storage is required.Īn intrinsic part of the extraction involves data validation to confirm whether the data pulled from the sources has the correct/expected values in a given domain (such as a pattern/default or list of values). Common data-source formats include relational databases, flat-file databases, XML, and JSON, but may also include non-relational database structures such as IBM Information Management System or other data structures such as Virtual Storage Access Method (VSAM) or Indexed Sequential Access Method (ISAM), or even formats fetched from outside sources by means such as a web crawler or data scraping. Each separate system may also use a different data organization and/or format. Most data-warehousing projects combine data from different source systems. In many cases, this represents the most important aspect of ETL, since extracting data correctly sets the stage for the success of subsequent processes. ĮTL processing involves extracting the data from the source system(s). 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.

What does extraction transformation and loading mean