Product-defined FTL templates

These FTL templates are built-in and their output can be customized using different parameters, allowing you to adapt the generated logic to your specific use case.

ATTRIBUTE_TRANSFORMATION_LIST

This FTL template uses the first entity mapping of the load to generate a list of attribute transformations. It can be used to dynamically produce transformation logic based on the attribute mapping.

Syntax

CODE

<ftltemplate:ATTRIBUTE_TRANSFORMATION_LIST:param1=value:param2=value>

Parameters

All parameters are optional.

Name

Type

Description

key_attribute_name

STRING

Name of the attribute used as a key. Currently used with the HASH_LIST transformation.

prefix

STRING

Prefix applied to mapped source attribute fields.

target_attribute_type

STRING

Filters the output to include only the transformation of the attribute with a specific technical type in the target entity.

transform_empty

STRING

Transformation used for 1:1 mappings where no specific transformation is defined.

Value	Description
CAST_NAME	Casts source attributes by name to the corresponding target attribute data type.
CAST_PARQUET	Casts source attributes from a Parquet file to the defined target data type.
CAST_EXTTABLE	Defines transformation format for external tables.
CAST_EXTTABLE_PARQUET	Defines external table transformation from Parquet data.
CAST_POSITION	Refers to attributes by their position instead of name (supported in Databricks SQL, Azure SQL Database, Azure Synapse SQL, Microsoft Fabric, Snowflake).
Custom	Fully custom transformation can also be defined, requires separate configuration.

show_target_name_bool

BOOLEAN

Controls whether the target attribute name should be included in the output.

Default: true

Example

Template used in a load step:

SQL

SELECT
    <ftltemplate:ATTRIBUTE_TRANSFORMATION_LIST:prefix=src>
FROM
    <source_entity_schema>.<source_entity_name> src

Generated SQL:

SQL

SELECT
    , CURRENT_TIMESTAMP::timestamp_ntz AS stg_create_time
    , '<source_system_name>' AS stg_source_system
    , src.addressid AS addressid
    , src.addressline1 AS addressline1
    , src.addressline2 AS addressline2
    , src.city AS city
    , src.stateprovinceid AS stateprovinceid
    , src.postalcode AS postalcode
    , src.spatiallocation AS spatiallocation
    , src.rowguid AS rowguid
    , src.modifieddate AS modifieddate
FROM
    staging.STG_ADDRESS src;

SOURCE_ATTRIBUTE_NAME_LIST

This FTL template generates a list of source entity attribute names for the specified source entity mapping position.

Syntax

CODE

<ftltemplate:SOURCE_ATTRIBUTE_NAME_LIST:param1=value:param2=value>

Parameters

All parameters are optional.

Name	Type	Description
position	INT	Specifies which source entity mapping (by position) to use for generating the attribute list. Default: `1`
prefix	STRING	Prefix to apply to each mapped source attribute name.
attribute_type	STRING	Filters the output to include only the attribute of a specific technical type from the source entity.
indentation	INT	Defines the indentation used in the result, useful for formatting queries with proper indentation.

Example

Template used in a load step:

SQL

SELECT
<ftltemplate:SOURCE_ATTRIBUTE_NAME_LIST:position=2>
FROM
    <source_entity_schema_2>.<source_entity_name_2>

Note that parameter position=2 refers to the second entity mapping in the load.

Generated SQL:

SQL

SELECT
customerid
, personid
, storeid
, territoryid
, accountnumber
, rowguid
, modifieddate
FROM
    staging.STG_CUSTOMER;

TARGET_ATTRIBUTE_NAME_LIST

This FTL template generates a list of attribute names from the target entity.

Syntax

CODE

<ftltemplate:TARGET_ATTRIBUTE_NAME_LIST:param1=value:param2=value>

Parameters

All parameters are optional.

Name	Type	Description
prefix	STRING	Prefix to apply to each target entity attribute name.
attribute_type	STRING	Filters the output to include only the transformation of the attribute with a specific technical type in the target entity.
indentation	BOOLEAN	Defines the indentation used in the result, useful for formatting queries with proper indentation.

Example

Template used in a load step:

SQL

