upsert.entity

by

Summary

The [upsert].[entity] procedure registers or updates an entity object in the Common Data Model. It constructs a standardised payload using object_code and object_class, along with derived metadata like domain, entity label, and schema. This payload is then passed to [cdm].[patch_upsert].

It is used for adding or updating entries in core tables (e.g. project_core, employee_core) and acts as the authoritative way to track objects within your CDM.

🧭 Key Steps

  1. Initialise Parameters & Defaults
    • Ensures authoredby, sourceid, objectDesc, and create_date are set.
    • Defaults tableType to core, making it ideal for primary object records.
  2. Parse Table Name
    • Splits @tableName into:
      • @SchemaName = domain suffix (e.g. cdm)
      • @ObjectName = full table name (e.g. project_core)
      • @EntityName = prefix (e.g. project)
  3. Construct Payload
    • Builds three JSON fragments:
      • object_seq (logical identifier)
      • object_metadata (additional descriptors like label, domain, entity)
      • Full payload for patching
  4. Delegate to [cdm].[patch_upsert]
    • Payload is passed along with table name and metadata context.
  5. Error Handling & Debug Logging
    • Prints payload if @debug = 1 and includes robust error context if something goes wrong.

🧪 Example Usage

EXEC [upsert].[entity]
    @tableName   = 'cdm.project_core',
    @objectCode  = 'project_123-456',
    @objectClass = 'cdm',
    @objectDesc  = 'Flagship Project: Renewable Energy',
    @debug       = 1;

📦 Parameters

ParameterDescription
@tableNameFull or short name of the target table (e.g. cdm.project_core)
@objectCodeUnique code identifying the object (e.g. project_123-456)
@objectClassSchema or classification for the object (e.g. cdm, dim)
@objectDescOptional description of the object (defaults to `class
@tableTypeTemplate category (defaults to core)
@authoredbyName of the update author
@sourceidOptional lineage/source tag
@create_dateOptional override for creation timestamp
@debugEnables logging and payload printing

🧩 JSON Payload Example

{
  "object_seq": {
    "object_class": "cdm",
    "object_code": "project_123-456"
  },
  "object_class": "cdm",
  "object_code": "project_123-456",
  "object_name": "cdm|project_123-456",
  "object_metadata": {
    "tableType": "core",
    "entityName": "project",
    "domainSuffix": "cdm",
    "label": "cdm|project_123-456"
  }
}

✅ Use Cases

  • Creating new entries in *_core tables (e.g. employee_core, project_core)
  • Registering new identifiers and associated metadata
  • Integrating into onboarding or object-creation pipelines

Leave a Comment