Customizing Tables Using Extension Objects

Some Nitro Connectors use centralized metadata to keep each connector up-to-date with the latest content. This table metadata is not directly modifiable and can only be updated by Veeva with new product updates. The table definitions are static; if their YML definitions are updated and reloaded, the changes are rejected.

For example, the Symphony Claims connector uses this method to protect its product metadata. The following sample displays one of the protected table objects (symphony_claims_patient_ods__v). This object has a “version: v202_3_200” field which is how a specific version is stamped in the metadata, thus protecting it from being updated.

Copy 
---
name: symphony_claims_patient_ods__v
version: v202_3_200
primaryKey: patient_id__v
distStyle: key
distKey: patient_id__v
tableName: symphony_claims_patient_ods__v
enabled: true
filter: ""
csvDelimiter: ','
maxErrorCount: 1000
skipHeader: true
textQualifier: '"'
loadPattern: pt_ftp_ods_upsertload__v
syncProfiles: []
columns:
- name: patient_id__v
  type: numeric(22)
  compression: ZSTD
  columnBehavior: PRESENT
  colOrder: 100
  notNull: true
....

Changing the object, for example, changing the type field in the column from “numeric(22)” to “numeric(25),” and reloading the newly updated package results in the following warning:

Copy 
Warnings:
Changes detected in uploaded TABLE symphony_claims_patient_ods__v.  Changes ignored, central TABLE content unchanged.

This indicates that a change was detected in the protected object but is ignored. If the package is downloaded again, the original protected values display.

Extension Objects to Change Protected Objects

Extension Objects can be used to make changes to protected objects, allowing metadata developers to identify changes that need to be modeled, in addition to the base object distributed by Veeva.

Extension objects are YAML files, with the same name as the object intended to be modified, but with an “_ext” suffix. In the previous “symphony_claims_patient_ods__v” example, the extension object “symphony_claims_patient_ods__v_ext” is defined as an additional YAML file, next to the original:

  • symphony_claims_patient_ods__v.yml
  • symphony_claims_patient_ods__v_ext.yml

The extended version of the object contains only the changes to made in addition to the existing definition.

Copy 
name: symphony_claims_patient_ods__v_ext
loadPattern: pt_ftp_ods_upsertload__v
columns:
- name: patient_id__v
  label : Patient ID 
- name: patient_zip3__v
  columnBehavior:  NOT_PRESENT
- name: new_column__v
  type: varchar(100)          # new column not found in original table

In the previous extension example, there are four changes:

  • Table level loadPattern property is updated so that the table uses the ODS load technique Upsert Load instead of GenLoad.
  • Column level attribute “label” for the existing column patient_id__v is updated to provide a user- friendly name when the object is viewed from the Nitro Admin Console.
  • Existing column patient_zip3__v is indicated as Not Present so it will not be created in the ODS data model .
  • “new_column__v”is added to the base table and is treated as a new column definition with the corresponding data types assigned.

The resulting composite table definition is as follows:

Copy 

---
name: symphony_claims_patient_ods__v
primaryKey: patient_id__v
distStyle: key
distKey: patient_id__v
tableName: symphony_claims_patient_ods__v
enabled: true
filter: ""
csvDelimiter: ','
maxErrorCount: 1000
skipHeader: true
textQualifier: '"'
loadPattern: pt_ftp_ods_upsertload__v
syncProfiles: []
columns:
- name: patient_id__v
  label : Patient ID 
  type: numeric(25)  
  compression: ZSTD
  columnBehavior: PRESENT
  colOrder: 100
  notNull: true
…
- name: patient_zip3__v
  columnBehavior: NOT_PRESENT
...
- name: new_column__v
  type: varchar(255)
  columnBehavior: PRESENT
…

The Nitro metadata engine loads metadata for the patient data object by loading the base protected metadata then making changes to it from the extension object. This means that the ultimate object that is displayed is a mixture of both sets of attributes.

Valid Changes via Extension Objects

The following table attributes can be changed using extension objects:

Scope

Attribute

Notes

Table

name

Cannot be changed - Used to identify an object and align it to corresponding extension objects.

Table

enabled

n/a

Table

loadPattern

Load technique to be used for object.
Table csvDelimiter Attribute used to separate columns in source data

Table

textQualifier

Attribute used to identify text strings in data

Column

columnBehavior

PRESENT, NOT_PRESENT, PRESENT_NOT_POPULATED

Column

colOrder

Controls relative order of columns for physical load files (see Column Ordering section)

Column

label

Descriptive label for a loaded column

Column Ordering

In relational databases, column ordering is typically not important. Columns can be extracted in any order required regardless of physical table layout. However, column order inside CSV text files used during the load process is very important since columns in a text file can only be accessed as they are physically listed, read left to right. Those columns must be ordered exactly as imported into Redshift staging tables.

To control physical ordering of columns within a table, Nitro uses the colOrder attribute assigned at the column level. When data is loaded via SFTP as a text file, two table definitions (FTP and STG tables) control how data is parsed and loaded into Redshift.

For example, in the symphony_claims_patient_ods__v table definition distributed with Nitro, the column ordering values are intended to be relative to each other. The first column (patient_id__v) is assigned colOrder 100 to indicate it physically comes before other columns that have higher colOrder assignments. colOrder 100 comes before colOrder 200, comes before colOrder 300, and so on.

Copy 
---
name: symphony_claims_patient_stg__v
version: v202_3_200
primaryKey: patient_id__v
distStyle: key
distKey: patient_id__v
tableName: symphony_claims_patient_stg__v
loadPattern: pt_ftp_stg_standardload__v
columns:
- name: patient_id__v
  type: numeric(22)
  colOrder: 100
  notNull: true
- name: patient_birth_year__v
  type: numeric(4)
  colOrder: 200
- name: patient_gender__v
  type: varchar(1)
  colOrder: 300
- name: patient_zip3__v
 type: varchar(3)
  colOrder: 400
- name: patient_state__v
  type: varchar(2)
  colOrder: 500

For example:

  • To add a column between the 3rd and 4th column, add that column definition to the end of the YAML file and assign a colOrder of 350.
  • To reorder the data so that the 2nd column is last, reassign its colOrder value from 200 to 550 so it is processed after the column ordered as 500.