In Tx Templates, the properties section is used to define and customize the behavior and appearance of different elements within the resulting Tx Object. Below is a guide on existing YAML properties and their functions:
This guide organizes properties by order and theme, but they can be defined in any order as long as proper indentation is maintained.
All the properties mentioned here are included in the All Props Template , which is the default when creating a new Tx Template.
General Properties
The following properties are used to provide basic metadata such as template name, color, and naming patterns for resulting Tx Objects.
name: The name of the template itself (not the resulting object).
Example:
name: stage template
physicalName: Defines the physical name of the Tx Object, typically using a pattern that includes variables and a prefix or suffix.
Example:
physicalName: stg_${parentPhysicalName}
logicalName: Defines the logical name of the object.
Example:
logicalName: ${parentLogicalName} stage
color: Specifies the color of the object on the diagram. Acceptable values include predefined colors like green, blue, red, or other colors specific to SqlDBM.
Example:
color: orange
inheritIdentifiersAs: Determines how primary key columns are inherited from parent tables. Options include pk (i.e., identifying), non-pk (i.e., non-identifying), or none (often used in Data Vault modeling).
Example:
inheritIdentifiersAs: pk
inheritAttributes: Indicates whether non-primary key columns should be inherited from parent tables. A boolean value where true means attributes are inherited.
Example:
inheritAttributes: true
whereCondition: defines a WHERE clause that is inherited by the resulting Tx objects. If the template uses multiple sources, the WHERE clause is applied to each respective CTE. (Can be used in conjunction with the
${parentRef}variable [see the related "Tx Templates" article for more info])
Example configuration:
name: All Props Template
physicalName: 'tmp_${parentPhysicalName}'
logicalName: '${parentLogicalName} dimension'
color: 'red'
inheritIdentifiersAs: pk
inheritAttributes: true
whereCondition: |
TRUE
AND load_date = SELECT MAX(load_date) FROM ${parentRef}
Column Definitions
These properties allow users to create default or system columns in the resulting Tx Object as either PK or non-PK (i.e., attributes). These are defined within the primaryColumns or defaultColumns properties, respectively.
primaryColumns: Defines the primary columns for the template.
Example configuration
primaryColumns:
- name: 'load_date'
logicalName: 'load date'
dataType: date
comment: 'load date'
nullable: false
unique: false
default: 'current_date()'
position: last
logic: 'current_date()'
The position of the column is relative to the property in which it's declared (e.g., the last element of the primaryColumns will come before the first element of the defaultColumns).
The logic attribute is used to define how this column will be calculated. This will be used to construct the SELECT statement of the resulting Tx object. In the above example, the resulting column would generate the following SELECT:
SELECT
...
current_date() AS "load_date",
...
FROM ...
defaultColumns: Specifies default attribute (i.e., non-primary) columns for the Tx Object. This follows the same convention as primaryColumns
Object Settings
The following template properties are available, separated into system and user sections. The system properties support built-in, predefined Tx functionality, while user properties can be configured and set ad-hoc in Tx Objects and used in related jinja.
Sistem UI Items
The following system properties are available in this section of the template. Each is designated by its pre-defined item type.
Please note that indentation for nested items is required for YAML files.
groupName: Short name that will be visible in the property panel of the resulting Tx Object
groupDesc: An additional description for this section
isEnabled: toggles visibility of this element in related Tx Object properties
Example configuration:
nodeSettings:
- groupName: 'System UI Items'
groupDesc: 'System UI Items'
isEnabled: true
items:
- itemType: someItem
Materialization: This defines how the SQL should be materialized, either as a table or a view. It is a required element used in the Create and Run portion of the template.
Note that while any materialization type can be hand-configured, system default materialization options include:
table
view
(more object types coming soon)
Pre/Post SQL
The Pre/Post SQL item type in a template's YAML definition allows users to execute SQL scripts before or after the main Create or Run script in transformational modeling. This item comes in four identical flavors and can be used for preCreate, postCreate, preRun, and postRun, respectively.
Depending on the itemName specified, the system will execute the corresponding SQL scripts at different stages of the template execution:
preCreate: Executed before the Create script.
(Create)
postCreate: Executed after the Create script.
preRun: Executed before the Run script.
(Run)
postRun: Executed after the Run script.
Multiple values, including multiple defaults are allowed. However, they must be declared as an array in YAML or separate sections through the UI (i.e., do not put multiple statements in one line separated by semicolons).
Example configuration:
- itemType: prePostSql
itemDesc: "Description of the SQL script"
itemName: preCreate # Options: preCreate, postCreate, preRun, postRun
default:
- ALTER SESSION SET QUERY_TAG = 'ETL_LOADS' # Optional
- GRANT OWNERSHIP ON TABLE {{this}} to ROLE 'MY_ROLE' # Optional
isRequired: false
isValidated: false
isEnabled: true
Defining the Pre/Post SQL Item in the YAML Template
The following attributes can be used when defining the Pre/Post item type:
itemType: Specifies that this item is of the type
prePostSql.itemDesc: A description for the SQL script, visible in the resulting object
itemName: Determines when the SQL script will be executed:
preCreate: Before the Create script.postCreate: After the Create script.preRun: Before the Run script.postRun: After the Run script.
default: Optionally, specify the default SQL text to include in the script.
isRequired: Indicates whether the SQL script is mandatory.
isValidated: If true, the script is included in validation checks.
Note, Snowflake only validates DDL, DML, and DQL statements. Grants, permission, and session alters can not be validated.
isEnabled: Determines if the item is visible.
User UI items
The properties are available in this section of the template and can be used ad hoc. Each is designated by its pre-defined item type, but item types can be repeated using unique itemName values.
toggleButton: Defines a toggle button with a boolean default value.
Example:
- itemType: toggleButton
itemDesc: 'My Button'
itemName: my_button
default: true
isEnabled: truedropdownSelector: Specifies a dropdown selector with options.
Example:
- itemType: dropdownSelector
itemDesc: 'My dropdown selector'
itemName: my_dropdown
default: 'option 1'
options:
- 'option 1'
- 'option 2'
isRequired: false
isEnabled: truecolumnSet:
Example:
- itemType: columnSet
itemDesc: 'My column selector'
itemName: my_columnSelector
isRequired: false
isEnabled: truemultiSelector: Specifies a single-line text input.
Example:
- itemType: multiSelector
itemDesc: 'My multi selector'
itemName: my_multi_selector
options:
- 'option 1'
- 'option 2'
isRequired: false
isEnabled: truetextArea:
Example:
- itemType: textArea
itemDesc: 'My Text Area'
itemName: my_textArea
isRequired: false
isEnabled: truetextLine:
Example:
- itemType: textLine
itemDesc: 'My text line'
itemName: my_textline
isRequired: false
isEnabled: true
The items in the examples above will result in the following UI elements being available in the related Tx Objects.
Using Object Settings in Jinja
Tx Templates (and the properties defined inside) add corresponding UI elements to the related Tx Objects that use them. Once values or options are selected, these are saved as part of the Tx Object definition and can be referenced through Jinja to create dynamic SQL.
The following example shows a simple process for moving a property from a Tx Template to a Tx Object to the final SQL.
In this example, a toggle button called Rely on constraints is included in a Tx Template (e.g., 'my template'). A Tx Object called 'my object' is created using 'my template' and thereby has the Rely on constraints property set.
The property value is then referenced somewhere in the Tx Object (e.g., in the Create or Run templates or Select statement).
Example:
{%- for pkCol in colSet.columns -%}
"{{pkCol.name}}"
{%- if not loop.last %}, {% endif -%}
{%- endfor -%}
)
{%- if rely_button %} RELY {% endif -%}
Results in:
...
, CONSTRAINT my_pk PRIMARY KEY (col1, col2) RELY
...