- March 3, 2025
- Posted by: Azar Mohammed
- Category: Snowflake
Introduction:
In today’s data-driven world, organizations seek seamless ways to interact with their enterprise data securely and efficiently. The combination of Snowflake’s Cortex Analyst, Azure Bot Framework, and modern authentication mechanisms creates a robust solution for AI-powered conversational insights, and TEXT-to-SQL conversions. This article explores how an AI-driven bot architecture enables users to interact with their Snowflake data, without bringing the data outside of the snowflake horizon using natural language through Microsoft Teams, Slack, and web interfaces.
Pre-requisites:
1. Snowflake Account — Serves as the primary data source for storing and managing data.
Note. While creating the snowflake account make sure you check the availability of Cortex functions.
2. Snowflake Cortex Agent — A semantic model built on top of Snowflake tables, enabling intelligent data access and interaction.
3. Microsoft Azure Account — Hosts the necessary services for deploying and managing the AI-powered bot.
4. Communication Channel — The bot can be integrated into Microsoft Teams, Slack, or custom web applications for seamless interaction.
Initial Setup:
For this application, we utilized the Customer, Orders, and Nation tables from the Snowflake sample dataset. These datasets were selected to ensure comprehensive insights and seamless integration with our solution.
To set up Cortex Analyst, we define the Semantic Model by referencing the required data source tables. For detailed configuration, including the YAML file, please refer to the link below.
For guidance on configuring and building custom models in Snowflake, refer to the official Snowflake documentation. It provides step-by-step instructions and best practices for model creation.
Cortex Analyst semantic model specification | Snowflake Documentation
Once Cortex Analyst is configured, you can test it by submitting queries through Snowflake’s intuitive UI. The model can interpret and process natural language questions, providing relevant insights based on the configured Semantic Model.
Additionally, Snowflake offers features to add Verified Queries, which can be used to train the model and refine its responses for better accuracy and relevance. Leveraging these queries helps tailor the model to specific business needs, ensuring consistent and reliable results.
To further optimize performance, consider:
· Fine-tuning query patterns to align with user expectations.
· Monitoring model responses and iterating on query refinements.
· Utilizing access controls to manage data security and governance.
Authentication and Cortex-Analyst API configuration:
The authentication method used to communicate with the snowflake data in a secured way is External OAuth Application.
We have configured App registration in Microsoft Entra ID which are used to generate the authentication token, and later that token is passed as a Bearer token to send the User Questions to Cortex Analyst.
1. Generate an Authentication Token
First, obtain an authentication token from Microsoft Entra ID using your App Registration credentials.
curl --location 'https://login.microsoftonline.com/<Tenant-ID>/oauth2/v2.0/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'grant_type=client_credentials' \
--data-urlencode 'scope=<App-Registration-Scope>.default' \
--data-urlencode 'client_id=<App-ID>' \
--data-urlencode 'client_secret=<Client-Secret>'
2. Use the Token to Query Cortex Analyst
Once you have the token, send a query to Cortex Analyst in Snowflake by passing it as a Bearer token.
curl --location 'https://<accountidentifier>.snowflakecomputing.com/api/v2/cortex/analyst/message' \
--header 'Authorization: Bearer <Token>' \
--header 'Content-Type: application/json' \
--data-raw '{ "messages": [ { "role": "user", "content": [ { "type": "text", "text": "How many customers are from Canada?" } ] } ], "semantic_model_file": "@SEMANTIC_MODE/.YAML" }'
Bot Framework :
Bot framework is an azure offering which can used to build custom code-based bots and applications which can used by organization’s users.
We have built this cortex-analyst application using python-based code, which can be pulled from the below repository,
Microsoft/botbuilder-python: The Microsoft Bot Framework
The folder structure, will be something like this in the below,
We are calling the cortex-analyst API in the bot.py-file and processing the SQL Query which is returned by cortex Analyst to showcase the result in a tabular manner.
def token_generator():
try:
url = "https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token"
data = {
"grant_type": "client_credentials",
"scope": "scope/.default",
"client_id": client_id,
"client_secret": client_secret
}
headers = {"Content-Type": "application/x-www-form-urlencoded"}
response = requests.post(url, data=data, headers=headers)
response.raise_for_status()
return response.json()["access_token"]
except requests.exceptions.RequestException as error:
print("Error fetching token:", error)
raise
def analyst_query(token, question):
try:
url = "https://<account-identifier>.snowflakecomputing.com/api/v2/cortex/analyst/message"
data = {
"messages": [
{"role": "user", "content": [{"type": "text", "text": question}]}
],
"semantic_model_file": "@POWER_AUTOMATE.PUBLIC.SALES/file.yaml"
}
headers = {"Authorization": f"Bearer {token}", "Content-Type": "application/json"}
response = requests.post(url, json=data, headers=headers)
response.raise_for_status()
return response.json()["message"]["content"][1]["statement"]
except requests.exceptions.RequestException as error:
print("Error in analyst query:", error)
raise
def analyst_sql(token, sql_statement):
try:
url = "https://<account-identifier>.snowflakecomputing.com/api/v2/statements"
data = {
"statement": sql_statement,
"timeout": 60,
"resultSetMetaData": {"format": "json"},
"database": "POWER_AUTOMATE",
"schema": "PUBLIC",
"warehouse": "SANDBOX_WH",
"role": "ANALYST"
}
headers = {"Authorization": f"Bearer {token}", "Content-Type": "application/json"}
response = requests.post(url, json=data, headers=headers)
response.raise_for_status()
return response.json()
except requests.exceptions.RequestException as error:
print("Error in analyst SQL execution:", error)
raise
class MyBot(ActivityHandler):
# See https://aka.ms/about-bot-activity-message to learn more about the message and other activity types.
async def on_message_activity(self, turn_context: TurnContext):
auth_token = token_generator()
question_ = turn_context.activity.text
query_statement = analyst_query(auth_token, question_).replace("__", "")
sql_result = analyst_sql(auth_token, query_statement)
# Extract column names
column_names = [col["name"] for col in sql_result["resultSetMetaData"]["rowType"]]
# Prepare the data in DataFrame format
data = [dict(zip(column_names, row)) for row in sql_result["data"]]
# Create a DataFrame
df = pd.DataFrame(data)
markdown_table = df.to_markdown(index=False)
response_message = (
f"Here is a sample response:\n\n"
f"```markdown\n{markdown_table}\n```\n\n"
f"Here is the Query Statement:\n\n"
f"```sql\n{query_statement}\n```"
)
await turn_context.send_activity(f"{response_message}")
async def on_members_added_activity(
self,
members_added: ChannelAccount,
turn_context: TurnContext
):
for member_added in members_added:
if member_added.id != turn_context.activity.recipient.id:
await turn_context.send_activity("Hello and welcome!")
Testing the Bot:
Before deploying the application to the cloud, we can perform a local test to verify its functionality using the Bot Framework Emulator. Follow these steps to run the application locally:
1.Run the Application Locally:
Start the bot by running the app.py file on your local machine. This will start the application and make it available on your local server.
2. Launch the Bot Framework Emulator:
Open the Bot Framework Emulator on your machine.
3. Connect to the Bot:
In the emulator, enter the bot’s URL: http://localhost:3978/api/messages. This connects the emulator to your locally running bot.
4. Test the Bot:
Once connected, you can begin interacting with your bot, testing its functionality, and ensuring everything is working as expected.
Now if you post the question to the cortex analyst as a message, you will get the response with the tabulated result along with the Query which is generated by cortex analyst by parsing the text.
Once after the testing is completed, we can now deploy the application into azure and publish them into different channels for users to utilize them.
Deploying the Bot:
To integrate our bot with multiple channels, we need to host it in the cloud as a web app service. Azure offers various deployment methods for web apps, and in this case, we are using ZIP deployment.
Once the deployment is complete, we can verify its success by checking the web app’s deployment logs. If the status shows “Success,” the bot is ready for testing in Azure. This allows us to validate its functionalities before publishing it to different channels for users to interact with.
On successful, deployment you would receive a warm welcome from the bot, and we are good to pass our questions and get the TEXT to SQL response from the bot.
Conclusion:
The best part is that once deployed, our agent can seamlessly integrate with multiple channels, ensuring a smooth and accessible user experience. Additionally, we can generate an embed code, making it easy to integrate the bot directly into our website for user interaction.
Popular deployment channels include Microsoft Teams, Slack, and Outlook, allowing users to engage with the agent across different platforms. Further enhancements can be made by optimizing responses and refining the agent’s intelligence. By adding verified queries to the YAML file, we can significantly improve the TXT-to-SQL conversion, enabling the agent to handle complex queries more efficiently. This includes advanced time intelligence metrics like Year-to-Date (YTD), Month-over-Month comparisons, and more, making data insights more powerful and actionable. Please feel free to reach out to us for your Snowflake solution needs. Cittabase is a Premier partner with Snowflake.
Referral links:
Cortex Analyst | Snowflake Documentation
Getting Started with Cortex Agents and Microsoft Teams