Aidp Verified Queries

aidp-verified-queries — the verified-query repository (VQR)

Maintain .aidp/verified-queries.md: validated question → Spark SQL pairs that aidp-analyzing-data reuses before generating SQL from scratch — the highest-reliability NL-to-SQL mechanism.

When to use

• Save a working query as the canonical answer to a recurring question.
• Curate/clean the verified-query repository.

Quality gate (critical — do not skip)

A wrong verified query makes accuracy worse. Before setting verified: true, the pair MUST:

1. be syntactically valid Spark SQL,
2. execute on the cluster (run it via the bundled scripts/aidp_sql.py helper),
3. actually answer the stated question (sanity-check the result shape/values). If any check fails, keep verified: false (DRAFT) and explain why — never auto-promote a failing pair.

Workflow

1. Read the candidate question + SQL (or take the last query run in aidp-analyzing-data).
2. Prefer logical names from .aidp/semantic.md; record the physical tables touched.
3. Validate by running the SQL on the cluster with the bundled helper (no MCP required):
   bash

   python "$PLUGIN_DIR/scripts/aidp_sql.py" \  --region <region> --datalake <DATALAKE_OCID> --workspace <ws> --cluster <cluster-key> \  --code "spark.sql('''<your SELECT … LIMIT 50>''').show(50, truncate=False)"

   It mints a UPST from the api_key DEFAULT profile, auto-creates a scratch notebook, and returns JSON {status, outputs, spark_job_ids}. Require status == "ok" and a result that answers the question. Run on a bounded sample (add LIMIT) to keep validation cheap.
4. Append the entry to .aidp/verified-queries.md in the documented format; set verified: true only on a recorded successful run (note cluster + date).
5. On reuse, aidp-analyzing-data matches by question similarity + table overlap and adapts only dates/bind values.

Notes

• .aidp/verified-queries.md is user-editable and git-ignored (per-project).
• Keep entries small, single-purpose; complex asks get a complete worked example.
• This skill is self-contained: validation runs through scripts/aidp_sql.py, not any MCP server. If an aidp MCP happens to be configured you may use its nb_execute_code as an accelerator, but it is not required.

References

• references/verified-queries.md · references/semantic-model.md
• SQL execution helper: references/no-mcp-rest-map.md (No-MCP SQL via scripts/aidp_sql.py)