Essential Transformations used in the ETL Pipeline
- Overview
- Transaction Control Transformation
- Sequence Generator Transformation
- Update Strategy Transformation
- Union Transformation
- Normaliser Transformation
- Rank Transformation
- Sorter Transformation
- Aggregator Transformation
- Router Transformation
- Joiner Transformation
- Look up Transformation
- Author Details
ETL Transformations are essential for unlocking Data Insights and crucial for Enhanced Analytics.
Design, Develope and Data validation of several Transformations are performed, which are crucial for ETL Pipeline.
Transformations are Designed and Developed with the Help of Informatica Powercenter.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Lookup Transformation is performed on a lookup table, to get the department names.
• TCT (Transaction control transformation) is used to get the File names extracted based upon the department name.
• Separate target files need not be mapped. Based on the file name feature in the flat file. Files will be saved dynamically. TCT Commands helps to achieve this requirement.
• Transaction control Commands: (TC_Continue_Transaction, TC_Commit_Before, TC_Commit_After, TC_Rollback_Before, TC_ Rollback _After)
• Check Run properties and session logs for the detailed report
• Validate source and target with necessary SQL Queries
• Check all the target files for data quality, completeness and correctness.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Sorter Transformation is used to get the distinct values in a column.
• Aggregator Transformation will also give unique values in a column
• Unconnected Lookup Transformation is used to get the Department_Name from Department_ID
• Sequence generator is used to generate a integer numbers to a row with incremental values
• Check Run properties and session logs for the detailed report
• Validate source and target with necessary SQL Queries
• Check all the target files for data quality, completeness and correctness.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Update Strategy is used to perform DML Operations. These commands are used (DD_Insert, DD_Update, DD_Delete, DD_Reject)
• Make use of necessary Commands as per the requirement.
• Check Run properties and session logs for the detailed report
• Validate source and target with necessary SQL Queries
• Check Target for data quality, completeness and correctness.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Union Transformation is used to Merge the data from two different sources, it is necessary to have the same column fields Data types, Count and order.
• Check Run properties and session logs for the detailed report
• Validate source and target
• Check all the target files for data quality, completeness and correctness.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Normaliser Transformation is used to UNPVOT the table data. GCID (Generated Column ID) and GK (Generated Key) helps to transform the data.
• Check Run properties and session logs for the detailed report
• Validate source and target
• Check target for data quality, completeness and correctness.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Rank transformation is used to get the records based on the rank defined at the port level in Informatica Designer
• Bottom or Top options can be selected to get Ascending or Descending values.
• Check Run properties and session logs for the detailed report
• Validate source and target
• Check target for data quality, completeness and correctness.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Expression transformation used to transform data in to the necessary format
• Sorter transformation is used to input the data of a column in ascending or Descending order.
• Select the keys of a column to sort in ascending or descending.
• Check Run properties and session logs for the detailed report
• Validate source and target with necessary SQL Queries
• Check Weather the data is sorted in the Target Tables
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Aggregator Transformation is used to Group data and perform aggregator functions on the data
• Check Run properties and session logs for the detailed report
• Validate source and target with necessary SQL Queries
• Check all the target files for data quality, completeness and correctness.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Router Transformation is used to Route the data according to the groups formed based on the condition specified
• Check Run properties and session logs for the detailed report
• Validate source and target with necessary SQL Queries
• Check all the target tables for data quality, completeness and correctness.
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Joiner Transformation is performed when data extracted from two different sources.
• Joining condition can be Normal join, Master outer/ Detail outer or Full outer join (inner, left, right, Full outer joins)
• Master Table – Less Records, Detail Table – More Records
• Check Run properties and session logs for the detailed report
• Validate source and target with necessary SQL Queries
• Check all the target tables for data quality, completeness and correctness.
• Use Minus queries to check for truncation of data
• Import source and target details to the Designer
• Design Mapping from source to target with necessary transformation
• Lookup Transformation is performed on a lookup table, it can be connected or unconnected transformation.
• If Lookup is connected in the data flow, then it is connected. Or else it is unconnected. Connected LKP can return more than one column but unconnected Lookup returns only one column values
• Look up transformation by default provides Left outer join and it provides option with (<,>, =, =) operators while specifying joining condition
• Check Run properties and session logs for the detailed report
• Validate source and target with necessary SQL Queries
• Check Lookup column values for null values
• Check all the target tables for data quality, completeness and correctness.
SQL, ETL, Python, Power BI...
