Decube
Search…
⌃K

Custom SQL Monitors

Write your own SQL scripts to set up tests specific to your needs.
Custom SQL Monitor provides and easy and flexible interface for monitoring the database based on custom SQL filter. This feature takes in the select query provided by the user and wraps it in a subquery which does a row_count on the result and record incident if row_count > 0.

Adding the monitor

Navigate to the Data Quality section and press Add new monitor. A window will prompt which lets you select the option for monitoring. Toggle the Custom Query option. And additional configuration fields will show up
Add Custom Monitor

Configuring the monitor

Input the custom sql in the ADD YOUR CUSTOM SQL QUERY text box and press Validate query, this will issue a query validation on the database. Note that only a single read query will work in the validation. DDL query or separated query will fail to validate immediately. The timeout of the validation currently is 55 seconds so be sure to LIMIT your query during the validation if it's big table.
Configure Custom Monitor
Press the Add new monitor button and you are all good to go!

Tips and Tricks

In this section we will show a few ways you can configure the monitor using the power of SQL!

Label invalidation

Filtering records with specified label to trigger an incident
SELECT *
FROM public.sale
WHERE created_at BETWEEN
NOW() - '30mins'::INTERVAL AND NOW()
AND status in ('pending', 'rejected')

Value Sum

Filtering sum of value over a threshold of a specified interval
SELECT 1
FROM public.sale
WHERE created_at BETWEEN
NOW() - '30mins'::INTERVAL AND NOW()
HAVING SUM(price * quantity) > 10000

Discrepancy

The discrepancy between 2 tables
WITH sale AS (
SELECT SUM(price * quantity) as amount
FROM public.sale
WHERE created_at BETWEEN
NOW() - '30mins'::INTERVAL AND NOW()
)
, in_flow AS (
SELECT SUM(amount) as amount
FROM public.in_flow
WHERE created_at BETWEEN
NOW() - '30mins'::INTERVAL AND NOW()
)
, base AS (
SELECT
(SELECT amount FROM sale)
- (SELECT amount FROM in_flow)
AS discrepancy
)
SELECT discrepancy
FROM base
WHERE discrepancy > 10000