This blog shows how to connect Google Gemini, n8n, and BigQuery to turn plain chat requests into SQL queries. Using MCP as a secure bridge, the setup cleanly separates AI intent from query execution, making the workflow modular, scalable, and safe.

By integrating Gemini with BigQuery through n8n, you not only reduce the manual effort of writing SQL but also gain a faster and smarter way to access data. The workflow enhances testing, strengthens security, and simplifies maintenance, creating a system that’s both efficient and user-friendly.

Prerequisites

1. Google Cloud account with BigQuery Sandbox
2. BigQuery Public Dataset access
3. OAuth 2.0 credentials in bigquery
4. n8n Cloud instance
5. Google Gemini API

Setting Up the Environment

1.Set Up Google Cloud Project and Sandbox

• Open your browser and head to the Google Cloud Console.
• Create a new project or pick an existing one.
• Turn on the BigQuery Sandbox feature — this gives you free access without requiring a billing account.

2.Explore BigQuery Public Datasets

• Navigate to the BigQuery Explorer inside the console.
• Expand the bigquery-public-data section from the sidebar.
• Run a quick test query to confirm access.
• If the query returns rows, your sandbox and public dataset access are working correctly.

3.Set Up OAuth Credentials for n8n

• In the Google Cloud Console, go to APIs & Services → Credentials.
• Create an OAuth 2.0 Client ID with these settings:
• Application Type: Web application
• Redirect URI: http://localhost:5678/rest/oauth2-credential/callback (or your n8n Cloud redirect link)
• Securely retain your Client ID and Client Secret.

Dataset used: bigquery-public-data.usa_names

The workflow makes use of the USA Names dataset available in Google BigQuery’s public data program. This dataset contains records of baby names registered across U.S. states and years, offering valuable insights into demographic trends, cultural shifts, and naming patterns.

Each record represents how often a given name was used for babies born in a particular state and year. The schema is straightforward and human-readable, making it ideal for demonstrations and analytical queries.

Overview of tools:

What is n8n?

n8n (short for “nodemation”) is an open-source platform designed to automate how different apps and data sources work together. Instead of coding complex integrations, you can drag, drop, and connect nodes in a flowchart-style editor. This makes it easy to build powerful automations that move data, trigger actions, and process information — all visually.

n8n’s true power is its adaptability — you can opt for the ease of n8n Cloud or take full ownership with self-hosting. It’s designed to be approachable for non-technical users who want simple automation, while still offering advanced options for developers to extend workflows using JavaScript and custom nodes.

Key Features of n8n:

• Automated Workflows: Connect apps, APIs, and databases into seamless flows that handle tasks for you.
• Visual Editor: Design processes like flowcharts with an intuitive drag-and-drop builder.
• Smart Logic: Add conditions, loops, and custom code to create powerful, flexible automations.
• Deployment Freedom: Run securely in the cloud or self-host with full visibility and control.

What is MCP (Model Context Protocol)?
Model Context Protocol (MCP) is a communication standard that enables AI models to interact safely with external systems — from databases and APIs to cloud-based applications. Rather than exposing raw SQL queries or direct commands, MCP works as a middle layer, converting natural language into structured and secure executable instructions.

Why MCP Matters in n8n with BigQuery Workflows

Without MCP, users would need to write SQL or scripts. With MCP, the AI + n8n workflow handles translation, execution, and response formatting automatically. This makes data exploration conversational, safe, and accessible even for non-technical users.

How to Sign Up for n8n Cloud

Follow these steps to create your n8n Cloud account and start automating workflows:

1. Go to the official n8n website (n8n.io) in your browser and click the Sign-Up button to begin.

2. Create Your Account: Fill in the registration form with your Full Name, Email Address, and Password.

3. Verify & Log In: After signing up, check your inbox for a verification email and click the activation link. Once verified, log in with your credentials to access the n8n Cloud Dashboard. Open the instance, start a new workflow, and add steps using the plus icon to explore available tools.

Main Workflow in N8N:

The integration is powered by two linked workflows in n8n: a Main Workflow that manages chat-driven requests and a Data Retrieval Sub-Workflow dedicated to executing BigQuery queries. Working in tandem, they create a smooth pipeline where AI-driven intent translates directly into orchestrated database actions.

The Main Workflow activates whenever a new chat message comes in. It leverages the Google Gemini 2.0 Chat Model to interpret user intent, track conversation history, and determine whether BigQuery access is needed.

Core elements of this workflow include:

• Trigger Node – Initiates the pipeline as soon as a chat message is received.
• AI Agent Node – Uses Gemini with built-in memory and tool orchestration to analyze the request.
• Database Utilities – Connect through the MCP client to explore schemas and metadata.
• Data Retrieval Tool – Calls the sub-workflow responsible for running queries and formatting results.

With this architecture, any query requiring database interaction is automatically routed by the AI to the right tool. The workflow then merges query results back into the response, giving users seamless, intelligent access to BigQuery.

Retrieve Sub Workflow in N8N:

The Sub-Workflow is dedicated to running queries in a controlled and reliable way:

• Trigger Node – Receives SQL statements and parameters from the Main Workflow.
• Code Node – Validates, sanitizes, and formats queries for BigQuery execution.
• BigQuery Node – Connects securely to BigQuery and runs the validated query.
• Aggregation Node – Processes, restructures, and prepares the results.
• Conditional Checks – Limit the output to fewer than 100 rows, preventing unnecessary load or data spillage.
• Return Node – Sends the cleaned JSON results back to the Main Workflow.

Built-in error handling ensures that faulty queries or connection failures are gracefully managed and reported, without disrupting the end-to-end workflow.

Steps in the Workflow

Main Workflow:

When Chat Message Received

• Entry point of the workflow.
• Captures user queries such as “Show me the top 10 records by table”.
• Sends input directly into the AI Agent.

