Writing effective system instructions can provide your Conversational Analytics API data agents with useful context for answering questions about your data sources. System instructions are a kind of authored context that data agent owners can provide to shape the behavior of a data agent and to refine the API's responses.
This page describes the recommended structure for writing effective prompts for your Conversational Analytics API data agents that connect to BigQuery data. These prompts are authored context that you define as strings by using the system_instruction parameter.
This page describes how to write system instructions for BigQuery data sources, which are based on BigQuery databases.
Define context in system instructions
System instructions consist of a series of key components and objects that provide the data agent with details about the data source and guidance about the agent's role when answering questions. You can provide system instructions to the data agent in the system_instruction parameter as a YAML-formatted string.
The following template shows a suggested YAML structure for the string that you provide to the system_instruction parameter, including the available keys and expected data types.
The following YAML template shows an example of how you might structure system instructions for a BigQuery data source.
- system_instruction: str # A description of the expected behavior of the agent. For example: You are a sales agent.
- tables: # A list of tables to describe for the agent.
- table: # Details about a single table that is relevant for the agent.
- name: str # The name of the table.
- description: str # A description of the table.
- synonyms: list[str] # Alternative terms for referring to the table.
- tags: list[str] # Keywords or tags that are associated with the table.
- fields: # Details about columns (fields) within the table.
- field: # Details about a single column within the current table.
- name: str # The name of the column.
- description: str # A description of the column.
- synonyms: list[str] # Alternative terms for referring to the column.
- tags: list[str] # Keywords or tags that are associated with the column.
- sample_values: list[str] # Sample values that are present within the column.
- aggregations: list[str] # Commonly used or default aggregations for the column.
- measures: # A list of calculated metrics (measures) for the table.
- measure: # Details about a single measure within the table.
- name: str # The name of the measure.
- description: str # A description of the measure.
- exp: str # The expression that is used to construct the measure.
- synonyms: list[str] # Alternative terms for referring to the measure.
- golden_queries: # A list of important or popular ("golden") queries for the table.
- golden_query: # Details about a single golden query.
- natural_language_query: str # The natural language query.
- sql_query: str # The SQL query that corresponds to the natural language query.
- golden_action_plans: # A list of suggested multi-step plans for answering specific queries.
- golden_action_plan: # Details about a single action plan.
- natural_language_query: str # The natural language query.
- action_plan: # A list of the steps for this action plan.
- step: str # A single step within the action plan.
- relationships: # A list of join relationships between tables.
- relationship: # Details about a single join relationship.
- name: str # The name of this join relationship.
- description: str # A description of the relationship.
- relationship_type: str # The join relationship type: one-to-one, one-to-many, many-to-one, or many-to-many.
- join_type: str # The join type: inner, outer, left, right, or full.
- left_table: str # The name of the left table in the join.
- right_table: str # The name of the right table in the join.
- relationship_columns: # A list of columns that are used for the join.
- left_column: str # The join column from the left table.
- right_column: str # The join column from the right table.
- glossaries: # A list of definitions for glossary business terms, jargon, and abbreviations.
- glossary: # The definition for a single glossary item.
- term: str # The term, phrase, or abbreviation to define.
- description: str # A description or definition of the term.
- synonyms: list[str] # Alternative terms for the glossary entry.
- additional_descriptions: # A list of any other general instructions or content.
- text: str # Any additional general instructions or context not covered elsewhere.
Examples of key components of system instructions using BigQuery data sources
The following sections contain examples of key components of system instructions in BigQuery. These keys include the following:
system_instructiontablesfieldsmeasuresgolden_queriesgolden_action_plansrelationshipsglossariesadditional_descriptions
system_instruction
Use the system_instruction key to define the agent's role and persona. This initial instruction sets the tone and style for the API's responses and helps the agent understand its core purpose.
For example, you can define an agent as a sales analyst for a fictitious ecommerce store as follows:
- system_instruction: >-
You are an expert sales analyst for a fictitious ecommerce store. You will answer questions about sales, orders, and customer data. Your responses should be concise and data-driven.
tables
The tables key contains a list of table descriptions for the agent and provides details about the specific data that is available to the agent for answering questions. Each table object within this list contains the metadata for a specific table, including that table's name, description, synonyms, tags, fields, measures, golden queries, and golden action plans. The following YAML code block shows the basic structure for the tables key for the table bigquery-public-data.thelook_ecommerce.orders:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- description: Data for customer orders in The Look fictitious e-commerce store.
- synonyms:
- sales
- orders_data
- tags:
- ecommerce
- transaction
fields
The fields key, which is nested within a table object, takes a list of field objects to describe individual columns. Not all fields need additional context; however, for commonly used fields, including additional details can help improve the agent's performance.
The following sample YAML code describes key fields such as order_id, status, created_at, num_of_items, and earnings for the orders table:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- fields:
- field:
- name: order_id
- description: The unique identifier for each customer order.
- field:
- name: user_id
- description: The unique identifier for each customer.
- field:
- name: status
- description: The current status of the order.
- sample_values:
- complete
- shipped
- returned
- field:
- name: created_at
- description: The timestamp when the order was created.
- field:
- name: num_of_items
- description: The total number of items in the order.
- aggregations:
- sum
- avg
- field:
- name: earnings
- description: The sales amount for the order.
- aggregations:
- sum
- avg
measures
The measures key, which is nested within a table object, defines custom business metrics or calculations that aren't directly present as columns in your tables. Providing context for measures helps the agent answer questions about key performance indicators (KPIs) or other calculated values.
As an example, you can define a profit measure as a calculation of the earnings minus the cost as follows:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- measures:
- measure:
- name: profit
- description: Raw profit (earnings minus cost).
- exp: earnings - cost
- synonyms: gains
golden_queries
The golden_queries key, which is nested within a table object, takes a list of golden_query objects. Golden queries help the agent provide more accurate and relevant responses to common or important questions that you can define.
As an example, you can define golden queries for common analyses for the data in the orders table as follows:
- tables:
- table:
- golden_queries:
- golden_query:
- natural_language_query: How many orders are there?
- sql_query: SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
- golden_query:
- natural_language_query: How many orders were shipped?
- sql_query: >-
SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
WHERE status = 'shipped'
golden_action_plans
The golden_action_plans key, which is nested within a table object, takes a list of golden_action_plan objects. You can use golden action plans to provide the agent with examples of how to handle multi-step requests, such as to fetch data and then create a visualization.
As an example, you can define an action plan for showing order breakdowns by age group and include details about the SQL query and visualization-related steps:
- tables:
- table:
- golden_action_plans:
- golden_action_plan:
- natural_language_query: Show me the number of orders broken down by age group.
- action_plan:
- step: >-
Run a SQL query that joins the table
sqlgen-testing.thelook_ecommerce.orders and
sqlgen-testing.thelook_ecommerce.users to get a
breakdown of order count by age group.
- step: >-
Create a vertical bar plot using the retrieved data,
with one bar per age group.
relationships
The relationships key contains a list of join relationships between tables. By defining join relationships, you can help the agent understand how to join data from multiple tables when answering questions.
As an example, you can define an orders_to_user relationship between the bigquery-public-data.thelook_ecommerce.orders table and the bigquery-public-data.thelook_ecommerce.users table as follows:
- relationships:
- relationship:
- name: orders_to_user
- description: >-
Connects customer order data to user information with the user_id and id fields to allow an aggregated view of sales by customer demographics.
- relationship_type: many-to-one
- join_type: left
- left_table: bigquery-public-data.thelook_ecommerce.orders
- right_table: bigquery-public-data.thelook_ecommerce.users
- relationship_columns:
- left_column: user_id
- right_column: id
glossaries
The glossaries key lists definitions for business terms, jargon, and abbreviations that are relevant to your data and use case. By providing glossary definitions, you can help the agent accurately interpret and answer questions that use specific business language.
As an example, you can define terms like common business statuses and "OMPF" according to your specific business context as follows:
- glossaries:
- glossary:
- term: complete
- description: Represents an order status where the order has been completed.
- synonyms: 'finish, done, fulfilled'
- glossary:
- term: shipped
- description: Represents an order status where the order has been shipped to the customer.
- glossary:
- term: returned
- description: Represents an order status where the customer has returned the order.
- glossary:
- term: OMPF
- description: Order Management and Product Fulfillment
additional_descriptions
The additional_descriptions key lists any additional general instructions or context that is not covered elsewhere in the system instructions. By providing additional descriptions, you can help the agent better understand the context of your data and use case.
As an example, you can use the additional_descriptions key to provide information about your organization as follows:
- additional_descriptions:
- text: All the sales data pertains to The Look, a fictitious ecommerce store.
- text: 'Orders can be of three categories: food, clothes, and electronics.'
Example: System instructions in BigQuery
The following example shows sample system instructions for a fictitious sales analyst agent:
- system_instruction: >-
You are an expert sales analyst for a fictitious ecommerce store. You will answer questions about sales, orders, and customer data. Your responses should be concise and data-driven.
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- description: Data for orders in The Look, a fictitious ecommerce store.
- synonyms: sales
- tags: 'sale, order, sales_order'
- fields:
- field:
- name: order_id
- description: The unique identifier for each customer order.
- field:
- name: user_id
- description: The unique identifier for each customer.
- field:
- name: status
- description: The current status of the order.
- sample_values:
- complete
- shipped
- returned
- field:
- name: created_at
- description: >-
The date and time at which the order was created in timestamp
format.
- field:
- name: returned_at
- description: >-
The date and time at which the order was returned in timestamp
format.
- field:
- name: num_of_items
- description: The total number of items in the order.
- aggregations: 'sum, avg'
- field:
- name: earnings
- description: The sales revenue for the order.
- aggregations: 'sum, avg'
- field:
- name: cost
- description: The cost for the items in the order.
- aggregations: 'sum, avg'
- measures:
- measure:
- name: profit
- description: Raw profit (earnings minus cost).
- exp: earnings - cost
- synonyms: gains
- golden_queries:
- golden_query:
- natural_language_query: How many orders are there?
- sql_query: SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
- golden_query:
- natural_language_query: How many orders were shipped?
- sql_query: >-
SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
WHERE status = 'shipped'
- golden_action_plans:
- golden_action_plan:
- natural_language_query: Show me the number of orders broken down by age group.
- action_plan:
- step: >-
Run a SQL query that joins the table
sqlgen-testing.thelook_ecommerce.orders and
sqlgen-testing.thelook_ecommerce.users to get a
breakdown of order count by age group.
- step: >-
Create a vertical bar plot using the retrieved data,
with one bar per age group.
- table:
- name: bigquery-public-data.thelook_ecommerce.users
- description: Data for users in The Look, a fictitious ecommerce store.
- synonyms: customers
- tags: 'user, customer, buyer'
- fields:
- field:
- name: id
- description: The unique identifier for each user.
- field:
- name: first_name
- description: The first name of the user.
- tag: person
- sample_values: 'alex, izumi, nur'
- field:
- name: last_name
- description: The first name of the user.
- tag: person
- sample_values: 'warmer, stilles, smith'
- field:
- name: age_group
- description: The age demographic group of the user.
- sample_values:
- 18-24
- 25-34
- 35-49
- 50+
- field:
- name: email
- description: The email address of the user.
- tag: contact
- sample_values: '222larabrown@gmail.com, cloudysanfrancisco@gmail.com'
- golden_queries:
- golden_query:
- natural_language_query: How many unique customers are there?
- sql_query: >-
SELECT COUNT(DISTINCT id) FROM
bigquery-public-data.thelook_ecommerce.users
- golden_query:
- natural_language_query: How many users in the 25-34 age group have a cymbalgroup email address?
- sql_query: >-
SELECT COUNT(DISTINCT id) FROM
bigquery-public-data.thelook_ecommerce.users WHERE users.age_group =
'25-34' AND users.email LIKE '%@cymbalgroup.com';
- relationships:
- relationship:
- name: orders_to_user
- description: >-
Connects customer order data to user information with the user_id and id fields to allow an aggregated view of sales by customer demographics.
- relationship_type: many-to-one
- join_type: left
- left_table: bigquery-public-data.thelook_ecommerce.orders
- right_table: bigquery-public-data.thelook_ecommerce.users
- relationship_columns:
- left_column: user_id
- right_column: id
- glossaries:
- glossary:
- term: complete
- description: Represents an order status where the order has been completed.
- synonyms: 'finish, done, fulfilled'
- glossary:
- term: shipped
- description: Represents an order status where the order has been shipped to the customer.
- glossary:
- term: returned
- description: Represents an order status where the customer has returned the order.
- glossary:
- term: OMPF
- description: Order Management and Product Fulfillment
- additional_descriptions:
- text: All the sales data pertains to The Look, a fictitious ecommerce store.
- text: 'Orders can be of three categories: food, clothes, and electronics.'