FTL Template Reference

Load steps support customizable FTL (FreeMarker Template Language) templates through the following approaches:

Product-defined transformations – Built-in logic provided by Agile Data Engine to handle common transformation patterns.
Product-defined templates – Preconfigured FTL templates included with the product for reuse across typical use cases.
User-defined templates – Custom templates created by users to support platform-specific logic and dynamic SQL generation.

Product-defined transformations

Product-defined transformations can be invoked using the standard <variable> syntax.

Product-defined FTL templates

Predefined templates can be directly used without modification, see Product-defined FTL templates for a list of available templates and instructions.

User-defined FTL templates

Any FTL template defined in the CONFIG_LOAD_TEMPLATES package can be called as a user-defined FTL template in an entity load step using the following format:

CODE

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

Escape control characters with a backslash (\) when needed.

Multi-line parameter values can be given with the triple-quote (""") syntax:

CODE

<ftltemplate:MY_TEMPLATE:param1=value:param2=
"""
SELECT
  *
FROM
  \<target_schema\>.\<target_entity_name\>
"""
>

The transformation engine uses the Freemarker Template Engine. The version currently in use is FreeMarker 2.3.33.

Helper functions and macros

There are multiple built-in functions and macros that can be used directly within FTL templates. See Supported FTL functions and macros.

Template model

The template model includes various predefined objects that can be referenced within FTL templates. Refer to the diagram below for an overview of the available objects and their structure.

Below are examples of how to reference commonly used objects within the FTL template model:

Target database name: model.targetDbName
Target load name: model.load.name
Target entity: model.load.target
Target entity attributes: model.load.target.attributes
All source entities: model.load.sources

These objects can be used within FTL templates to dynamically generate logic based on the load context.

Examples

Syntax examples

Basic usage

Refer to the above diagram for details about the template model structure. For a complete list of available predefined functions and macros, see Supported FTL functions and macros.

Note that model, function and macro references can be directly used in FTL templates. This example covers some basic use cases and syntax:

CODE

<#-- Assigning a variable -->
<#assign targetEntity = model.load.target>

<#-- Calling a function -->
<#assign targetRunId = loads.getAttributeForType(model.load.target.attributes, "RUN_ID")>

<#-- Printing a variable value -->
${targetEntity.name}

<#-- Calling a macro -->
<@sql.generateEntityAttributeList
    attributes=model.load.target.attributes
    prefix="src"
    indentation=2
/>

<#-- Conditional logic -->
<#if model.load.target.type == "HUB">
    -- Do something with HUB
<#elseif model.load.target.type == "SAT">
    -- Do something with SAT
<#else>
    -- Do something with ${model.load.target.type}
</#if>

<#-- Iterating over a list -->
<#list attributesFiltered as attr>
    - ${attr.name}
</#list>

<#-- Defining a reusable sequence -->
<#assign excludeTypes = ["DV_HASHKEY", "DV_LOAD_TIME", "RUN_ID"]>

<#-- List filtering with the defined sequence -->
<#assign attributesFiltered = model.load.target.attributes?filter(
    attr -> !(excludeTypes?seq_contains(attr.attributeType))
)>

Testing variable output

The ${var} syntax is useful for printing variable values during development or debugging.

Defining and calling a function

This example defines a FreeMarker function to return a fully qualified entity name. This function builds the name in the format [database.]schema.table, depending on whether a database name is provided.

Function definition:

CODE

<#-- 
  Function: entityFullyQualifiedName
  Description: Constructs a fully qualified entity name in the format [database.]schema.table.
  Parameters:
    - entityObject (REQUIRED): an object with `schemaName` and `name` fields.
    - databaseName (OPTIONAL): a string representing the database name. If provided, it will be prepended followed by a dot.
  Returns:
    - A string like "database.schema.table" or "schema.table" if no database name is given.
-->
<#function entityFullyQualifiedName entityObject databaseName="">
    <#-- Construct the database prefix if a database name is provided -->
    <#local targetDbUsed>
        <#compress>
            <#if databaseName?has_content>
                ${databaseName}.
            </#if>
        </#compress>
    </#local>

    <#-- Return the fully qualified name -->
    <#return "${targetDbUsed}${entityObject.schemaName}.${entityObject.name}">
</#function>

Function call:

CODE

<#-- Context: Target entity is staging.STG_CUSTOMER -->
<#assign targetEntity = model.load.target>

<#assign fullNameWithDb = entityFullyQualifiedName(sampleEntity, "dw_dev")>
<#assign fullNameWithoutDb = entityFullyQualifiedName(sampleEntity)>

${fullNameWithDb}
${fullNameWithoutDb}

Output:

CODE

dw_dev.staging.STG_CUSTOMER
staging.STG_CUSTOMER

Importing from another FTL template

You can import and use content from other FTL templates to promote code reuse and maintainability.

In this example:

The function from the previous example is saved into a separate FTL template called generic_functions.
It is then imported into another FTL template, and the function is called from there.

CODE

<#import "generic_functions" as utils>

<#assign targetEntity = model.load.target>
<#assign fullNameWithDb = utils.entityFullyQualifiedName(targetEntity, "dw_dev")>

Generate SELECT statement for mapped source entities

This FTL template can be used to accelerate e.g. publish fact or dimension load logic development, where multiple entities from the data warehouse need to be joined together. The goal is not to produce complete load logic, but rather to provide a helpful starting point for writing custom, manually-maintained logic tailored to the requirements.

FTL template definition:

CODE

<#-- 
  This template dynamically generates a SELECT statement that:
  - Lists attributes from all mapped source entities.
  - Adds comments for entity names and attribute descriptions.
  - Joins the source entities based on their mappings.
  - Assigns dynamic aliases (t1, t2, ...) to each source entity.
-->

<#-- Assign the list of source entities -->
<#assign sources = model.load.sources>

<#-- Initialize a list to hold aliases -->
<#assign aliases = []>

<#-- Generate aliases for each source entity -->
<#list sources as source>
  <#assign alias = "t${source_index + 1}">
  <#assign aliases += [alias]>
</#list>

-- SELECT clause with attributes from all source entities
SELECT
<#list sources as source>
	<#assign alias = aliases[source_index]>
	-- Attributes from entity: ${source.schemaName}.${source.name}
	<#list source.attributes as attr>
	<#assign isLast = source?is_last && attr?is_last>
	${alias}.${attr.name}<#if !isLast>,</#if><#if attr.description??> -- ${attr.description}</#if>
	</#list>
</#list>
-- FROM clause with JOINs between source entities
-- Replace the below ON conditions with actual join conditions based on your schema
FROM ${sources[0].schemaName}.${sources[0].name} ${aliases[0]}
<#list sources[1..] as source>
	<#assign alias = aliases[source_index + 1]>
	JOIN ${source.schemaName}.${source.name} ${alias}
		ON ${alias}.<join_column> = ${aliases[0]}.<join_column>
</#list>

Template used on an example load which has multiple mapped source entities:

Generated SQL

SQL

-- SELECT clause with attributes from all source entities
SELECT
	-- Attributes from entity: rdv.L_T_MARINE_AIS_VOYAGE_BARENTSWATCH
	t1.dv_id, -- Data Vault Hash Key
	t1.dv_load_time, -- Data Vault Load Time
	t1.dv_run_id, -- Data Vault Run Identifier
	t1.dv_source_system, -- Data Vault Source System
	t1.dv_source_entity, -- Data Vault Source Entity
	t1.dv_package_version, -- Data Vault Package Version
	t1.dv_load_name, -- Data Vault Load Name
	t1.dv_id_marine_vessel_mmsi,
	t1.dv_id_marine_vessel_imo,
	t1.dv_id_marine_vessel_type,
	t1.msgtime,
	t1.destination,
	t1.eta,
	t1.draught,
	t1.dimensiona,
	t1.dimensionb,
	t1.dimensionc,
	t1.dimensiond,
	t1.positionfixingdevicetype,
	-- Attributes from entity: rdv.S_MARINE_VESSEL_TYPE_REFERENCE_C
	t2.dv_id, -- Data Vault Referencing Hash Key
	t2.dv_load_time, -- Data Vault Load Time
	t2.dv_run_id, -- Data Vault Run Identifier
	t2.dv_source_system, -- Data Vault Source System
	t2.dv_source_entity, -- Data Vault Source Entity
	t2.dv_package_version, -- Data Vault Package Version
	t2.dv_load_name, -- Data Vault Load Name
	t2.dv_datahash, -- Data Vault Comparison Hash
	t2.type_code,
	t2.description,
	t2.description_fi,
	t2.type_hierarchy,
	t2.hierarchy_description,
	-- Attributes from entity: rdv.S_MARINE_VESSEL_MMSI_DIGITRAFFIC_C
	t3.dv_id, -- Data Vault Referencing Hash Key
	t3.dv_load_time, -- Data Vault Load Time
	t3.dv_run_id, -- Data Vault Run Identifier
	t3.dv_source_system, -- Data Vault Source System
	t3.dv_source_entity, -- Data Vault Source Entity
	t3.dv_package_version, -- Data Vault Package Version
	t3.dv_load_name, -- Data Vault Load Name
	t3.dv_datahash, -- Data Vault Comparison Hash
	t3.callsign,
	t3.imo,
	t3.shiptype,
	t3.mmsi,
	t3.name,
	-- Attributes from entity: rdv.R_MARINE_VESSEL_COUNTRY
	t4.mid,
	t4.country,
	t4.country_code,
	t4.dv_load_time,
	t4.dv_source_system,
	-- Attributes from entity: rdv.S_MARINE_VESSEL_MMSI_BARENTSWATCH_C
	t5.dv_id, -- Data Vault Referencing Hash Key
	t5.dv_load_time, -- Data Vault Load Time
	t5.dv_run_id, -- Data Vault Run Identifier
	t5.dv_source_system, -- Data Vault Source System
	t5.dv_source_entity, -- Data Vault Source Entity
	t5.dv_package_version, -- Data Vault Package Version
	t5.dv_load_name, -- Data Vault Load Name
	t5.dv_datahash, -- Data Vault Comparison Hash
	t5.mmsi,
	t5.imonumber,
	t5.callsign,
	t5.name,
	t5.shiplength,
	t5.shipwidth,
	t5.shiptype,
	t5.reportclass,
	-- Attributes from entity: rdv.H_MARINE_VESSEL_MMSI
	t6.dv_id, -- Data Vault Hash Key
	t6.dv_load_time, -- Data Vault Load Time
	t6.dv_run_id, -- Data Vault Run Identifier
	t6.dv_source_system, -- Data Vault Source System
	t6.dv_source_entity, -- Data Vault Source Entity
	t6.dv_package_version, -- Data Vault Package Version
	t6.dv_load_name, -- Data Vault Load Name
	t6.business_key, -- Hub business key
	-- Attributes from entity: rdv.L_T_MARINE_AIS_VOYAGE_DIGITRAFFIC
	t7.dv_id, -- Data Vault Hash Key
	t7.dv_load_time, -- Data Vault Load Time
	t7.dv_run_id, -- Data Vault Run Identifier
	t7.dv_source_system, -- Data Vault Source System
	t7.dv_source_entity, -- Data Vault Source Entity
	t7.dv_package_version, -- Data Vault Package Version
	t7.dv_load_name, -- Data Vault Load Name
	t7.dv_id_marine_vessel_mmsi,
	t7.dv_id_marine_vessel_imo,
	t7.dv_id_marine_vessel_type,
	t7.ts_epoch,
	t7.ts,
	t7.destination,
	t7.referencepointa,
	t7.referencepointb,
	t7.referencepointc,
	t7.referencepointd,
	t7.draught,
	t7.eta,
	t7.postype
-- FROM clause with JOINs between source entities
-- Replace the below ON conditions with actual join conditions based on your schema
FROM rdv.L_T_MARINE_AIS_VOYAGE_BARENTSWATCH t1
	JOIN rdv.S_MARINE_VESSEL_TYPE_REFERENCE_C t2
		ON t2.<join_column> = t1.<join_column>
	JOIN rdv.S_MARINE_VESSEL_MMSI_DIGITRAFFIC_C t3
		ON t3.<join_column> = t1.<join_column>
	JOIN rdv.R_MARINE_VESSEL_COUNTRY t4
		ON t4.<join_column> = t1.<join_column>
	JOIN rdv.S_MARINE_VESSEL_MMSI_BARENTSWATCH_C t5
		ON t5.<join_column> = t1.<join_column>
	JOIN rdv.H_MARINE_VESSEL_MMSI t6
		ON t6.<join_column> = t1.<join_column>
	JOIN rdv.L_T_MARINE_AIS_VOYAGE_DIGITRAFFIC t7
		ON t7.<join_column> = t1.<join_column>;

Product-defined transformations

Product-defined FTL templates

User-defined FTL templates

Helper functions and macros

Template model

Examples

Syntax examples

Basic usage

Defining and calling a function

Importing from another FTL template

Generate SELECT statement for mapped source entities

Other examples