Refs

Refs #

One of the most important keyword in a model is ref. This helps lesql to identify on which tables this model is based to build the DAG for the whole workflow.

Lets look at the two models from Getting started and Add an additional model to the workflow:

workflows/zzzexampleproject/models/001-test2.le.sql

{{ block "CONFIG" .}}
{{end}}

{{ block "SELECT" .}}
SELECT * FROM {{ .Project }}.{{ ref "test1" }}
WHERE "date" BETWEEN '{{ .StartDate }}' and '{{ .EndDate }}'
{{end}}

workflows/zzzexampleproject/models/002-test3.le.sql

{{ block "CONFIG" .}}
  {{ setTimestampColumn "date2"}}
{{end}}

{{ block "SELECT" .}}
SELECT
  "dimension1",
  "kpi1",
  "date" AS "date2",
  'step2'::TEXT AS "step"
FROM
  {{ .Project }}.{{ ref "test2" }}
WHERE
  "date" BETWEEN '{{ .StartDate }}' and '{{ .EndDate }}'
{{end}}

The DAG of the workflow looks like this:

test1test2test3

Table test1 must exist and be filled with data by a 3d-party. If you look into the file 001-test2.le.sql which creates the table test2, you see, that we reference the table test1: {{ ref "test1" }} (shown as the left arrow in the diagram above).

File 002-test3.le.sql which creates table test3 has table test2 as reference: {{ ref "test2" }} (shown as the arrow on the right).

When running the workflow, lesql builds the DAG and checks where to start the ETL process. As 001-test2.le.sql references a table not managed by lesql (test1), it first runs this model. As soon as this model is finished successfully, model 002-test3.le.sql will run.

Multiple refs in one model #

test1test2test3test5test4

Advanced ref naming #

You can use variables in refs as well. Have a look at the Use variables and arrays documentation.