Building the MotherDuck Remote MCP Server: A Journey Through Context Engineering and OAuth Proxies

2025/12/18 - 12 min read

What we learned about MCP tool design and MCP OAuth from building multiple iterations of our MCP Server, and watching agents run 4,000+ queries against MotherDuck at a hackathon.

The starting point

We released our open-source DuckDB MCP server on November 26, 2024 as one of the first MCP servers in the ecosystem. It's accumulated over 370 GitHub stars, 58 forks, and 10,000+ downloads last month. That was barely a year ago, but in MCP time it feels like an eternity.

What surprised us was the adoption. People were actually using AI assistants to query their production data warehouses. While we never intended it to be just a toy, it was surprising that it immediately became part of real workflows.

But the local server had limitations. Web-based AI clients like Claude.ai or ChatGPT.com can't spawn local processes. Less technical users had to manage tokens and config files. We wanted something that just works - connect your AI client to MotherDuck, authenticate once, and start querying, without the need to run an MCP server locally.

That meant building a remote MCP server, and bringing DuckDB clients to the cloud. The server logic itself seems straightforward - validate tokens, run queries, return results - Right? The real challenge: OAuth. Specifically, making OAuth work in an ecosystem where none of the major identity providers support what the MCP spec requires.

INFO: MCP Launch Blog For the product announcement, see Announcing the MotherDuck Remote MCP Server. For setup instructions, see our Remote MCP Server documentation.

Moving fast

Our first instinct was to move fast. We spun up a prototype on Vercel with serverless functions using Fluid Compute to run a fleet of DuckDB clients in SaaS mode that can execute read-only queries against MotherDuck on the user’s behalf. Within a week or two, we had something working.

One trade-off was clear from the outset: dual execution doesn’t work the same way as with regular MotherDuck clients, since there’s no DuckDB client running client-side. Experiencing the convenience that the Remote MCP access unlocked, we deemed it a reasonable trade-off for the moment - given that we also maintain an OSS variant of the MCP which has full dual execution capabilities.

The prototype showed the feasibility and confirmed that MCP provides a powerful and liberating way to work with data. Authenticating to MotherDuck seamlessly and being able to query data with natural language from virtually any device with a web browser or a Claude or ChatGPT app installed, felt a goal worth pursuing further.

Our Vercel MCP prototype also helped us discover client particularities: how long clients wait before timing out, what result sizes are useful before clients choke on in, and how differently each client handles auth. It also showed us that our existing Auth0 setup wouldn't work out of the box. Vercel's mcp-handler SDK let us prototype OAuth proxying, and we learned we weren't alone. The FastAPI-MCP docs described the same challenges: missing DCR support, inconsistent scope handling, audience requirements.

Having a working prototype was furthermore invaluable as a reference for further development. We decided not to pursue the Vercel-based solution for production, as it was important to us to own the infrastructure. We see the potential of MCP becoming a core part of how people interact with their data in the future, and we take that seriously. Owning the infrastructure means we can ensure the best possible client experience, control where data is processed, maintain strong security and authentication guarantees, and react quickly to any performance or reliability issues. It also allows us to build a better experience - like maintaining connection state - which a purely serverless deployment wouldn't support.

We believe MCP will be an integral part of MotherDuck for years to come.

The OAuth rabbit hole

The MCP specification mandates OAuth 2.1 with Dynamic Client Registration (DCR) (RFC 7591). We use Auth0. Auth0 doesn't currently support DCR in its standard offering. Neither does Google, GitHub, or Azure. As the Fabi.ai team put it:

TL;DR: Don't use Auth0 for MCP unless you enjoy debugging OAuth flows at 2 a.m.

Since we missed the DCR support from our native Auth provider, we built an OAuth proxy that bridges the gap. The proxy implements the DCR endpoints which MCP clients expect while proxying actual authentication through to Auth0.

The MCP spec uses two metadata documents to coordinate this:

Protected Resource Metadata (RFC 9728): served by the API, points clients to the OAuth proxy
Authorization Server Metadata (RFC 8414): served by our proxy, lists OAuth endpoints of our proxy, including the DCR endpoint

