How we built an MCP Server to create data pipelines

June 23, 2025

4 min read

Summarize with AI:

AI tools are becoming increasingly useful in software development, but they often lack direct integration with real-world developer workflows. MCP (Model Code Protocol) bridges that gap. It’s a lightweight protocol that allows developers to connect AI models to specific tools, scripts, and services—making it possible to automate meaningful tasks directly within the development environment.

Take Cursor, for example. It is an incredibly popular AI powered IDE. For a developer who is building data pipelines, they likely already use PyAirbyte, or similar libraries to create these pipelines in code. Traditionally, a developer would look up the documentation on what configuration parameters are required on a source and destination, read up on the framework documentation for handing streams, error and transactional management, before finally get down to writing the code.

With AI ,and the new PyAirbyte MCP server, this all goes away. If you are building pipelines in python, all you need is a simple prompt:

‍

The PyAirbyte MCP server is provided by Airbyte’s AI team. With the pace of innovation in the AI space, we are constantly experimenting with tools and frameworks. Our team’s exposure to the space allows us to share learnings and outcomes with the developer community. We do not currently offer official support for these projects, but encourage you to join the #airbyte-ai Slack channel to share feedback, collaborate with other developers building with AI, and connect directly with AI engineering within Airbyte.

Get Started with the PyAirbyte MCP Server

The PyAirbyte MCP server is a remote server hosted on Heroku. To add the server to Cursor, tap Settings > Cursor Settings > Tools & Integrations, and add a New MCP Server.

Note: If you are not running the latest version of Cursor, the settings location is Settings > Cursor Settings > MCP Tools

Paste in the following mcp.json, adding your OpenAI API key.



{
  "mcpServers": {
    "pyairbyte-mcp": {
      "url": "https://pyairbyte-mcp-7b7b8566f2ce.herokuapp.com/mcp",
      "env": {
        "OPENAI_API_KEY": "your-openai-api-key"
      }
    }
  }
}

‍

Save the config, and you should see a new server, pyairbyte-mcp with one tool enabled, generate_pyairbyte_pipeline. (you should see a green dot and “1 tools enabled”)

Once the server is connected and configured correctly the service knows to listen for chat prompts similar to “generate / create pipeline”.

Let’s try it out. Start a new chat adding the prompt:

“create a data pipeline from source-postgres to destination-snowflake”.

Notice the connector prefixes source- and destination-. These are how connectors are defined within the Airbyte connector registry. Specifying the prefix ensures the AI does not hallucinate and give you a destination configuration when you really wanted a source, and visa versa.

Cursor will interpret your request to run the MCP server. If it your first time prompting the server, you will asked for permission to execute the prompt. Go ahead and give it permission, allowing the MCP server to generate the code and implementation instructions for you.

‍

By default, Cursor can not create .env files, unless you have explicitly allowed this by modifying the global gitignore via Settings. Even if you do not allow direct writing of a .env, the MCP will provide all of the instructions for you to create your own and use the configuration provided.

And, that’s it. With a simple prompt, you just created an entire data pipeline. Why not try another prompt that chains a pipeline request with a common requirement such as visualizing data with popular frameworks such Hex, Evidence, or Streamlit?

“create a pipeline from source-faker to dataframe and visualize the data in a streamlit bar chart.”

The MCP server will create a pyairbyte_pipeline.py file which creates a dataframe and exposes it for use within your code base.

‍

And because you are invoking the PyAirbyte MCP server within Cursor chat context, AI will create the relevant Streamlit code for you, with full context of how to access the dataframe.

Under the Hood

As you can see from the quick get started guide above, the PyAirbyte MCP server is very easy to use and quick to set up. Under the hood of the server, there is some interesting things happening, that are worth diving into.

PyAirbyte MCP

The actual server implementation is a python app, deployed on Heroku. The app uses the FastMCP framework and @mcp.tool() annotation to expose tools for clients to call. The server currently only provides one tool, generate_pyairbyte_pipeline, but we have have plans on many more to come. As we add these tools to the server, all you need to do is stop and start your config within Cursor to read the new tools.

Looking at the code of the generate_pyairbyte_pipeline tool, you will notice that the tool returns a dictionary object. If you are building local MCP servers, you can return anything you want. However, for remote servers, this must be a Dictionary object.

‍



@mcp.tool()
async def generate_pyairbyte_pipeline(
    source_name: str,
    destination_name: str,
    ctx: Context # MCP Context object
    ) -> Dict[str, Any]:

Within the tool function, the server uses the OpenAI chat completion API in conjunction with vector embeddings of relevant documentation that has been loaded into OpenAI’s file storage. This approach allows the server to determine which libraries and coding patterns it needs to implement using the PyAirbyte framework, including what configuration settings are required for each Airbyte connector. The output of the OpenAI request is then used to complete code templates and generate instructions for the developer. The server currently uses gpt-4o as the LLM, but will likely change to openrouter in the near future to allow us to constantly utilize the best model for generating code.



 response = await openai_client.chat.completions.create(
            model="gpt-4o", 
            messages=[
                {"role": "user", "content": query}
            ],

OpenAI File Storage

A few months back, OpenAI launched file storage, with full embedding support, as part of a number of tool updates to the Response API. The PyAirbyte MCP server uses this vector store to provide OpenAI with context on how to write pipelines, best practices using the PyAirbyte library, and what are the configuration parameters required for each of the 600+ connectors supported in the Airbyte Connector Registry. With this context, the MCP server knows everything it needs to generate pipelines without relying on general knowledge the LLM obtained from the internet. In our experience, providing data context is critical to produce accurate results from any AI agent or MCP service.

‍

Remote MCP

Each client supports the MCP schema slightly differently. Cline, for example, with throws a schema error when you add the mcp.json. This is due to an unsupported env attribute. The PyAirbyte MCP server requires the user to pss in their openAI API key to use the service. Due to the dependency on env support via the mcp.json, the remote PyAirbyte Server is currently designed to only work with Cursor currently. In addition, Claude Desktop does not currently support remote servers at all, althought Claude Code does, but I have not verified that directly.

Summary

The new PyAirbyte MCP server is available now for you to add to Cursor. With a short prompt, you are now able to let AI generate a complete data pipeline using any of the hundreds of connectors offered by Airbyte. We will continue to release more tools designed for developers to leverage popular AI tools and frameworks and Airbyte to create an amazing developer experience building any app that needs data and context for their AI powered workflows. We would love your feedback on this service and what other tools you would find useful. Drop us a note in the new #airbyte-ai slack channel.

Limitless data movement with free Alpha and Beta connectors

Introducing: our Free Connector Program ->