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:
Go to Settings → Site Settings → Computes.
Click Add Compute.
You will see a setup screen with the following fields:
Compute Configuration
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:
Condition [Boolean] - True if the compute logic evalutes to True; False otherwise.
SubText [String] - details that subscribed user would receive in the notification email body.
You can use dynamic placeholders:
${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.
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
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
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
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