Accelerate Oracle Data Transforms development with AI-driven pipeline generation using Oracle AI Lakehouse MCP Server and Codex.
Introduction
Data engineering has traditionally required developers to spend significant time designing ETL pipelines, configuring transformations, and orchestrating workflows. While Oracle Data Transforms provides a powerful visual environment for building enterprise-grade data integration pipelines, creating these pipelines still involves multiple manual steps and familiarity with the platform.
What if you could simply describe your desired data pipeline in plain English?
With the Oracle AI Lakehouse MCP (Model Context Protocol) Server, this becomes a reality. By integrating AI coding assistants such as Codex with Oracle Data Transforms, developers can create sophisticated ETL pipelines through natural language. The MCP Server intelligently interprets user intent and translates it into Oracle Data Transforms Python SDK and REST API operations.
In this blog, we’ll walk through a practical scenario that demonstrates how an analyst can build a customer sales aggregation pipeline without manually designing every transformation.
Business Scenario
Consider an organization that maintains customer master information and sales transaction data in Oracle Database.
The ADMIN schema contains two operational tables:
- CUSTOMERS – Stores customer profile and master information.
- SALES – Stores customer sales transactions.
The business requires a reporting-ready target table named:
ADMIN.CUST_SALES_TARGET
that maintains consolidated customer sales metrics derived from the source tables.
Instead of manually developing an ETL pipeline inside Oracle Data Transforms, a data engineer wants to leverage AI to generate the complete pipeline through a simple conversation.
The following walkthrough showcases my interaction with the Codex extension in Visual Studio Code, integrated with the SQL Developer MCP (SQLcl) connection. Using natural language prompts, I communicate with my Oracle AI Lakehouse and Oracle Data Transforms environment, where the Oracle AI Lakehouse MCP Server translates user intent into Oracle Data Transforms Python SDK and REST API calls to automatically build and orchestrate ETL pipelines.
Using the sqlcl mcp tools to first get more understanding of the data in the tables then using it build my pipeline flow
Based on the business prompt provided, the Oracle AI Lakehouse MCP Server connects to the Oracle Data Transforms workbench and translates the natural language request into the appropriate ETL logic. It then automatically generates the corresponding ETL pipeline, including the required project, data flow, transformations, and workflow, which are rendered visually within the Oracle Data Transforms user interface.
ETL data flow is created like below based on the translated business logic in action
In this example, I use my preferred optimized LLM—GPT-5.6 Terra Medium—within the Codex extension. After the initial ETL pipeline is generated, I can continue the conversation using natural language to refine the implementation, modify transformations, update mappings, or extend the workflow based on evolving business requirements. The conversational experience is not limited to pipeline generation; through the Oracle AI Lakehouse MCP Server, I can also monitor pipeline and workflow executions, review job status and execution history, troubleshoot failures, and administer the Oracle Data Transforms workbench environment—all from a single AI-powered interface.
Conclusion
Oracle AI Lakehouse MCP Server introduces a new AI-assisted development paradigm for enterprise data integration. By combining conversational AI with Oracle Data Transforms, organizations can transform business requirements into production-ready ETL pipelines without manually configuring every component.
In this customer sales aggregation scenario, the developer focuses on describing the desired business outcome, while the MCP Server handles the creation of projects, data flows, transformations, mappings, and workflows using Oracle Data Transforms Python SDK and REST APIs.
As enterprises continue adopting AI-driven development practices, Oracle AI Lakehouse MCP Server empowers data engineers to build scalable, governed, and production-ready data integration pipelines faster than ever before.
Below are the reference documentation links for Python and REST API SDK’s Data Transforms exposes:
https://docs.oracle.com/en/database/data-integration/data-transforms/using/python-api-oracle-data-transforms1.html
https://docs.oracle.com/en/database/data-integration/data-transforms/dtapi/api-.html