The following are guidelines to help you migrate your Custom SQL Validator syntax from the previous version (v1) to the current version (v2). For more information about the previous and current versions of the Custom SQL syntax, see Custom SQL (deprecated) and Custom SQL.

Rewriting the SQL Query

Custom SQL v1 Query:

SELECT
	{{ select_columns }},
	count(*) as `validio_metric`
FROM
	{{ table }}
GROUP BY
	{{ group_by_columns }}

Custom SQL v2 Query:

SELECT
	`country`,
	{{ validio_window_id(`created_at`) }} AS `validio_window_id`,
	COUNT(*) AS `validio_metric`
FROM
	`project`.`dataset`.`table`
WHERE
	{{ validio_window_filter(`created_at`) }}
GROUP BY
	`country`,
	`validio_window_id`

To migrate SQL queries between v1 and v2 syntax:

1. Replace {{ select_columns }}
   1. If the validator is using segmentation, then each segmentation field should be added as a column in the SELECT clause. Ensure the column type is a string, use casting if necessary.
   2. If the validator is using a tumbling window, then add {{ validio_window_id(<CURSOR_COLUMN>) }} AS validio_window_id to the SELECT clause.
2. Replace {{ table }}
   1. Replace with the fully qualified name for the source table. For example project.dataset.table.
3. Add a WHERE clause
   1. Add WHERE {{ validio_window_filter(<CURSOR_COLUMN>) }} to ensure the query is only processing data that has not already been processed.
4. Replace {{ group_by_columns }}
   1. If the validator is using segmentation, then each segmentation field should be added as a column in the GROUP BY clause.
   2. If the validator is using a tumbling window, then add either validio_window_id or, if the warehouse does not support grouping by aliases, then add {{ validio_window_id(<CURSOR_COLUMN>) }} to the GROUP BY clause.

Updating the SQL Query in the UI

You can update the SQL query in existing Custom SQL validators directly in the UI:

1. Navigate to a source with Custom SQL validators.
2. Use the Type filter to select Custom SQL to view all the Custom SQL validators for the source.
3. For each validator, click the menu at the end of the row to select Update.
   If the SQL query contains outdated syntax, you will see a warning message.

4. Edit the SQL expression to use the correct syntax.
5. (Recommended) Use Test SQL to verify that the syntax is valid. You can also view the fully expanded raw query and results.
6. Click Save.