Natural Language to BigQuery SQL Generator using GPT-4o - n8n Workflow

Data analysts who want to democratize data access without relying on manual SQL writing.
Technical teams looking for advanced n8n templates integrating AI coding capabilities.
Businesses wanting to provide secure natural language access (Talk-to-Data) to their BigQuery datasets.
Developers needing a complex, robust, embeddable chat n8n node solution.

This sophisticated automation solves the challenge of securely and dynamically querying large BigQuery datasets without requiring users to write complex SQL. The core value of this n8n workflow lies in its ability to first retrieve the current database schema, pass that essential context alongside the user's question to GPT-4o, and then enforce a structured output to guarantee a valid SQL query. By using an embeddable chat n8n trigger, this solution can be deployed instantly, allowing non-technical users to access crucial data insights simply by asking questions. This is a robust demonstration of what is possible with advanced AI integration within an n8n node environment, providing a powerful n8n workflow that enhances organizational data literacy.

The entire process is initiated by the custom chat n8n trigger, designed for embedding:

1. Chat Activation (n8n trigger): The process begins when a user submits a question via the Embedable chat for users to ask questions of bigquery n8n trigger.


2. Schema Retrieval: The n8n workflow runs a parallel path, using the Google BigQuery n8n node to query the INFORMATION_SCHEMA.COLUMNS and retrieve the complete list of available tables, columns, and data types. This provides the AI with real-time, accurate context.


3. Context Formatting: The retrieved schema is aggregated and passed through a Code n8n node to format the metadata into a concise text string, ready for the LLM.


4. Input Merging: A Merge n8n node, combine the table names with user question, combines the original user question (from the n8n trigger) and the formatted schema text.


5. AI SQL Generation: The AI Agent - Write SQL Query n8n node, powered by the OpenAI Chat Model (GPT-4o) and configured with Simple Memory for persistent conversations, receives the complete context. The system prompt strictly mandates the AI to generate valid BigQuery SQL using only the provided schema elements, ensuring data security and query accuracy.


6. Output Validation: A Structured Output Parser n8n node guarantees the AI output adheres to the required JSON format ({"query": "..."}).


7. Query Execution: The second Google BigQuery n8n node, Run query against schema, executes the expression-based SQL query ({{ $json.output.query }}) generated by the AI.


8. Error Handling: If the BigQuery execution fails (common with complex SQL), the workflow uses the onError: continueErrorOutput setting on the BigQuery n8n node. This routes the failure path to a final Code n8n node, which gracefully returns an Ask User to try another question message back through the chat n8n trigger interface, ensuring a resilient n8n workflow.

To set up this advanced n8n workflow using these n8n templates, follow these steps:

1. Import Workflow: Copy the provided JSON data and use the Import from File or Paste JSON option in your n8n instance.


2. Configure Credentials: You must provide two sets of credentials:

1. Update Schema Fetcher: The initial BigQuery node (Output all table, and column names in your schema) contains a query to fetch schema metadata.

1. Update Execution Node: In the second BigQuery n8n node (Run query against schema), ensure you select the correct Project ID from the drop-down list.


2. Activate: Toggle the n8n workflow to Active so the Chat n8n trigger is listening for requests.

Embedable chat for users to ask questions of bigquery (Chat Trigger n8n node): This specialized n8n trigger starts the conversation and feeds the user's natural language question into the n8n workflow.
Output all table, and column names in your schema (Google BigQuery n8n node): Dynamically fetches database schema (table names, columns, data types) via INFORMATION_SCHEMA.COLUMNS to provide real-time context to the LLM.
Convert table names and columns into single text for agent (Code n8n node): A utility n8n node that maps and joins the structured output from BigQuery into a single string for effective AI consumption.
AI Agent - Write SQL Query (LangChain Agent n8n node): The core of this n8n workflow. It uses GPT-4o, the schema context, and conversation memory to formulate a complex, valid BigQuery SQL query.
OpenAI Chat Model (LangChain LM Chat n8n node): Utilizes the GPT-4o model, chosen for its strong code generation and instruction-following capabilities.
Simple Memory (LangChain Memory n8n node): Maintains conversational context using the session ID provided by the n8n trigger, crucial for multi-turn SQL refinement.
Structured Output Parser (LangChain Output Parser n8n node): Ensures that the AI's final output strictly adheres to the JSON structure { "query": "..." }, which is required for the subsequent n8n node to execute the SQL.
Run query against schema (Google BigQuery n8n node): Executes the dynamically generated SQL. Key Configuration: Set to onError: continueErrorOutput to handle failed queries gracefully, redirecting to a user error message.