How to get your BigQuery data ready for agentic AI

Land governed, typed source data in BigQuery with Airbyte, then let AI agents query it read-only through the Airbyte MCP and context layer.

|
Nov 16, 2026

Summarize with AI:

Airbyte loads data from your sources into typed BigQuery tables, and the Airbyte context layer lets AI agents run read-only SQL against that copy through the Airbyte MCP.

An AI agent that answers business questions needs somewhere reliable to look. Pointing it at a dozen SaaS APIs means a dozen sets of credentials, a dozen rate limits, and a dozen slightly different definitions of "customer." Pointing it at a warehouse means one place, one permission model, and data that has already been joined, typed, and checked. For many teams that warehouse is BigQuery.

This guide covers what it takes to make BigQuery a good home for agent workloads, and how Airbyte handles each step: loading data in, keeping it current, and giving agents governed, read-only access to it.

Why BigQuery data matters for agentic AI

Agents are good at turning a vague request ("which regions slowed down last quarter?") into a sequence of lookups. They are much less good at reconciling raw API payloads on the fly. A warehouse copy solves several problems at once:

  • One governed copy. Data from CRM, billing, ads, and product databases lands in datasets you control, under IAM roles you already manage. The agent sees what that copy contains, not whatever a live API happens to return.
  • SQL as the interface. Agents that can write SQL can aggregate, filter, and join across sources in one query instead of paging through results from several APIs.
  • Predictable cost and load. Queries hit BigQuery, not your production systems or the rate limits of third-party APIs.
  • History. Incremental syncs and append modes keep past states of records, so an agent can answer "what changed" as well as "what is."

What landing data in BigQuery means for agents

When Airbyte writes to BigQuery, the result is a set of tables an agent can reason about without guesswork:

  • Typed final tables. Airbyte writes typed columns matching the source schema. Nested objects and arrays map to BigQuery's JSON type, so they stay queryable instead of becoming opaque strings.
  • Consistent metadata columns. Every table includes _airbyte_raw_id, _airbyte_extracted_at, _airbyte_generation_id, and _airbyte_meta. An agent can use _airbyte_extracted_at to tell how fresh a row is, and _airbyte_meta records changes Airbyte made while typing the data.
  • Partitioned, clustered layout. Final tables are partitioned by day on _airbyte_extracted_at and clustered by that column plus the stream's primary keys. Queries that filter on time or on keys can scan less data.
  • Namespaces as datasets. Each Airbyte namespace maps to a BigQuery dataset, which gives you a natural boundary for grouping sources and granting access.

How Airbyte gets BigQuery data ready

Loading methods and authentication

The BigQuery destination supports two loading methods:

  • GCS staging (recommended). Airbyte writes files to a Google Cloud Storage bucket using an HMAC key and then runs load jobs into BigQuery. Buckets encrypted with customer-managed encryption keys are not supported for staging.
  • Batched Standard Inserts. Airbyte inserts records directly with no staging bucket.

On Airbyte Cloud, a service account key in JSON format is required. The service account needs the BigQuery User and BigQuery Data Editor roles. If you want connector jobs to run under a different project than the one holding your datasets, set the Job Execution Project ID, which keeps ingestion jobs from competing with other workloads for the same project quotas.

Two constraints to plan around: the dataset location is fixed once data has been written, and load jobs time out after 30 minutes.

Sync modes

BigQuery supports all five Airbyte sync modes:

  • Full Refresh Overwrite
  • Full Refresh Overwrite + Deduped
  • Full Refresh Append
  • Incremental Append
  • Incremental Append + Deduped

For agent workloads, Incremental Append + Deduped is usually the right default for entity tables such as customers, deals, or orders. The final table holds one current row per primary key, which is what an agent expects when it asks for "the current status." Incremental Append is useful for event-style data where history matters.

Change data capture and deletes

When the source uses change data capture (for example Postgres or MySQL with CDC enabled), the BigQuery destination exposes a CDC deletion mode setting. It controls whether rows deleted in the source are removed from the final table or kept and marked as deleted. Pick the behavior that matches the questions your agents will ask. If an agent needs to answer "which accounts were closed last month," keeping deleted rows with a marker is more useful than removing them.

