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 Custom Monitoring->Templates menu. This is a list that shows all existing templates by name and SQL values.

Create a new template

To create a template, follow these steps:

1. Log in to your Bigeye account and navigate to the Custom Monitoring page.
2. Go to the Templates tab and click New Template.
3. Select the source you want to run the custom query on.
4. Enter a name for the template.
5. Select the Results type. Valid values are:
   1. Boolean
   2. Number
6. Add parameters using {{parameter_name}} and create the query.
7. Select the type for each parameter. Each parameter defines an input that is used for the template. The input types can be columns, strings, or numbers.
8. Click Create Template.

Using a template

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

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

Example templates

Some examples of 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 that 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.