使用Oracle SQL Merge语句修复ABC表的数据完整性问题

Let's tackle this data integrity issue where we've got duplicate LATEST_FL='Y' records for CUST_ID=6842—both share the same version, overlapping validities, and break slowly changing dimension (SCD Type 2) rules. Below is a step-by-step breakdown of the problem, the expected outcome, and the Oracle MERGE statement to resolve it.

Existing Table Structure & Erroneous Data #

First, here's the original table definition and the problematic test data:

create table abc ( 
    job_id integer ,
    etl_job_run_day date ,
    cust_sid integer ,
    cust_id number(38,0) ,
    cust_first_name varchar2(100) ,
    cust_last_name varchar2(100) ,
    effective_dt_from date ,
    effective_dt_to date ,
    version integer ,
    latest_fl varchar2(1) ,
    actual_cust_id integer 
); 

insert into abc values (123,date'2020-01-01',100,6842,'Tony','Greig',date'2020-01-10',to_date('2199-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss'),1,'Y',''); 
insert into abc values (123,date'2020-01-01',123,6842,'Tony','Greig',date'2020-01-10',to_date('2199-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss'),1,'Y',9011);

The Core Issue #

We have two conflicting records for CUST_ID=6842:

• Both are marked as the latest record (LATEST_FL='Y')
• Same version number (1)
• Overlapping effective date ranges
• Mismatched actual_cust_id values

Expected Corrected State #

After fixing, the data should follow proper SCD Type 2 standards with non-overlapping validities, correct versioning, and only one latest record:

Solution: Oracle MERGE Statement #

This MERGE statement handles three actions in one operation: updating the old non-latest record, deleting the invalid duplicate, and inserting the corrected latest record:

MERGE INTO abc target
USING (
    -- Define the updated state for the original non-actual record
    SELECT
        job_id,
        etl_job_run_day,
        cust_sid,
        cust_id,
        cust_first_name,
        cust_last_name,
        DATE'2020-01-01' AS effective_dt_from,
        DATE'2020-01-15' AS effective_dt_to,
        version,
        'N' AS latest_fl,
        actual_cust_id,
        'UPDATE' AS action
    FROM abc
    WHERE cust_id = 6842 AND actual_cust_id IS NULL
    
    UNION ALL
    
    -- Define the new latest record to insert
    SELECT
        123 AS job_id,
        DATE'2020-01-15' AS etl_job_run_day,
        148 AS cust_sid,
        6842 AS cust_id,
        'Tony' AS cust_first_name,
        'Greig' AS cust_last_name,
        DATE'2020-01-15' AS effective_dt_from,
        TO_DATE('2199-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss') AS effective_dt_to,
        2 AS version,
        'Y' AS latest_fl,
        9011 AS actual_cust_id,
        'INSERT' AS action
    FROM dual
    
    UNION ALL
    
    -- Mark the invalid duplicate record for deletion
    SELECT
        job_id,
        etl_job_run_day,
        cust_sid,
        cust_id,
        cust_first_name,
        cust_last_name,
        effective_dt_from,
        effective_dt_to,
        version,
        latest_fl,
        actual_cust_id,
        'DELETE' AS action
    FROM abc
    WHERE cust_id = 6842 AND actual_cust_id = 9011
) source
ON (
    (source.action = 'UPDATE' AND target.cust_id = source.cust_id AND target.actual_cust_id IS NULL)
    OR
    (source.action = 'DELETE' AND target.cust_id = source.cust_id AND target.actual_cust_id = 9011)
)
WHEN MATCHED THEN
    UPDATE SET
        target.effective_dt_from = source.effective_dt_from,
        target.effective_dt_to = source.effective_dt_to,
        target.latest_fl = source.latest_fl
    WHERE source.action = 'UPDATE'
    DELETE WHERE source.action = 'DELETE'
WHEN NOT MATCHED THEN
    INSERT (
        job_id, etl_job_run_day, cust_sid, cust_id,
        cust_first_name, cust_last_name, effective_dt_from,
        effective_dt_to, version, latest_fl, actual_cust_id
    )
    VALUES (
        source.job_id, source.etl_job_run_day, source.cust_sid, source.cust_id,
        source.cust_first_name, source.cust_last_name, source.effective_dt_from,
        source.effective_dt_to, source.version, source.latest_fl, source.actual_cust_id
    )
WHERE source.action = 'INSERT';

Breakdown of the MERGE Logic #

1. UPDATE: Adjusts the original record (with empty actual_cust_id) to have the correct start/end dates and marks it as non-latest (LATEST_FL='N').
2. DELETE: Removes the invalid duplicate record that had actual_cust_id=9011 since we're replacing it with a corrected version.
3. INSERT: Adds the new latest record with version 2, updated cust_sid=148, and the correct effective date range.

Verification Query #

After running the MERGE, confirm the corrected data with this query:

SELECT
    job_id,
    TO_CHAR(etl_job_run_day, 'yyyy-mm-dd hh24:mi:ss') AS etl_job_run_day,
    cust_sid,
    cust_id,
    cust_first_name,
    cust_last_name,
    TO_CHAR(effective_dt_from, 'yyyy-mm-dd hh24:mi:ss') AS effective_dt_from,
    TO_CHAR(effective_dt_to, 'yyyy-mm-dd hh24:mi:ss') AS effective_dt_to,
    version,
    latest_fl,
    actual_cust_id
FROM abc
WHERE cust_id = 6842
ORDER BY effective_dt_from;

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