From the MCP client's perspective, it's talking to a fully DCR-compliant auth server. From Auth0's perspective, it's handling requests from our first-party application. The LLM clients (Claude, Cursor, etc.) are third-parties to us, but go through our proxy rather than directly to Auth0.

How the subsequent Auth flow works:
Before diving in, here's the full OAuth flow of our Remote MCP:

The client discovers our proxy, registers via the DCR layer to get a client_id, then goes through standard OAuth - but through our proxy, which also validates redirect URIs per RFC 8252. In the authorization step, we verify whether the redirect URI matches what was registered, and also set prompt=consent - per MCP Security Best Practices proxy servers using static client IDs must obtain user consent for each dynamically registered client. After token exchange, the client receives an audience-specific Access Token which is stored client-side. On subsequent calls, the MCP client provides this Access Token as Authentication Bearer Header. We validate it, exchange it for a user-specific short-lived MotherDuck token server-side, and establish a connection to MotherDuck in read-only mode on the user’s behalf.

If you're ever implementing MCP auth, pay close attention to the MCP Authorization Specification and Security Best Practices - they have been a very valuable resource for us - just like the MCP Inspector tool and rigorous testing with different real-world clients.

Build a remote MCP in one evening?

In November 2025, we helped organize the "America's Next Top Modeler" hackathon with 100+ participants, 63 data science questions, and messy enterprise data. One problem: our remote MCP server was not ready yet.

With the help of FastMCP (shoutout to Adam and team), we managed to build and deploy a one-off MotherDuck Remote MCP for the Hackathon within a couple of hours (check out the repo - beautifully simple). No OAuth - the MCP was serving a public dataset from a single MotherDuck service account using read scaling tokens.

The hackathon MCP processed 4,043 queries across 20+ unique users. Workload patterns confirmed Jordan's "Big Data is Dead" thesis: while the dataset was ~4GB, scanned bytes per query stayed in the KB-to-small-MB range, and result sets rarely exceeded a few KB (partly due to the MCP's result size limit). Lots of queries, small footprints. At peak, only four of 16 read scaling replicas were active. The setup quacked along without breaking a sweat. We could have lived with Pulses - our smallest compute tier, designed for lightweight workloads - instead of standard instances, and a smaller read-scaling fleet too.

NOTE: The MotherDuck advantage With agentic workloads, the big-data-small-queries pattern gets more pronounced - queries are small but volumes go up, as agents issue high-throughput exploratory queries to understand data before formulating solutions (a recent paper from UC Berkeley calls this "agentic speculation"). Systems not optimized for many-small-queries pay a hefty cost and efficiency penalty. MotherDuck's scaling and tenancy model happens to be particularly well suited for these types of workloads. If you want to learn more about it, feel free to get in touch - our team is happy to help advise on your specific workloads.

But the most interesting finding: winning teams used off-the-shelf tools like Claude Code, Cursor, and Codex with MotherDuck MCP connected. No custom agents needed.

Another fascinating detail: there was a human baseline - a data analyst with 20 years of experience but no AI tooling. The top agent-assisted teams scored 19, 17, and 17 correct answers. The human placed 8th with 12 correct answers in the same timeframe.

Designing MCP tools

Experience with our OSS DuckDB MCP and the Hackathon MCP taught us that tool design matters a lot. The OSS server only had a query tool, and agents often struggled with listing MotherDuck databases and schema exploration in general - database systems don't all do it the same way. For the Hackathon we added a show_tables tool to help agents with schema exploration. We also added a get_query_guide tool, in an attempt to help agents write effective DuckDB queries. However, we noticed that agents didn’t call this tool often, and that people actually had questions beyond that - e.g. on how to do semantic search in MotherDuck - which the agent couldn’t answer to a sufficient degree.

Context engineering

