cdm.put_update

by

Summary

The [cdm].[put_update] procedure enables safe, version-controlled updates to metadata tables by:

  • Checking for duplicate active entries (via CHECKSUM)
  • Expiring previous records with matching primary keys
  • Creating a new versioned record via [cdm].[post_insert]

This procedure ensures data immutability, supporting clean audit trails and history tracking.

🧭 Key Steps

  1. Initialise Defaults
    • Captures procedure metadata such as name, timestamp, author, and source ID.
  2. Resolve Table Name
    • Parses @tableName into [schema].[table] format for safe referencing.
  3. Generate CHECKSUM
    • Uses the metadata template to build a string of all fields flagged with is_checksum = 'YES'.
    • Computes an MD5 hash from the JSON payload to detect identical content.
  4. Check for Matching Active Row
    • Queries the table for a row with the same CHECKSUM and ACTIVE_FLAG = 1.
    • If one exists, the operation is aborted (no update needed).
  5. Locate & Expire Existing Version
    • Uses metadata-driven primary keys to identify an active row to expire.
    • Updates ACTIVE_FLAG to 0 and sets EXPIRY_DATE to current or supplied date.
  6. Insert New Version
    • Calls [cdm].[post_insert] to create a new version with updated values.
  7. Debug & Traceability
    • If @debug = 1, prints out critical steps and generated SQL commands for inspection.

🧪 Example Usage

EXEC [cdm].[put_update]
    @tableName = 'cdm.project_codes',
    @payload = N'{
        "OBJECT_SEQ": "project_123-456",
        "META_TYPE": "codes",
        "ATTRIBUTE": "category",
        "VALUE": "external"
    }',
    @authoredby = 'jkellett',
    @debug = 1;

📦 Parameters

ParameterDescription
@tableNameFully qualified or short name of the target table
@payloadJSON payload containing the metadata to update
@tableTypeTemplate type (e.g. meta, item, value)
@debugEnables debug output to assist in tracking logic and values
@authoredbyAuthor of the metadata update
@sourceidSource system or data lineage identifier
@create_dateOptional override for creation/expiry timestamp

🔍 Internal Logic Highlights

  • Immutability first: no destructive updates – previous entries are expired, not overwritten.
  • Checksum deduplication: saves storage and ensures only meaningful changes are versioned.
  • Expirable history: uses EXPIRY_DATE and ACTIVE_FLAG to track active vs. historical values.

✅ Good Practice

  • Always define your metadata in cont.table_templates to ensure full support.
  • Use [cdm].[patch_upsert] or [cdm].[upsert] as the public entry point — they call this procedure internally.
  • Set @debug = 1 during development to see the full decision path.

Leave a Comment