Add verified_queries as a core element of the spec

[rlittle08]

I've opened a PR for this - #81

This is inspired by "Verified Queries" in Snowflake Cortex Analyst, and early work we've done that's proven the effectiveness of including verified queries in context for AI query builders. I view this as common enough to warrant its own home in the spec. But I'm open to discussion. This discussion could relate to the ai_context point shared in #32

[nzsaurabh]

suggest to think it through because the bank of verified queries will increasingly need to be populated post evals piped from an evals framework such as langfuse, arise, etc.

Also they only those relevant to the user's prompt need to be included in the prompt through emerging patterns such as RAG, other types of filtering, etc.

There is definitely a use case to publish some verified queries at the time of publishing the docs...
but also of having a spec for storing verified question and sql answer pairs from an evals framework - separately from the yaml containing the dataset

[mergisi]

Strong +1 on making verified_queries a first-class spec element rather than burying it inside ai_context.

Building on @nzsaurabh's point about eval pipelines: the spec should probably distinguish between authored verified queries (human-curated examples shipped with the dataset definition) and derived verified queries (accumulated from eval frameworks like Langfuse/Arize post-deployment). Both serve the same purpose — grounding AI query generation — but have different lifecycle and trust characteristics.

For the authored set, a few fields beyond the question/SQL pair would be valuable:

• dialect — verified queries that work on Postgres may not parse on BigQuery. The spec already has dialect awareness at the dataset level, so inheriting or overriding it per query makes sense.
• complexity_tier — distinguishing single-table lookups from multi-join aggregations helps retrieval. RAG over verified queries works better when you can filter by structural similarity to the user's intent before doing semantic matching.
• schema_version — verified queries go stale when tables change. Tying them to a schema revision makes staleness detectable rather than silent.

For natural language to SQL tools (ai2sql.io is one we work on), verified queries function as few-shot examples that dramatically improve generation accuracy. Having them standardized in OSI means any tool consuming the spec gets them for free, rather than each tool maintaining its own proprietary example bank.

One design question: should the spec support parameterized verified queries (with placeholder values for filters/dates), or only concrete examples? Parameterized versions are more reusable but harder to validate automatically.

[rtruxler]

Plus 1 to verified queries being part of the spec.

This is not a suggestion, but something to think about. In my platform, we've started to break up verified queries into constituent parts. Many natural langauge questions have a "metric (one to N)" and "in a timeframe". Both parts of the natural language here can be non-trivial depending on the dataset, so semantically, mapping the part of the question to the timeframe portion (or for that matter, any kind of filtering clause), and the metric part of the question to the aggregation calculation separately is valuable. The reason it's valuable to separate them is that if you do any kind of semantic search on them, one portion of the query may dominate the semantic similarity in an ANN algorithm. And to Mustafa's point above, join clauses are their own separate concern that is apart from aggregations or filters.

So maybe all this just to say that I think there can be value in either capturing distinct query snippets (querylet? ew) tied to their natural language counterpart, or tagging the entire query with which part of it is the verified part.

[nzsaurabh]

Agree with you and @mergisi

Interesting thought, I usually follow the following principles:
For context to AI

1. Less is More
2. Human Verified is better than non verified
3. Semantics is for semantics - fine to match a user's question to other similar questions but may not work will on the data_query, column_name itself. It would be rare to find all columns required would match the keywords in a user's question.
4. Some type of tagging for deterministic retrieval e.g. dataset, column, type of join, aggregation

The more the human effort required the less scalable it is including human errors.

So i would tag a few good complex queries rather than necessarily break them into parts. Maybe even write a description (like code comments) on why the logic in the verified query was chosen (rather than explain it). It needs to highlight something about the business domain which wouldn't be available on the www or the LLMs training data.

This implies from OSI spec pov if we have the following elements, it might suffice multiple users and use cases:

- verified_queries:
    - user_question: "blah"
    - data_query: "blah"
    - query_dialect: "blah" or "inherit"
    - complexity:
        - high, med, low
    - verified_status:
        - new_untested, online_evals_worked, online_evals_failed, user_liked, user_thumbs_dow, human_verfied, expert_verified
        - verified_status_timestamp:
        - valid_upto_timsetamp (optional):
        - schema_version (optional)
    - datasets (optional):
        - fact_customer
        - fact_product
        - fact_transactions
    - tags (optional):
        - join_customer_id
        - join_sku_id
        - financial_year_aggregation

verified_status could be considered the lifecycle of the query.

given that it would be easy to parse through the data_query to create tags, it would still be efficient to create them in one go rather than parse each time we retrieve each time (RAGs allow filtering using tags before semantic search).

This will also allow the queries to sit separately from the dataset and filter->retrieve easily when a user asks a question.

[rtruxler]

I like this. FWIW I’ve found that complex example queries do not work as well as multiple minimal queries that exhibit different (but relevant) behaviors. But that’s neither here nor there for the spec I think.

[Yokoi-Tofu]

Building on @rtruxler decomposition idea and @nzsaurabh lifecycle concern — our team has been having the same debate internally, and two proposals have emerged:

