从事实表和维度表删除记录：同步源表删除逻辑的实现问题

Hey there, let's break this down—first, I can spot where your fact table sync went wrong, then we'll nail the dimension table delete sync properly.

What Happened With Your Fact Table #

It sounds like you had your delete logic reversed. When you tried MERGE or standalone DELETE, you ended up keeping only the record that was deleted from the source instead of removing it. That usually happens if:

• You used DELETE FROM target WHERE id IN (SELECT id FROM source) (which deletes all records that still exist in the source, leaving the ones that were removed)
• Or in a MERGE, you accidentally used logic that kept unmatched records instead of deleting them.

Correct Delete Sync for Dimension Tables #

Dimension tables have a couple of common patterns for handling deletes—hard deletion (physical removal) and soft deletion (marking records as inactive, which is more common for slowly changing dimensions). Let's cover both:

1. Hard Deletion (Physical Remove Matching Deleted Records)

Use a MERGE statement to delete records in the target dimension that no longer exist in the source. This is straightforward if you don't need to retain historical dimension data:

MERGE INTO target_dimension t
USING source_dimension s
ON t.dimension_key = s.dimension_key -- Match on your unique identifier (proxy or natural key)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

This tells the database: "For every record in the target dimension, if there's no matching record in the source, delete it." Exactly what you need—target keeps all records present in the source, removes the ones that were deleted from the source.

If you prefer a standalone DELETE instead of MERGE, use NOT EXISTS (safer than NOT IN to avoid NULL issues):

DELETE FROM target_dimension t
WHERE NOT EXISTS (
    SELECT 1 
    FROM source_dimension s
    WHERE s.dimension_key = t.dimension_key
);

2. Soft Deletion (Mark as Inactive, Recommended for Most Dimensions)

Most of the time, you don't want to physically delete dimension records—fact tables might still reference them for historical reporting. Instead, add an is_active flag (and maybe an inactivation_date) to your dimension table, then update those fields when the source record is deleted:

MERGE INTO target_dimension t
USING source_dimension s
ON t.dimension_key = s.dimension_key
WHEN MATCHED THEN 
    UPDATE SET 
        t.is_active = 1,
        t.last_updated = GETDATE(), -- Sync any other updated fields from source
        t.[other_dim_fields] = s.[other_dim_fields]
WHEN NOT MATCHED BY SOURCE AND t.is_active = 1 THEN -- Only update if it's still active
    UPDATE SET 
        t.is_active = 0,
        t.inactivation_date = GETDATE();

This way:

• Records still present in the source stay active and get updated with any source changes
• Records deleted from the source get marked as inactive (instead of being removed) so fact table joins still work for historical data.

Quick Fix for Your Original Fact Table Issue #

If you want to correct that fact table sync later, use the same MERGE logic as above (WHEN NOT MATCHED BY SOURCE THEN DELETE) or the NOT EXISTS delete statement. That'll ensure you only remove records from the fact table that no longer exist in the source.

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