Handling Deletes and Active Flags in the Party Address Domain

Managing data quality and consistency in the Party Address Domain is critical for effective data governance in a Master Data Management (MDM) system. Challenges arise when the source system sends new address IDs for a party (individual or organization) or removes an existing address by sending a NULL value. Without proper handling, this can lead to duplicate, outdated, or inconsistent records. Below, we explore how to handle these scenarios and implement a framework to maintain a clean and accurate Party Address Domain.

Understanding the Problem

In the Party Address Domain, common challenges include:

1. New Address Record: A source system sends a new Address ID for a party, replacing an old address while keeping the old record in the MDM system.
2. Address Removal: A source system sends a NULL for the Address ID, indicating the address is no longer valid.
3. Historical Tracking: Retaining old addresses for compliance or reporting purposes while identifying which address is the current one.

Framework for Address Management in MDM

1. Define Business Rules

• Active Address Flag (IsActive): Only one address per address type (e.g., Mailing, Billing) can be active for a party at any time.
• Retain Historical Records: Keep old addresses but mark them as inactive when a new address is received.
• Handle NULL Address IDs Gracefully: Treat NULL values as an indicator to inactivate existing active addresses for the given party and address type.
• Audit Trail: Track changes to address records, including timestamps and sources of updates.

2. Metadata Schema

Enhance the Party Address table with the following attributes:

3. Handling Scenarios

Scenario 1: New Address Record

When a new address is received for a party:

1. Insert the new address into the MDM system with IsActive = 1.
2. Mark the previous active address for the same PartyID and AddressType as IsActive = 0.
3. Update the EffectiveDate and UpdatedBy fields.

SQL Example:

-- Mark previous active addresses as inactive
UPDATE PartyAddress
SET IsActive = 0, 
    EffectiveDate = GETDATE(), 
    UpdatedBy = 'ETL_Process'
WHERE PartyID = @PartyID
  AND AddressType = @AddressType
  AND IsActive = 1;

-- Insert new address record
INSERT INTO PartyAddress (PartyID, AddressID, AddressType, AddressDetails, IsActive, EffectiveDate, UpdatedBy)
VALUES (@PartyID, @NewAddressID, @AddressType, @AddressDetails, 1, GETDATE(), 'ETL_Process');        

Scenario 2: Address ID is NULL in Incremental Updates

When a source sends NULL for the Address ID, indicating the party no longer has a valid address for that type:

1. Identify the active address for the PartyID and AddressType.
2. Mark the existing address as inactive (IsActive = 0).
3. Optionally, insert a placeholder record indicating "No Address."

SQL Example:

-- Mark active address as inactive when NULL is received
UPDATE PartyAddress
SET IsActive = 0,
    EffectiveDate = GETDATE(),
    UpdatedBy = 'ETL_Process'
WHERE PartyID = @PartyID
  AND AddressType = @AddressType
  AND IsActive = 1;

-- Optional: Insert a placeholder record for "No Address"
INSERT INTO PartyAddress (PartyID, AddressID, AddressType, AddressDetails, IsActive, EffectiveDate, UpdatedBy)
VALUES (@PartyID, NULL, @AddressType, 'No Address Available', 0, GETDATE(), 'ETL_Process');
        

4. Automating the Process

Stored Procedure: Managing Active/Inactive Flags

Create a stored procedure to handle new, updated, and NULL Address IDs:

CREATE PROCEDURE ManagePartyAddress
    @PartyID INT,
    @AddressID INT = NULL,
    @AddressType VARCHAR(50),
    @AddressDetails VARCHAR(255)
AS
BEGIN
    -- Deactivate existing active records
    UPDATE PartyAddress
    SET IsActive = 0,
        EffectiveDate = GETDATE(),
        UpdatedBy = 'ETL_Process'
    WHERE PartyID = @PartyID
      AND AddressType = @AddressType
      AND IsActive = 1;

    -- Handle new address
    IF @AddressID IS NOT NULL
    BEGIN
        INSERT INTO PartyAddress (PartyID, AddressID, AddressType, AddressDetails, IsActive, EffectiveDate, UpdatedBy)
        VALUES (@PartyID, @AddressID, @AddressType, @AddressDetails, 1, GETDATE(), 'ETL_Process');
    END
    ELSE
    BEGIN
        -- Optional: Add placeholder for NULL AddressID
        INSERT INTO PartyAddress (PartyID, AddressID, AddressType, AddressDetails, IsActive, EffectiveDate, UpdatedBy)
        VALUES (@PartyID, NULL, @AddressType, 'No Address Available', 0, GETDATE(), 'ETL_Process');
    END
END;
        

5. Audit and Monitoring

• Audit Trail: Track changes to address records using EffectiveDate and UpdatedBy.
• Validation Reports: Generate daily reports to verify that:No party has more than one active address for the same AddressType.All inactive records are properly flagged.

Validation Query:

-- Identify duplicate active addresses
SELECT PartyID, AddressType, COUNT(*)
FROM PartyAddress
WHERE IsActive = 1
GROUP BY PartyID, AddressType
HAVING COUNT(*) > 1;        

6. Example Input and Output

Benefits of the Framework

1. Data Integrity: Ensures a single active record for each address type per party.
2. Historical Accuracy: Retains old records for compliance and reporting.
3. Scalability: Easily handles bulk updates and deletions.
4. Traceability: Maintains audit trails for all changes.

By implementing this framework, the Party Address Domain in your MDM will remain consistent, reliable, and aligned with business rules.