CONFIG_LOAD_TEMPLATES

CONFIG_LOAD_TEMPLATES is a configuration package used to manage reusable load templates, which can include load steps and load options. Load templates help standardize and streamline the development of load logic across similar use cases and multiple entities.

In Designer, you can:

• Save existing load logic as a template.

• Import load logic from a previously created template.

• Set a template as the default for a specific entity type, allowing its load logic to be automatically applied during load creation.

Additionally, when the generated on deploy option is enabled, template-driven loads can be efficiently maintained by simply updating the template and redeploying the relevant packages.

Dynamic SQL Generation with FTL
CONFIG_LOAD_TEMPLATES also supports FTL templates (FreeMarker Template Language), enabling advanced, dynamic SQL generation. FTL allows conditional logic, loops, and variable substitution, making template development more flexible and adaptable to different scenarios.

See also:

• FTL Template Reference

• Loads

• Variables

• SCD Type 2 with FTL Templates - Example use case

• Sensitive Data Vault with FTL Templates - Example use case

Usage

Saving load logic as a load template

1. Open an entity load you would like to save as a template.

2. Click “Save load logic as template”.

3. Provide a template name and description. Choose whether to generate the load logic during deployment and whether to set the template as the default (see details below). Additionally, define the ordinal position, which determines the priority when multiple templates apply to the same entity type.

4. Save the template, and it will be stored in the CONFIG_LOAD_TEMPLATES configuration package.

All elements of the load logic, including load steps and load options, are saved to the template. For more details, refer to the examples section.

Note that after saving a load template, CONFIG_LOAD_TEMPLATES will have uncommitted changes. To maintain a change history, it is recommended to commit the package. If the generated on deploy option is used, deployment is also required.

Overwriting or updating a load template

To update an existing load template, follow the same steps as when creating a new one, but use the same template name. You’ll be prompted to confirm if you want to overwrite the existing template.

Alternatively, you can update the template in the CONFIG_LOAD_TEMPLATES package on the Load Templates tab or by manually editing the package JSON (see instructions below).

Editing package JSON

1. Open the CONFIG_LOAD_TEMPLATES configuration package.

2. Click “Show Editor” and “Edit”.

3. Edit the JSON file and save changes. See the Contents section below for reference.

After saving the configuration, changes are applied to Designer only. Commit and deploy the changes to Runtime environments as well.

Using generated on deploy

With the generated on deploy option enabled, template-driven loads can be updated across all relevant packages by editing the template and redeploying CONFIG_LOAD_TEMPLATES along with the associated entity packages.

Create a load template using the generated on deploy option:

1. Follow the standard steps for creating a new load template.

2. Check options generated on deploy and is default, then save the template.

Once saved, the load logic defined in the template will be automatically added to all new loads of the specified entity type. To extend the template’s usage, e.g. to apply it to additional entity types, you can directly edit the CONFIG_LOAD_TEMPLATES JSON.

In the Designer interface, loads using a template will display the TEMPLATED label next to load steps and load options inherited from the template.

To apply changes to loads using a template:

1. Edit the relevant template in CONFIG_LOAD_TEMPLATES by modifying the JSON.

2. Commit and deploy the CONFIG_LOAD_TEMPLATES package.

3. Deploy the entity packages that contain the affected loads.

Managing FTL templates

FTL templates can be created, modified and deleted in the CONFIG_LOAD_TEMPLATES package. Navigate to Load Templates, then to Load FTL Templates.

• Click Create Load FTL Template to create a new FTL template.

• Click the name of a template to manage template parameters.

• To edit a template:

  • Use the edit button .

  • Use the code preview button to open a view where you can iteratively develop and continuously test your FTL template against a specific entity load. This helps validate the output and refine the logic in real time before deployment. Remember to click Save template to save your changes.

Refer to the FTL Template Reference for further instructions.

Notes

• When using the generated on deploy option, if an entity load includes either an OVERRIDE or OVERRIDE_FILE_LOAD load step, any load steps and load options originating from a template will be excluded.

• When using the generated on deploy option, if the same load option is defined both in the template and directly within the load, the value from the load will take precedence and override the template-defined value.

• When CONFIG_LOAD_TEMPLATES is modified:

  • Templates with generatedOnDeploy = false do not affect existing loads.

  • Templates with generatedOnDeploy = true do affect all matching loads (based on entity type and load type). Changes can be verified with the Code Preview functionality in Designer.

• When using the generated on deploy option and deploying CONFIG_LOAD_TEMPLATES to a runtime environment, make sure to also deploy the associated entity packages. This ensures that all loads are regenerated using the updated versions of the load logic templates.

• FTL templates are always generated at deployment time.
  This means any modifications made to a template will automatically be applied to all loads that use it, once both the CONFIG_LOAD_TEMPLATES package and the related entity packages are deployed.

Contents

Load template configuration

