Every data team has now been asked some version of the same question: "can the AI just answer this from our data?" The instinct is to point a chat model at the warehouse and see what happens. That works in a demo and falls apart in production, for reasons that are entirely familiar to anyone who has run a pipeline: the model does not know which table is authoritative, it cannot see your row-level security, and nobody is logging what it read.
This tutorial covers the practical shape of an agent-facing data layer built on Fivetran-landed data: what the Model Context Protocol (MCP) actually gives you, how to model the tables an agent is allowed to touch, the governance guardrails to put in before anyone demos it to a VP, and where the costs hide. It assumes you already have Fivetran syncing into Snowflake, BigQuery, Databricks or a managed Iceberg lake.
What MCP is, in pipeline terms
MCP is an open protocol for describing tools and context sources to a model in a standard way. Instead of writing bespoke glue for every assistant, you stand up an MCP server that advertises a handful of capabilities — "list datasets", "describe this table", "run this parameterised query" — and any MCP-aware client (an IDE assistant, a chat product, an internal agent) can discover and call them.
For a data team the useful mental model is this: MCP is the API contract between your warehouse and a non-deterministic caller. Everything you already know about untrusted callers applies. You do not hand it your production credentials, you do not let it see raw PII, and you scope it to a defined surface.
Fivetran has moved in this direction with MCP support around its own metadata and pipeline surface, and the major destinations have their own agent-facing layers — Snowflake Cortex Analyst and Cortex Search, Databricks Genie and its model serving endpoints, BigQuery's Gemini integrations. These move fast enough that you should check current capability and plan availability against the vendor docs before you commit an architecture diagram. The pattern below is stable even as the products churn.
There are two distinct things an agent can be given, and conflating them is the most common design mistake:
- Metadata and pipeline context — which connections exist, when each table last synced, what the schema looks like, whether a sync is failing. This is what Fivetran's own metadata surface and the Platform Connector give you.
- Business data — the rows themselves, queried to answer a question.
Very different risk profiles. Start with the first.
Layer 1: give the agent pipeline context, not rows
The cheapest genuine win has nothing to do with answering business questions. It is letting an assistant answer "is this number stale?"
If you already land the Platform Connector into your warehouse, you have connection, sync_log, transformation_run and friends. Expose a small, read-only view over those to your agent layer:
create or replace view analytics.agent.pipeline_freshness as
select
c.connection_name,
c.connector_type,
c.destination_schema,
max(l.time_stamp) as last_sync_completed_at,
datediff('minute', max(l.time_stamp), current_timestamp()) as minutes_since_sync,
c.paused
from fivetran_log.connection c
left join fivetran_log.sync_log l
on l.connection_id = c.connection_id
and l.message_event = 'sync_end'
group by 1,2,3,6;
Now an assistant asked "why is yesterday's revenue low?" can check whether the Salesforce connector has synced since 14:00 before anyone opens a dashboard. Column and table names in the log schema shift between versions, so confirm yours rather than pasting this blind — but the shape holds, and this view is safe: no customer data, no PII, no write path.
Layer 2: build the semantic surface the agent queries
An agent pointed at raw Fivetran output will fail in predictable ways. Raw landed schemas are the source system's model: sfdc.opportunity with 340 columns, six of them named something like custom_field_c, soft-deleted rows still present behind _fivetran_deleted, and three different tables that each look like they could be "customers".
The fix is the same one that fixed BI: a curated layer. Build it with dbt or Fivetran Transformations, and hold it to stricter rules than your usual marts:
- One authoritative table per concept. If there are two candidate revenue models, the agent will pick the wrong one roughly half the time and be confident about it.
- Filter deletes and test states at the model level.
where coalesce(_fivetran_deleted, false) = falsebelongs in the model, not in the agent's prompt. See the deletes guide for the nuances. - Rename to business language.
opp_amt_cmeans nothing;opportunity_amount_usdis self-documenting and improves generated SQL more than any prompt engineering. - Describe every column. Descriptions in
schema.ymlare not documentation theatre here — they are the context the model reads. An empty description is a guess waiting to happen. - Pre-aggregate the common grain. A daily revenue-by-region table answers 60% of questions without the model inventing a join.
- Exclude PII by construction. Do not rely on instructions. If email addresses are blocked or hashed at the connector, they cannot leak through an agent no matter what it is asked.
A minimal, well-described model beats a broad one. Ten tables with excellent descriptions will outperform two hundred raw landed tables, and you can reason about what it is allowed to see.
Layer 3: the guardrails
This is the part that gets skipped and the part an audit will ask about.
A dedicated, minimal role. Never reuse the Fivetran service account or a human's credentials. Create a role that can read the curated schema and nothing else:
create role agent_reader;
grant usage on database analytics to role agent_reader;
grant usage on schema analytics.agent to role agent_reader;
grant select on all views in schema analytics.agent to role agent_reader;
-- no future grants on raw schemas, no write privileges, no access to fivetran_log.* beyond the curated views
Row-level security must live in the database. If tenant or region isolation is enforced by the agent's prompt, it is not enforced. Put row access policies on the curated views and pass the caller's identity through, so the same question from two users returns two correctly-scoped answers.
Cap the query. An agent that writes select * from events will find your largest table on its first try. Set a statement timeout, a result row limit, and run it on a small, separately-tracked warehouse or reservation so agent spend is visible rather than buried in the BI budget. Cost attribution is the thing finance will ask for in month two.
Log every call with the prompt. Store the question, the generated SQL, the caller and the row count. You need this for three separate reasons: debugging wrong answers, proving to security that access was scoped, and finding out which questions people actually ask — which is the single best input into what to model next.
Read-only, always. No writes, no DDL, no create table. If an agent needs to push a result somewhere, that goes through reverse ETL with its own review, not through the query path.
Unstructured sources belong here too
Agent questions are rarely purely tabular. "What did this customer complain about?" lives in support tickets, contract PDFs and call notes. Fivetran can land files and unstructured content for chunking and embedding — the RAG pipeline walkthrough covers that path — and the same governance rules apply, with one addition: carry source permissions into the index. A document an employee cannot open in the source system must not be retrievable through a vector search. Access control that is enforced in SharePoint and dropped at embedding time is a data breach with extra steps.
Evaluate it like a pipeline, not like a chatbot
"It seemed good in the demo" is not a release criterion. Build a small evaluation set — 30 to 50 real questions with known-correct answers computed in SQL by a human — and run it on every change to the semantic layer, the same way you run dbt tests. Track exact-match rate on the numbers, not vibes.
Expect the failure modes to be boring and fixable: ambiguous metric definitions, missing column descriptions, a date column that is actually a string, two tables that could answer the same question. Each one is a modelling bug with a modelling fix. That is the good news — this is data engineering work, not prompt alchemy.
A staged rollout that survives contact with users
- Week 1–2: expose pipeline freshness metadata only. Zero risk, immediate value, and it teaches the team how the MCP client behaves.
- Week 3–5: curate five to ten business tables with full descriptions, no PII, and a dedicated read-only role. Open it to the data team alone.
- Week 6–8: build the evaluation set from the questions the team actually asked. Fix the models the failures point at.
- Then, and only then: widen access, with row-level security, query caps, cost attribution and full prompt logging in place.
Teams that invert this — broad access first, guardrails later — end up either withdrawing the tool after a bad answer reaches a customer or, worse, leaving it running.
The unglamorous conclusion
An agent is only as good as the model underneath it, and the model is only as good as the pipeline underneath that. Stale syncs become confidently wrong answers. Undocumented columns become invented joins. Ungoverned access becomes an incident. None of the work in this article is novel — it is schema design, access control, testing and observability, which is exactly the work that makes a warehouse trustworthy for humans too.
SyncSpur builds these layers on Fivetran deployments: curated agent-facing models, scoped roles, freshness monitoring and the evaluation harness to prove it works. If you are being asked to make your data agent-ready and you are not sure the foundation is solid enough yet, get in touch — or read how we approach Fivetran + dbt transformations and data warehousing consulting.