How to Use Fivetran MCP with Claude Code (and Stop Clicking Through Connector UIs)

How to Use Fivetran MCP with Claude Code (and Stop Clicking Through Connector UIs)

{ }

Claude Code MCP Client

MCP

Fivetran MCP Official Server

161

Tools exposed

8 min

End-to-end setup

4×

Faster than the UI

You are sitting in a Slack thread with the customer support lead at a marketplace company. They have sent over a list of HelpScout tables they want in the warehouse. They are nervous about cost: HelpScout writes a lot, and the wrong tables will blow through the company’s MAR budget.

In the old workflow, you would open the Fivetran UI, click through the connector setup, hand-tick 11 tables, untick the other 15, paste a config screenshot back into Slack to get a thumbs-up, then start the sync and pray you did not fat-finger anything. That is a 30-minute job punctuated by context switches, with no diff view, no undo, and no audit trail.

What I actually did: typed five sentences into a terminal. Eight minutes later the connector was running with exactly the schema my client asked for, the docs were written, and a dozen SQL examples were validated against live data.

The unlock was Fivetran’s official MCP server, which exposes 161 tools to any MCP-compatible client. I run it through Claude Code. This article walks through what that workflow looks like, where it shines, and the gotchas worth knowing before you turn it on.

What MCP is, in one paragraph

MCP stands for Model Context Protocol. It is an open standard from Anthropic that lets Claude connect to external tools and read or write data through them. Think of it like a plugin system for AI agents. The model does not simulate using a tool. It actually uses it. You ask Claude to list your Fivetran connections, and Claude makes a real API call, gets the real list back, and answers based on what it found.

Fivetran shipped an official MCP server earlier this year. Once it is registered with Claude Code, the agent can list connections, inspect destinations, modify schema configs, trigger syncs, set up webhooks, and roughly 150 other operations. It is the entire Fivetran REST API, exposed as something an agent can reach. Without MCP, Claude can write API client code and walk you through the steps. With the Fivetran MCP, it just does the steps.

If you have used Claude Code before, this is the same agent you already know. The difference is what it can reach. Without MCP it is a coding assistant. With Fivetran MCP it is an operator who can stand up data pipelines.

A real-world job: HelpScout to BigQuery

Here is the case that turned me into a believer.

I work as a fractional Head of Data for a marketplace client. Their customer support runs on HelpScout. The CS lead wanted ticket data in BigQuery so he could build dashboards beyond what their BI tool already had wired up. He sent me a written shortlist: 11 parent tables to keep, around 15 children to skip. The shortlist was carefully chosen to keep monthly active rows (Fivetran’s billing unit) under control. HelpScout’s child tables are noisy. Skipping them was the difference between a reasonable bill and a not-so-reasonable one.

The work that needed to happen, in order:

1. Get HelpScout’s OAuth credentials from the CS lead (he owns the HelpScout admin account)
2. Create the Fivetran connector pointed at the existing BigQuery destination
3. Run a schema reload to pull HelpScout’s table list into Fivetran
4. Apply the keep/skip list as a schema config patch
5. Unpause the connector and trigger the historical sync
6. Validate that data actually landed
7. Write up usage docs for the team
8. Send the CS lead the link

In the old workflow, this is half a day of UI clicks plus an hour of writing docs. The schema-config step alone is 26 individual checkboxes in the Fivetran UI, and there is no undo if you misclick. The validation step means jumping into the BI tool or BigQuery and writing one-off SQL. The doc step is a separate context switch into Notion.

Here is what I actually did.

Step 1. Got Fivetran’s MCP server registered in Claude Code at user scope. One config edit, one window reload. The server exposes 161 tools, all of which Claude can now call. This is a one-time setup that took under five minutes.

Step 2. Asked Claude to list my groups, then list connections in the client’s group. Three seconds later I had a clean inventory. I confirmed the destination ID (BigQuery in the warehouse project) and which group the new connector should attach to.

Step 3. Drafted the CS lead a Slack DM asking him to OAuth into HelpScout through the Fivetran UI. Fivetran’s HelpScout connector requires the OAuth dance and he was the only one with the right account access. I had Claude generate the message based on the Fivetran setup guide, including the specific “I’ll sync later” option to click so the connector would land in our account paused. The OAuth took 90 seconds on his side.

