Jinja2 templating

Supported connections: BigQuery, Postgres, Presto, Redshift, Snowflake

All metrics calculations and executed SQL queries will be passed through the Jinja2 engine, so any basic Jinja2 templating, as you might expect, is supported. If you're not familiar with Jinja2, a basic example is shown below.

{% set table = census.public_data %}
select count(*) from {{ table }}

Reusable templates

On top of this, we provide support for reusable templates, which should be saved in the ~/.whale/templates folder and named after the name of the warehouse connection that you would like to use this template for. Connection names can be found by running wh connections, in the name field of each yaml block.

.whale
└── templates
    └── warehouse-connection-name.sql

For example, consider the following BigQuery connection setup:

---
name: bq-1
metadata_source: Bigquery
key_path: ~
project_credentials: ~
project_id: my-bigquery-project

The name of the connection here is bq-1, so you'll need to create a file as follows:

.whale
└── templates
    └── bq-1.sql

And the template within will automatically be pre-pended to queries against this connection.

Template example

The following snippet enables the value {{ last_day }} to be used to performantly get data from the latest partition in BigQuery.

{% set last_day = "_PARTITIONDATE = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)" %}

The following query, then, could be run by whale:

select count(*) from table.schema where {{ last_day }}