Option A: Decompose verified_queries into OSI YAML elements
Rather than storing whole SQL, break them into constituent parts (metrics, dimensions, filter patterns, relationships) as first-class elements in the OSI YAML.
The "verified" signal becomes metadata on those elements.
Natural language anchors become synonyms or ai_context fields on the relevant metric/dimension.

Option B: A Context Layer YAML that wraps/extends OSI YAML
Keep the core OSI YAML for semantic definitions, and introduce a separate Context Layer YAML that includes OSI YAML alongside things like verified_queries that don't fit neatly into semantic definitions.

My personal lean is Option A: if we decompose rigorously enough, almost everything that seems to belong in a "context layer" can be expressed as attributes on existing semantic elements — glossary becomes synonyms, caveats become notes, join patterns become relationships.
A separate YAML feels like synchronization overhead without much added expressiveness.

The one thing that gives me pause is lifecycle.
Semantic definitions are relatively stable (they change when the data model changes).
Eval-derived verified_queries accumulate continuously and have a very different update rhythm.
Is mixing these in the same YAML a concern worth addressing in the spec itself — or is that safely left to implementations?

To make Option B more concrete, here's what a Context Layer YAML might look like (fields are illustrative — exact
schema TBD):

# Option B: Context Layer YAML (wraps OSI YAML)
context_layer:

  # OSI YAML embedded as-is (semantic definitions — stable, model-driven)
  semantic_model:
    - name: ad_performance_analysis
      metrics:
        - name: conversion_rate
          expression:
            dialects:
              - dialect: ANSI_SQL
                expression: "COUNT(DISTINCT c.user_id) * 100.0 / NULLIF(COUNT(DISTINCT e.user_id), 0)"
          ai_context:
            synonyms: ["CVR", "conversion rate"]
      relationships:
        - name: events_to_conversions
          from: ad_events
          to: conversions
          join_columns: [user_id]

  # Context Layer additions (dynamic, eval-driven — different lifecycle)
  verified_queries:
    - nl_question: "What is the CVR by region last quarter?"
      sql: "SELECT region, COUNT(DISTINCT c.user_id) * 100.0 / ... GROUP BY region"
      source: eval_framework
      validated_at: "2026-04-01"
      complexity: medium

  caveats:
    - "Data only available from 2024 onwards"

  anti_patterns:
    - "Never join ad_events with user_master without filtering env='production'"

In Option A, verified_queries would dissolve into ai_context fields on the relevant metrics/dimensions, and caveats/anti_patterns would become notes or constraints attributes — eliminating the outer context_layer wrapper entirely.
The lifecycle mixing concern is what makes Option B appealing despite the extra structure.

One more question this raises: are there things that genuinely belong in a Context Layer but can't be expressed as attributes in OSI YAML — cases where the YAML structure itself is the limiting factor, not just a matter of adding new fields?

[harelix]

verified queries are grounding artifacts, not semantic constraints - and critical operational semantics shouldn't depend on the LLM reconstructing them at query time.

Coming at this from the agentic-consumer side. I've been working through how agentic consumers interact with semantic layers, and I want to push back on the assumption that verified queries are enough, because I think they solve a narrower problem than the thread is implicitly asking them to solve.

Verified queries solve a real problem as they ground the LLM on common pathways / questions, capture validated SQL queries for known cases, and fall back to general inference when the question isn't in the bank. As a grounding mechanism for in-distribution questions, they work.

But they're advisory. They demonstrate what good answers look like; they don't enforce anything about the metrics themselves. That distinction matters more when you cross vendor boundaries. It also shows up once an agent starts composing across constructs the data engineer never anticipated as standalone questions (which I suspect is where a lot of long-tail behavior emerges).

TPC-DS reference, here:

- name: sales_by_brand
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(store_sales.ss_ext_sales_price)
        description: Total sales by brand (requires grouping by item.i_brand)
        ai_context:
          synonyms:
            - "brand sales"
            - "brand performance"
            - "brand revenue"

The SQL is identical to total_sales. What actually differentiates this metric is the intended grouping. The data engineer encoded that requirement in a prose parenthetical inside description because the spec gave them nowhere structured to put it.

You could try to fix this with verified queries: ship a few examples that group by brand and hope the LLM picks up and generalizes from them. From the consumer perspective, that hope is the reconstruction burden we keep running into. For questions sitting inside the verified bank, fine. For the long tail (and especially for compositional cases, where sales_by_brand is one piece of a larger answer), the agent has to infer the grouping requirement from examples. That inference is fragile, vendor-dependent, and provides no consistency guarantees across implementations.

A required grouping isn't a suggestion to the LLM. It's a property of the metric itself, and it should be expresible as one.

For @Yokoi-Tofu's Option A vs B: leaning A. Decomposing into first-class properties on the semantic elements gives each piece of authorial intent its own identity, separately addressable and validatable. Verified queries can sit alongside as supporting evidence, not as the only home for structural constraints.

Adjacent to #32 but distinct: that thread is about whether ai_context should be a namespace. The gap I'm pointing at sits in plain description too, so de-namespacing alone wouldn't address it.

I'd be up for collaborating on a concrete proposal, either as part of the verified_queries work or as a sibling issue.
The structural-constraints gap feels distinct enough to be its own thread.