Add an additional model to the workflow

1. Naming convention #

It is important to understand the naming convetion: [sorting]-[tableName].le.sql

2. Create a model #

As we already have a model named 001-test2.le.sql we now create a file named 002-test3.le.sql. 002 is for sorting in your file structure only. The table name that will be created by this model is table3.

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

Add this to the file

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

3. Model explained #

As the model test2 this one does three steps as well:

3.1 Create a table if not exists #

Based on the the SELECT statement, lesql analyzes which columns have to be created. You could and in many cases you should cast the data type, as shown for the step column.

CREATE TABLE IF NOT EXISTS "zzzexampleproject"."test3" (
  "dimension1" VARCHAR,
  "kpi1" NUMERIC,
  "date2" DATE,
  "step" TEXT
);

3.2 Delete data for a specific timeperiod #

The test3 table does not have a date column, but a date2 column. Therefore we have to tell lesql to use the date2 column to identify which timeperiod to delete. This is done in the CONFIG block of the model file: {{ setTimestampColumn "date2"}}

DELETE FROM "zzzexampleproject"."test3" WHERE "date2" BETWEEN '2019-10-24' AND '2019-11-23';

3.3 Insert data into new table #

blub