Raw tables and schema

By default Airbyte writes directly to final tables. If you need the untyped records as well, you can enable legacy raw tables, which Airbyte stores in the airbyte_internal dataset. Most agent use cases only need the typed final tables.

How agents reach BigQuery data

Airbyte offers two layers on top of the data you load.

The Airbyte MCP. A hosted Model Context Protocol server at https://mcp.airbyte.com/mcp connects agents such as Claude, ChatGPT, or VS Code Copilot to your Airbyte organization. It signs in through OAuth and acts with your Airbyte permissions. A read-only variant is available by adding ?read-only=true to the URL. With it, an agent can build and troubleshoot pipelines, for example creating a connection from a source into BigQuery or reading sync logs when a load fails.

The context layer. The context layer has two independent parts that an organization admin can turn on in Airbyte Cloud:

  • Agent access. BigQuery is one of two destinations (Snowflake is the other) where agents can run read-only SQL. The MCP tool execute_external_sql_query accepts only SELECT statements and SHOW TABLES. Airbyte uses the credentials saved in the destination, so the agent never sees them, and the data it can read depends on what that service account can read.
  • Search. When a connection's source and destination are both agent connectors, Airbyte can index synced records for keyword, semantic, or hybrid search. Turning search on indexes subsequent syncs. For existing data, Airbyte can backfill the index from BigQuery; that backfill requires the destination to use service account key authentication.

A few practical notes from the docs:

  • The context layer is free for up to 100,000 direct reads and searches combined per organization per month. SQL queries and backfills use BigQuery compute, which Google bills to you.
  • Agent access returns data as of the latest sync. Search returns data as of the latest indexed sync.
  • The context layer is available in Airbyte Cloud only. It is not available on self-managed Airbyte Core, and not in workspaces whose data planes run on customer-owned infrastructure with Airbyte Flex.

What this unlocks

  • Agents that answer cross-source questions with one SQL query over a single governed copy.
  • Read-only access by design, enforced by the tool rather than by prompt instructions.
  • Freshness the agent can check, through _airbyte_extracted_at.
  • Lower load on source systems, since agents query BigQuery instead of calling SaaS APIs directly.
  • Pipeline maintenance through the same MCP connection: agents can check sync status and read job logs when data looks stale.

Getting started

  1. Create a BigQuery destination in Airbyte with a service account key and GCS staging.
  2. Connect the sources your agents need and choose Incremental Append + Deduped for entity streams.
  3. Ask an organization admin to turn on agent access for the BigQuery destination, and search where it fits.
  4. Connect your agent to the Airbyte MCP and start with the read-only URL.

Try Airbyte free or talk to sales about your BigQuery setup.

Frequently asked questions

Can an AI agent write to BigQuery through Airbyte?

No. Context layer agent access to BigQuery is read-only. Only SELECT statements and SHOW TABLES are allowed, so an agent can query synced tables but cannot insert, update, or drop anything.

Do I need a sync before an agent can query BigQuery?

Agent access queries the BigQuery destination with the credentials you saved in Airbyte, so it does not require a connection or a sync. In practice the tables an agent finds useful are the ones Airbyte connections have already loaded.

Which loading method should I choose for the BigQuery destination?

Airbyte recommends GCS staging. It writes files to a Google Cloud Storage bucket using an HMAC key and then loads them into BigQuery. Batched Standard Inserts is the alternative when you do not want a staging bucket.

Is the context layer available on self-managed Airbyte Core?

No. The context layer, including agent access and search, is available only in Airbyte Cloud. You can still run the Airbyte MCP locally with Core to manage pipelines, but direct reads and search are not part of that setup.

How does search relate to the BigQuery destination?

Search indexes data from connections where both the source and destination are agent connectors. If you turn search on for an existing connection that writes to BigQuery, Airbyte can backfill the index from BigQuery, which requires the destination to use service account key authentication.

Integrate with 700+ apps using Airbyte

Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.