SQL Query Templates

Query Templates allow you to define reusable snippets of SQL components to be used across different reports. This is useful when you have a snippet of code that gets repeated on multiple reports.

A query template is defined by 3 components:

name: name of template to be used in SQL reports
variables: dynamic parameters inside the template
body: content of the template

Example - Abstracting repeated components of your SQL#

Say you have a report that lists all jobs, together with their statuses (stored as int in the database).

SELECT
  J.id as "ID",
  J.title as "Title"
  CASE J.status
    WHEN 0 THEN 'Running'
    WHEN 1 THEN 'Success'
    WHEN 2 THEN 'Failure'
    WHEN 3 THEN 'Created'
    ELSE 'Other' END as "Status"
FROM jobs J

You can see that the CASE WHEN J.status is something that will appear again in other reports with Status column. So we can define a {{ @job_status(status) }} query template as follows:

Then rewrite your report's SQL as:

SELECT
  J.id as "ID",
  J.title as "Title"
  {{ @job_status('J.status') }} AS "Status"
FROM jobs J

Now, whenever your report runs, the query template will automatically be replace with the actual template's content. This is just like programming language's functions inside SQL itself.

Adding variables to Query Templates#

Using variables inside query templates allows you to accept additional parameters, when your query template runs inside a report.

The basic query template with variable structure is template(variable1, variable2). In this example, the query template age_groups accepts two variables that were added, field and range. So this query template with variable would operate as age_groups(field, range) when called, and pass the respective input values into the query template.

Here you can see it in action in a report. In this report, the field variable has accepted a line of SQL code EXTRACT(year from age...), while range accepted a shared filter input called age_groups that was added to the report.

Notes#

The |noquote in the above example is to remove the default single-quoted applying to the variable, since in this case we're referring to the actual column.
If you're using filter variable in your report, you can pass that variable to query template: {{{template_name(filter_var_name)}}} or {{@template_name(filter_var_name)}}.
Other query syntaxes ({{#if}}, etc) works fine inside your query templates.

The Benefits#

Your SQL code now looks cleaner and more maintainable
When your logic changes, you just need to modify the query template once.