Informatica数据库加载：同记录多更新及SCD2场景处理规则问询

Question 1: Handling Duplicate Updates for the Same Record in a Single File #

When loading data into a database with Informatica, if you have two update operations targeting the same record in the same input file, the outcome hinges on your mapping’s logic—but here’s the default, out-of-the-box behavior:

• Informatica processes records in the exact order they appear in the file, with no automatic deduplication unless you explicitly set it up.
• If your mapping uses a standard "update existing, insert new" strategy (no special aggregation or sorting steps), the last update in the file will overwrite the previous one. For example, if the first update sets status = 'active' and the second sets status = 'inactive' for the same user_id, the final record in the database will stick with status = 'inactive'.
• To avoid this overwriting, you’d need to add transformations like a Sorter (to order records by a timestamp/sequence) or an Aggregator (to pick the latest valid record based on a key) before the target load step.

Question 2: Loading Multiple Updates for a New Record into an SCD2 Table #

Let’s walk through exactly what happens with your xyz SCD2 table and the two id=2 records. First, a quick SCD2 refresher: this type preserves historical data by marking old records as inactive (act_flg = 'N') with an end date, and creating new active records (act_flg = 'Y') with a start date and null end date whenever values change.

Step 1: Process the first id=2 record (2 vipul abc,z mumbai) #

• Since id=2 doesn’t exist in the existing table, this is treated as a brand-new record.
• A new row gets inserted into xyz:
  id: 2, name: vipul, add: abc,z, city: mumbai, act_flg: Y, start_dtm: [load date], end_dtm: null
• The original id=1 record stays completely unchanged.

Step 2: Process the second id=2 record (2 vipul asdf bangalore) #

• Informatica detects that id=2 already has an active record, and the add and city values have changed—this triggers an SCD2 update.
• First, the existing active id=2 record is updated:
  act_flg: N, end_dtm: [load date] (matches the timestamp of this current load)
• Then, a new active record is inserted with the updated values:
  id: 2, name: vipul, add: asdf, city: bangalore, act_flg: Y, start_dtm: [load date], end_dtm: null

Final Table State #

After loading both records, your xyz table will have 3 rows total:

1. The original id=1 record (unchanged)
2. The inactive id=2 record with add: abc,z and city: mumbai
3. The active id=2 record with add: asdf and city: bangalore

内容的提问来源于stack exchange，提问作者Amit Kumar