1. Data Reporting
2. SQL Query Templates

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.

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.