Templates

If the predefined metrics provided by Bigeye do not satisfy your particular needs, you can also create a metric Template in order to provide specific functionality. This can include matching against a custom format, computing something involving multiple columns or a custom formula, or even checking columns against another table.

What is a template?

A template is a piece of custom SQL that can be run to return a value. They can be used in place of the predefined metrics when creating a new metric. Templates are parameterized so they can be reused across multiple different columns and tables without having potential typographic errors or so that they can all be updated at the same time if something about them needs to change.

Template SQL fragments will be run in the SELECT clause of the generated metric query. They must return either a boolean or numeric value. When used in a metric, they will be further aggregated. Boolean values will be aggregated either in a count or a percent. Numeric values can be aggregated with the min, max, or average value.

See all templates

Templates can be found in the Settings->Templates menu. This is a list that shows all existing templates by name and SQL values.

Create a new template

To create a new template, you click on the new button. You need to provide a warehouse, as you may be writing database-specific SQL in order to make this template work, and it may not make sense in another warehouse. A template takes a name, a return type, and an expression. You can provide placeholders using {{placeholder_name}}. Each placeholder will describe an input that will be used for the template. The types of the input can be either columns, strings, or numbers. The column type will be filled with columns from the table of the metric. Press save, and the template will be saved.

Using a template

Once your template has been created, you can create a new metric using that template. After navigating to a column in the appropriate warehouse, if you click on new metric, you will now see your new template. That will also enable you to input any placeholder values that have been selected. In this case, as the placeholder type was column, a dropdown with the list of columns from this table is populated.

When the metric is executed, Bigeye will drop the template directly in the executed query on the source, surrounded by the level of aggregation selected.

Example templates

Some examples for templates:

1. Custom formats

If you have a custom format you commonly use, you may want to add a template to ensure that the format is being followed. If the warehouse supports it, any regex support can be very useful. Otherwise formats may be expressed using the like syntax. For example, a validator format which starts as a two or three capitalized letter code, a hyphen, then a number of digits could be expressed on Snowflake using RLIKE({{column}}, '[A-Z]{2,3}-\d+')

2. Foreign key verification

As a workaround for Bigeye not currently supporting metrics across multiple tables, templates can be used to validate foreign keys. One template is required per foreign key type. Here is a template that could be used to validate a foreign key that points to my_db.my_table: {{column}} in (select id from my_db.my_table). Note: the final table should be fully qualified to make this query function properly.