Comment on page

Set Up 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.
Set Up Monitoring
Begin by selecting "Custom SQL" card from our config module landing page.
Upon selecting the "Custom SQL" card, it's compulsory to first choose the data source. After selecting your data source, a list of active/inactive Custom SQL monitors that have been previously set up will be loaded.
To set up custom SQL Monitoring for the first time, click the "Add New Monitor" button located to the left of the "Sort By Button". 
Once clicked, a setup pop-up for Custom SQL monitors will emerge.
In the Set Up Monitoring pop-up, users are required to fill in the following required fields:
Custom SQL Test name.
Add Your Custom SQL Query 
Validated Custom SQL Query
Incident Levels
After filling up the required fields, press the Save Preferences button and you are all good to go!
Add Your Custom SQL Query
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.
Be aware that after validating your SQL Query and it becomes eligible for saving preferences, any subsequent edits to that validated query will automatically disable the ability to save preferences unless it is being validated again.
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

Data Quality - Previous

Set Up Field Health Monitors

Next - Data Quality

Set Up Schema Drift Monitors

Last modified 2mo ago