DuckDB has its own SQL dialect with features like Friendly SQL - GROUP BY ALL, SELECT * EXCLUDE, trailing commas, and more. MotherDuck adds its own statements on top. Not all agents are aware of these out of the box. We return mcp_server_instructions.md (check it out) via the instructions field on initialization - an enhanced version of our query_guide.md. This gives agents crucial context so they're proficient at writing DuckDB SQL from the start. The MCP spec has a Prompts concept that we tried using in the OSS MCP, but it's thoroughly ignored by most clients, and the Hackathon MCP showed that simply adding a tool for those instructions isn’t quite effective either. The instructions field on the initialize response actually works - supported clients actually surface it as context to the agent.

For cases where the agent gets stuck, we provide ask_docs_question, a RAG agent (powered by RunLLM) with access to both MotherDuck and DuckDB web documentation. This helps with effectively answering questions about more complex concepts, such as semantic search in MotherDuck. The interesting thing about ask_docs_question: it's an agent calling an agent. You ask a question, RunLLM searches the docs, synthesizes an answer, and returns it. Sub-agents are a powerful concept we want to explore further.

Minimize tool calls & keep responses lean

We added a few dedicated tools for schema exploration: list_databases, list_shares, list_tables, list_columns, and search_catalog. The idea behind search_catalog is that clients can use this tool to find relevant databases, tables or columns, without needing to exhaustively list the entire catalog into the Agent’s context. Every token counts on the client side. Notably, list_tables and list_columns also return table and column comments - by defining comments with COMMENT ON, you can help the agent develop a better semantic understanding of your data (see our AI Builder's Guide).

Guard against runaway queries

We limit result sets to 2,048 rows and truncate response messages to 50k characters - in both cases adding an explicit truncation notice so the agent knows data was cut off. We experienced clients struggling with result sets larger than this. Query execution is capped at 55 seconds to prevent client timeouts. While some clients support configurable timeouts, web-based clients like Claude.ai are more restrictive and time out after only 60 seconds. With a 55-second query timeout server-side, we ensure that the user’s duckling doesn't expend unnecessary resources after a client has already given up. Also it provides a path to provide an actionable response about the timeout to the client, with suggestions on how to proceed. Also it’s good to note that 55 seconds is actually a pretty long time in the DuckDB/MotherDuck world - the 99.95th percentile of queries on MotherDuck complete faster than that, and the 99th percentile sits just below 3 seconds. Progress notifications would be a nice solution for long-running queries, but they're not available in stateless HTTP-based MCP servers - something to explore in the future alongside a handoff mechanism back to our WebUI.

Why these specific tools? Mostly informed by experience with the OSS MCP and multiple iterations of the remote version. Automated MCP tool optimization is something we're interested in, and Opik's tool optimization looks like an intriguing approach we are keen to explore.

What's next

There's a lot we're excited to explore:

Expanding tool capabilities - write access via sandboxing with zero-copy clones, sharing data, admin tasks like inviting users, while thinking carefully about when and where to put the human-in-the-loop for consent
Richer context - leveraging MotherDuck's dual execution model and features like Instant SQL to give agents better understanding of your queries
Stateful features - elicitation and progress notifications for more interactive agent conversations
UI and handoff - MCP Apps (formerly MCP-UI) for interactive interfaces, and ways to seamlessly transition between agent and MotherDuck UI for long-running queries and tasks that call for the full UI experience

The MCP ecosystem is moving fast. Three weeks ago, the MCP 2025-11-25 spec was released - among other things, adding OpenID Connect as a requirement - and we're aiming to be compliant with it eventually. Auth0's Auth for MCP Early Access program is one sign that native DCR and OpenID Connect support is coming - when it's fully available, we're planning to transition.

It's been a journey from our first OSS MCP server to a production-ready remote service. The ecosystem is maturing, the tools are getting better, and we're excited to see where this is going and what people are going to build.

Try it yourself: The remote MCP server is now available at api.motherduck.com/mcp. Connect it to Claude, ChatGPT, Cursor or others, and start querying your data warehouse with AI.

Set up the remote MCP server →

Have questions or want to share your own MCP implementation stories? Join us in the MotherDuck Slack.

Special thanks to the Fabi.ai team and FastMCP team for sharing their MCP learnings, to Theory Ventures and Bryan Bischof for organizing ANTM, and to everyone building in the MCP ecosystem.

Till Döhmen, MotherDuck