Cortex Analyst Verified Query Repository¶
The Cortex Analyst Verified Query Repository (VQR) can help improve the accuracy and trustworthiness of results by providing a collection of questions and corresponding SQL queries to answer them. Cortex Analyst then leverages relevant SQL queries from the repository when answering similar questions. You can specify verified queries in your semantic model YAML file.
Important
Verified SQL queries must use the names of the logical tables and columns defined in the semantic model, not those in the underlying dataset. See the example query and its discussion for more information.
Verified queries are specified in the verified_queries
section of the semantic model, as shown here.
verified_queries:
# Verified Query 1
- name: # A descriptive name of the query.
question: # The natural language question that this query answers.
verified_at: # Optional: Time (in seconds since the UNIX epoch, January 1, 1970) when the query was verified.
verified_by: # Optional: Name of the person who verified the query.
sql: # The SQL query for answering the question.
# Verified Query 2
- name:
question:
verified_at:
verified_by:
sql:
Below is a sample semantic model that includes a verified query.
name: Sales Data
tables:
- name: sales_data
base_table:
database: sales
schema: public
table: sd_data
dimensions:
- name: state
description: The state where the sale took place.
expr: d_state
data_type: TEXT
unique: false
sample_values:
- "CA"
- "IL"
# Time dimension columns in the logical table.
time_dimensions:
- name: sale_timestamp
synonyms:
- "time_of_sale"
- "transaction_time"
description: The time when the sale occurred. In UTC.
expr: dt
data_type: TIMESTAMP
unique: false
# Measure columns in the logical table.
measures:
- name: profit
synonyms:
- "earnings"
- "net income"
description: The profit generated from a sale.
expr: amt - cst
data_type: NUMBER
default_aggregation: sum
verified_queries:
- name: "California profit"
question: "What was the profit from California last month?"
verified_at: 1714497970
verified_by: Jane Doe
sql: "
SELECT sum(profit)
FROM __sales_data
WHERE state = 'CA'
AND sale_timestamp >= DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE))
AND sale_timestamp < DATE_TRUNC('month', CURRENT_DATE)
"
In the verified query in the example above, __sales_data
corresponds to the sales_data
table defined in the
model. To avoid name avoid conflicts, the name of the logical table is prefixed with two underscores. The columns used
in the query (state
, sale_timestamp
, and profit
) are the logical columns defined in the model’s
sale_data
table. The names of the underlying columns (d_state
, dt
, amt
, and cst
) are not used
directly in the query.
As illustrated in the example, the question doesn’t need to be a complete sentence, or actually in the form of a question, but it should reflect something a user might ask. Ensure that the SQL queries are syntactically correct and actually answer the posed questions; this is the essence of a “verified query.” Invalid or inaccurate queries can negatively impact Cortex Analyst’s performance and accuracy.
Tip
Use the open-source semantic model generator app, described in the next section, to help add verified queries to your semantic model, without needing to concern yourself with SQL or YAML syntax.
Adding verified queries using the semantic model generator¶
Snowflake provides an open-source Streamlit app to help add verified queries to your model. To install and use this app, follow these instructions.
Clone the repository. Start by cloning the semantic-model-generator repository. Follow the setup instructions in the repo’s README to provide your Snowflake credentials to the app.
Install the App. Follow the instructions in the admin_app README to install the necessary dependencies and start the app.
Set Up the App. Once the app is running, enter the database, schema, and stage location of your semantic model YAML file into the provided fields. The YAML file will appear in an interactive editor on the left side of the window.
Generate a Query. On the right side of the window, use the chat interface to ask a question that will generate a SQL query.
Verify and Save the Query.
Inspect the generated query and the results it produces. If it works as expected, select the Save as verified query button below the assistant’s answer to add the query to your semantic model.
If the generated query is incorrect, select the Edit button to modify the query. Run the modified query to check if it produces the intended results. Continue editing and testing until the query works as desired. Then select Save as verified query to add it to your semantic model.
Update the Semantic Model. Select the Save button in the bottom left of the window to update the semantic model. Repeat the process to add more queries.
Upload the new YAML file. Once you’re satisfied with the queries you’ve added, select the Upload button, enter a file name for your new YAML file, and select Submit Upload.
When you return to your stage in Snowsight, you’ll see the new semantic model YAML file with your verified queries.