1. Home
  2. Docs
  3. Models
  4. Use variables and arrays

Use variables and arrays

Use vars #

{{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}}

Use arrays #

{{ block "CONFIG" .}}
  {{ setarray "columns" "dimension1" "dimension2" "kpi1" "kpi2"}}
{{end}}

SELECT
  {{range .Arrays.columns}}{{.}}, {{end}}
  NOW() as update_ts
FROM
  {{ .Project }}.{{ ref "test2" }}
WHERE
  "date" BETWEEN '{{ .StartDate }}' and '{{ .EndDate }}'

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.

Variables in refs #

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.

Default/fallback values for variables #

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