upsert.table_list

by

Here’s your KnowHow page for the upsert.table_list controller—a great example of a reusable shortcode that leverages the metadata engine elegantly.

Summary

The [upsert].[table_list] procedure registers or updates a metadata record in the log.table_list using a structured payload. It acts as a shortcode controller that builds the required JSON and delegates the actual upsert logic to [cdm].[patch_upsert].

Its purpose is to log metadata tables—including DataMart views and core domain entities—into a standardised registry that supports lineage, discovery, and table classification in the Common Data Model (CDM).

🧭 Key Steps

  1. Set Context & Defaults
    • Automatically fills in author, source, and object description if not supplied.
    • Constructs a consistent payload structure using object_class and object_code.
  2. Derive Metadata Components
    • Splits object_code (e.g. project_codes) into:
      • entityName = project
      • domainSuffix = codes
    • Sets fixed valuesize = 32 (used by templates).
  3. Generate Payload
    • Uses a Common Table Expression (CTE) to shape a JSON object inline.
    • Flattens values into a payload ready for submission to [cdm].[patch_upsert].
  4. Invoke Patch Upsert
    • Calls [cdm].[patch_upsert] with the target table set as log.table_list and the constructed payload.
  5. Error Handling
    • Captures and raises contextual errors, including procedure and table name.

🧪 Example Usage

EXEC [upsert].[table_list]
    @objectCode  = 'employee_values',
    @objectClass = 'cdm',
    @debug       = 1;

📦 Parameters

ParameterDescription
@objectCodeName of the object/table (e.g. project_codes)
@objectClassSchema or grouping of the object (e.g. cdm, dim, stage)
@objectDescOptional description; defaults to [schema].[object]
@tableTypeTemplate category such as meta, dim, core, or value
@authoredbyOptional override for who created the record
@sourceidOptional override for source lineage tracking
@create_dateOptional creation date; defaults to system time
@debugSet to 1 to print payload and procedure name

🧩 Payload Example

Here’s what the payload might look like internally for project_codes:

{
  "object_seq": {
    "object_class": "cdm",
    "object_code": "project_codes"
  },
  "object_class": "cdm",
  "object_code": "project_codes",
  "object_name": "[cdm].[project_codes]",
  "object_metadata": {
    "tableType": "meta",
    "entityName": "project",
    "domainSuffix": "codes",
    "valuesize": "32"
  }
}

✅ Best Practice

  • Use this controller consistently when registering new DataMart tables or views.
  • Extend object_metadata if new classification fields are needed in future (e.g. sensitivity, data owner).
  • Combine with cdm.create_table to create + register a new table in one step.

Leave a Comment