Generate BigQuery SQL from Natural Language Queries using GPT-4o Chat

Give business users a chat box; get back valid BigQuery SQL and live query results.
The workflow:

1. Captures a plain-language question from a chat widget or internal portal.
2. Fetches the current table + column schema from your BigQuery dataset (via INFORMATION_SCHEMA).
3. Feeds both the schema and the question to GPT-4o so it can craft a syntactically correct SQL query using only fields that truly exist.
4. Executes the AI-generated SQL in BigQuery and returns the results.
5. Stores a short-term memory by session, enabling natural follow-up questions.

Perfect for analysts, customer-success teams, or any stakeholder who needs data without writing SQL.

---

⚙️ Setup Instructions

1. Import the workflow

   • n8n → Workflows → Import from File (or Paste JSON) → Save

2. Add credentials

   Service

   Where to create credentials

   Node(s) to update

   OpenAI

   <https://platform.openai.com> → Create API key

   OpenAI Chat Model

   Google BigQuery

   Google Cloud Console → IAM & Admin → Service Account JSON key

   Google BigQuery (schema + query)

3. Point the schema fetcher to your dataset

   • In Google BigQuery1 you’ll see:

     SELECT table_name, column_name, data_type
     FROM `n8nautomation-453001.email_leads_schema.INFORMATION_SCHEMA.COLUMNS`
     

   • Replace n8nautomation-453001.email_leads_schema with YOUR_PROJECT.YOUR_DATASET.

   • Keep the rest of the query the same—BigQuery’s INFORMATION_SCHEMA always surfaces table_name, column_name, and data_type.

4. Update the execution node

   • Open Google BigQuery (the second BigQuery node).
   • In Project ID select your project.
   • The SQL Query field is already {{ $json.output.query }} so it will run whatever the AI returns.

5. (Optional)Embed the chat interface

6. Test end-to-end

   • Open the embedded chat widget.
   • Ask: “How many distinct email leads were created last week?”
   • After a few seconds the workflow will return a table of results—or an error if the schema lacks the requested fields.
   • As specific questions about your data

7. Activate

   • Toggle Active so the chat assistant is available 24/7.

🧩 Customization Ideas

• Row-limit safeguard: automatically append LIMIT 1000 to every query.
• Chart rendering: send query results to Google Sheets + Looker Studio for instant visuals.
• Slack bot: forward both the question and the SQL result to a Slack channel for team visibility.
• Schema caching: store the INFORMATION_SCHEMA result for 24 hours to cut BigQuery costs.

---

Contact

• Email: [email protected]
• Website: https://ynteractive.com
• YouTube: https://www.youtube.com/@ynteractivetraining
• LinkedIn: https://www.linkedin.com/in/robertbreen