Snowflake

Connect Snowflake to BotDojo so your agents can run SQL, inspect schema, and write data in your warehouse.

Before You Start

You will need:

• your Snowflake account identifier
• a Snowflake username
• a Snowflake private key in PEM format
• the private key passphrase, if the key is encrypted
• the default database, schema, warehouse, and role you want BotDojo to use

Connect Snowflake In BotDojo

1. Open Integrations in BotDojo.
2. In Agent Tools, find Snowflake and click Connect.
3. Enter your account identifier, username, private key, optional private key passphrase, database, schema, warehouse, and role.
4. Click Connect.

BotDojo uses Snowflake key-pair authentication when a private key is configured. Password authentication remains available for legacy integrations.

What This Integration Supports

Major supported actions include:

• execute SQL queries
• insert data
• update data
• inspect table schema
• ask single or multi-turn questions with Cortex Analyst and execute generated read SQL

Snowflake also supports document loading in BotDojo, but this page is focused on the Agent Tool connection flow.

Cortex Analyst

The Cortex Analyst action uses a semantic model stored on a Snowflake stage. Configure Cortex Semantic Model File with its fully qualified path, for example @DATABASE.SCHEMA.STAGE/model.yaml. This optional connection setting is required when using the Analyst action. Existing key-pair and legacy password authentication continue to work.

Use a Snowflake role with only the required Analyst/model access and SELECT grants on approved objects. For a restricted context, expose only cortexAnalyst; the general SQL, write, and stored procedure tools have broader capabilities.

BotDojo creates a fresh SDK connection, sets the statement timeout, authenticates the Analyst REST request with that connection's session token, and executes generated SQL on the same connection. It closes the connection on every outcome and does not retry with a replacement session.

Optional session user ID

Ordinary Cortex Analyst use does not require a custom user ID or runtime context. Leave the tool's User ID property and the connection's Cortex User ID and Cortex User ID Header blank when your semantic model and permissions do not use a custom USER_ID session variable. Direct integration MCP calls can use this mode.

To give every call on a connection the same identity, set Cortex User ID on the Snowflake connection, for example to 4504. BotDojo runs SET USER_ID = <id> before every Analyst call on that connection, including direct integration MCP calls, agent tools, and flow nodes. Values must be positive safe integers. A user_id argument that differs from the connection value fails before connecting, so the model cannot switch users. Configuring both Cortex User ID and Cortex User ID Header fails before connecting.

To set a fixed identity for a single flow instead, set the Cortex Analyst tool's User ID (user_id) as a saved property in your flow, for example with a value of 4504. BotDojo runs SET USER_ID = <id> before calling Analyst. Values must be positive safe integers. user_id is an ordinary tool input: a saved property is hidden from the agent's tool schema, but it is not enforced against the model. A model that adds user_id to its tool call, or a flow that wires a different value, can change the identity. Use this mode only when that is acceptable, such as a demo.

For identity that varies per authenticated caller, leave User ID and Cortex User ID unset and configure a runtime header name such as x-app-user-id on the connection. The authenticated calling application must resolve the user's trusted numeric identity and supply it in the BotDojo flow request's header map (FlowRequestParams.header), and must prevent callers from supplying or overwriting this header. When a header is configured, BotDojo requires it on every call and rejects any user_id argument, so the model cannot change the identity. Missing context, missing identity, invalid values, and malformed header configuration fail before connecting.

Direct native-integration MCP calls cannot carry runtime headers or saved flow properties: ordinary mode and the connection's Cortex User ID work, and required-header calls fail closed. A flow exposed through MCP also needs a caller that supplies the mapped flow-request header when using header mode; ordinary Workspace metadata is not an authenticated application user ID. Confirm your model and secure-object policies enforce the intended access in the actual Snowflake account; the integration does not implement application identity mapping or warehouse policies.

Send questions and follow-ups

Supply messages in the Analyst conversation format:

{
  "messages": [
    {
      "role": "user",
      "content": [{ "type": "text", "text": "How many active users are there?" }]
    }
  ]
}

For a follow-up, include the earlier user message, the complete returned analyst.message, and the new user message. The last message must be from user. Keep SQL confidence and other content metadata intact, and keep conversation histories separate for each authenticated user.

The tool returns the complete response under analyst, including text, SQL, suggestions, confidence, warnings, and response metadata. request_id is also available at the top level when supplied by Snowflake. Suggestions or explanations without SQL succeed with executed: false. Pass executeSql: false through the API or agent tool to request generation only. For graph nodes, save this option as false in the node configuration; dynamically wiring a false value currently has a caller limitation.

Generated SQL executes by default. BotDojo validates one SELECT/WITH query and preserves its ordering, LIMIT, and OFFSET when executing it. The guard rejects multiple statements, write/session commands, ambiguous multiple SQL blocks, SYSTEM functions, cached-result access through RESULT_SCAN, query-history access through QUERY_HISTORY and its variants, and dynamic SQL/name invocation through TO_QUERY, IDENTIFIER, or TABLE with a string/session/bind argument. Ordinary TABLE functions such as FLATTEN remain available. These restrictions also cover quoted and qualified identifiers: cached results under a shared Snowflake username could expose another application's user's previous query rows without applying the current session's row policy.

The returned data contains at most Cortex Maximum Result Rows (default 1,000, maximum 10,000), with a 1 MB result byte limit. BotDojo limits the SDK result stream rather than rewriting the generated query. queryId identifies the executed Snowflake statement. rowCount is the statement's full result row count before limiting the data returned to the agent; an existing query LIMIT still applies. truncated indicates that some statement rows were omitted from data. The statement deadline bounds query execution and streaming.

Cortex Timeout defaults to 60 seconds and is capped at 300 seconds for the REST request and each statement, including result streaming. Request bodies are limited to 256 KB and Analyst responses to 1 MB. Redirects are rejected. The session-token adapter is covered by a real-driver contract fixture and pins Snowflake SDK 1.15.0 because its public serialization format can change between versions.

References

• Snowflake key-pair authentication
• Snowflake Node.js driver authentication
• Cortex Analyst REST API
• Cortex Analyst tutorial
• Snowflake persisted result access
• Snowflake query-history functions
• Snowflake dynamic SQL through TO_QUERY