...
WHERE
  YEAR(<ftltemplate:TARGET_ATTRIBUTE_NAME_LIST:prefix=src:attribute_type=STG_CREATE_TIME>) = 2025
...

Generated SQL:

SQL

...
WHERE
  YEAR(src.stg_create_time) = 2025
...

SET_TARGET_ATTRIBUTE_BY_NAME

This FTL template generates value assignment expressions based on the target entity attribute list.
It can be used, for example, in MERGE statements, where attributes are matched and updated by name.

Syntax

CODE

<ftltemplate:SET_TARGET_ATTRIBUTE_BY_NAME:param1=value:param2=value>

Parameters

All parameters are optional.

Name	Type	Description
included_attributes_list	LIST	A list of specific attribute names to include in the output.
included_attribute_prefix_list	LIST	A list of attribute name prefixes to include. Only attributes starting with these prefixes will be processed.
included_attribute_type_list	LIST	A list of technical attribute types to include.
excluded_attributes_list	LIST	A list of attribute names to exclude from the result.
trg_prefix	STRING	Prefix to apply to target entity attribute references.
src_prefix	STRING	Prefix to apply to source entity attribute references. Default: `src`
indentation	INT	Defines the indentation used in the result, useful for formatting queries with proper indentation.

Set list parameters using a comma-separated list of values, for example:
included_attributes_list=attribute1,attribute2,attribute3

Example

Template used in a load step:

SQL

...
WHEN MATCHED THEN UPDATE SET 
<ftltemplate:SET_TARGET_ATTRIBUTE_BY_NAME:trg_prefix=trg:included_attribute_prefix_list=a,b,c>
...

Generated SQL:

SQL

...
WHEN MATCHED THEN UPDATE SET 
trg.addressid = src.addressid
, trg.addressline1 = src.addressline1
, trg.addressline2 = src.addressline2
, trg.city = src.city
...

SET_TARGET_ATTRIBUTE_BY_TRANSFORMATION

This FTL template generates target entity attribute transformation lists and allows overriding the transformation logic selected in the attribute mapping. You can filter the output to specific attributes by name, prefix, or technical type.

Syntax

CODE

<ftltemplate:SET_TARGET_ATTRIBUTE_BY_TRANSFORMATION:param1=value:param2=value>

Parameters

All parameters are optional.

Name	Type	Description
included_attributes_list	LIST	A list of attribute names to be included in the output.
excluded_attributes_list	LIST	A list of attribute names to be excluded from the output.
included_attribute_prefix_list	LIST	A list of attribute name prefixes to include in the transformation.
included_attribute_type_list	LIST	A list of technical attribute types to include.
override_prefix_transformation_map	MAP	A map assigning specific transformations to attributes by prefix.
override_attribute_type_transformation_map	MAP	A map assigning transformations based on technical attribute types.
prefix	STRING	Prefix to apply to source entity attribute references. Default: `src`
show_target_name_bool	BOOLEAN	Indicates whether the target attribute name should be displayed in the output.
indentation	INT	Sets the indentation level for formatting the output.

Set list parameters using a comma-separated list of values, for example:
included_attributes_list=attribute1,attribute2,attribute3

Set map parameters using the following syntax:

override_attribute_type_transformation_map=ATTRIBUTE_TYPE1#TRANSFORMATION_TYPE2,ATTRIBUTE_TYPE2#TRANSFORMATION_TYPE2

Example

Template used in a load step:

NONE

...
SELECT
<ftltemplate:SET_TARGET_ATTRIBUTE_BY_TRANSFORMATION:
included_attribute_type_list=DV_HASHKEY,BUSINESS_KEY:
override_attribute_type_transformation_map=DV_HASHKEY#BUSINESS_KEY,BUSINESS_KEY#HASH_KEY:
prefix=source>
FROM
...

Generated SQL:

SQL

...
SELECT
UPPER(NVL(NULLIF(TRIM(CAST(source.addressid AS VARCHAR)), ''), '-1')) AS dv_id
, MD5(UPPER(NVL(NULLIF(TRIM(CAST(source.addressid AS VARCHAR)), ''), '-1'))) AS business_key
FROM
...