Templates
The following templates are available by default for Custom SQL monitors in Monte Carlo. Users can also create their own, custom templates.
To use the templates below, the user must populate the {{variable}} values. This can either be done by adding variable values in the 'advanced' dropdown of the Define SQL section. Or, they can be manually overwritten in the SQL itself.
By far, the most common threshold for these templates is "alert when row count is greater than 0".
Referential integrity
All IDs in a child table must be present in a parent table.
WITH child
AS (SELECT {{column}} AS id
FROM {{table_1}}),
parent
AS (SELECT {{column}} AS id
FROM {{table_2}})
SELECT *
FROM child
LEFT JOIN parent using (id)
WHERE child.id IS NOT NULL
AND parent.id IS NULL
No duplicates
No duplicates in a given column.
SELECT {{column}}
FROM {{table}}
GROUP BY {{column}}
HAVING Count(*) > 1
Label validation
All values in a column are part of a set of valid labels.
SELECT {{column}}
FROM {{table}}
WHERE {{column}} NOT IN ( '{{value_1}}', '{{value_2}}' )
Null values
There are no records with a NULL value in a given column.
SELECT {{column}}
FROM {{table}}
WHERE {{column}} IS NULL
Empty strings
There are no records with an empty string in a given column.
SELECT {{column}}
FROM {{table}}
WHERE TRIM({{column}}) = ''
Minimum and maximum
There are no values that exceed particular thresholds.
SELECT {{column}}
FROM {{table}}
WHERE NOT( {{column}} > {{value_1}}
AND {{column}} <= {{value_2}} )
Null-like strings
No records contain the string "Null" or "None".
SELECT {{column}}
FROM {{table}}
WHERE Lower({{column}}) LIKE 'null'
OR Lower({{column}}) LIKE 'none'
Row count match
Two tables have the same number of rows.
WITH t1
AS (SELECT Count(*)
FROM {{table_1}}),
t2
AS (SELECT Count(*)
FROM {{table_2}}),
t3
AS (SELECT (SELECT *
FROM t1) - (SELECT *
FROM t2) AS diff_count)
SELECT diff_count
FROM t3
WHERE diff_count != 0
Identical tables
Two tables are identical record for record.
(SELECT *
FROM {{table_1}}
{{except_operator}}
SELECT *
FROM {{table_2}})
UNION ALL
(SELECT *
FROM {{table_2}}
{{except_operator}}
SELECT *
FROM {{table_1}})
The "except_operator" varies per warehouse. On BigQuery, you can use EXCEPT DISTINCT
, while EXCEPT
works for Redshift and Snowflake.
More than one value
A column has at least two distinct values.
SELECT Count(DISTINCT {{column}}) c
FROM {{table}}
HAVING c = 1
Record validation
A SQL expression holds true for all individual records in a table.
SELECT *
FROM {{table}}
WHERE NOT({{expression}})
An example of an expression is {{column_1}} + {{column_2}} = {{value}}
, which would check that the sum of two values is constant.
String patterns
All strings in column match a particular pattern, expressed as a regular expression. Can be used to validate common patterns like UUIDs, phone numbers, emails, numbers, escape characters, dates, etc.
SELECT *
FROM {{table}}
WHERE NOT({{regex_function}}({{column}}, {{pattern}}))
Please note:
{{regex_function}}
will be the appropriate regex matching function for your use case and your data warehouse. For example, to fully match the string with a pattern on Snowflake you would use REGEXP_LIKE, whereas to partially match the string with a pattern on BigQuery, you would use REGEXP_CONTAINS.{{pattern}}
can be any regular expression. For example, to check for US zip codes, you might use^[0-9]{5}(?:-[0-9]{4})?$
.
Updated 15 days ago