Load template configurations are managed within the JSON array block named entityLoadLogicTemplates.

Example: Load template configuration

"entityLoadLogicTemplates": [
  ...
  {
    "entityLoadLogicTemplateId": "dcb95551-8b4e-4ac1-86a9-ae33bffd9dc6",
    "templateName": "DUPLICATES_SMOKE_BLACK",
    "templateDescription": "SMOKE_BLACK duplicate test"
  }
  ...
]

Load step configuration

Load step configurations of the templates are managed within the JSON array block named entityLoadLogicTemplateLoadSteps.

SELECT COUNT(*) FROM <target_schema>.<target_entity_name>

Example: Load logic configuration

"entityLoadLogicTemplateLoadSteps": [
  ...
  {
    "entityLoadLogicTemplateLoadStepId": "47e26041-dfc3-4ddb-a6cb-77ca014b823f",
    "entityLoadLogicTemplateId": "dcb95551-8b4e-4ac1-86a9-ae33bffd9dc6",
    "loadStepName": "duplicates_smoke_black",
    "position": 1,
    "stepType": "SMOKE_BLACK",
    "loadLanguageName": "SQL",
    "textStep": "SELECT COUNT(*) FROM <target_schema>.<target_entity_name>",
    "readAffectedRows": false
  }
  ...
]

Load option configuration

Load option configurations of the templates are managed within the JSON array block named entityLoadLogicTemplateLoadOpts.

Example: Load option configuration

"entityLoadLogicTemplateLoadOpts": [
  ...
  {
    "entityLoadLogicTemplateLoadOptId": "4bc00e7a-a2b2-4b44-a32f-35cfb33e66c3",
    "entityLoadLogicTemplateId": "dcb95551-8b4e-4ac1-86a9-ae33bffd9dc6",
    "loadOptName": "OPT_USE_RUN_IDS",
    "value": "true",
    "loadOptType": "OPT_USE_RUN_IDS"
  }
  ... 
]

Entity type & load type configuration

The JSON array block named entityTypeEntityLoadTypeEntityLoadLogicTemplates defines which combinations of entity types and load types a template can be applied to.

Example: Entity type & load type configuration

"entityTypeEntityLoadTypeEntityLoadLogicTemplates": [
  ...
  {
    "entityTypeEntityLoadTypeEntityLoadLogicTemplateId": "be64ab95-6980-48d3-b049-2a8ee806b5d7",
    "entityType": "HUB",
    "entityLoadType": "TRANSFORM_PERSIST",
    "entityLoadLogicTemplateId": "dcb95551-8b4e-4ac1-86a9-ae33bffd9dc6",
    "templateOrdinalPosition": 100,
    "isDefault": false
    "generatedOnDeploy": false
  }
  ...
]

FTL template configuration

FTL template configurations are managed within the JSON array block named entityLoadFtlTemplates.

<#function provideEntityName targetEntity targetDbName="">
    <#local targetDbUsed><#compress><#if targetDbName?has_content>${targetDbName}.</#if></#compress></#local>
    <#return "${targetDbUsed}${targetEntity.schemaName}.${targetEntity.name}">
</#function>

Example: FTL template configuration

"entityLoadFtlTemplates": [
  ...
  {
    "entityLoadFtlTemplateId": "3c3caacd-2a65-4ea3-9396-b6279f10f69c",
    "templateName": "generic_functions",
    "textStep": "<#function provideEntityName targetEntity targetDbName=\"\">\n    <#local targetDbUsed><#compress><#if targetDbName?has_content>${targetDbName}.</#if></#compress></#local>\n    <#return \"${targetDbUsed}${targetEntity.schemaName}.${targetEntity.name}\">\n</#function>",
    "dbmsProduct": "DATABRICKS",
    "systemDefined": false,
    "documentation": "Reusable functions for FTL templates."
  }
  ...
]

FTL template parameter configuration

FTL template parameter configurations are managed within the JSON array block named entityLoadFtlTemplateParameters.

Example: FTL template configuration

"entityLoadFtlTemplateParameters": [
  ...
  {
    "entityLoadFtlTemplateParameterId": "c13163e8-0923-4283-ba75-87b57540f455",
    "entityLoadFtlTemplateId": "3c3caacd-2a65-4ea3-9396-b6279f10f69c",
    "name": "param1",
    "datatype": "STRING",
    "mandatory": false,
    "systemDefined": false
  },
  ...
]

Examples

Creating a new load template from an existing load

In a load defined for a Hub entity, a load step of type SMOKE_GREY is specified:

SELECT 1
FROM <target_schema>.<target_entity_name>
WHERE <targetrunidattr> = <targetrunid>
GROUP BY <target_entity_attribute_by_dv_hashkey>
HAVING COUNT(1) > 1;

Additionally, the load option OPT_USE_RUN_IDS has been enabled.

