This feature may not be available by default and might need to be enabled upon request. Please contact your account manager for more information.
Table Templates are currently available for the following Databases:
Snowflake
Google BigQuery
Databricks
-
Azure Synapse
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)
view (default) - Snowflake only
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
_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
# ============================================================================= # Object Template # ----------------------------------------------------------------------------- # Defines a reusable object (table, view, materialized view, or dynamic table) # that can be applied to new objects in a project. Properties marked #required # must be supplied; all others fall back to the indicated #default. # ============================================================================= # ----------------------------------------------------------------------------- # Template metadata # ----------------------------------------------------------------------------- name: 'unique template name' # required description: 'My template description' materialization: table | dynamicTable | view | materializedView # default: table # ----------------------------------------------------------------------------- # Object-level properties # ----------------------------------------------------------------------------- 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 # in days # Type of relationship auto-generated to the parent object. generateRelationship: none | physical | VFK | VJ | VDEP # default: none for table # VDEP for view, MV, dynamic table # How identifier columns are inherited from the parent object. inheritIdentifiersAs: pk | non-pk | false # default: non-pk inheritAttributes: true | false # default: true # Names of column templates to apply to this object. columnTemplates: ['my_col_temp1', 'my_col_temp2', '...'] # Layer in which the object is visible. visibility: physical&logical | physicalOnly | logicalOnly # default: physical&logical # ----------------------------------------------------------------------------- # Columns # ----------------------------------------------------------------------------- # Two column groups are supported and share the same property set: # primaryColumns - the object's keys and required attributes # defaultColumns - additional standard attributes (e.g. audit fields) # ----------------------------------------------------------------------------- primaryColumns: - name: 'myColumn' # required logicalName: 'My logical name' dataType: '' # required, e.g. VARCHAR(50), NUMBER(18,0) comment: 'My DDL comment' nullable: true | false # default: false unique: true | false # default: false useQuotes: true | false # default: false default: '' isVPK: true | false # mark as Virtual Primary Key VBK: 'VBK name' # Virtual Business Key; reuse the same # name across columns for compound keys # SQL expression for view / MV / dynamic-table columns. # Escape single quotes per YAML rules. logic: "'SQL formula'" identity: true | false # default: false sequence: 'mySeq' visibility: physical&logical | physicalOnly | logicalOnly # default: physical&logical flags: ['flag1', 'flag2', ...] snowflakeTags: - name: myTag value: myVal position: first | last # default: first defaultColumns: - name: 'myColumn' # required logicalName: 'My logical name' dataType: '' # required comment: 'My DDL comment' nullable: true | false # default: false unique: true | false # default: false useQuotes: true | false # default: false default: '' isVPK: true | false VBK: 'VBK name' # reuse the same name for compound keys # SQL expression for view / MV / dynamic-table columns. logic: "'SQL formula'" identity: true | false # default: false sequence: 'mySeq' visibility: physical&logical | physicalOnly | logicalOnly # default: physical&logical flags: ['flag1', 'flag2', ...] snowflakeTags: - name: myTag value: myVal position: first | last # default: first # ----------------------------------------------------------------------------- # Post-script # ----------------------------------------------------------------------------- postScript: 'Any SQL;' # appended after the object DDL # ============================================================================= # Materialization-specific properties # ----------------------------------------------------------------------------- # Each block below applies only to the indicated materialization type(s) # and is ignored for all others. # ============================================================================= # --- View --- recursive: true | false # default: false changeTracking: true | false # default: false # --- Materialized view --- clusterBy: 'cluster by value' # default: empty dataMetricSchedule: type: unspecified | interval | cron # default: unspecified expression: 'interval or cron expression' # ignored when type = unspecified # --- View and materialized view (shared) --- copyGrants: true | false # default: false secure: true | false # default: false # --- Dynamic table --- targetLag: type: Downstream | Specify # default: Downstream specifyLag: 50 # default: empty specifyInterval: Seconds | Minutes | Hours | Days # default: Seconds warehouse: myWH # default: empty initializationWarehouse: myInitWh # default: empty initializeOn: Create | Schedule # default: Create refreshMode: Auto | Full | Incremental # default: Auto dataRetention: 3 # days, default: empty maxDataExtension: 4 # days, default: empty # ============================================================================= # Template variables # ----------------------------------------------------------------------------- # Can be used as values anywhere in this template. # # ${objectPhysicalName} Default physical name for a new object in the # project, respecting naming conventions. # ${parentPhysicalName} Physical name of the parent object when the # template is created from one; otherwise blank. # ${parentLogicalName} Logical name of the parent object when applicable; # otherwise blank. # ${parentSchema} Schema of the parent object when applicable; # otherwise blank. # =============================================================================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', ...] visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. expiresAt: '1970-01-01 00:00:00 UTC' #optional expirationDays: #optional partitionFilter: true | false #default: false collation: 'inherit_from_dataset' | 'binary' | 'und:ci' #default: inherit_from_dataset generateRelationship: none | physical | VFK | VJ | VDEP #default: none 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: '' #required comment: 'My DDL Comment' nullable: true | false #default: false unique: true | false #default: false default: '' collation: 'inherit_from_table' | 'binary' | 'und:ci' #default: inherit_from_table roundingMode: 'none' visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. flags: ['flag1', 'flag2', ...] #default: none position: first | last #default: first defaultColumns: - name: 'myColumn' #required logicalName: 'My logical name' dataType: '' #required comment: 'My DDL Comment' nullable: true | false #default: false unique: true | false #default: false default: '' collation: 'inherit_from_table' | 'binary' | 'und:ci' #default: inherit_from_table roundingMode: 'none' visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. 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 blankAzure 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' visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. color: grey | purple | pink | red | orange | yellow | green | blue flags: ['flag1', 'flag2', ...] generateRelationship: none | physical | VFK | VJ | VDEP #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: '' #required collation: 'collationValue' comment: 'My DDL Comment' nullable: true | false #default: false unique: true | false #default: false default: 'current_date()' visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. flags: ['flag1', 'flag2', ...] position: first | last #default: first defaultColumns: - name: 'myColumn' #required logicalName: 'My logical name' dataType: '' #required collation: 'Cyrillic_General_CI_AS' comment: 'My DDL Comment' nullable: true | false #default: false unique: true | false #default: false default: 'value' visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. 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 blankDatabricks
name: 'unique template name' #required description: 'My template description' # 'external' value is supported for 'materialization' parameter materialization: 'table' #default: 'table' ('table' | 'external') schema: mySchemaName | '${parentSchema}' #default: '${parentSchema}' physicalName: '${objectPhysicalName}' #default: '${objectPhysicalName}' logicalName: 'My logical name' #default: '${objectLogicalName}' comment: 'My DDL comment' visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. color: grey | purple | pink | red | orange | yellow | green | blue flags: ['flag1', 'flag2', ...] generateRelationship: none | physical | VFK | VJ | VDEP #default: none inheritIdentifiersAs: pk | non-pk | none #default: non-pk inheritAttributes: true | false #default: true 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 visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. 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 visibility: physical&logical | physicalOnly | logicalOnly #default: physical&logical. flags: ['flag1', 'flag2', ...] position: first | last #default: first # 'fileFormat' is mandatory for external tables fileFormat: 'Avro' | 'Csv' | 'Delta' | 'Json' | 'Orc' | 'Parquet' | 'Text' #default 'none', mandatory if materialization: 'external' # 'location' parameter is added location: '/data/location_path' #String, if materialization: 'external' (mandatory), esle (optional) tblProperties: - name: 'delta.columnMapping.mode' #'delta.columnMapping.mode' property to make the alter scripts work correctly value: 'name' # (string, required), default '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 blankEditing 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
See also: