We will be using Informatica Mapping Variables to building our Change Data Capture logic. Before even we talk about the implementation, lets understand the Mapping Variable
Informatica Mapping Variable:
What is Mapping Variable:These are variables created in PowerCenter Designer, which you can use in any expression in a mapping, and you can also use the mapping variables in a source qualifier filter, user-defined join, or extract override, and in the Expression Editor of reusable transformations.
Mapping Variable Starting Value
- Parameter file
- Pre-session variable assignment
- Value saved in the repository
- Initial value
- Default Value
Setting Mapping Variable Value
- SetMaxVariable. Sets the variable to the maximum value of a group of values. To use the SetMaxVariable with a mapping variable, the aggregation type of the mapping variable must be set to Max.
- SetMinVariable. Sets the variable to the minimum value of a group of values. use the SetMinVariable with a mapping variable, the aggregation type of the mapping variable must be set to Min.
- SetCountVariable. Increments the variable value by one. In other words, it adds one to the variable value when a row is marked for insertion, and subtracts one when the row is marked for deletion. To use the SetCountVariable with a mapping variable, the aggregation type of the mapping variable must be set to Count.
- SetVariable. Sets the variable to the configured value. At the end of a session, it compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final value to the repository.
Change Data Capture Implementation:
- $$M_DATA_END_TIME as Date/Time
- STG_CUSTOMER_MASTER.UPDATE_TS> CONVERT(DATETIME,'$$M_DATA_END_TIME')
Note : This filter condition will make sure that, latest data is pulled from the source table each and every time. Latest value for the variable $M_DATA_END_TIME is retrieved from the repository every time the session is run.