n8n

How to Connect n8n to Google BigQuery: Automate Data Warehouse Reporting (2025)

Rodrigo Ferreira

— 5 min read

Your data already lives in BigQuery, but the reporting still happens by hand: someone opens the console, runs a query, copies numbers into a slide, and pastes a summary into Slack. n8n turns that ritual into a workflow. This guide walks through connecting n8n to Google BigQuery with a service account, running a parameterized SQL query, scheduling it daily, and piping the result to Sheets, Slack, or an LLM that writes the narrative for you.

Why route BigQuery through n8n?

BigQuery is a warehouse, not a delivery system. It answers questions extremely fast, but it does nothing with the answer. n8n sits on top as the orchestration layer that decides when a query runs, where the result goes, and what happens next.

  • Scheduling without infrastructure. No cron server, no Cloud Functions to maintain. n8n's Schedule Trigger fires the query on any cadence you like.
  • Fan-out to many destinations. One query result can update a Google Sheet, post to Slack, email stakeholders, and write back to another BigQuery table in the same run.
  • Logic between steps. Add IF nodes, thresholds, and alerts. Only notify the team when revenue drops more than 10%, for example.
  • AI in the loop. Hand the raw rows to an LLM node and get a plain-English summary instead of a table nobody reads.

Step 1: Create a Google Cloud service account

n8n authenticates to BigQuery with a service account — a non-human Google identity with its own JSON key. This is the reliable path for automated, headless workflows.

  1. In the Google Cloud Console, open your project and go to IAM & Admin → Service Accounts. Click Create service account and give it a clear name like n8n-bigquery-reader.
  2. Grant it the roles it actually needs. For read-and-run reporting, assign BigQuery Data Viewer and BigQuery Job User. If your workflow inserts rows, add BigQuery Data Editor on the target dataset. Avoid handing it broad Owner or Editor roles.
  3. Open the new account, go to the Keys tab, click Add key → Create new key, and choose JSON. A key file downloads once — store it safely.
  4. Confirm the BigQuery API is enabled for the project under APIs & Services.

Why Job User matters: running any query in BigQuery creates a job. Data Viewer alone lets you see tables but not execute queries — the missing Job User role is the single most common reason a first n8n query fails with a permissions error.

Step 2: Add the credential in n8n

In n8n, go to Credentials → New and search for Google BigQuery API. You have two authentication choices:

  • Service Account (recommended for automation). Paste the entire contents of the JSON key file into the credential. n8n uses it to mint access tokens automatically — no browser login, no token that expires on you.
  • OAuth2. Fine for interactive testing, but it ties the workflow to a personal Google login and can require re-consent. For a daily scheduled report, prefer the service account.

Save the credential and give it a recognizable name. You will reuse it across every BigQuery node.

Step 3: Understand the BigQuery node operations

The n8n BigQuery node exposes a focused set of operations. The two that carry most reporting workflows are:

  • Execute Query — run a SQL statement and return the rows. This is your reporting workhorse.
  • Insert Rows — stream one or more rows into a table, mapping incoming n8n fields to columns. Use it to write computed metrics back to a warehouse table for history.

For Execute Query, set the Project ID, paste your SQL, and make sure it uses Standard SQL (the modern default). Each returned row becomes a separate n8n item, so downstream nodes can loop over them naturally.

Skip the build — grab the ready-to-import template

An n8n AI agent that queries your data and writes a plain-English insight report automatically — connect it to BigQuery, GA4, HubSpot or Sheets.

Get the Data Analytics AI Agent template →

Step 4: Write a parameterized SQL query

A good reporting query is date-scoped so it returns only fresh data each run. Here is a daily revenue rollup against a public-style orders table:

  • SELECT DATE(created_at) AS day, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS revenue
  • FROM `my_project.sales.orders`
  • WHERE DATE(created_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  • GROUP BY day ORDER BY day DESC

Because the WHERE clause uses CURRENT_DATE(), the query self-adjusts every day without editing. To inject values from earlier n8n steps, use expressions in the query field, for example {{ $json.region }}, and always validate or whitelist those inputs so you are not concatenating untrusted text into SQL.

Tame the bytes scanned

BigQuery bills on bytes scanned, not rows returned. A query on a partitioned table that filters on the partition column reads far less data. Two habits keep costs predictable:

  • Never SELECT * in a scheduled job — name only the columns you need.
  • Filter on the partition field (often a date) so BigQuery prunes partitions instead of scanning the whole table.

Step 5: Schedule the daily report

Replace the manual trigger with a Schedule Trigger node. Set it to run once a day at, say, 07:00 in your timezone. Wire it straight into the BigQuery Execute Query node. That is the entire engine — a trigger and a query. Everything after is delivery.

Step 6: Pipe results to Sheets, Slack, or email

With rows in hand, fan out to wherever people actually look:

  • Google Sheets — use the Google Sheets node's Append operation to log each day's numbers into a running tab that feeds a Looker Studio dashboard.
  • Slack — format the metrics into a short message and post to a channel. A Set or Code node builds the text; the Slack node sends it.
  • Email — the Gmail or SMTP node delivers the same summary to stakeholders who live in their inbox.

All three can run in parallel from the same query output, so one workflow serves every audience.

Step 7: Add an AI narrative layer

Numbers alone rarely land. Add an LLM node after the query and give it the rows plus a tight prompt: "You are a data analyst. Summarize yesterday's sales in three sentences, call out the biggest change versus the prior day, and flag anything that needs attention." The model returns a paragraph a busy executive will actually read, and you post that to Slack instead of a raw table.

This is where BigQuery, n8n, and an AI agent combine into something more than a scheduled query — a workflow that queries, interprets, and communicates on its own.

Cost and quota tips

  • Set a maximum-bytes-billed limit on heavy queries so a runaway scan cannot surprise your invoice.
  • Cache-friendly scheduling. Identical queries can hit BigQuery's result cache; varying the query slightly (like a live timestamp) forces a fresh, billed scan.
  • Prefer scheduled reads over polling. One daily run costs less and stays well inside concurrent-query quotas versus hammering the API every few minutes.
  • Aggregate in SQL, not in n8n. Let BigQuery do the GROUP BY; returning millions of raw rows to n8n is slow and pointless.

Common errors and how to fix them

  • "Access Denied: Permission bigquery.jobs.create" — the service account is missing the BigQuery Job User role. Add it at the project level.
  • "User does not have permission to query table" — grant BigQuery Data Viewer on the specific dataset the query touches.
  • Insufficient authentication scopes. When using a service account, confirm you pasted the full JSON key and that the BigQuery API is enabled; a truncated key or disabled API surfaces as a scope error.
  • "Not found: Dataset" — check the Project ID in the node and the fully qualified table name. BigQuery is region-aware, so a dataset in EU will not resolve if the job assumes US.
  • Insert Rows silently returns nothing new — the streaming buffer can take a moment to appear in query results; the write usually succeeded even if an immediate read does not show it.

Ready to automate?

Connecting n8n to BigQuery gives you the pipes; the last mile is turning query results into insight your team acts on. The Data Analytics AI Agent template does exactly that — it runs the queries, reads the numbers, and writes a plain-English report automatically, whether your data sits in BigQuery, GA4, HubSpot, or Sheets. Import it, point it at your credential, and skip the whole build.

Ready to automate? Get this template on Gumroad →

Read more