CDM Value Presence Reconciliation Framework

by

🎯 Objective

To proactively expire or flag metadata rows that are no longer present in the source data even if their values haven’t changed, avoiding persistence of stale or misleading data.

1. 🏗 Recommended Table Extension

Add these optional columns to any table using patch_upsert or put_update:

ALTER TABLE [cdm].[project_values]
ADD [EXPIRY_REASON] VARCHAR(64) NULL,
    [AUDIT_FLAG] BIT DEFAULT 0,
    [EXPECTED_THIS_PERIOD] BIT NULL; -- used only for staging/reconciliation

2. 📥 Staging Input Table – stage.project_values_current

Your ingestion pipeline should load the latest snapshot from source systems into a staging view/table that reflects only current values:

CREATE OR ALTER VIEW [stage].[project_values_current] AS
SELECT
    OBJECT_SEQ,
    META_TYPE,
    ATTRIBUTE,
    VALUE,
    METADATA,
    [HASHED_KEY] = CONVERT(VARCHAR(32), HASHBYTES('MD5', ...), 2) -- optional
FROM source...
WHERE snapshot_date = @latest

3. 🔁 Reconciliation Procedure

cdm.reconcile_presence

CREATE OR ALTER PROCEDURE [cdm].[reconcile_presence]
    @targetTable SYSNAME,        -- e.g. 'cdm.project_values'
    @stageTable SYSNAME,         -- e.g. 'stage.project_values_current'
    @debug BIT = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    -- Step 1: Expire missing rows
    SET @sql = '
        UPDATE tgt
        SET 
            ACTIVE_FLAG = 0,
            EXPIRY_DATE = GETDATE(),
            EXPIRY_REASON = ''Missing from source'',
            AUDIT_FLAG = 1
        FROM ' + @targetTable + ' tgt
        LEFT JOIN ' + @stageTable + ' stg
          ON tgt.OBJECT_SEQ = stg.OBJECT_SEQ
         AND tgt.META_TYPE = stg.META_TYPE
         AND tgt.ATTRIBUTE = stg.ATTRIBUTE
        WHERE stg.OBJECT_SEQ IS NULL
          AND tgt.ACTIVE_FLAG = 1
    ';

    IF @debug = 1 PRINT @sql;
    EXEC sp_executesql @sql;
END

✅ You can parameterise joins or keys further using cont.table_templates if desired.

4. 📊 Optional: Reconciliation Dashboard View

CREATE OR ALTER VIEW [audit].[value_presence_anomalies] AS
SELECT *
FROM cdm.project_values
WHERE AUDIT_FLAG = 1
  AND EXPIRY_REASON = 'Missing from source'
  AND EXPIRY_DATE >= DATEADD(DAY, -7, GETDATE());

5. 📆 Suggested Use in Pipeline

  • Load to [stage].[project_values_current]
  • Perform patch_upsert of all current values
  • Run [cdm].[reconcile_presence] to catch missing values
  • Optionally: run [audit].[value_presence_anomalies] to review

🧠 Enhancements You Can Layer Later

  • Expected key lists stored in cdm.object_expectations
  • Threshold days before a value is auto-expired
  • Flag-only mode (no auto-expiry, just audit)

Leave a Comment