MotherDuck's new MCP server gives us the opportunity to have a conversation with an AI models like Claude or ChatGPT and ask questions about our data that are directly transformed into SQL. The queries are executed against the actual data in our cloud data warehouse. This removes one more layer of indirection when it comes to bridging the gap between intent and insight. In this article, we want to explore the initial setup and run first queries to have a baseline for further investigations.
The Tool Chain
MCP is Anthropic's open standard for connecting AI assistants to external data sources and tools. MotherDuck is offering the MCP server and we are using opencode to create a MCP client. opencode is an open-source AI coding agent with a terminal user interface (TUI). It lets you switch between different models and sessions, and integrate multiple tools and agents.
The easy-to-use MCP support made it a good alternative for us to inspect the performance of different models and quickly create agents that could potentially help us to achieve better results. The client discovers available tools and uses them to create and execute SQL queries against the MotherDuck databases. The connection is read-only so that the agent will not be able to change data. The big advantage: no custom integration, no proprietary APIs, no coding.
Test Data and Context
We decided to use two different datasets that could eventually be combined to look for insights. One dataset that we know and one that we only have meta information about. This approach tests how well the system handles initial knowledge retrieval and whether MotherDuck MCP works for us to inspect unknown data.
The known dataset contains 15 years of historical weather data for Munich. The unknown dataset contains bike traffic measurements in Munich from 2008 onward, sourced from public records. The bike data comes with inconsistent column names, schema evolution, missing data, and a few other artifacts.
Initial Setup
The initial setup is straightforward. You need a MotherDuck account, opencode installed, and the MCP connection settings in your config file. After running the authentication command (opencode mcp auth <name-of-mcp-server>) and allowing the connection via the MotherDuck website in your browser, the connection establishes and you can execute prompts against your data in MotherDuck.
Context Management
The agent starts stateless with no knowledge of your database, schema, or contents. Initial context priming is necessary to create a baseline before asking specific questions about the data, like "which month has the most days with temperatures above 30 degrees celsius?". MotherDucks mentions this specifically in the documentation. Good first prompts create context that supports more specific queries later. Additionally, well-documented tables with COMMENT ON descriptions should help the model to interpret the data better based on the user's request.
The initial prompts inspired by MotherDuck's examples were "What tables and databases do I have access to in MotherDuck?", "Can you give more details about my tables in my_db?" and "Do all bicycle tables in my_db have the same schema?" We wanted to create an overview of the tables in my_db and highlight the schema changes in the tables containing bicycle data. Without any prior knowledge, the agent understood the assignment, inspected the schema and targeted the appropriate tables with tool calls to the MotherDuck MCP server.
Besides seeing the responses, we can also observe the thinking and SQL executed. This almost always follows the "thought, tool call, observation" pattern. When we export the session contents, we can also see the data returned from the MCP server. However, the data is often truncated due to its size in the Markdown export.
Deeper Analysis
With context established, we can test the real potential of this approach. We know that months in the middle of the year are almost always the hottest in Munich. To find out which months are the hottest and to unravel some trends, it is essential to frame our question with context. The documentation suggests including time ranges, filters, metrics, and an output format.
We created the following prompt: "Use the MotherDuck query tool to first identify the weather table schema. Then find the hottest month for every year by returning the year, month name, and maximum temperature. Additionally, analyze if the timing of the warmest month is shifting earlier or later in the year and calculate the year over year temperature change to see if peak heat is rising. Provide the results in a table followed by a brief summary of the climate trends.".
We received results in 15 seconds. Writing these queries manually would have taken several minutes at minimum. We could see the different queries being executed to group the data by year and month, find the hottest date overall for each month, and apply a mapping for the month from a number to its name. This worked not only for the initial gathering of data points but also for the comparison that was part of the task.
The query returned detailed year-over-year trends:
1| Year | Month | Max Temperature (°C) | YoY Change (°C) | Timing Shift | 2|------|-------|----------------------|-----------------|--------------| 3| 2015 | July | 36.5 | — | — | 4| 2016 | July | 34.6 | -1.9 | Same | 5| 2017 | June | 34.8 | +0.2 | Earlier | 6| 2018 | August | 36.2 | +1.4 | Later | 7| 2019 | July | 40.0 | +3.8 | Earlier | 8| 2020 | August | 36.2 | -3.8 | Later | 9| 2021 | June | 33.5 | -2.7 | Earlier | 10| 2022 | July | 37.7 | +4.2 | Later | 11| 2023 | July | 34.1 | -3.6 | Same | 12| 2024 | August | 33.6 | -0.5 | Later 13| 2025 | July | 35.1 | +1.5 | Earlier |
Additionally, we received an explanation that there is no clear shift when it comes to the occurrence of the hottest month, and no clear indication of a trend when it comes to peak temperatures. This makes sense given the limited time frame of only a few years, which is also supported by the average temperature not changing drastically from 2015 to 2025. If the time frame ranged back to 1950 or 1900, the answer would be drastically different.
Conclusion and Outlook
The setup proved straightforward, and creating context for analysis took minimal effort. More sophisticated prompts worked well, delivering complete answers to our questions. Asking clarifying questions after receiving the initial response was also seamless.
This concludes the first part of our multi-part series covering MotherDuck's MCP server. In the next part we will see how far we can push the natural language interface when it comes to retrieval performance and accuracy and find out where hallucinations can occur.
Was this post helpful?
More articles in this subject area
Discover exciting further topics and let the codecentric world inspire you.
10.2.2026
Blog authors
Niklas Niggemann
Working Student Data & AI
Do you still have questions? Just send me a message.
Hendrik Kamp
IT Consultant
Do you still have questions? Just send me a message.
Do you still have questions? Just send me a message.