> ## Documentation Index
> Fetch the complete documentation index at: https://docs.validio.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom SQL Validator Recipes

> BigQuery-based Custom SQL validator recipes for common use cases like duplicate counts, cross-table joins, and date checks.

The following Custom SQL validator examples are based on **BigQuery SQL**. You can use these recipes as a starting point when writing Custom SQL validators for other data sources, such as Redshift or Athena.

For information about configuring Custom SQL validators, see [Custom SQL](https://docs.validio.io/docs/validators/validator-types/custom-sql.md).

## Duplicate count

This example validator counts the number of occurrences for a specific column with a window and sums the total count. The resulting value represents the number of duplicates for that particular value.

```sql title="SQL (BigQuery)" theme={null}
SELECT
    `validio_window_id`,
    SUM(duplicates) as `validio_metric`
FROM (
    SELECT
        {{ validio_window_id(`time`) }} AS `validio_window_id`,
        COUNT(*) - 1 AS duplicates
    FROM 
        `project`.`dataset`.`table`
    WHERE
        {{ validio_window_filter(`time`) }}
    GROUP BY
        `validio_window_id`, `column_to_check_for_duplicates`
)
GROUP BY
    `validio_window_id`
```

For the following example data, the result would be 3, where the duplicate values are b, b, and NULL.

| column\_to\_check\_for\_duplicates |
| :- |
| a |
| b |
| b |
| b |
| NULL |
| NULL |

## Referential integrity

**Cross-asset dependency check.** This example validator computes how many non-NULL values exist in a foreign key table which is not present in the primary key table. See also [Tumbling Reference Count](#tumbling-reference-count) for another cross-asset example, and [Assessing Cross-Asset Dependencies](https://docs.validio.io/docs/sources/custom-sql-sources.md#assessing-cross-asset-dependencies) for the broader pattern.

```sql title="SQL (BigQuery)" theme={null}
SELECT
    {{ validio_window_id(`foreign_key_table`.`time`) }} AS `validio_window_id`,
    COUNTIF(`primary_key_table`.`foreign_key` IS NULL) AS `validio_metric`
FROM
    `project`.`dataset`.`foreign_key_table`
LEFT JOIN
    `project`.`dataset`.`primary_key_table`
ON
    `foreign_key_table`.`foreign_key` = `primary_key_table`.`foreign_key`
WHERE
    {{ validio_window_filter(`foreign_key_table`.`time`) }} AND
    `foreign_key_table`.`foreign_key` IS NOT NULL
GROUP BY
    `validio_window_id`
```

For the following example tables, the result would be 2, where the missing keys are 1003 and 1004.

| foreign\_key\_table.foreign\_key |
| :- |
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| NULL |

| primary\_key\_table.primary\_key |
| :- |
| 1001 |
| 1002 |

## Global row count

This example validator uses a global window to count the total number of records in the table. No template functions are needed since the full table will be scanned during each poll. The only requirement is that the query must return the `validio_metric` column.

```sql title="SQL (BigQuery)" theme={null}
SELECT
	COUNT(*) AS `validio_metric`
FROM
	`project`.`dataset`.`table`
```

## Segmented tumbling row count

This example validator is configured with a tumbling window on the field `created_at` and a segmentation on the field `country`. The SQL query uses the window template functions to calculate the number of records per country and window. The query must return the `validio_metric`, `validio_window`, and `country` columns.

```sql title="SQL (BigQuery)" theme={null}
SELECT
    {{ validio_window_id(`created_at`) }} AS `validio_window_id`,
    `country`
    COUNT(*) AS `validio_metric`
FROM
    `project`.`dataset`.`table`
WHERE
    {{ validio_window_filter(`created_at`) }}
GROUP BY
    `validio_window_id`,
    `country`
```

## Segmented tumbling mean ratio

This example validator is configured with a tumbling window on the field `created_at` and a segmentation on the field `city`. The SQL query uses the window template functions to calculate the mean ratio by comparing the mean of `amount` in the current window against the two previous windows. The query must return the `validio_metric`, `validio_window`, and `city` columns.

```sql title="SQL (BigQuery)" theme={null}
SELECT
    	`target`.`validio_window_id`,
    	`target`.`city`,
    	`target`.`target_mean` / NULLIF(
        	(
            	-- Calculate the mean for the previous two windows 
            	SUM(`reference`.`reference_amount`) /
            	NULLIF(COUNT(CAST(`reference`.`reference_amount` AS FLOAT64)), 0)
        	),
        	0
    	) AS `validio_metric`
FROM (
    	-- Calculate the mean for each window + segment
    	SELECT
            	{{ validio_window_id(`created_at`)}} AS `validio_window_id`,
            	`city`,
            	AVG(amount) AS `target_mean`
    	FROM
            	`project`.`dataset`.`table`
    	WHERE
            	{{ validio_window_filter(`created_at`) }}
    	GROUP BY
            	`validio_window_id`,
            	`city`
) `target`
JOIN (
    	SELECT
            	{{ validio_window_id(`created_at`)}} AS `validio_window_id`,
            	`city`,
            	`amount` AS `reference_amount`
    	FROM
            	`project`.`dataset`.`table`
    	WHERE
            	-- Use the reference version of the filter function
            	-- to include records from the two previous windows.
            	{{ validio_reference_window_filter(`created_at`, 2) }}
) `reference` ON (
    	-- Join the current window + segment with records from the two previous windows
    	`reference`.`validio_window_id` >= `target`.`validio_window_id` - 2 AND
    	`reference`.`validio_window_id` <= `target`.`validio_window_id` - 1 AND
    	`reference`.`city` = `target`.`city`
)
GROUP BY
    	`target`.`validio_window_id`,
    	`target`.`city`,
    	`target`.`target_mean`
```

## Tumbling reference count

**Cross-asset dependency check.** This example validator compares the number of records in two tables using hourly tumbling windows. See also [Referential Integrity](#referential-integrity) for another cross-asset example, and [Assessing Cross-Asset Dependencies](https://docs.validio.io/docs/sources/custom-sql-sources.md#assessing-cross-asset-dependencies) for the broader pattern.

```sql title="SQL (BigQuery)" theme={null}
WITH ref AS (
	-- count web records in aggregation table per window
	SELECT
    		count(*) as `count`,
    		{{ validio_window_id(`created_at`) }} as `validio_window_id`
	FROM
    		`project`.`dataset`.`traffic_agg`
	WHERE
    		{{ validio_window_filter(`created_at`) }} AND
    		`platform` = 'web'
	GROUP BY
    		`validio_window_id`
), target AS (
	-- count records in web table per window
	SELECT
    		count(*) as `count`,
    		{{ validio_window_id(`created_at`) }} as `validio_window_id`
	FROM
    		`project`.`dataset`.`traffic_web`
	WHERE
    		{{ validio_window_filter(`created_at`) }}
	GROUP BY
    		`validio_window_id`
)
SELECT
	`target`.`validio_window_id`,
	(COALESCE(`ref`.`count`, 0) / `target`.`count`) * 100 AS `validio_metric`
FROM
	`target`
LEFT JOIN
	`ref` ON (
    		`ref`.`validio_window_id` = `target`.`validio_window_id`
	)
```

For the following example tables, the result is: window 10:00 = 100, window 11:00 = 50.

| traffic\_web.created\_at |
| :- |
| 10:00 |
| 10:01 |
| 11:00 |
| 11:01 |

| traffic\_agg.created\_at | traffic\_agg.platform |
| :- | :- |
| 10:00 | web |
| 10:01 | web |
| 11:01 | web |

## Duration between records

This example validator is configured with an hourly tumbling window on `created_at` and calculates the maximum duration in seconds between the records timestamp in each window.

```sql title="SQL (BigQuery)" theme={null}
SELECT
	`validio_window_id`,
	MAX(`duration`) AS `validio_metric`,
FROM (
	SELECT
    		{{ validio_window_id(`created_at`) }} AS `validio_window_id`,
    		COALESCE(
        		TIMESTAMP_DIFF(
            			`created_at`,
            			LAG(`created_at`) OVER(PARTITION BY {{ validio_window_id(`created_at`) }} ORDER BY `created_at`),
            			SECOND
        		),
    		0) AS `duration`
	FROM
    		`project`.`dataset`.`table`
	WHERE
    		{{ validio_window_filter(`created_at`) }}
)
GROUP BY
	`validio_window_id`
```

For the following example data, the result is: window 10:00 = 9s, window 11:00 = 5s.

| traffic\_web.created\_at |
| :- |
| 10:00:00 |
| 10:00:01 |
| 11:00:10 |
| 11:00:10 |
| 11:00:15 |

## Exponentially weighted moving average

This validator computes a 14-day exponentially weighted moving average (EWMA) over the specified metric field (revenue in this example). The validator is configured to use a tumbling window on the Date timestamp.

```sql title="SQL (BigQuery)" theme={null}
WITH base_metrics AS (
     SELECT
          AVG(revenue) AS `validio_metric`,
         {{ validio_window_id(`Date`) }} AS `validio_window_id`
     FROM
         `project`.`dataset`.`table`
     WHERE
         {{ validio_reference_window_filter(`Date`, 13) }}
     GROUP BY
         {{ validio_window_id(`Date`) }}
 ),
weighted_metrics AS (
     SELECT
         `validio_metric`,
         `validio_window_id`,
         ROW_NUMBER() OVER(ORDER BY `validio_window_id` DESC) AS rn
     FROM base_metrics
 )
 SELECT
     `validio_window_id`,
     SUM(`validio_metric` * POWER(0.5, rn - 1))
         OVER(ORDER BY `validio_window_id` RANGE BETWEEN 13 PRECEDING AND CURRENT ROW)
         / SUM(POWER(0.5, rn - 1))
         OVER(ORDER BY `validio_window_id` RANGE BETWEEN 13 PRECEDING AND CURRENT ROW)
     AS `validio_metric`
FROM weighted_metrics
```

You can customize this example by changing the metric field, window length, and timestamp field as needed:

* **Metric Field** - Replace revenue with the desired metric field.
* **Window Length** - Adjust the number of windows over which the moving average is calculated (the example shown uses 14 days). For an X-window moving average, set the reference\_window\_filter function to include X-1 past windows to capture the current window as part of the calculation.
* **Timestamp Field** - Replace Date with the relevant timestamp field for the metric.

For a simple, non-weighted moving average, you can configure our out-of-the-box validators to use a Sliding Window.

## Trend alert

This validator detects significant drifts in a metric over time. It compares the current value of the metric with its value seen previously Y period ago (for example, 30 days when Y=30 for daily windows) and triggers an alert if the change exceeds X%.

The recipe uses the `LAG` function to compare the current metric value with its value from the previous Y past window. It calculates the percentage change and returns 1 if the change is above a specified threshold of X%.

```sql title="SQL (BigQuery)" theme={null}
WITH current_and_past AS (  
SELECT  
    Date,  
    Sales as current_metric,  
    LAG(Sales, Y) OVER (ORDER BY {{ validio_window_id(`Date`)}}) AS past_metric  
    FROM  
        `project`.`dataset`.`table`  
WHERE  
    {{ validio_reference_window_filter(`Date`, Y) }}  
)  
SELECT  
    {{ validio_window_id(`Date`) }} AS `validio_window_id`,  
    CASE WHEN ABS(100 \* (SAFE_DIVIDE(current_and_past.current_metric, current_and_past.past_metric) -1)) > X THEN 1 ELSE 0  END AS `validio_metric`  
FROM  
    current_and_past
```

You can customize this example by changing the metric field, the timestamp field, the Y parameter as needed:

* **Metric Field** - Replace the metric field (for example, Sales, Profit) to track trends.
* **Timestamp Field** - Replace Date with the relevant timestamp field for the metric.
* **Y** - Adjust the Y parameter to define the number of past windows for comparison.

Combine this with a Fixed Threshold (>= 1) to alert on metric changes when the value changes by more than X%. For more information, see [Configuring Fixed Thresholds](https://docs.validio.io/docs/thresholds/configuring-fixed-thresholds.md).

## Numeric anomaly

This validator identifies anomalies in numeric data by counting the number of datapoints, within a given window, that deviate from expected values.

The validator uses a reference source to define the acceptable range of values as (`ref_mean ± 1/precision * ref_std`). Higher precision means that the accepted range of values is narrower, which identifies more anomalies. Conversely, lower precision values imply a wider range of accepted values, which identifies fewer anomalies.

The recipe uses the `REF_WIN_LEN` parameter to define number of windows in the reference source and the `OFFSET_WIN_LEN` parameter to define number of offset windows from current window. For more information. Refer to [Reference Source Validation](https://docs.validio.io/docs/validators/validators-configuration/reference-source-config.md).

```sql title="SQL (BigQuery)" theme={null}
WITH
  reference_source AS (
  SELECT
    `validio_window_id`,
    ref_mean - 1/precision * ref_std AS lower_bound,
    ref_mean + 1/precision * ref_std AS upper_bound
  FROM (
    SELECT
      `validio_window_id`,
      AVG(metric_mean) OVER(ORDER BY `validio_window_id` ROWS BETWEEN REF_WIN_LEN+OFFSET_WIN_LEN PRECEDING AND OFFSET_WIN_LEN PRECEDING) ref_mean,
      AVG(metric_std) OVER(ORDER BY `validio_window_id` ROWS BETWEEN REF_WIN_LEN+OFFSET_WIN_LEN PRECEDING AND OFFSET_WIN_LEN PRECEDING) ref_std
    FROM (
      SELECT
        {{ validio_window_id(`timestamp`) }} AS `validio_window_id`,
        AVG(metric) AS metric_mean,
        STDDEV(metric) AS metric_std,
        COUNT(metric) metric_count
      FROM
        `project`.`dataset`.`table`
      WHERE
        {{ validio_reference_window_filter(`timestamp`,REF_WIN_LEN+OFFSET_WIN_LEN) }}
      GROUP BY
        `validio_window_id` 
      )
    WHERE
      metric_count >= 100 -- Minimum number of reference datapoints
    )
  )

SELECT
  {{ validio_window_id(a.`timestamp`) }} AS `validio_window_id`,
  SUM(CASE
        WHEN a.metric NOT BETWEEN ref.lower_bound AND ref.upper_bound THEN 1
        ELSE 0 END
    ) AS `validio_metric`,
FROM
  `project`.`dataset`.`table` AS a
LEFT JOIN
  reference_source AS ref
ON
  ({{ validio_window_id(a.`timestamp`) }} = ref.`validio_window_id`)
WHERE
  {{ validio_window_filter(a.`timestamp`) }}
GROUP BY
  `validio_window_id`
```

You can customize this example by changing the metric field, the timestamp field, and other parameters as needed:

* **Metric Field** - Replace the `metric` field with your relevant field.
* **Timestamp Field** - Replace `timestamp` with the relevant timestamp field.
* Adjust the `REF_WIN_LEN` and `OFFSET_WIN_LEN` parameters to define the number of past windows included in the reference calculations.
* Adjust the `precision` parameter to control the width of acceptable range of values.
* Combine this Custom SQL validator with a Dynamic Threshold to alert on deviation of datapoints from the reference source.