Example 1

Assume that there are two “Sales” tables, one source “Table 1” and another target “Table 2”.

In the source “Table 1”, you have updated an existing price of a product to a new value from 2016 onwards. You want to insert the new prices in the target “Table 2” as well as retain the old price till 2015. In this case, you must map the source operation “Modify” to “Insert”.

This will not change all the values in “Table 2” to the new price value, but new records will be inserted into the target table with another key (to calculate another unique key, you can define an expression for the required key column in the FeatureMap Designer) and the old price values for the product will be retained.

Example 2

Assume that a bank has two databases, source table “Payment Database” (to track the payment), and target table “Debt Database” (to track the debts).

Whenever a customer uses his credit card, a record will be inserted in the “Debt Database”, and whenever he pays the debts, a record is inserted into the “Payment Database”. In this case, you must map the source command Insert to Delete in the target. So, whenever the customer pays his debts, the Insert command in source table “Payment Database” is substituted to Delete command in target table “Debt Database”.

Example 3

Assume that in Example 2, the customer wants to keep the debt records for a specific period of time (say 2016-09-01 to 2016-10-01), and not delete it even when he pays his debts.

In this case, you cannot map the source command Insert to Delete in the target. For this, you need to set a conditional DML substitution at the classifier level, by mapping the source command Insert to Update in the target, with a filter condition "TransactionDate> 2016-09-01 && TransactionDate <= 2016-10-01".

By setting this DML command substitution condition, the debt record will not be deleted from the target table “Debt Database”, even when the customer pays his debts within that specified period of time. The debt records will be updated with markup only.

However, when the condition is false and a transaction happens beyond this specified time limit, it will work as in Example 2. It will inherit the Delete target command from the transformation level. As a result, the debt records will be deleted, whenever the customer pays his debts.

Example 4

Assume that there are two employee tables, source “Employee Table 1” and target “Employee Table 2”.

In the source “Employee Table 1”, one “EMP1” record is deleted. But, you want to retain the same “EMP1” record in the target “Employee Table 2”. In this case, you must map the source command Delete to <Ignore> in the target.