A Tx Template contains a repeatable, reusable boilerplate code and properties used to create the Tx object while abstracting its business logic. The template controls the create and run scripts and visual properties for Tx Objects that use it.

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.

For a complete list and detailed description of all properties and UI items, please see the related article, Tx Template Properties, at the bottom of this page.

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)
Dimension (Type 2 or mixed Type 1 and 2)

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:
1. Add or remove properties
2. Modify the property values
3. Leave comments (using the hash "#" sign)

The Errors & Warnings 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 object or source
${parentLogicalName} - The logical name of the parent object or source
${parentRef} - The physical name of the parent object or source in ref format

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"
1. 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 Source or another Tx Object.

Tx Objects are linked with the Tx Template used to create them, and when executing Create and Run commands, they use the templates directly from the Tx Template.

Changing the Create and Run scripts for a given Tx Template will affect subsequent executions of the Tx Objects that use it.

Changing the YAML properties for a given Tx Template will affect related Tx Objects:

By determining which UI elements appear on the right-hand properties panel of the Tx Objects that use it.

And will not affect:

Tx Object columns - columns will not be changed, removed, or added.
Tx Object names - existing object names will not be updated.
Tx Object property settings:
- Added - new properties added to the template will be visible on related Tx Objects but any default values will not automatically be assigned. Property values set through the UI will be saved.
- Removed - assigned property values set through the properties panel will remain in the Tx Object even if the property is removed from the UI.

Working with default Tx Templates

All Tx projects come with prebuilt templates that can be used to set up a transformational pipeline from scratch in a few simple steps. This section covers the existing templates and their intended usage.

Besides this documentation, Tx Templates also contain comments and instructions in their YAML properties and source code so please be sure to review them where applicable for more in-depth and technical details.

For reference, the source code (YAML and Jinja) for default templates, as well as non-default templates and other Tx assets like macros, can be found in the "samples" folder of this GitHub repository.

Although default templates can be used as-is, they can also be copied and modified to fine-tune their behavior to desired team standards and requirements.

Stage

The stage template is intended for use with intermediary objects to clean up data before a load or hold it in temporary storage. The Run template of a Stage template uses insert logic and does not update or remove existing records. (To remove records from previous loads, set this object's "Truncate before" property.)

Required settings: none

Persistent Stage

The Persistent Stage template builds upon the functionality of the standard Stage by adding merge logic to the Run script. The merge is performed based on the PK set on the given Tx Object.

Required settings:
- Primary key: must be set, true to the minimum granularity of the given Tx Object. Remember to include any relevant system columns in addition to the business if required when declaring the PK.

Dimension

The Dimension template creates Type 2 dimension tables (by default) and requires minimal configuration to operate. Optionally, columns can be declared as Type 1, creating a mixed Type 1 and 2 dimension.

Out of the box, this dimension includes all required system columns (system columns begin with a double-underscore) like from/to date and optional columns like create and update date.

This dimension uses timestamp granularity for all system columns (not date) to allow multiple loads on a given day.

Required settings:
- Business key: the column(s) uniquely identifying a unique record of this dimension in the source system. (Do not include system columns such as __FROM_DTS).
- System timestamp column: the loaded-at column for the incoming source records (e.g., load_date, etl_date_timestamp, dw_insert_date).
Optional settings
- 'All columns are Type 2' flag: causes all attributes (i.e., non-system and non-identifying columns) to be considered as delta tracking for Type 2 changes. Default: true.
- or
- 'Type 2 change tracking columns': Manually declare Type 2-relevant columns and treat the rest as Type 1. To avoid confusion, remember to uncheck the 'All columns are Type 2'.
  - These two properties are mutually exclusive. If 'All columns are Type 2' flag is set to true, it will take precedence over 'Type 2 change tracking columns' and the latter will be ignored.

If declaring Type 1 columns, bear in mind that for performance reasons, historic records will not be updated by default (i.e., only the latest version of each record will update). If you wish to also update historic records, create a copy of this template and comment out the line which says {# remove this to update historic records too. Performance will suffer #}