Step 4. Asked Claude to find the new connection in Fivetran and confirm its setup state. Claude polled list_connections_in_group and surfaced the new one. Schema status was ready, sync state was paused. Exactly what we wanted.

Step 5. This is where MCP actually earns its keep. Fivetran’s MCP runs in read-only mode by default (a smart safety choice). I toggled FIVETRAN_ALLOW_WRITES to true in the config, reloaded the window, and ran four writes in sequence:

• reload_connection_schema_config to make Fivetran query HelpScout for the table list (returned 26 tables)
• modify_connection_schema_config with the keep list (11 enabled, 15 disabled, schema-change handling set to ALLOW_COLUMNS so new columns flow but new tables do not auto-enable)
• modify_connection with paused: false to start the connector
• sync_connection with force: true to kick off the historical sync immediately

The schema config patch is the operation that would have been 26 manual checkboxes in the UI. As an API call, it is one JSON body with one field per table. Claude wrote that body, called the endpoint, and confirmed each table’s enabled state in the response.

Step 6. While the historical sync ran, I asked Claude to flip the MCP back into read-only. Two minutes later, Claude pulled get_connection_details and confirmed the sync had succeeded. 1.4 million rows landed across the 11 tables.

Step 7. I asked Claude to validate the data with real queries. It generated a dozen SQL examples (daily volume, status breakdown, custom-field analysis, first response time, agent rankings, CSAT distribution, AI-vs-human resolution, Jira escalations) and ran each one through the Metabase API to verify it returned sensible results. Every query worked on the first try except one CSAT query that exposed a small naming oddity in the connector’s schema. Claude flagged the issue, fixed the query, and noted the gotcha in the eventual doc.

Step 8. Claude wrote the Notion page. TL;DR up top, table inventory with row counts, the specific tables that were intentionally skipped and why, six caveats about how to read the data, the 12 validated SQL examples, a schema cheatsheet, and a section on joining to the production tables across BigQuery datasets. Page created under the existing “Data Sources & Tables” parent in the team’s Notion workspace.

Step 9. I drafted the CS lead a message announcing the connector was live, with the Notion link. He replied within hours asking whether he could join HelpScout customers to the product users table. I had Claude validate the join across datasets (81% of HelpScout customers match a product user by email), update the doc with a worked example, and draft the reply.

The whole thing, end to end, was about two hours of my time spread over a day. Most of it was waiting for the OAuth handoff and waiting for the historical sync. The actual hands-on engineering was under twenty minutes.

I want to be clear about what this is and is not. The agent did not make any of the judgment calls. The CS lead picked the table shortlist. I decided the schema-change handling. I read every API response before approving the next write. The model was the operator, not the architect. But the part where you write Python against the Fivetran API, or click 26 boxes in the right order, or context-switch between the connector UI, BigQuery console, BI tool, and Notion: that is gone.

There is also a documentation byproduct that mirrors what I have seen with other MCP-driven workflows. After the connector was live, the docs already existed. They were written from the actual setup, with the actual row counts and actual schema. Not “what we planned to do” but “what we did.” That is a quality of documentation most data teams never achieve because the writer is always one step ahead of the implementer.

Want to run this on your own Fivetran? The free setup guide walks through every step, including the read-write safety toggle and the prompts I used.

What the setup actually looks like

If you are thinking “this sounds great in theory but I bet it is a nightmare to install,” here is the actual setup, end to end.

You will need three things: a running Fivetran account (any plan), Claude Code installed locally, and a Fivetran API key + secret pair.

The Fivetran credentials take 30 seconds. Open your Fivetran admin panel, go to Account Settings, then API Config. Generate a new key pair, copy both values to a password manager. If your account has multiple users, generate the key under a service-account user with appropriate group permissions, not your personal account. Fivetran’s permissions model has both account-level and group-level roles. Account Analyst is the lowest role that can create connections without managing destinations, which is probably what you want for an agent-driven workflow.

The MCP server itself is a Python package. Clone the official repo, set up a virtualenv, pip install. One terminal session. The repo includes a server.py that reads from open-api-definitions/ (the full Fivetran OpenAPI spec) and dynamically generates 161 tools, one per endpoint. There is nothing magic about it: it is a thin layer between Claude Code and the Fivetran REST API.

