Custom Metrics

Some metrics are not stored in tables, or are computed in the semantic layer of a business intelligence (BI) tool, but you may still want to monitor them. For these cases, use Sifflet's Custom Metrics template to define your own metric with a SQL query and apply Sifflet machine learning (ML) models to it.

The SQL query must return two columns:

• A time field
• A numerical field that you want to monitor

You can return a third column on which Sifflet creates a multi-dimensional monitor. This column must be one of the dataset fields and cannot be renamed with an alias.

Example

Suppose you want to monitor your global revenue per day.

In the table called orders, there are the following fields:

• ordered_at : timestamp of the order
• order_total : amount of the order without tax
• tax_paid : tax paid for the order

The revenue per order is the sum of order_total and tax_paid. To monitor the global revenue per day, the SQL query aggregates that sum by day.

Here is the custom SQL query on Snowflake:

SELECT ordered_at::date, sum(order_total+tax_paid) FROM orders group by 1;

This detects anomalies in the revenue, such as the drop on February 22 in this example.