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.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
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!In this section we will show a few ways you can configure the monitor using the power of SQL!
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')
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
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
Last modified 1mo ago