Reduce your equipment downtime with Snowflake Cortex and Streamlit. (a RAG based approach)

CoAuthors: Tim Long

With the explosion of Generative AI, every executive is clamoring for a “Real” use case that their IT teams can implement yesterday. This is especially true in the manufacturing space. The age old questions still persist today.

• How do I increase my manufacturing yield?
• How do I reduce my Bill of Materials cost, without sacrificing quality?
• How can I reduce my exposure to over building the wrong product?
• How can I increase my product quality?
• How can I introduce new technology to help?
• How can I reduce machine downtime?

Those last two are the ones we want to go after. Large scale manufacturing is like any other technical field. If you experience too much brain drain from your line operators, maintenance technicians, or leads. You can end up in a situation where the only person that knew “how to do that”, just walked out the door. For this article we will focus on the maintenance technician. First let’s review the Snowflake Architecture we will use for this solution.

Snowflake offers many different AI tools natively in the platform. We want to focus on two of them. Streamlit in Snowflake and Snowflake Cortex.

Snowflake Cortex-

Is an intelligent, fully managed service that offers machine learning and AI solutions to Snowflake users. Snowflake Cortex capabilities include:

• LLM Functions: SQL and Python functions that leverage large language models (LLMs) for understanding, querying, translating, summarizing, and generating free-form text.
• ML Functions: SQL functions that perform predictive analysis using machine learning to help you gain insights into your structured data and accelerate everyday analytics.

Streamlit-

Is an open-source Python library that makes it easy to create and share custom web apps for machine learning and data science. By using Streamlit you can quickly build and deploy powerful data applications. For more information about the open-source library, see the Streamlit Library documentation.

Streamlit in Snowflake helps developers securely build, deploy, and share Streamlit apps on Snowflake’s data cloud. Using Streamlit in Snowflake, you can build applications that process and use data in Snowflake without moving data or application code to an external system.

Using these two features along with snowflake’s built in data engineering features we will build an Ai Guided Equipment Maintenance solution. Its goal will be to reduce machine downtime, by reducing the time a maintenance technician needs to spend on repairs. All the while introducing cutting edge technology to your manufacturing process.

Architecture-

First we will start with the repair manuals for our little AMR, the Otto 1500.

The repair manuals are uploaded to an internal stage in Snowflake. We use an open source python pkg to read them in with a Snowpark UDF. This enables us to chunk them into manageable sizes while reading them in. Why do we need manageable sizes? LLM’s and text embedding engines have context windows and the imported repair manuals need to fit into those windows. For this effort we used the e5-base-v2 encoding model. Embedding our chunked text looks like this:

----------------------------------------------------------------------
-- "Vectorize" the chunked text into a language encoded representation
----------------------------------------------------------------------  
CREATE OR REPLACE TABLE repair_manuals_chunked_vectors AS 
SELECT 
    file_name, 
    chunk_number, 
    chunk_text, 
    combined_chunk_text,
    snowflake.cortex.embed_text('e5-base-v2', combined_chunk_text) as combined_chunk_vector
FROM 
    repair_manuals_chunked;

The real power of this application come from the repair logs. Your organization likely has thousands of repair logs/records for the equipment i your facility. Putting these to work digitally is true power.

The final dataflow uses both the repair logs from your organization and the equipment maintenance manuals supplied by the manufacturer.

Behind the scenes we made some modifications to the repair logs for better prompt engineering. This will enable the LLM to give our maintenance technician the highest quality answer.

----------------------------------------------------------------------
-- Format the logs in a way that will be helpful context for the LLM
----------------------------------------------------------------------  
CREATE OR REPLACE TABLE repair_logs_formatted AS
SELECT
    *,
    CONCAT(
        'The following Problem was Reported for a ',
        equipment_model,
        ' AGV.\n\nProblem:\n', 
        problem_reported, 
        '\n\nResolution:\n', 
        resolution_notes) AS combined_text
FROM
    repair_logs;

Streamlit App-

The final solution runs multiple calls to UDF’s that invoke LLM’s in Snowflake and return the best answer combined and in a summarized state.

----------------------------------------------------------------------
-- Run both LLMs, combine the contents, and ask Snowflake Cortex to summarize
----------------------------------------------------------------------  
CREATE OR REPLACE FUNCTION COMBINED_REPAIR_LLM(prompt string)
RETURNS TABLE (response string)
AS
    $$
       WITH stacked_results AS
        (
            SELECT TOP 1 response FROM TABLE(REPAIR_MANUALS_LLM(prompt)) 
            UNION
            SELECT response FROM TABLE(REPAIR_LOGS_LLM(prompt))
        ),
        collapsed_results AS (
            SELECT 
                LISTAGG(response) AS collapsed_text 
            FROM 
                stacked_results
        )
        SELECT
            SNOWFLAKE.CORTEX.SUMMARIZE(collapsed_text) AS response
        FROM
            collapsed_results
    $$;

After a question has been asked, the app returns three responces each on their own tab.

1. A summarized answer based only on the equipment manuals provided by the manufacturer.
2. a summarized answer based only on the repair logs from previous repairs on the piece of equipment.
3. A combined answer using both repair logs and equipment manuals. This is also summarized and provides clear steps to resolve the issue asked by the technician.

Conclusion

Using data your organization already has and combining it with the power of LLM’s in Snowflake can reduce your machine downtime. Snowflake provides easy to use LLM’s that are only a SQL prompt away. You do not have to stand up additional compute, or process’s. These features are available in Snowflake natively.

The complete codebase and step by step instructions for this solution are available via the snowflake quick-starts page. Search for “Build Rag Based Equipment Maintenance App Using Snowflake Cortex”