You can define and use simple variables. First parameter is the variable name, the second the value.
{{block "CONFIG" . }}
{{setvar "key1" "value1" }}
{{setvar "key2" "value2" }}
{{end}}
In the SELECT block, you can use the variables
{{block "SELECT" . }}
SELECT
CONCAT(foo, " ", {{.Vars.key1 }}) as foo_advanced1,
CONCAT(foo, " ", {{.Vars.key2 }}) as foo_advanced2
FROM {{ ref "table1" }}
{{end}}
First parameter is the array name, all following the values.
{{ block "CONFIG" .}}
{{ setarray "columns" "dimension1" "dimension2" "kpi1" "kpi2"}}
{{end}}
{{block "SELECT" . }}
SELECT
{{range .Arrays.columns}}{{.}}, {{end}}
NOW() as update_ts
FROM
{{ .Project }}.{{ ref "test2" }}
WHERE
"date" BETWEEN '{{ .StartDate }}' and '{{ .EndDate }}'
{{end}}
This is a little bit useless example, as it only adds overhead. But as soon as you use the column names at two or more places within a model, this totally makes sense, as you have to define the columns only once. The full power of variables can be achieved in combination with global models. You can reuse global models for different projects even if they differ a bit. In this case you would define a varibale in model which then can be used in the global model.
With named 2 dimensional arrays you can build more complex variable structures. First parameter is the array name, all following are pairs of keys and values while keys names can be used multiple times
{{ block "CONFIG" .}}
{{setMultiDimArray2 "multiDimArray2Name" "keyName" "A" "keyName" "B" "keyName" "C" "keyName2" "L" "keyName2" "M" "keyName2" "N"}}
{{end}}
{{block "SELECT" . }}
{{ range $index, $element := .MultiDimArrays2.multiDimArray2Name}}
{{ range $index2, $element2 := $element}}
"{{$index}}_index{{ $index2 }}_value{{$element2}}",
{{- end}}
{{- end}}
{{end}}
This example creates following statement. Even if it is not useful in the sense of SQL it shows what you can do with it.
"keyName_index0_valueA",
"keyName_index1_valueB",
"keyName_index2_valueC",
"keyName2_index0_valueL",
"keyName2_index1_valueM",
"keyName2_index2_valueN",
Variables like {{ .Project }} or variable set with setvar can be used in refs as well. Therefore we have use the printf function:
{{block "CONFIG" . }}
{{setvar "key1" "value1" }}
{{end}}
{{block "SELECT" . }}
SELECT
foo,
bar
FROM {{ ref ( printf "%s_table1_%s" .Project .Vars.key1 ) }}
{{end}}
This will reference the table mycustomer_table1_value1, if you are in the project called mycustomer.
You can define default/fallback values, which are used if the variable is not defined in the CONFIG block.
{{block "CONFIG" . }}
{{end}}
{{block "SELECT" . }}
SELECT
CONCAT(foo, " ", {{.Vars.key1 | default "bla" }}) as foo_advanced1,
CONCAT(foo, " ", {{.Vars.key2 | default "blub" }}) as foo_advanced2
FROM {{ ref "table1" }}
{{end}}