A load template is created with “Save load logic as template” with the following options:

After the template is saved, the following JSON entries will appear in the CONFIG_LOAD_TEMPLATES package:

"entityLoadLogicTemplates": [
  {
    "entityLoadLogicTemplateId": "2661f7c5-901f-4057-9723-ef0cdc979b3e",
    "templateName": "DUPLICATES_SMOKE_GREY_WITH_RUNID",
    "templateDescription": "Detects duplicate values in DV entities."
  }
],
"entityLoadLogicTemplateLoadSteps": [
  {
    "entityLoadLogicTemplateLoadStepId": "db852992-b29a-4d8d-b44f-751c58c44846",
    "entityLoadLogicTemplateId": "2661f7c5-901f-4057-9723-ef0cdc979b3e",
    "loadStepName": "duplicates_smoke_grey",
    "position": 1,
    "stepType": "SMOKE_GREY",
    "loadLanguageName": "SQL",
    "textStep": "SELECT 1\\nFROM <target_schema>.<target_entity_name>\\nWHERE <targetrunidattr> = <targetrunid>\\nGROUP BY <target_entity_attribute_by_dv_hashkey>\\nHAVING COUNT(1) > 1;"
    "readAffectedRows": false
  }
],
"entityLoadLogicTemplateLoadOpts": [
  {
    "entityLoadLogicTemplateLoadOptId": "9cb351b5-e253-4391-87e7-9f2c12a0efdd",
    "entityLoadLogicTemplateId": "2661f7c5-901f-4057-9723-ef0cdc979b3e",
    "loadOptName": "OPT_USE_RUN_IDS",
    "value": "true",
    "loadOptType": "OPT_USE_RUN_IDS"
  }
],
"entityTypeEntityLoadTypeEntityLoadLogicTemplates": [
  {
    "entityTypeEntityLoadTypeEntityLoadLogicTemplateId": "62e058d3-6d0d-48fb-9048-fa47de728632",
    "entityType": "HUB",
    "entityLoadType": "TRANSFORM_PERSIST",
    "entityLoadLogicTemplateId": "2661f7c5-901f-4057-9723-ef0cdc979b3e",
    "templateOrdinalPosition": 100,
    "isDefault": false
    "generatedOnDeploy": false
  }
]

If you would like to use the same logic also in Link loads, add another entry to entityTypeEntityLoadTypeEntityLoadLogicTemplates:

"entityTypeEntityLoadTypeEntityLoadLogicTemplates": [
  {
    "entityTypeEntityLoadTypeEntityLoadLogicTemplateId": "62e058d3-6d0d-48fb-9048-fa47de728632",
    "entityType": "HUB",
    "entityLoadType": "TRANSFORM_PERSIST",
    "entityLoadLogicTemplateId": "2661f7c5-901f-4057-9723-ef0cdc979b3e",
    "templateOrdinalPosition": 100,
    "isDefault": false
    "generatedOnDeploy": false
  },
  {
    "entityTypeEntityLoadTypeEntityLoadLogicTemplateId": "623d7f38-0bf5-42e3-9d77-edb765d8591e",
    "entityType": "LINK",
    "entityLoadType": "TRANSFORM_PERSIST",
    "entityLoadLogicTemplateId": "2661f7c5-901f-4057-9723-ef0cdc979b3e",
    "templateOrdinalPosition": 100,
    "isDefault": false
    "generatedOnDeploy": false
  },
]

The load template can now be used in Hub and Link loads with “Import load logic from template”.

Using generated on deploy

In this example, we’ll recreate the same load template as above, but using the generated on deploy feature. This approach allows the template to be maintained in CONFIG_LOAD_TEMPLATES, making it easy to propagate changes to all associated loads by simply updating the logic in CONFIG_LOAD_TEMPLATES and deploying both it and the corresponding entity packages.

In this case, the load template is created with the following options:

The generated JSON will be similar to the one shown in the above example, but the entityTypeEntityLoadTypeEntityLoadLogicTemplateId entry will differ:

"entityTypeEntityLoadTypeEntityLoadLogicTemplates": [
  {
    "entityTypeEntityLoadTypeEntityLoadLogicTemplateId": "62e058d3-6d0d-48fb-9048-fa47de728632",
    "entityType": "HUB",
    "entityLoadType": "TRANSFORM_PERSIST",
    "entityLoadLogicTemplateId": "2661f7c5-901f-4057-9723-ef0cdc979b3e",
    "templateOrdinalPosition": 100,
    "isDefault": true
    "generatedOnDeploy": true
  }
]

Load steps and load options included in the template will appear in Designer with the TEMPLATED label:

These cannot be modified directly in the entity load and must be updated through the template configuration in CONFIG_LOAD_TEMPLATES. Adding OVERRIDE steps to the load will disable the template for that specific entity. However, load options can still be used alongside the template. For more details, see the Notes section above.