ETL Transformation process

ETL Transformation process :: The transform stage applies to a series of rules or functions to the extracted data from the source to derive the data for loading into the end target. Some data sources will require very little or even no manipulation of data. In other cases, one or more of the following transformations types to meet  the business and technical needs of the end target may be required: 

• ƒ Selecting only certain columns to load (or selecting null columns not to load) 
• ƒ Translating coded values and automated data cleansing 
• ƒ Encoding free-form values 
• ƒ Deriving a new calculated value 
• ƒ Filtering 
• ƒ Sorting 
• ƒ Joining data from multiple sources 
• ƒ Aggregation 
• ƒ Generating surrogate-key values 
• ƒ Transposing or pivoting columns 
• ƒ Splitting a column into multiple columns 

Applying any form of simple or complex data validation. If validation fails, it may result in a full, partial or no rejection of the data, and thus none, some or all the data is handed over to the next step, depending on the rule design and exception handling. Many of the above transformations may result in exceptions, for example, when a code translation parses an unknown code in the extracted data.

In Source, the data available is First name and Last name. To get the full name, the transformation logic is applied by concatenating both the first and last name. The place where these transformations take place is called the Staging Area.