How to Connect BigQuery to a SQL Editor: Step-by-Step Guide

Google BigQuery's built-in console is fine for ad-hoc queries — but it lacks AI assistance, query history, team collaboration, and scheduled reporting. Connecting BigQuery to a dedicated SQL editor unlocks all of that. Here is exactly how to do it.

What you will need

Before you start, make sure you have:

• A Google Cloud project with BigQuery enabled
• Owner or BigQuery Admin role in that project (to create a service account)
• A SQL editor that supports BigQuery — this guide uses WorkFlows, but the service account steps apply to any tool

Step 1: Create a service account in Google Cloud

A service account is how your SQL editor authenticates with BigQuery. You do not use your personal Google credentials — this is intentional. Service accounts can be scoped to minimal permissions and revoked independently.

Use least privilege: for SELECT-only access, BigQuery Data Viewer + BigQuery Job User is sufficient. Do not grant BigQuery Admin unless strictly required.

• Go to Google Cloud Console → IAM & Admin → Service Accounts
• Click Create Service Account
• Give it a descriptive name, e.g. workflows-bigquery-reader
• Click Create and Continue
• Grant the role BigQuery Data Viewer (read-only) or BigQuery User if your tool runs queries on your behalf
• Click Done

Step 2: Download the service account key (JSON)

Next, create a key the editor will use to authenticate:

Security note: never commit this JSON file to a git repository. Add it to .gitignore immediately. If it is ever exposed, revoke and regenerate the key.

• In the Service Accounts list, click the service account you just created
• Go to the Keys tab
• Click Add Key → Create new key
• Select JSON and click Create
• A JSON file downloads automatically — store it securely and treat it like a password

Step 3: Connect BigQuery in WorkFlows

With the key in hand, add the connection:

• Log into WorkFlows and go to Connections → Add Connection
• Select Google BigQuery from the connector list
• Enter your Google Cloud Project ID (find it in the top bar of the Cloud Console)
• Upload the JSON key file, or paste the JSON content directly
• Optionally specify a default dataset (e.g. analytics_prod)
• Click Test Connection — you should see success within a few seconds
• Click Save

Step 4: Run your first query

With the connection saved, open the SQL editor and select your BigQuery connection. The schema browser on the left populates with your datasets and tables. Try a simple query to verify everything works:

SELECT DATE(event_timestamp) AS event_date, COUNT(*) AS event_count FROM `your_project.your_dataset.events` WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) GROUP BY 1 ORDER BY 1 DESC

Replace your_project.your_dataset.events with your actual table path. BigQuery uses backtick notation for fully-qualified table names — most SQL editors handle this automatically when you click a table in the schema browser.

Step 5: Use AI to write BigQuery SQL faster

Once connected, WorkFlows' AI assistant knows your schema. You can type natural-language questions and it generates BigQuery-compatible SQL:

• Show me the top 10 users by total purchase value in the last 30 days
• What is the daily active user count for this week vs. last week?
• Find all orders where the shipping date is more than 3 days after the order date

Step 6: Schedule automated BigQuery reports

Once you have queries that work, turn them into scheduled reports: save the query as a named report, click Schedule and choose a frequency (daily, weekly, custom), set up delivery to email recipients or a Slack channel, and save. The report runs automatically on schedule and delivers fresh results — replacing the manual pattern of running queries, exporting to CSV, and pasting into a Google Sheet.

Common BigQuery connection issues and fixes

If something goes wrong, the cause is usually one of these:

• Access Denied: Project — the service account lacks job permission; add the BigQuery Job User role
• BigQuery API has not been used in project — enable it at Cloud Console → APIs & Services → Library → BigQuery API → Enable
• Invalid JSON key — the file may have been corrupted on copy-paste; re-download the original JSON
• Tables not appearing — ensure the service account has BigQuery Data Viewer on the dataset or at project level