Skip to main content
The debug query that Validio generates when you debug an incident contains placeholders rather than values specific to that one incident. A placeholder is written as {{ validio_... }}, and Validio replaces it with the incident’s own values at the moment the query runs. This is what makes a debug query reusable. Because the time range and segment are placeholders rather than fixed values, the same query can be saved once and run against a different incident later, where it investigates that incident’s data instead.

​
Anatomy of a debug query

The generated query is a SELECT against the source table with a WHERE clause assembled from several labeled conditions. Comments in the generated SQL mark each part:
  • Window filter — restricts the query to the time window the incident was detected in. On a partitioned source, this also bounds the partition column.
  • Segment conditions — restricts the query to the segment the incident was detected in.
  • Segmentation filter — applies the filter configured on the segmentation attached to the source, if there is one.
  • Validator filter — applies the filter configured on the validator, if there is one.
  • Reference window and Reference filter — on a validator that compares against a second window or a second source, the same treatment for the reference side.
The query also orders records by how far they deviate from the expected value, so the most prominent outliers appear first, and limits the result to 50 rows. Through the API you can raise the limit to 1,000 — see Fetching the query through the API.

​
Placeholders

The placeholders below appear in the generated query. Which of them you see depends on the validator and how it is configured — a validator with no segmentation, for example, has no segment to bind to. A placeholder used outside the validator types it supports makes the query fail with an error rather than being left in place, so a query that runs is a query whose placeholders all applied. The three filter placeholders resolve to 1 = 1 when no filter is configured — an expansion that looks empty is not a fault.
Placeholders expand differently per warehouseA placeholder expands to whatever SQL dialect the source’s warehouse expects, so the same saved query produces different literal SQL on BigQuery than it does on Snowflake. You do not need to account for this yourself.

​
Window bounds

The incident’s time window is bound by a pair of placeholders, compared against the Data-time field configured on the validator’s window. On PostgreSQL, the generated condition looks like this:
SQL (templated)
Each placeholder resolves to a single timestamp literal, so the same condition against an hourly window becomes:
SQL (expanded)
Because they resolve independently, you can use one without the other — to compare the incident’s window against a different table, for instance, or to select the window bounds as columns alongside the records.

​
Segment conditions

On a validator with a segmentation, the query is narrowed to the segment the incident was detected in:
SQL
Two things to note about this placeholder. Its argument is a single-quoted string — the segmentation field’s jsonpath as configured, not a SQL identifier — and for a nested field that jsonpath is the dotted path, as in {{ validio_incident_segment('Delivery.Method') }}. And it resolves to the segment value, not to a whole condition, which is why it sits on the right-hand side of a comparison rather than standing alone.

​
Composite segmentations

The placeholder always takes exactly one field. A segmentation on several fields produces one placeholder per field, each in its own condition:
SQL
Each condition resolves on its own, so you can remove one to widen the query to every value of that field while keeping the incident’s own value for the rest. A field name that is not part of the incident’s segment makes the query fail with an error naming the fields it does carry.

​
Placeholders and reuse

Whether a saved query keeps its window placeholders decides what it does the next time you open it, and both answers are useful. A query that keeps at least one of validio_incident_window_start or validio_incident_window_end re-binds to whichever incident you open it from, so it investigates that incident’s own data. This is what the generated query does, and what you usually want from a query you intend to reuse. A query with the timestamps written in returns the same range every time, whichever incident you open it on. That is sometimes exactly the point — a query joining a reference table, or comparing the incident against a fixed baseline period, pins its range deliberately. Validio saves either kind, so the choice is yours to make. A validator on a global window has no time window to bind to, so its generated debug query carries no window placeholder. It saves and reuses like any other query.
A placeholder only resolves in its full {{ ... }} form, with at most one space inside the braces. A mention of the name in a comment or a string binds nothing, and a misspelled or misspaced placeholder is left in the query as written — which the warehouse then rejects as a syntax error. Use the editor’s autocomplete to insert them.
If Save is unavailable, hover it to see why — there is nothing to save yet, or you are on the generated query, which is the validator’s own. Select New to start a query from it.

​
Seeing the expanded query

To read the query with its placeholders filled in, expand Executed SQL beneath the sample records in the debug dialog. It shows the literal SQL that produced the rows currently on screen, which is what you would run in your own database tools. See Debugging an Incident for how to load samples.

​
Custom SQL validators keep their own template functions

A Custom SQL validator has a generated debug query too: Validio wraps your metric query in a common table expression and selects the incident’s records out of it. Your query is embedded as written, which means the template functions it uses — validio_window_id, validio_window_filter, validio_reference_window_filter, and validio_source_table — are still placeholders in the debug query, and are documented in Custom SQL Validator Query Syntax. In a debug query they bind to the incident’s window rather than to the validator’s polling range, so the wrapped query returns the records behind the incident. They require a tumbling window, as they do when the validator runs. Segment conditions work slightly differently here: because the wrapper selects from your query’s result set, each condition compares the aliased segment column your query returns rather than the raw source column, while the placeholder’s argument stays the source field’s jsonpath. See Segmentation Fields for how those aliases are derived.
One name belongs to both sets{{ validio_segmentation_filter() }} is both a debug query placeholder and a Custom SQL template function, and it means the same thing in either place — the filter configured on the segmentation attached to the source. Validio resolves both occurrences the same way, so you do not need to treat it differently depending on which query you are editing.

​
Fetching the query through the API

If you build your own tooling on the Validio API, set templated: true on the ValidatorMetricDebugInfoInput of the validatorMetricDebugInfo query to receive the debug query in its placeholder form rather than with the incident’s literal values. The default is false. Templated queries are expanded automatically when executed, so a templated query can be passed straight back to validatorMetricDebugRecords without resolving it first. The same input takes two options that Debug does not offer:
  • rowLimit sets the row limit of the generated query, up to 1,000. The default is 50, the limit Debug uses.
  • fields lists the columns the query selects, as JSON pointers, in place of the ones Validio picks. It applies only to warehouse sources, and only to validators whose debug columns are not dictated by the metric they compute; for other validators it is ignored.
See validatorMetricDebugInfo in the API Reference for the full input.