Table Templates

stage, dim, hub—create any kind of table in one click through predefined settings

S
Written by Serge Gershkovich
Updated over a week ago

Table Templates are currently available for the following:

  • Plans: Small and Standard Enterprise

  • Databases:

    • Snowflake

    • Google BigQuery

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 existing 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 a column as well as table templates. There are several differences in their behavior that 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 the template will affect the tables that use them. Table templates are non-binding, and changes to the template do not affect existing tables that have been created as a result.

Coming soon: to give users full flexibility in adding binding or non-binding columns to a table template, it will soon be possible to assign 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:

  1. Right-click or select the "three-dot" menu on the template you wish to duplicate

  2. From the options provided, select "Duplicate"

  3. 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:

  1. Right-click on the "Table templates" header or any existing table template, or click on their respective "three-dot" menus

  2. Select "Create new"

  3. 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

name: 'unique template name'             		#required 
description: 'My template description'


schema: mySchemaName | '${parentSchema}' #default: '${parentSchema}'
physicalName: '${objectPhysicalName}' #default: '${objectPhysicalName}'
logicalName: 'My logical name' #default: '${objectPhysicalName}'
comment: 'My DDL comment'

color: grey | purple | pink | red | orange | yellow | green | blue
flags: ['flag1', 'flag2', ...]

snowflakeTags:
- name: myTag
value: myVal

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
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
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

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>'


flags: ['flag1', 'flag2', ...]

collation: 'inherit_from_table' | 'binary' | 'und:ci' #default: inherit_from_table

roundingMode: 'none' #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>'

flags: ['flag1', 'flag2', ...]

collation: 'inherit_from_table' | 'binary' | 'und:ci' #default: inherit_from_table

roundingMode: 'none' #defaul: 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

More databases coming soon.

Editing a table template

(Non-default) templates can be modified using the YAML editor.

  1. Right-click or select the "three-dot" menu on the table template you want to modify

  2. Select "Edit"

  3. 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 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:

  1. Right-click or select the "three-dot" menu on the template you would like to rename

  2. Select "Edit"

  3. The YAML script editor will open

  4. 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:

Deleting a table template

To delete an existing table template, follow the steps below. (Default templates can not be deleted).

  1. Right-click or select the "three-dot" menu on the template you would like to delete

  2. 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.

  1. Identify the parent table you wish to reference on the diagram or the explorer panel

  2. Right-click on the table and navigate to the "Create from template" menu

  3. Select the template that should be applied

  4. 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

  1. Right-click on the diagram canvas

  2. Select "create from template"

  3. Select the template to use

  4. The resulting table is added to the active diagram

From the top-bar menu

  1. "Add table" icon from the top bar menu

  2. Select the template to use

  3. The resulting table is added to the active diagram


See also:

Did this answer your question?