cdm.post_insert

by

Summary

The [cdm].[post_insert] stored procedure is responsible for inserting a new metadata row into a specified table using a structured JSON payload. It dynamically builds the insert logic using the column metadata from cont.table_templates, including default values and computed checksum logic.

This procedure is a core component of the CDM metadata engine, providing consistency, reusability, and integrity for metadata ingestion.

🧭 Key Steps

  1. Initialise Context & Defaults
    • Sets default values for @authoredby, @sourceid, and @create_date.
    • Records the name of the procedure being executed and the current timestamp.
  2. Parse Target Table
    • Splits @tableName into schema and object name.
    • Constructs a fully qualified name in [schema].[table] format.
  3. Generate CHECKSUM
    • Dynamically builds a hash expression using the fields marked with is_checksum = 'YES' in the template.
    • Computes an MD5 checksum to support later change detection.
  4. Prepare Columns and Values
    • Constructs column and value lists by:
      • Pulling template metadata
      • Resolving values from the JSON payload
      • Applying default values for fields like EXPIRY_DATE, ACTIVE_FLAG, and SOURCE_SYSTEM
  5. Execute INSERT
    • Builds and runs a dynamic SQL statement to insert the new row.

🧪 Example Usage

This procedure is called internally by:

  • [cdm].[patch_upsert]
  • [cdm].[upsert]

Direct use is rare, but it can be manually tested:

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

📦 Parameters

ParameterDescription
@tableNameFully qualified or short name of the target table
@payloadJSON object containing metadata to insert
@tableTypeTemplate category used to build column/value list (e.g. meta, item)
@debugIf 1, prints debug messages to the console
@authoredbyName of the author stored in the row metadata
@sourceidExternal system source ID for lineage tracking
@create_dateOptional creation timestamp, default is system datetime

🧩 Template-Driven Logic

This procedure relies on the cont.table_templates structure, using it to:

  • Match column names
  • Extract corresponding JSON parameter mappings
  • Determine field defaults and checksum contribution

✅ Features

  • 🔐 Ensures metadata integrity with MD5-based CHECKSUM generation
  • 📦 Designed for JSON payloads — schema is dynamically inferred
  • 🔄 Supports flexible domain variations through @tableType
  • 🧾 Automatically assigns:
    • EXPIRY_DATE = '2099-12-31'
    • ACTIVE_FLAG = 1
    • SOURCE_SYSTEM, CREATE_DATE, CHECKSUM values

Leave a Comment