Chat with Snowflake via n8n AI Agent and Generate Dynamic Reports

Natural language question → AI generates and executes SQL → aggregates results → returns small data directly / generates an interactive report link for large datasets

Who

Developers and data analysts who have a Snowflake database and want to query it via natural language in n8n, with automated visual report generation

You get

Query Snowflake through a chat interface; AI auto-generates and executes SQL. Results under 100 rows are returned directly; larger sets produce a dynamic report link with pagination, filtering, charts, and CSV export

You need

n8n

Steps

1. Configure Agent and Database Schema Tools

   After importing the template, configure the Agent Workflow: 1) 'When chat message received' as the trigger; 2) 'AI Agent1' using OpenAI gpt-4o-mini with Simple Memory attached (sessionKey from the chat trigger's sessionId); 3) Attach 'DB Schema1' and 'Get table definition' Snowflake Tool nodes as AI tools. 'DB Schema1' queries information_schema.tables for table lists; 'Get table definition' queries information_schema.columns for column metadata. Both use snowflake credentials, and the schema name in queries must be replaced with your actual schema.

   Execution prompt · Source text。AI Agent1 node systemMessage field

   Copy prompt

   You are Snowflake SQL assistant.
   
   Use tools to retrieve data from Snowflake and answer user.
   
   IMPORTANT Always check database schema and table definition for preparing SQL query.

2. Configure Data Retrieval Sub-Workflow

   Configure the 'Retrieve Data' tool node (Call n8n Workflow Tool), which passes AI-generated SQL to the Data Retrieval Workflow for execution. In 'Retrieve Data' parameters: workflowId points to your Data Retrieval sub-workflow; the description field contains the tool description telling the AI how to generate SQL; workflowInputs defines the input variable 'query', dynamically filled by the AI. The Data Retrieval Workflow itself uses 'When Executed by Another Workflow' as trigger, receives the query parameter, executes it via the 'Execute SQL' Snowflake node, with error output handled by 'Return Error'.

   Execution prompt · Source text。Retrieve Data tool node description field (AI tool description)

   Copy prompt

   Generate custom SQL queries using knowledge about DB schema and table definitions to provide needed response for user request.
   Use ->> operator to extract JSON data.
   
   Supported functions for big data analysis:
   • GROUP BY – for grouping data
   • SUM() – for summing values
   • AVG() – for calculating averages
   • COUNT() – for counting records
   • MIN() – for finding the minimum value
   • MAX() – for finding the maximum value
   • MEDIAN() – for median calculation
   • STDDEV() – for standard deviation
   • VARIANCE() – for variance calculation
   • PERCENTILE_CONT() – for percentile calculations
   • MODE() – for most frequent value
   • TREND() – for trend analysis over time
   • WINDOW FUNCTIONS – for advanced analytics (e.g., ROW_NUMBER(), RANK(), PARTITION BY)
   
   Query example:
   SELECT * FROM FILES

3. Configure Aggregation and Reporting Decision Branch

   In the Data Retrieval Workflow, after 'Execute SQL', connect to 'Aggregate Data' (aggregateAllItemData), then to 'If Count>100' condition node. The condition checks whether the aggregated data array length exceeds 100: if yes, route to 'Link to Report' to generate a webhook link with URL-encoded SQL; if no, route to 'Return Data' to return raw results to the Agent. The 100 threshold can be adjusted in the If node.

4. Configure Report Viewing Workflow

   Configure the Report Viewing Workflow: entry via 'Webhook' node (responseMode=responseNode), receiving the sql parameter from the report link. 'Snowflake1' re-executes the SQL (query from $json.query.sql), with error output routed to 'Error page'; normal results pass through 'Aggregate1' to 'Set HTML', which generates a complete HTML report page (pagination, column filtering, sorting, chart view, CSV export), finally returned by 'Respond to Webhook'. Replace the webhook address in 'Link to Report' with your actual n8n instance Webhook URL.

You are an n8n workflow setup assistant. The user wants to reproduce the 'AI Agent chat with Snowflake and dynamic reports' template. First confirm the user has: 1) an accessible n8n instance; 2) Snowflake connection details (host, account, warehouse, database, schema, username, password); 3) an OpenAI API Key. Then guide them to: import template → create openAiApi and snowflake credentials → replace schema and database placeholders → replace webhook address → test connection → start chatting. If SQL generation fails, check whether the AI Agent's systemMessage is complete and whether schema tools return correct table structures.