AI Agent

Intelligent workflow component that understands natural language and interacts with data systems.

• Role: Core engine handling user queries.
• Integration: Connected to Google Gemini Chat Model for query interpretation.
• Memory: Uses Simple Memory to retain context for follow-ups.
• Function: Converts instructions into SQL and forwards them to MCP Client.
• System Message: Defines responsibilities for accurate SQL generation and context management.
• Returning intermediate steps lets you see how the AI reached its answer, making the process transparent and easier to debug.

Google Gemini Chat Model

• Create API Key: Go to Google Cloud Console → APIs & Services → Credentials, generate a key for Gemini (PaLM), and copy it.
• In your workflow, add Google Gemini Chat Model node.
• Click the pencil icon under Credential, paste your API key, and save.
• Choose the model (e.g., models/gemini-2.5-flash).

Simple Memory

• Stores recent queries and conversation history.
• Ensures continuity for multi-step conversations.

MCP Client.

• Add an MCP Client Node and connect it under Tools in the AI Agent.
• Receives SQL from the AI Agent and sends it to the MCP Server.
• Requires the MCP Server URL to be added in client settings.

MCP Server Trigger

• The MCP Server Trigger acts as the entry point for database query execution requests. Instead of running queries directly, it listens for incoming SQL tasks and forwards them securely to the right workflow.
• The MCP Server offers two distinct endpoints—Test (for experimentation) and Production (for live use). When deploying real workflows, connect the Production endpoint to the MCP Client to ensure stable and secure operations.

Retrieve Data (call n8n workflow tool)

• Under Workflow, choose the sub-workflow that contains your query logic. This sub-workflow should begin with an Execute Workflow trigger.
• In Workflow inputs to send, add the following expression:

{{ $fromAI(‘query’, `SQL query`, ‘string’) }}

This setup ensures that AI-generated SQL queries are dynamically executed in BigQuery with proper input handling.

Sub workflow:

Retrieve Data Sub-Workflow:

• Create a new workflow by clicking + New Workflow.
• Add the Execute Workflow Trigger node to the canvas and label it “Triggered from Main Workflow”.
• In the Workflow Input Schema, define a field named query and set its type to string.
• This configuration tells n8n that the sub-workflow expects a text-based parameter, which will carry the SQL query generated by the AI Agent in the Main Workflow.

Add a Code Node

• Place a Code Node on the canvas and link it to the Execute Workflow Trigger.
• Set the execution mode to Run Once for Each Item, so every input payload is validated individually.
• In the code, add a check to verify the query field. If it’s missing or blank, raise an error message: “No SQL query received from AI”. Otherwise, pass the JSON object forward unchanged.

This ensures that only valid SQL queries are processed, while malformed requests are stopped early in the workflow.

Configure the BigQuery Node

• Drag a BigQuery Node onto the canvas and connect it to the Code Node.
• Choose Execute Query as the operation.
• In the Query field, insert:
• so that the SQL statement is dynamically pulled from the input.
• Under Settings, turn on Continue on Fail / Error Output. This ensures the workflow doesn’t break when a query fails—such as from invalid syntax or missing tables—and instead routes the error for handling.
• Configure the outputs:
• Success Output → Connect to an Aggregate Node to transform valid query results.

This setup guarantees both successful and failed queries are handled gracefully, keeping the workflow resilient.

Add an Aggregate Node

• Drop an Aggregate Node onto the canvas and connect it to the BigQuery Success Output.
• In the configuration, set Aggregate Mode to “All item data into single list”.
• This merges all returned rows into one structured JSON array, making the results easier to pass back to the Main Workflow or transform further downstream.
• This way, multiple query results are neatly bundled into a single, clean response.

Add an IF Node

• Place an IF Node on the canvas (label it “Row Count > 100”) and connect it to the Aggregate Node.
• Define the condition as: {{ $json.count > 100 }}
• This checks whether the query returned more than 100 rows.
• False branch → Link to a Set Node that prepares the successful response payload.
• With this safeguard, oversized result sets are filtered out early, ensuring only manageable data is passed back to the Main Workflow.

Configure the Set Node (False Branch)

• Add a Set Node and connect it to the False output of the IF Node.
• In the node settings, choose JSON as the output mode.
• Use the expression: {{ $json }}
• to forward the aggregated results exactly as received from the previous node.
• This ensures that when the row count is within the safe limit, the complete query output is returned in clean JSON format without additional transformation.

Example: Fetching Column Names

User Input:
“List all the column names and data types in the usa_1910_2013 table in the bigquery-public-data.usa_names dataset?”

• Chat Trigger
  The workflow starts automatically as soon as the user sends the message.
• AI Agent Interpretation
  The AI Agent interprets the request and generates the SQL query needed to fetch the column names.
• MCP Server Trigger + Call Sub-Workflow

Passes the SQL request to the sub-workflow where BigQuery runs the query.

• AI Response Formatting
  The AI Agent receives the results and reformats them into a clean, human-friendly answer instead of raw database output.

Output:

Conclusion:

By combining Google Gemini, n8n, BigQuery, and MCP, we’ve demonstrated how natural language can be transformed into secure, executable SQL without exposing users to the complexity of databases. This layered approach separates AI intent from query execution, ensuring workflows remain modular, safe, and easy to maintain. With built-in validation, error handling, and row limits, the system not only safeguards resources but also delivers clean and structured outputs back to the user.

The result is a conversational data exploration framework where anyone can interact with BigQuery’s public datasets simply by asking questions. From schema discovery to fetching records, the workflow proves that data access can be intuitive, resilient, and scalable. In practice, this means faster insights, less manual effort, and a more inclusive way to explore data opening the door to a future where AI-driven analytics are both accessible and secure.