Custom Computes for Event Based Subscriptions

Custom Computes

Custom Computes are SQL-based conditions that periodically run against your datasets to monitor KPIs (Key Performance Indicators) and trigger alerts based on the evaluation of these conditions.

They help automate event-based subscriptions, notifying users via email when specific business conditions are met or breached.


Overview

  • Custom Computes run encapsulated SQL queries on your data tables.

  • They snapshot the latest data hourly and execute compute logic on a scheduled basis (e.g., daily).

  • If the compute condition returns true, a notification email is triggered.

Typical use cases include:

  • Monitoring if Total Sales in the current month fall below a threshold.

  • Detecting if Market Share of a product drops unexpectedly.


How It Works

Each Custom Compute consists of:

  • KPI/Metric: The field being monitored (e.g., Total Sales).

  • Condition: The logical comparison (e.g., < 1M).

  • Value: The threshold that triggers an alert (e.g., 1M).

A Custom Compute:

  • Periodically runs a SQL query.

  • Evaluates a condition.

  • Sends alerts when conditions are satisfied.


Pre-requisites

Before creating a Custom Compute:

  • Create a Chart in Infinity. This chart serves as the foundation for running and monitoring the compute.


Creating a Custom Compute

To create a Custom Compute:

  1. Go to Settings → Site Settings → Computes.

  2. Click Add Compute.

You will see a setup screen with the following fields:


Compute Configuration

Field
Description

Name

Unique name for the compute.

Type

Programming language for logic. (Options: BIG_QUERY, JavaScript, Python)

Return Type

Define output: Condition (Boolean, Integer, Double, Percent, String) SubText (String only).

Message: Condition Met

Notification text when condition is true.

Message: Condition Unmet

Notification text when condition is false.

Enable Custom Email

Enable a personalized email format from the Email tab.

Default Chart

The chart associated with this compute.

Period

How often the compute runs (e.g., Daily).


DB Query Tab

Write your SQL query that defines the compute's business logic. The query must always return the following two columns with the exact same names:

  1. Condition [Boolean] - True if the compute logic evalutes to True; False otherwise.

  2. SubText [String] - details that subscribed user would receive in the notification email body.

You can use dynamic placeholders:

Placeholder
Description

${dataset_id}

Dataset ID

${table_name}

Table Name

${condition}

Logical operator

${operator}

Logical operator (alternative)

${value}

Threshold value

These placeholders are automatically replaced at runtime.


Email Tab

Configure the alert email.

Field
Description

Subject

Subject line for the notification email.

Email Message

Body content of the notification email.

You can dynamically include:

  • ${subtexts} to inject a custom sub-message.

  • ${metric} and ${result} in the body.


Example: Data Refresh Monitoring Compute

Let's walk through an example of monitoring whether your dataset is properly refreshed.


DB Query

WITH base1 AS (
    SELECT *, dense_rank() OVER (ORDER BY subscription_upload_date DESC) AS snapshot_rank
    FROM `${dataset_id}.${table_name}`
    WHERE period = 'HOURLY'
),
base2 AS (
    SELECT * EXCEPT(subscription_upload_date, period, snapshot_rank, data_refreshed)
    FROM base1
    WHERE snapshot_rank = 1
    EXCEPT DISTINCT
    SELECT * EXCEPT(subscription_upload_date, period, snapshot_rank, data_refreshed)
    FROM base1
    WHERE snapshot_rank = 2
),
base3 AS (
    SELECT * EXCEPT(subscription_upload_date, period, snapshot_rank, data_refreshed)
    FROM base1
    WHERE snapshot_rank = 2
    EXCEPT DISTINCT
    SELECT * EXCEPT(subscription_upload_date, period, snapshot_rank, data_refreshed)
    FROM base1
    WHERE snapshot_rank = 1
),
base4 AS (
    SELECT * FROM base2
    UNION DISTINCT
    SELECT * FROM base3
)
SELECT (CASE WHEN COALESCE(COUNT(*), 0) > 0 THEN TRUE ELSE FALSE END) AS condition,
concat('<p style="font-family: Georgia, serif; text-shadow: 1px 1px 2px lightgray;">',
'Data is Refreshed at:', FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', CURRENT_DATETIME()), '.</p>') as SubText
FROM base4;

Compute Settings

Field
Value

Name

Data Refresh

Type

BIG_QUERY

Return Type

Boolean

Message: Condition Met

Metric: ${metric} with ${result}

Message: Condition Unmet

Condition not met with result: ${result}

Enable Custom Email

Enabled

Period

Daily


Email Setup

Field
Value

Subject

Data Refresh

Email Message

You wanted us to let *you* know when ${subtexts}.

The email will be sent when a data refresh issue is detected.


Best Practices

  • Always ensure your compute SQL query is optimized for execution efficiency.

  • Use meaningful names for computes.

  • Test the compute manually before setting it to periodic execution.

  • Keep notification messages clear and actionable.

  • Use placeholders to make queries flexible across environments.


Troubleshooting

Issue
Solution

Compute not triggering

Check if the dataset is correctly refreshed and the condition is evaluated properly.

Incorrect values in email

Verify placeholder usage like ${metric}, ${result}, and ${subtexts}.

SQL errors

Test query directly in the database first before pasting it into the compute setup.


Quick Template for New Computes

You can use this template to quickly create a new compute:

WITH base AS (
    SELECT *
    FROM `${dataset_id}.${table_name}`
    WHERE --your conditions here--
)
SELECT (CASE WHEN [your condition] THEN TRUE ELSE FALSE END) AS condition, 
[your details appear in the mail body] AS subtext
FROM base;
  • Replace --your conditions here-- and [your condition] as needed.

  • Replace [your details appear in the mail body] with your content. You can use plain text or format it using HTML markup.

  • Setup corresponding email notifications under the Email Tab.


Summary

Custom Computes empower you to monitor key data-driven events automatically and notify the right teams when something important happens. By integrating computes into your Infinity workflows, you ensure proactive detection of potential business issues.


Last updated