AI has been on everyone’s lips in the IT industry for the past few years, and its development has been rapid. Application providers have also developed and incorporated more and more AI-enabled capabilities into their software. Matillion is no exception.
Matillion’s Data Productivity Cloud (DPC) includes several AI-based objects for use in developing loading pipelines. These include, among others, Copilot for natural language generation, AI Notes for automated documentation, and components for interacting with large language models (LLMs) such as OpenAI, Azure OpenAI, and Amazon Bedrock. In addition, Matillion DPC offers components for vector database operations (Pinecone, Snowflake) and for interacting with AI services such as Amazon Textract, Amazon Transcribe, and Azure Document Intelligence.
In this blog, we will look at some of the opportunities offered by the Matillion Data Productivity Cloud (DPC) from three perspectives:
- Using AI in loading pipeline development with Matillion’s Maia AI assistant,
- Using AI in loading pipeline documentation, and
- Using Snowflake’s Cortex AI functions in a business use case.
Using AI in Loading Pipeline Development
In Matillion, loading pipelines have traditionally been built by dragging loading components onto a canvas, then connecting them and defining the desired processing rules. This has been a clear, intuitive, and easy way to build loading pipelines. Matillion’s AI assistant, Maia, takes this one step further.
Maia is especially useful in cases where:
- You have only recently started using Matillion DPC or are otherwise inexperienced with ETL/ELT tools,
- The development environment is unfamiliar, or
- You want suggestions on how to solve a specific loading-related technical problem.
In the example below, Maia was instructed to create a loading pipeline that:
- Loads customer and order data from the SNOWFLAKE_SAMPLE_DATA database,
- Joins the table data, and
- Creates a table and loads the data into the newly created table.
Maia can be used for further development of the loading process or in individual loading components. In the example below, Maia is used to add metadata fields to the load and to perform calculations.
Using AI in Loading Pipeline Documentation
Documentation of loading pipelines often receives little attention or is not done at all. This frequently slows down further development of the loads, the fixing of errors, or, more generally, the understanding of the pipeline’s logic. Matillion DPC enables the automatic creation of descriptions for loading pipelines and individual components using AI, quickly and with just a few mouse clicks.
In the example below, the Add Metadata calculation component contains added fields and calculations that we want to highlight. With Matillion, automatic description generation provides a clear picture of the operations performed within the object.
By using Maia, it is possible to automatically create a complete description of the entire loading pipeline, including the loading steps and logic in detail. This feature enables effortless documentation of the entire pipeline and is highly beneficial when there is a need to understand the operation of a previously unfamiliar loading pipeline.
Snowflake Cortex AI Use Case
Matillion DPC also enables the use of Snowflake’s AI capabilities with ready-made objects that can be added as part of regular loading pipelines. This low-code / no-code approach makes AI accessible to a wider group of users and lowers the threshold for adopting AI capabilities, as no special skills are required.
Below is a simplified example where AI is used to create responses to customer feedback. The feedback has been received in three different languages (Finnish, Swedish, and English). The Matillion orchestration pipeline contains three different stages (transformation jobs), each using different Snowflake Cortex functions. The stages are:
- REVIEWS_1_TRANSLATE – Translates all feedback into English using the CORTEX.TRANSLATE function.
- REVIEWS_2_SENTIMENT – Determines the sentiment of the feedback (positive / negative) using the CORTEX.SENTIMENT function.
- REVIEWS_3_REPLY – Generates responses to the feedback using the CORTEX.COMPLETE function.
In the first stage, the Cortex Translate object is used to translate customer feedback, that is loaded into Snowflake and written in multiple languages, into English. In the Cortex Translate object, the column to be translated is specified, along with the source language (in this case, automatic detection) and the target language, which is English. After the translation, the columns are renamed, and the data is loaded into the database for the next stage.
In the second stage, the Cortex Sentiment object is used to identify the sentiment of the customer feedback. In the object’s settings, the column to which the sentiment analysis is applied is selected. Matillion creates a new column for the value, with a scale ranging from -1 (negative) to +1 (positive). Finally, in the loading pipeline, the necessary columns are renamed, and the data is loaded into the database for the final stage.
In the final loading pipeline, the processing performed in the previous stages is used to generate a response to the customer feedback. At the start of the pipeline, a Filter object is used to split positive and negative feedback into separate data streams, based on the sentiment analysis carried out in the previous stage. Both data streams are then directed to their own Cortex Completions objects. The following are defined for these objects:
- The model to be used for generating the response.
- A system prompt that provides context for creating the response.
- A user prompt that specifies the actual response.
- The input to be used for generating the response: in this case, the feedback.
After generating the response, the data streams are merged, transformed into a columnar format, the columns are renamed, and the data is loaded into the database, for example, to be used by customer service systems.
Summary
Matillion’s AI capabilities are not just technical accelerators, they are collaborative tools that bring data engineers, analysts, and business stakeholders onto the same page. They enable natural language interaction, automatic documentation generation, and the integration of diverse perspectives directly into data pipelines. Matillion bridges the gap between business and data systems, enabling understanding of actions without the need for technical skills such as code literacy. This opens the door to more agile development, faster iteration cycles, and, ultimately, more data products that are truly aligned with business priorities.
The AI objects in Matillion DPC that can be used within loading flows speed up and simplify the adoption of these capabilities in organizations. The Snowflake Cortex objects are a good example of this. Matillion DPC is used to build and orchestrate the loading pipeline, while the actual data processing is performed in Snowflake. This eliminates data transfers, supports real-time execution, and ensures that security practices are followed consistently throughout.
-Asko Ovaska
