On this page
Templates¶
A template is a YAML file under templates/ that declares a list of
parametrised actions. A flowgroup invokes it with use_template: and supplies
template_parameters:; LHP renders the {{ param }} placeholders with those
values and expands the template into the flowgroup’s actions.
name: <template_name>
version: "1.0"
presets: []
parameters:
- name: <param_name>
required: <bool>
default: <value>
actions:
- name: "<action_{{ param_name }}>"
type: <load|transform|write|test>
...
See also
How-to guide: Parameterize a template for many tables.
Template file fields¶
Top-level fields of a template file. Only name is required; every other
field has a default.
Field |
Type |
Required |
Default |
Description |
|---|---|---|---|---|
|
string |
Yes |
— |
Template identifier, surfaced in validation errors. |
|
string |
No |
|
Version tag for change tracking. |
|
string |
No |
— |
Human-readable summary of the template. |
|
list[string] |
No |
|
Preset names applied to the actions this template generates. |
|
list[mapping] |
No |
|
Parameter declarations (entry schema below). |
|
list[mapping] |
No |
|
Action patterns carrying |
Parameter fields¶
Keys of a single entry in the parameters: list. The engine reads only
name, required, and default.
Key |
Type |
Required |
Default |
Description |
|---|---|---|---|---|
|
string |
Yes |
— |
Parameter name. Referenced as |
|
bool |
No |
|
When |
|
any |
No |
— |
Value used when the flowgroup omits the parameter. |
|
string |
No |
— |
Human-readable note. Not read by the engine. |
Invoke from a flowgroup¶
A flowgroup expands a template through these two fields.
Field |
Type |
Required |
Default |
Description |
|---|---|---|---|---|
|
string |
No |
— |
Path under |
|
mapping |
No |
— |
Values supplied to the template’s parameters, keyed by parameter name. Merged over declared defaults. |
name: csv_to_bronze
version: "1.0"
parameters:
- name: table_name
required: true
- name: file_format
default: csv
actions:
- name: "load_{{ table_name }}"
type: load
readMode: stream
source:
type: cloudfiles
path: "${landing_volume}/{{ table_name }}/*.{{ file_format }}"
format: "{{ file_format }}"
target: "v_{{ table_name }}_raw"
pipeline: bronze
flowgroup: customer_ingestion
use_template: csv_to_bronze
template_parameters:
table_name: customer
file_format: csv
Parameters in SQL files¶
A SQL file that a template action references through sql_path uses the
template parameters like the template’s own inline SQL. The sql_path value
can use a parameter to choose the file, and the file can use {{ name }}
expressions, filters, loops and conditions:
# templates/gold_materialized_view.yaml
name: gold_materialized_view
parameters:
- name: table
required: true
- name: group_by
required: true
- name: silver_schema
default: "${silver_schema}"
actions:
- name: "write_{{ table }}"
type: write
write_target:
type: materialized_view
catalog: "${catalog}"
schema: gold
table: "{{ table }}"
sql_path: "sql/gold/{{ table }}.sql"
-- sql/gold/orders_by_period.sql
SELECT
{%- for column in group_by %}
o.{{ column }},
{%- endfor %}
SUM(o.total_price) AS total_revenue
FROM ${catalog}.{{ silver_schema }}.orders o
GROUP BY {{ group_by | join(', ') }}
The file is rendered during template expansion when it contains {{,
{% or {#. Three places read a file: an SQL load’s source.sql_path,
an SQL transform’s sql_path and a materialized view’s
write_target.sql_path. The parameters are the ones the template’s inline
values receive: the flowgroup’s template_parameters with the template’s
defaults applied. The rendered text becomes the action’s inline sql at
that place, and sql_path is kept to show where it came from; the template
preview displays the rendered SQL.
Presets, ${token} and ${secret:...} substitution and the
unresolved-token check (LHP-CFG-010) then run on the rendered text, exactly
as on inline SQL. Environment values therefore keep coming from
substitutions/<env>.yaml, and a parameter value may itself hold a token. An
unresolved token in rendered SQL names the file it was rendered from.
Rendering a file differs from rendering the template’s YAML values in three ways:
It is strict: a name that is not a parameter fails
lhp validateandlhp generatewithLHP-CFG-071, which names the file, the line of a syntax error, the template, the action and the parameters available. A file that renders to empty SQL, for example because all of it sits inside a false{% if %}, fails the same way.The whole file is a template, so literal
{{,{%or{#in the SQL, including in comments, must be wrapped in{% raw %}...{% endraw %}.It runs in Jinja’s immutable sandbox.
These files are read as before, at generation, with ${token} substitution
only:
a file without
{{,{%or{#;a file referenced by an action that does not come from a template;
a file whose
sql_pathcontains a${token}, because expansion reads the file before environment substitution;a
sql_paththat a preset adds after expansion.
A file that does not exist is reported by lhp validate and
lhp generate as before.
Dependency analysis (lhp dag) parses the rendered SQL, so table references
built from parameters still order the pipelines and flowgroups, and editing
only the .sql file still invalidates the cached graph. If a file fails to
render, lhp dag logs a warning and analyses that flowgroup without its
template actions; lhp validate reports LHP-CFG-071. lhp dag
matches a reference to the table’s producer by its text, without substituting
${token} values, so write each table name the same way on both sides. If
the producing write target uses ${silver_schema}, give the parameter that
default (default: "${silver_schema}") rather than the literal schema name;
otherwise the reference is reported as an external source and the pipelines
lose their ordering.