Table Templates are currently available for the following:
Plans: Small and Standard Enterprise
Databases:
Snowflake
Google BigQuery
Databricks
Azure Synapse
more coming soon...
What are table templates?
Table templates allow you to accelerate the build of tables that would follow a consistent pattern and reduce design time.
A typical use case for this would be creating a data vault 2.0 table based on another table already in your model (see "How to use Table Templates" later in this article for further instructions).
Table templates allow you to automate things like:
Table naming standards - by adding common patterns to the names of tables and columns. An example of this is prefixing dimension tables with the prefix "Dim_".
Table colors - if you're working with Data Vault 2.0, you can ensure the objects (e.g., hubs, sats, and links) are always created with the standard colors.
System columns - consistently add system columns like load_date or valid_from.
How do table templates differ from column templates?
SqlDBM offers users the possibility to create column and table templates. However, several differences in their behavior should be considered.
Column templates are added to an existing table, while table templates create a new table.
Column templates are binding, and future changes to them will affect the tables that use them. Table templates are non-binding, and changes to them do not affect existing tables that have been created as a result.
Users can benefit from the best of both worlds by assigning column templates through table templates.
Table templates in action
Default table templates
There are four out-of-the-box templates to help get you started:
hub (default)
link (default)
satellite (default)
type 2 dimension (default)
The default templates are for reference and cannot be removed or edited by users. However, they can be cloned and adjusted to your exact specifications.
Example code for "2 dimension (default)" Table Template
name: type 2 dimension (default)
physicalName: dim_${parentPhysicalName}
color: orange
inheritIdentifiersAs: pk
inheritAttributes: true
generateRelationship: none
primaryColumns:
- name: from_dts
dataType: timestamp_ntz(9)
nullable: false
position: last
defaultColumns:
- name: to_dts
dataType: timestamp_ntz(9)
position: first
- name: load_dts
dataType: timestamp_ntz(9)
position: last
Creating a table template
Table templates can be created from scratch or by duplicating one that already exists.
Duplicating a table template
To duplicate a table 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 now be named <Table Template Name>_Clone
Creating a new table template
When creating a new table template, a default YAML showing all template properties is included. This default should be adjusted by removing any unnecessary properties and setting the values for those that remain. See the "Editing a table template" section for more on this.
To create a new template:
Right-click on the "Table templates" header or any existing table template, or click on their respective "three-dot" menus
Select "Create new"
A new table template will be created and added to the bottom of your table template list
The only required property for a template is the "name." All other properties are optional or have defined defaults.
Complete YAML properties reference
The following sections describe all properties supported by table templates for each type of project.
Snowflake
Snowflake
name: 'unique template name' #required
description: 'My template description'
schema: mySchemaName | '${parentSchema}' #default: '${parentSchema}'
physicalName: '${objectPhysicalName}' #default: '${objectPhysicalName}'
logicalName: 'My logical name' #default: '${objectLogicalName}'
useQuotes: true | false #default: false
comment: 'My DDL comment'
color: grey | purple | pink | red | orange | yellow | green | blue
flags: ['flag1', 'flag2', ...]
snowflakeTags:
- name: myTag
value: myVal
isTransient: true | false
dataRetention: 1
generateRelationship: none | virtual | physical #default: none
inheritIdentifiersAs: pk | non-pk | false #default: non-pk
inheritAttributes: true | false #default: true
columnTemplates: ['my_col_temp1', 'my_col_temp2', 'etc.']
primaryColumns:
- name: 'myColumn' #required
logicalName: 'My logical name'
dataType: '<datatype>' #required
comment: 'My DDL Comment'
nullable: true | false #default: false
unique: true | false #default: false
useQuotes: true | false #default: false
default: '<default>'
identity: true | false #default: false
sequence: 'mySeq'
flags: ['flag1', 'flag2', ...]
snowflakeTags:
- name: myTag
value: myVal
position: first | last #default: first
defaultColumns:
- name: 'myColumn' #required
logicalName: 'My logical name'
dataType: '<datatype>' #required
comment: 'My DDL Comment'
nullable: true | false #default: false
unique: true | false #default: false
useQuotes: true | false #default: false
default: '<default>'
identity: true | false #default: false
sequence: 'mySeq'
flags: ['flag1', 'flag2', ...]
snowflakeTags:
- name: myTag
value: myVal
position: first | last #default: first
postScript: 'Any SQL;'
# The following variables can also be used in template values
#
# ${objectPhysicalName} - the default name for a new table in a given project, respecting naming conventions
# ${parentPhysicalName} - the physical name when creating the template from a parent object, otherwise blank
# ${parentLogicalName} - the logical name when creating the template from a parent object, otherwise blank
# ${parentSchema} - the schema when creating the template from a parent object, otherwise blank
#
# For the full YAML template documentation, please see our Help Center:
# https://intercom.help/SqlDBMhelpcenter/en/articles/8662960-table-templates
Google BigQuery
Google BigQuery
name: 'unique template name' #required
description: 'My template description'
schema: mySchemaName | '${parentSchema}' #default: '${parentSchema}'
physicalName: '${objectPhysicalName}' #default: '${objectPhysicalName}'
logicalName: 'My logical name' #default: '${objectLogicalName}'
comment: 'My DDL comment'
color: grey | purple | pink | red | orange | yellow | green | blue
flags: ['flag1', 'flag2', ...]
expiresAt: '1970-01-01 00:00:00 UTC' #optional
expirationDays: <integer> #optional
partitionFilter: true | false #default: false
collation: 'inherit_from_dataset' | 'binary' | 'und:ci' #default: inherit_from_dataset
generateRelationship: 'none' #default: none (physical | virtual coming soon)
inheritIdentifiersAs: pk | non-pk | false #default: non-pk
inheritAttributes: true | false #default: true
generateRelationship: none | virtual | physical #default: none
columnTemplates: ['my_col_temp1', 'my_col_temp2', 'etc.']
primaryColumns:
- name: 'myColumn' #required
logicalName: 'My logical name'
dataType: '<datatype>' #required
comment: 'My DDL Comment'
nullable: true | false #default: false
unique: true | false #default: false
default: '<default>'
collation: 'inherit_from_table' | 'binary' | 'und:ci' #default: inherit_from_table
roundingMode: 'none'
flags: ['flag1', 'flag2', ...] #default: none
position: first | last #default: first
defaultColumns:
- name: 'myColumn' #required
logicalName: 'My logical name'
dataType: '<datatype>' #required
comment: 'My DDL Comment'
nullable: true | false #default: false
unique: true | false #default: false
default: '<default>'
collation: 'inherit_from_table' | 'binary' | 'und:ci' #default: inherit_from_table
roundingMode: 'none'
flags: ['flag1', 'flag2', ...] #default: none
position: first | last #default: first
# The following variables can also be used in template values
#
# ${objectPhysicalName} - the default name for a new table in a given project, respecting naming conventions
# ${parentPhysicalName} - the physical name when creating the template from a parent object, otherwise blank
# ${parentLogicalName} - the logical name when creating the template from a parent object, otherwise blank
# ${parentSchema} - the schema when creating the template from a parent object, otherwise blank
#
# For the full YAML template documentation, please see our Help Center:
# https://intercom.help/SqlDBMhelpcenter/en/articles/8662960-table-templatesSqlDBMhelpcenter/en/articles/8662960-table-templates
Azure Synapse
Azure Synapse
name: 'unique template name' #required
description: 'My template description'
schema: mySchemaName | '${parentSchema}' #default: '${parentSchema}'
physicalName: '${objectPhysicalName}' #default: '${objectPhysicalName}'
logicalName: 'My logical name' #default: '${objectLogicalName}'
comment: 'My DDL comment'
color: grey | purple | pink | red | orange | yellow | green | blue
flags: ['flag1', 'flag2', ...]
generateRelationship: 'none' #default: none | (virtual coming soon)
inheritIdentifiersAs: pk | non-pk | false #default: non-pk
inheritAttributes: true | false #default: true
columnTemplates: ['my_col_temp1', 'my_col_temp2', 'etc.']
primaryColumns:
- name: 'myColumn' #required
logicalName: 'My logical name'
dataType: '<datatype>' #required
collation: 'collationValue'
comment: 'My DDL Comment'
nullable: true | false #default: false
unique: true | false #default: false
default: 'current_date()'
flags: ['flag1', 'flag2', ...]
position: first | last #default: first
defaultColumns:
- name: 'myColumn' #required
logicalName: 'My logical name'
dataType: '<datatype>' #required
collation: 'Cyrillic_General_CI_AS'
comment: 'My DDL Comment'
nullable: true | false #default: false
unique: true | false #default: false
default: 'value'
flags: ['flag1', 'flag2', ...]
position: first | last #default: first
indexes:
- indexName: 'indexName'
indexType: 'unique' | 'index' #default: index
generateAsConstraint: true | false #default: true
members:
- 'columnName1'
- 'columnName2'
description: 'Any text'
structureOptions: 'clusteredColumnstoreIndex' | 'heap' | 'clusteredIndex' # default 'clusteredColumnstoreIndex'
structureMember: 'columnName'
distributionOptions: 'roundRobin'| 'hash' | 'replicate' #default 'roundRobin'
distributionMemder: 'columnName'
partitionOptions:
partitionMember: 'colunName'
partitionValues:
- partitionValue
range: right | left #default left
postScript: 'Any SQL;'
# The following variables can also be used in template values
#
# ${objectPhysicalName} - the default name for a new table in a given project, respecting naming conventions
# ${parentPhysicalName} - the physical name when creating the template from a parent object, otherwise blank
# ${parentLogicalName} - the logical name when creating the template from a parent object, otherwise blank
# ${parentSchema} - the schema when creating the template from a parent object, otherwise blank
#
# For the full YAML template documentation, please see our Help Center:
# https://intercom.help/SqlDBMhelpcenter/en/articles/8125291-yaml-table-templates
Databricks
Databricks
name: 'unique template name' #required
description: 'My template description'
schema: mySchemaName | '${parentSchema}' #default: '${parentSchema}'
physicalName: '${objectPhysicalName}' #default: '${objectPhysicalName}'
logicalName: 'My logical name' #default: '${objectLogicalName}'
comment: 'My DDL comment'
color: grey | purple | pink | red | orange | yellow | green | blue
flags: ['flag1', 'flag2', ...]
generateRelationship: 'none' #default: none
inheritIdentifiersAs: pk | non-pk | false #default: non-pk
inheritAttributes: true | false #default: true
columnTemplates: ['my_col_temp1', 'my_col_temp2', 'etc.']
primaryColumns:
- name: 'myColumn' #required
logicalName: 'My logical name'
dataType: '<datatype>' #required
comment: 'My DDL Comment'
nullable: true | false #default: false
unique: true | false #default: false
default: '<default>'
#or
expression: 'LEFT(col1,2)'
identity: true | false #default: false
flags: ['flag1', 'flag2', ...]
position: first | last #default: first
defaultColumns:
- name: 'myColumn' #required
logicalName: 'My logical name'
dataType: '<datatype>' #required
comment: 'My DDL Comment'
nullable: true | false #default: false
unique: true | false #default: false
default: '<default>'
#or
expression: 'LEFT(col1,2)'
identity: true | false #default: false
flags: ['flag1', 'flag2', ...]
position: first | last #default: first
fileFormat: 'Avro' | 'Csv' | 'Delta' | 'Json' | 'Orc' | 'Parquet' | 'Text' #default 'none'
tblProperties:
- name: 'delta.columnMapping.mode'
value: 'name'
# The following variables can also be used in template values
#
# ${objectPhysicalName} - the default name for a new table in a given project, respecting naming conventions
# ${parentPhysicalName} - the physical name when creating the template from a parent object, otherwise blank
# ${parentLogicalName} - the logical name when creating the template from a parent object, otherwise blank
# ${parentSchema} - the schema when creating the template from a parent object, otherwise blank
#
# For the full YAML template documentation, please see our Help Center:
# https://intercom.help/SqlDBMhelpcenter/en/articles/8125291-yaml-table-templates
More databases coming soon...
Editing a table template
(Non-default) templates can be modified using the YAML editor.
Right-click or select the "three-dot" menu on the table template you want to modify
Select "Edit"
Use the YAML template editor to modify the contents to:
Add or remove properties
Modify the property values
Leave comments (using the hash "#" sign)
The editor contains two tabs: YAML Script and Warnings & Errors
YAML Script is where you will edit the template.
Warnings & Errors is where you'll be notified of any warnings and errors.
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
${objectPhysicalName} - The system-generated physical name for a new SqlDBM object, respecting existing naming conventions. This is the same name that a new object would take when created on a diagram, following project naming conventions (typically, "table_n").
${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.
${parentSchema} - The schema name of the parent if creating the template from an existing object. Otherwise, blank.
Inheriting parent table columns
Table templates can be used to generate tables from scratch or using a parent table. When creating from a parent table, the following properties can be used to determine which columns will be inherited and the order in which they will appear.
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 table template
To rename an existing table template:
Right-click or select the "three-dot" menu on the template you would like to rename
Select "Edit"
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:
Deleting a table template
To delete an existing table 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"
Using table templates
Table templates can be used to create a new table from scratch or from an existing parent table.
Create a new table from an existing table
Using a template of your choice, you can create a table from an existing parent.
Identify the parent table you wish to reference on the diagram or the explorer panel
Right-click on the table and navigate to the "Create from template" menu
Select the template that should be applied
The resulting table is added to the active diagram
Create a new table from scratch
Users can create an independent new table using a template in one of two ways.
From the diagram
Right-click on the diagram canvas
Select "create from template"
Select the template to use
The resulting table is added to the active diagram
From the top-bar menu
"Add table" icon from the top bar menu
Select the template to use
The resulting table is added to the active diagram