Configuring Claude Code to use it is one block of JSON in your ~/.claude.json (the user-scope config, not ~/.claude/mcp.json, which is sometimes confused for the canonical location). You add an mcpServers entry pointing at the server.py path, pass three environment variables (API key, API secret, and FIVETRAN_ALLOW_WRITES=false), and reload Claude Code.

The first time you launch Claude Code with the MCP active, you can verify it worked by asking Claude to list your Fivetran groups. You should see the call hit the API and return your real groups. If you see that, you are done with setup.

The whole thing is a 30-minute job for someone who has never touched MCP before. For someone who has, it is closer to 10 minutes. The setup is genuinely the easy part. The interesting work is figuring out what to do with it.

I have put the entire setup, including troubleshooting for the common gotchas and the safe write-mode toggle pattern, in the free guide at valiotti.com/fivetran-mcp-guide. If something does not work, the guide has the answer. If it is something the guide does not cover, drop me a line and I will help.

Read-only by default (the safety pattern)

Fivetran’s MCP ships with a FIVETRAN_ALLOW_WRITES environment variable. By default it is false. With writes off, the agent has 161 tools but every write operation refuses with a permission error. List, get, search: those work. Create, modify, delete, sync: those do not.

This is the right default. An agent that can drop a connector or change a destination’s credentials by accident is not an agent you want loose in your stack. The read-only mode lets you give Claude the full inventory of your Fivetran setup for analysis, planning, and prompt-iteration without any risk of stateful changes.

The pattern I use: keep the MCP in read-only by default. When I have a specific write task, I flip it to true, reload Claude Code, run the writes, then flip it back to false and reload again. Reload-flip-write-flip-reload is overhead, but it forces a deliberate moment of consent before any state change. It is the same pattern people use for sudo in normal terminal work.

For team workflows, you can go further. Use a service account API key with restricted permissions for the day-to-day MCP. When you need elevated privileges, swap in a higher-permission key and immediately swap back. The MCP does not care which credentials it has; the API gate is what matters.

There is a third pattern worth mentioning for teams that want auditability. Fivetran’s API supports webhooks on connector events. You can wire a “every time a connection is created or modified, post to #data-changelog Slack” webhook. The agent can do all the writes you want, but every write becomes visible to the team in real time. I have not run this in production yet, but it is the next pattern I am planning to set up.

Schema configs as JSON, not 26 checkboxes

The Fivetran UI is fine for casual setups, but it falls down on schema configuration for high-table connectors. HelpScout has 26 tables. Salesforce has 700+. A connector with 100 tables means a schema config screen that is a wall of checkboxes, with no diff view, no version control, and no way to apply the same config to multiple environments cleanly.

The MCP turns the schema config into a JSON document. Here is the actual call I ran for HelpScout, simplified:

{
  "schema_change_handling": "ALLOW_COLUMNS",
  "schemas": {
    "helpscout": {
      "enabled": true,
      "tables": {
        "conversation_history": {"enabled": true},
        "conversation_thread_history": {"enabled": true},
        "customer_history": {"enabled": true},
        "...": "...",
        "team_user_history": {"enabled": false}
      }
    }
  }
}

That JSON is a contract. It is reviewable in a PR. It is reproducible across staging and prod. It is grep-able when you want to know which connector enabled a particular table. The UI has none of these properties.

The bigger unlock: an agent can write this JSON for you. You describe what you want in English (“enable the 11 tables on the stakeholder’s keep list, disable the 15 children, set schema-change handling to allow new columns but block new tables”), and Claude generates the right JSON, calls the right endpoint, and reads back the response to confirm.

Schema-change handling is the parameter most data teams under-think. There are three values: ALLOW_ALL (new schemas, tables, and columns auto-included), ALLOW_COLUMNS (new columns yes, new schemas/tables no), and BLOCK_ALL (nothing new is auto-included). For a MAR-conscious setup, BLOCK_ALL is safest but loses you new columns on existing tables. ALLOW_COLUMNS is the sweet spot for most cases: your existing tables stay current as the source evolves, but cost growth is bounded. The MCP lets you set this consistently across every connector without remembering to click the right radio button each time.

Validation as part of the same flow

The last piece that makes this workflow work is that validation happens in the same context as the setup.

