A Tx template contains a repeatable, reusable base used to create the Tx object, where a specific transformation based on a business requirement can be written and applied.
Components of a Tx template
A Tx template is composed of three components:
Properties
Create Statement
Run Statement
Properties
The template properties are a YAML definition that defines how the new node based on it will be created. It can contain:
Column Definitions: Specifies the columns that will be included in the Tx object.
Inheritance Settings: Determines how properties are inherited from parent templates.
System UI Items: Includes system-defined user interface elements.
User UI Items: Includes user-defined interface elements. These may be referenced in the Create and Run templates.
Complete set of properties available
Complete set of properties available
name: All Props Template
physicalName: 'tmp_${parentPhysicalName}'
logicalName: '${parentLogicalName} dimension'
color: 'red' # Color of object on diagram (string, green | blue | red | SqlDBM colors, optional)
inheritIdentifiersAs: pk # Inherit primary key columns from parent table(s) as PK or non-PK attributes (string, pk | non-pk | none, default non-pk)
inheritAttributes: true # Inherit non-pk columns from parent table(s) (boolean, default true)
primaryColumns:
- name: 'pk_load_date' # Column name (string, required)
logicalName: 'load date' # Logical name of the column (string, optional)
dataType: date # Column data type (string, required)
comment: 'load date' # Comment or description (string, optional)
nullable: false # Set not null constraint (boolean, default false)
unique: false
default: 'current_date()'
position: first
logic: 'current_date()'
defaultColumns:
- name: 'load_date'
logicalName: 'load date'
dataType: date
comment: 'load date'
nullable: false
unique: false
default: 'current_date()'
position: last
logic: 'current_date()'
nodeSettings:
- groupName: 'System UI Items'
groupDesc: 'System UI Items'
isEnabled: true
items:
- itemType: materialization
default: 'table'
options:
- 'table'
- 'view'
isRequired: true
isEnabled: true
- itemType: prePostSql
itemDesc: Pre Create SQL
itemName: preCreate
default: '' # Some default SQL text (string, optional)
isRequired: false
isEnabled: true
- itemType: prePostSql
itemDesc: Post Create SQL
itemName: postCreate
default: '' # Some default SQL text (string, optional)
isRequired: false
isEnabled: true
- itemType: prePostSql
itemDesc: Pre Run SQL
itemName: preRun
default: '' # Some default SQL text (string, optional)
isRequired: false
isEnabled: true
- itemType: prePostSql
itemDesc: Post Run SQL
itemName: postRun
default: '' # Some default SQL text (string, optional)
isRequired: false
isEnabled: true
- groupName: 'Column settings'
groupDesc: 'Column settings'
isEnabled: true
items:
- itemType: columnSet
itemName: PK # PK | AK | BK | DELTA | FK | custom string
itemDesc: 'Primary key'
isRequired: false
isEnabled: true
- itemType: columnSet
itemName: AK # PK | AK | BK | DELTA | FK | custom string
itemDesc: 'Alternate key'
isRequired: false
isEnabled: true
- itemType: columnSet
itemName: BK # PK | AK | BK | DELTA | FK | custom string
itemDesc: 'Business key'
isRequired: false
isEnabled: true
- itemType: columnSet
itemName: DELTA
itemDesc: 'Change tracking for type.2 SCD'
isRequired: false
isEnabled: true
- groupName: 'User UI Items'
groupDesc: 'Optional user configured items'
isEnabled: true
items:
- itemType: toggleButton
itemDesc: 'My Button'
itemName: my_button
default: true
isEnabled: true
- itemType: dropdownSelector
itemDesc: 'My dropdown selector'
itemName: my_dropdown
default: 'option 1'
options:
- 'option 1'
- 'option 2'
isRequired: false
isEnabled: true
- itemType: columnSet
itemDesc: 'My column selector'
itemName: my_columnSelector
isRequired: false
isEnabled: true
- itemType: multiSelector
itemDesc: 'My multi selector'
itemName: my_multi_selector
options:
- 'option 1'
- 'option 2'
isRequired: false
isEnabled: true
- itemType: textArea
itemDesc: 'My Text Area'
itemName: my_textArea
isRequired: false
isEnabled: true
- itemType: textLine
itemDesc: 'My text line'
itemName: my_textline
isRequired: false
isEnabled: true
- itemType: toggleButton
itemDesc: 'Rely on constraints'
itemName: rely_button
default: true
isEnabled: true
Create Template
The create template is a Jinja template that dynamically references elements defined in the Template YAML and in the object to compile into a create statement of the object when executed against the database. This is executed at deploy time, setting up the data structure without loading data.
A sample create template is provided below for reference. This template allows for the creation of a table or view object and references the node file structure through dynamic Jinja elements to form the final DDL, which is compiled at runtime.
{%- if materialization == 'table' %}
CREATE OR REPLACE TABLE {{ location.databaseName }}.{{ location.schemaName }}.{{name}}
(
{% for col in columns -%}
{{ col.name }} {{ col.dataType }}
{%- if not col.allowNulls %} NOT NULL
{%- if col.default | length > 0 %} DEFAULT {{ col.default }}{% endif %}
{%- endif %}
{%- if col.description | length > 0 %} COMMENT '{{ col.description }}'{% endif %}
{%- if not loop.last %}, {% endif %}
{% endfor -%}
{% for colSet in columnSets %}
{%- if colSet.itemName == 'PK' -%}
, CONSTRAINT {{ colSet.setName }} PRIMARY KEY (
{%- for pkCol in colSet.columns -%}
{{pkCol.name}}
{%- if not loop.last %}, {% endif -%}
{%- endfor -%}
)
{%- if rely_button %} RELY {% endif -%}
{%- endif -%}
{%- endfor %}
{% for colSet in columnSets %}
{%- if colSet.itemName == 'AK' -%}
, CONSTRAINT {{ colSet.setName }} UNIQUE (
{%- for akCol in colSet.columns -%}
{{akCol.name}}
{%- if not loop.last %}, {% endif -%}
{%- endfor -%}
)
{%- if rely_button %} RELY {% endif -%}
{%- endif -%}
{%- endfor %}
)
{%- if description | length > 0 %} COMMENT = '{{ description }}'{% endif %}
;
{%- elif materialization == 'view' %}
CREATE OR REPLACE VIEW {{ location.mapping }}.{{name}}
(
{% for col in columns -%}
{{ col.name }}
{%- if col.description | length > 0 %} COMMENT '{{ col.description }}'{% endif %}
{%- if not loop.last %}, {% endif %}
{% endfor %}
{%- if description | length > 0 %} COMMENT = '{{ description }}'{% endif %}
)
AS
{{logic}}
;
{%- endif %}
Run Template
The run template is a Jinja template that defines how data is loaded into the object. The compiled run template results in the execution of SQL statements, such as INSERT INTO, MERGE INTO, or other SQL commands to perform the necessary data operations. This is called on demand through a scheduler, allowing for flexible data loading based on defined schedules.
A sample run template is provided below for reference. This template inserts records into a table and references the node file structure through dynamic Jinja elements to form the final DDL, which is compiled at runtime. Note that the SELECT logic is referenced from the node itself in the {{ logic }} parameter.
{% if materialization == 'table' %}
INSERT INTO {{ location.mapping }}.{{name}}
(
{%- for col in columns %}
{{ col.name }}
{%- if not loop.last %}, {% endif %}
{%- endfor %}
)
{{ logic }}
;
{%- endif %}
Difference between Create and Run templates:
The main goal of having two separate templates per template is to use the Create template without loading data, just deploying the data structure.
Working with Tx templates
There are some out-of-the-box Tx templates available for all projects by default:
Stage template (insert)
Persistent stage (merge)
(more templates coming soon [Type 2 Dim, DV, etc.])
The default templates are for reference and cannot be removed by users. However, they can be cloned and adjusted to your exact specifications.
Creating a Tx template
Tx templates can be created from scratch or by duplicating existing ones.
Duplicating a Tx template
To duplicate a Tx template, do the following:
Right-click or select the "three-dot" menu on the template you wish to duplicate
From the options provided, select "Duplicate"
The duplicated template will be named
<Table Template Name>_n, ensuring a unique template name.
Creating a new Tx template
Right-click on the "Tx templates" header or any existing Tx template, or click on their respective "three-dot" menus.
Select "Create new".
A new Tx template will be created and added to the bottom of the Tx templates list.
A default YAML showing all template properties is included when creating a new Tx template. Adjust this default by removing any unnecessary properties and setting the values for the remaining ones.
Editing a Tx template Properties
Templates can be modified using the YAML editor, the create template, and the run template:
Right-click or select the "three-dot" menu on the Tx template to modify
Select "Open"
Use the YAML template editor to modify the contents to:
Add or remove properties
Modify the property values
Leave comments (using the hash "#" sign)
Warnings & Errors tab is where you'll be notified of any warnings and errors in the Yaml structure.
Remember that YAML rules require all properties to have a value. Properties without a value (e.g., "myProperty: ") will result in an error and invalidate the template.
Inconsistencies like unrecognized property names or values will result in a warning and will be skipped during object creation. They will not invalidate the template.
The following variables can be used as part of property values
${parentPhysicalName}- The physical name of the parent if creating the template from an existing object. Otherwise, blank${parentLogicalName}- The logical name of the parent if creating the template from an existing object. Otherwise, blank.
Inheriting parent source columns
Tx templates can generate objects from scratch or using a parent source. When creating from a parent source, the following properties can determine which columns will be inherited and their order:
inheritIdentifiersAs: (string, pk | non-pk | none , default: non-pk) - determines whether or not identifiers (PKs) are inherited by the resulting table. A value of "pk" means that they will also be primary in the resulting table, while "non-pk" will add them as only attributes; "none" will exclude them entirely.inheritAttributes: (boolean, default: true ) - determines whether or attributes (non-PK columns) are inherited by the resulting table.position: (string, first | last) - inherited columns (PKs and attributes) are position-neutral and will be inherited in the order that they appear in the parent table. However, template columns (defined in the PK and attribute section of the template) can be assigned a position value of "first" or "last" to indicate the order in which they appear in their respective section. Refer to the "Dimension (default)" template to see an example of how this property is used.
Renaming a Tx template
To rename an existing Tx template:
Right-click or select the "three-dot" menu on the template you would like to rename
Select "Open"
The YAML script editor will open
Update the value in the "Name" field
example: enter "I have a new name"
will result in the template being shown in the database explorer as:
Previewing a Tx Template
To preview how the Yaml properties included will show in a created Tx object
Go to the Preview section in the Tx template properties
Select a node to use in the preview.
The preview of the Tx object's Properties will be displayed below the preview section.
Deleting a Tx template
To delete an existing Tx template, follow the steps below. (Default templates can not be deleted).
Right-click or select the "three-dot" menu on the template you would like to delete
Select "Delete"
Editing a Tx template Create or Run Templates
All elements defined in the YAML properties can be used in the create and run Jinja templates. There is no validation at the template edition; validation is achieved when creating a Tx object with the template and using the Validate Create and Validate Run functionalities.
Both Create and Run templates are written in Jinja. Jinja version and documentation: https://jinja.palletsprojects.com/en/3.1.x/
Using Tx templates
Tx templates can be used to create a new Tx object starting from a previous Source of Tx object.
Tx objects are linked to the template they were created with, and when executing Create and Run commands, they use the templates directly from the Tx template. Changes to the Tx template will impact the Tx object when executing those commands. The YAML properties are used only upon creation of the object, and updating the object based on changes is a feature coming soon.
See also: