FTL Template Reference

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

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

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

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

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

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

${fullNameWithDb}
${fullNameWithoutDb}

dw_dev.staging.STG_CUSTOMER
staging.STG_CUSTOMER

<#import "generic_functions" as utils>

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

<#-- 
  This template dynamically generates a SQL SELECT statement that:
  
  - Iterates over all source entities defined in the load model.
  - Assigns simple aliases (t1, t2, ...) to each source entity for use in the SQL.
  - Outputs a flat SELECT clause listing all attributes from all sources, 
    with optional inline comments for attribute descriptions.
  - Starts the FROM clause using the first source entity.
  - Appends JOIN clauses for the remaining source entities with placeholder ON conditions.
  - Includes inline comments listing available reference-based join suggestions
    for each JOINed entity (based on entity-level references).
-->

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

<#-- Step 2: Generate aliases (t1, t2, ...) for each source entity -->
<#assign aliases = []>
<#list sources as source>
    <#assign alias = "t${source_index + 1}">
    <#assign aliases += [alias]>
</#list>

-- SELECT clause with entity-qualified attributes and inline descriptions
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 based on mapped entities and references
FROM ${sources[0].schemaName}.${sources[0].name} ${aliases[0]}

<#-- Step 3: Loop over all sources (except the first) to emit JOINs -->
<#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>
    <#-- Step 4: Print reference-based join suggestions as comments -->
    <#list source.references?default([]) as ref>
        <#assign parent = ref.parentEntity>
        <#list ref.referenceAttributes?sort_by("position") as refAttr>
            <@utils.indent
                level=1
                content="-- Reference: ${alias}.${refAttr.attribute.name} = ${parent.name}.${refAttr.parentAttribute.name}"
            />
        </#list>
    </#list>

</#list>