When the HelpScout sync finished, I had Claude run the row-count query against warehouse.helpscout.__TABLES__. 1.4 million rows. Then sample queries to confirm joins work, custom fields populate, and timestamps are sensible. Each query ran in under a second through the BI API. Each result fed back into Claude’s context.

What this means in practice: the agent can answer “did the sync work” with actual numbers, not just “the sync state is succeeded.” It can answer “is there any obvious data quality issue” by spotting row counts that do not match expectations, or columns that are unexpectedly null. It can flag schema oddities (in our case, that rating_id in happiness_rating is the rating value, not a primary key) before they become bugs in someone’s dashboard.

Validation is the step most data teams skip because it is tedious. With an agent in the loop, it is free.

The other thing you get for free: the validated queries become the SQL examples in your eventual docs. Every query in the HelpScout doc was run against live data before it was published. None of them were guesses. That is a different quality of doc than the standard “here is how the schema looks, good luck.”

What MCP cannot do (yet)

I am not going to pretend this is a finished product. Here is what does not work as well as you would hope.

OAuth flows still need humans. Connectors that authenticate with OAuth require a user to click through the consent flow in the Fivetran UI. The agent can prepare everything else (group, destination, naming), but it cannot fully automate “create the connector from zero” unless you are using API-key-based auth (databases, some SaaS APIs). For OAuth connectors, the workflow becomes “agent does setup, human OAuths, agent finishes.” That is still way faster than fully manual, but it is not zero-touch.

Some endpoints have quirky responses. Fivetran’s API is mostly clean, but a few endpoints return data structures that do not match what the OpenAPI spec describes. The MCP catches most of these, but I have hit one or two where the agent had to retry with different parameters. Read the response carefully on writes you have never run before.

Pagination is not always automatic. Some list endpoints return up to 100 items by default and the MCP tool wrapper does not auto-paginate. If you have 700+ connectors, asking “list everything” might silently truncate. The fix is to pass limit=1000 (or the max) explicitly, or to hit the API directly when you need exhaustive listings.

Permissions confusion can be silent. If your API key is at the group level instead of account level, certain endpoints work and others do not, with error messages that do not always make this obvious. The fix is to know up front which scope your key has and prefer account-level keys for general agent use, with the trade-off that it is more permission than you might want.

Large result sets bloat context. A list_connections call that returns 200 connections will eat through Claude’s context window faster than you would like. The MCP does not summarize results; it returns the raw JSON. For exploratory work this is fine, but for production agents you want to either filter the request or have Claude hand off raw lists to a separate process and only pull summaries back.

None of these are dealbreakers. They are the actual texture of working with this tool. If you go in expecting magic, you will be disappointed. If you go in expecting “junior data engineer with API access who is a little overeager but does not know your business yet,” you will be very happy.

Debugging when things go wrong

A few patterns I have hit and how to fix them, since these will probably bite you too.

The MCP is not visible after restart. The most common cause is registering it in the wrong config file. Claude Code reads ~/.claude.json at user scope, not ~/.claude/mcp.json (despite the name). The CLI command claude mcp add fivetran -s user -- is the cleanest way to register at user scope. The second most common cause is a Python venv path issue: make sure the command field in your config points at the venv’s Python, not the system one.

Tool calls return empty errors. Fivetran’s API occasionally returns transient 503s or partial responses. If a write call returns a blank error, retry once before assuming the call actually failed. I have seen this on sync_connection specifically: the call appears to error but the sync starts anyway. Always confirm with get_connection_details before retrying writes that are not idempotent.

Schema config patches act unexpectedly. The schema config endpoint replaces the schema config; it does not merge. If you patch with only one table specified, the others stay at their previous state. But if you toggle enable_new_by_default while patching, you can flip the global default for the connector. Read the enable_new_by_default field in the response carefully. The MCP exposes the OpenAPI semantics faithfully, which means the same gotchas apply.

Connectors created in the UI do not appear in API list immediately. There is a few-second propagation delay. If you have just created a connector in the UI and your list_connections_in_group does not show it, wait 30 seconds and retry. I have not seen this take more than a minute.

Service IDs differ from what you would expect. HelpScout’s service ID in Fivetran is helpscout, not help_scout. Salesforce’s is salesforce, not sales_force. If you are scripting connector creation, use list_metadata_connectors to look up the canonical service ID first. The OpenAPI spec does not enumerate them.

