Understanding NULL Handling in Informatica MDM: Allow NULL Update vs. Apply NULL Values

 NULL handling in Informatica MDM plays a crucial role in data consolidation and survivorship. Two key properties that determine how NULL values are managed are Allow NULL Update and Apply NULL Values. Let’s break them down:

1. Allow NULL Update on the Staging Table

This property controls whether a NULL value can overwrite a non-NULL value during a load job.

• Enabled: A non-NULL value in a column can be updated to NULL.

• Disabled: Prevents NULL updates, retaining existing non-NULL values.

• Behavior in Cross-Referenced (XREF) Records:

  • If a Base Object has a single XREF, a NULL can overwrite a non-NULL value.

  • For multiple XREFs, NULL updates are managed based on the Allow NULL Update setting.

  • To maintain consistency across single and multi-XREF records, a user exit can be implemented.

---

2. Apply NULL Values on the Base Object

This property determines how NULL values are treated during the consolidation process.

• By Default (Disabled):

  • NULL values are automatically downgraded, ensuring non-NULL values survive.

• When Enabled:

  • NULL values are treated normally with trust scores.

  • NULLs may overwrite non-NULL values during put-operations or consolidations.

  • Higher trust scores allow NULL values to survive in the Base Object.

---

3. Comparison: Allow NULL Update vs. Apply NULL Values

---

4. How MDM Determines NULL Survivorship?

For each XREF column, MDM follows these steps:

1. Identify the source stage table:

   • If the XREF record has a non-null STG_ROWID_TABLE, use it.

   • If not, use ROWID_SYSTEM to find the source stage table.

2. If only one source stage table exists:

   • Use the Allow NULL Update setting of that table.

3. If multiple source stage tables exist:

   • If all have the same setting, use it.

   • If inconsistent, refer to Apply NULL Value setting in the Base Object.

4. If no stage table is found, use Apply NULL Value setting in the Base Object.

5. If Allow NULL Update is false, the trust score of NULL values is significantly downgraded, reducing the likelihood of NULLs surviving.

---

5. Operations Affected by NULL Handling

All operations involving Best Version of Truth (BVT) calculation follow these rules, including:

• Load/Put/CleansePut

• Merge/Unmerge

• Recalculate BVT

• Revalidate

By understanding these settings, you can better manage data integrity and ensure accurate MDM processing!