How to get your Snowflake data ready for agentic AI
Load source data into Snowflake with Airbyte, keep it current with incremental syncs, and give AI agents read-only SQL access via the Airbyte MCP.

Airbyte loads and updates source data in Snowflake, and the Airbyte context layer gives AI agents read-only SQL access to it through the Airbyte MCP.
Snowflake is already where many teams keep the numbers they trust. That makes it a natural place to send AI agents: the data has been consolidated, roles and grants already limit who sees what, and analysts have built definitions on top of it. The question is how to keep that copy complete and current, and how to let an agent query it without handing it a password or write access.
This guide walks through both halves with Airbyte: loading data into Snowflake so it is agent-friendly, and exposing it to agents in a governed way.
Why Snowflake data matters for agentic AI
An agent that queries Snowflake inherits the work your data team has already done:
- Consolidation. CRM, billing, product, and marketing data sit side by side, so the agent can join them instead of stitching API responses together.
- Access control. Snowflake roles decide which databases and schemas a user can read. An agent that connects through a dedicated role sees only what that role can see.
- Stability. Warehouse queries do not consume source API quotas or add load to production databases.
- Time awareness. With incremental syncs and Time Travel retention, an agent can compare current and recent states of data.
What landing data in Snowflake means for agents
Airbyte's Snowflake destination produces tables that are straightforward for an agent to inspect and query:
- Typed columns. Source fields arrive as typed Snowflake columns. Objects become
OBJECT, and union or unknown types becomeVARIANT, so semi-structured fields stay queryable with Snowflake's JSON functions. - Decimal handling you choose. The destination lets you load decimals as
NUMBER(38,9)(recommended for precision) orFLOAT(the default). For revenue or billing data that agents will sum, precision matters. - Schemas as namespaces. Each Airbyte namespace maps to a Snowflake schema, a clean unit for grants.
- Automatic schema evolution. New source columns are added automatically. Removed columns stay in the table, so historical queries keep working.
Keep Snowflake's size limits in mind: VARCHAR values are limited to 16 MB and VARIANT values to 128 MB.
How Airbyte gets Snowflake data ready
Setup and authentication
The Snowflake destination docs include a setup script that creates a dedicated role, a service user, an X-Small warehouse with auto_suspend set to 60 seconds, and a database for Airbyte. Data is loaded through a Snowflake internal stage.
For authentication, use one of:
- Key pair authentication, the most common choice for service users.
- Programmatic access token (PAT). PATs expire after 15 days by default and can last up to 365 days, so plan for rotation.
Username and password authentication is deprecated. Snowflake is enforcing multi-factor authentication for password users, so existing password-based destinations should move to key pair or PAT.
Sync modes
All five Airbyte sync modes work with Snowflake:
- Full Refresh Overwrite
- Full Refresh Overwrite + Deduped
- Full Refresh Append
- Incremental Append
- Incremental Append + Deduped
For entity tables that agents query for "current state," use Incremental Append + Deduped. For event or ledger-style data, Incremental Append keeps every version.
Deletes and change data capture
When a source uses CDC, the destination's CDC deletion mode controls deletes:
- Hard delete (default): the row is removed from the final table.
- Soft delete: the row stays and is marked as deleted.
Soft delete is often the better choice for agents. A question like "how many subscriptions ended this quarter" needs the ended records to still exist.
Retention
The destination exposes a data retention setting for Time Travel, with a default of one day, and an option to create a transient database if you do not need Time Travel or fail-safe storage.
How agents reach Snowflake data
The Airbyte MCP. The hosted MCP server at https://mcp.airbyte.com/mcp lets agents work with your Airbyte organization using your Airbyte permissions. Agents can create connections into Snowflake, start syncs, and read job logs. Add ?read-only=true to the URL to limit the agent to read-only tools.
The context layer. In Airbyte Cloud, an organization admin can turn on two independent features:
- Agent access. Snowflake is one of two destinations, with BigQuery, where agents can run SQL. The tool
execute_external_sql_queryallows onlySELECTstatements andSHOW TABLES. Airbyte uses the credentials saved in the destination, so the agent never handles them, and results are limited to what the destination's Snowflake role can read. Queries use your Snowflake warehouse compute. - Search. Connections where both source and destination are agent connectors can feed a search index with keyword, semantic, and hybrid modes. Turning search on indexes subsequent syncs. Snowflake supports backfilling the index from existing data, which requires the destination to use username and password or key pair authentication.
Usage of direct reads and searches is free up to 100,000 combined per organization per month. The context layer is available only in Airbyte Cloud, not on self-managed Airbyte Core and not in workspaces on customer-owned Airbyte Flex data planes.
One governance note from the MCP docs: because direct reads use the stored connector credentials rather than each user's own, an Airbyte user could see records through an agent that their own Snowflake role would not show them. Scope the destination's role to the data you are comfortable exposing.
What this unlocks
- Agents that answer questions from the same tables your dashboards use.
- Read-only SQL enforced by the tool, not by prompt wording.
- A dedicated role and warehouse for Airbyte, so access and spend are easy to audit.
- Soft deletes and retention that let agents reason about what changed.
- The same MCP connection for pipeline upkeep when data looks stale.
Getting started
- Run the Snowflake setup script and create a key pair for the Airbyte service user.
- Create the Snowflake destination in Airbyte and connect your sources.
- Choose Incremental Append + Deduped for entity streams and soft delete where history matters.
- Have an admin turn on agent access for the destination, then connect your agent to the Airbyte MCP.
Try Airbyte free or talk to sales about Snowflake and agents.
Frequently asked questions
Which authentication should I use for the Snowflake destination?
Use key pair authentication or a programmatic access token. Username and password authentication is deprecated because Snowflake is enforcing multi-factor authentication for password users. Programmatic access tokens expire, 15 days by default and up to 365, so plan for rotation.
Can agents modify Snowflake tables through Airbyte?
No. Context layer agent access allows only SELECT statements and SHOW TABLES against Snowflake. Agents can read synced tables but cannot change them.
What happens to deleted source rows in Snowflake?
For sources that use change data capture, the destination's CDC deletion mode decides. Hard delete, the default, removes the row. Soft delete keeps the row and marks it as deleted, which is useful when agents need to reason about churn or closures.
Does the Snowflake destination handle schema changes?
Yes. It supports automatic schema evolution. New source columns are added to the destination table. Columns removed from the source are kept in Snowflake rather than dropped.
Can I backfill search from Snowflake?
Yes. Snowflake is one of two destinations, with BigQuery, that support search backfills. The destination must use username and password or key pair authentication for the backfill to run.
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.