These are the failure modes worth knowing about up front. None of them are blockers. They are the kind of friction you stop noticing after a week of use.

The bigger picture

Here is the thing I have been thinking about since I started using this.

Modern data teams are middle managers between requirements and infrastructure. A stakeholder needs HelpScout in the warehouse. The data team translates that into a Fivetran setup, a schema config, a backfill plan, a doc, and a notification when it is done. The translation work is the bottleneck. Every team I have worked with has a backlog of “obvious” connector requests that are sitting there because nobody has time to do the translation.

The unlock that MCP gives the data team is not faster Fivetran setup. It is that the translation step collapses. The stakeholder describes what they want, the agent does the translation, the data team reviews and approves. The work that used to be a week of analyst time becomes an afternoon of review time.

That is a structural shift. It is not “the agent is faster than the analyst.” It is “the analyst is now an architect, not a typist.” And the speed-up is not the headline benefit. The headline benefit is that the work that used to never happen because nobody had time now happens routinely.

This is what I have started calling the AI-native data stack. The components are not dramatically new: clean data warehouse, dbt or similar transformation layer, an EL tool with an MCP, a BI tool with an MCP, an agent that can reach across all of them. What is new is that the agent is a first-class citizen of the stack. You design for it from the start, not as an afterthought.

For Fivetran specifically, this means the operational interface stops being a UI you navigate by hand and starts being an API you describe in natural language. The UI does not go away. It is still where you do exploration, troubleshooting, and one-off lookups. But the day-to-day operations (creating connectors, modifying schemas, kicking off resyncs, monitoring sync status) move into the agent layer, where they are scriptable, reproducible, and reviewable.

If you are a Series A or B founder reading this and thinking “we do not need an AI agent yet, we need a real data team first,” I get the instinct. But I would push back. The companies setting up their data stack today have a small advantage that compounds over the next two years. They are building infrastructure that an agent can use immediately, instead of building infrastructure first and bolting on an agent later. The bolting-on phase is more expensive than getting it right the first time.

If you are a data engineer reading this and worried about your job, my honest take is: nothing here threatens the people who can architect systems and ask hard questions. It threatens the role of “person who clicks through the Fivetran UI for the fourth connector setup this week.” Those tasks are now agent work. The rest of data engineering becomes more interesting, because the parts you used to skip for time reasons (proper docs, reproducible configs, validated migrations) become free.

There is a related shift in how data teams should think about documentation. With an MCP-enabled agent, your connector configs, your dbt model docs, your column comments are not just human-readable metadata. They are context for the agent. A well-described connector is a connector the agent can manage. A cryptic schema config is a config the agent will guess wrong about. The investment in documentation that data teams have always under-prioritized starts paying off in a new way: it makes your agent smarter.

I am starting to use a simple heuristic when I evaluate data tooling for clients: can an MCP-enabled agent reach this? If yes, it is compatible with the future stack. If no, it is a candidate for replacement. Fivetran’s MCP is a bet on the same direction Metabase made earlier this year, and the bet looks correct.

What I would actually recommend

If you have a Fivetran account and you are curious, do this:

1. Spend 30 minutes setting up the MCP server with Claude Code, with FIVETRAN_ALLOW_WRITES=false. The free guide walks through every step.
2. Ask Claude to inventory your current Fivetran setup: which groups, which connectors, which destinations, when each connector last synced. Spot the connectors that have been failing or paused. This is the kind of audit you have been meaning to do for six months.
3. Pick one connector that is overdue for a schema cleanup. Have Claude propose a slimmed-down config based on usage. Review the proposal, flip writes on, apply it, flip writes off.
4. Then start experimenting with new-connector setups. The next time someone asks for a SaaS source in the warehouse, walk it through the agent.

That is the on-ramp. After about a week of this, you will have a feel for what the agent does well and where it falls down, and you can decide whether to invest more.

The free setup guide is here: valiotti.com/fivetran-mcp-guide. It includes the exact prompts I used, the JSON config for Claude Code, the read-write toggle pattern, and a list of starter prompts that work out of the box.

If you would rather skip the DIY and have us configure it for your team, including custom prompts, automations, governance patterns, and a session to train your data team on how to use it: book a 30-minute discovery call. Most engagements like this take a single working day to ship.