Stories by Karthik S Raman on Medium

Snowflake Cortex Series — Part 7 : Sentiment Analysis Reimagined with SENTIMENT Function

Karthik S Raman — Sat, 11 Jan 2025 05:28:18 GMT

Snowflake Cortex Series — Part 7 : Sentiment Analysis Reimagined with SENTIMENT Function

Welcome to Part 7 of our Snowflake Cortex Series! In this series, we’re exploring how Snowflake Cortex’s advanced functions are empowering businesses to extract actionable insights from data with ease.

In previous installments, we’ve explored how these functions empower businesses to harness the power of large language models (LLMs):

In Part1, we covered a high level overview of all the available Snowflake Cortex LLM functions namely the Complete function, Task-specific functions and the helper functions.

In Part 2, we introduced the COMPLETE function, demonstrating how it enables users to generate creative content, answer complex questions, and produce customized summaries effortlessly.

In Part 3, focused on the CLASSIFY_TEXT function, showcasing its ability to categorize unstructured text data, optimize workflows, and drive informed decision-making.

In Part 4, focused on EXTRACT_ANSWER function, revealing how users can retrieve precise, natural-language answers from structured and semi-structured datasets.

In Part 5, we looked at how PARSE_DOCUMENT function helps to extract valuable insights from documents using OCR and machine learning, enhancing search, operations, and data-driven decision-making.

In Part 6, we explored how SUMMARIZE function uses advanced AI to condense large text into concise, context-rich summaries for efficient analysis and decision-making.

In this instalment, Part 7, we turn our attention to the SENTIMENT function. This Blog will explore its core features, configurable parameters, and practical implementation strategies. By the end, you’ll be equipped with the knowledge to transform data queries into valuable insights faster than ever.

SENTIMENT Function:

In today’s data-driven world, understanding customer sentiment is crucial for delivering exceptional experiences, managing brand perception, and refining product offerings. The SENTIMENT function in Snowflake Cortex is a cutting-edge solution designed to address this need.

Leveraging advanced natural language processing (NLP) capabilities, this function identifies whether a given text expresses positive, negative, or neutral emotions, providing actionable insights for improving customer experience, brand reputation, and product feedback.

Whether it’s customer reviews, social media posts, or support tickets, this function excels at analyzing unstructured text data, uncovering emotional insights that might otherwise remain hidden. By seamlessly integrating into the Snowflake ecosystem, it ensures real-time, scalable sentiment analysis without the need for complex external processing, empowering organizations to make data-driven decisions faster and more effectively.

Key Applications:

The SENTIMENT function opens the door to numerous opportunities across a wide range of domains, enabling businesses to gain deeper insights and drive impactful decisions:

Customer Feedback Analysis:

· Identify customer satisfaction and pain points by analyzing sentiment in reviews, surveys, and support tickets for actionable improvements.

Social Media Monitoring:

· Track brand reputation and detect potential crises by evaluating sentiment in social media mentions and online discussions.

Employee Sentiment Analysis:

· Assess workplace morale and engagement through sentiment scoring of employee feedback and surveys.

Product Development Insights:

· Gauge customer reactions to new features or services by analyzing sentiment in feedback to guide development priorities.

Competitive Intelligence:

· Strengthen market strategies by analyzing sentiment in competitor reviews, news articles, and social media discussions to uncover valuable insights.

Understanding how SENTIMENT Function works

Syntax:

The basic syntax for using the SENTIMENT function is as follows:

SNOWFLAKE.CORTEX.SENTIMENT()

Required Parameters:

text: A string containing the text for which a sentiment score should be calculated.

Returns:

A floating-point number ranging from -1 to 1 (inclusive) that represents the sentiment of the text, with negative values indicating negative sentiment, positive values reflecting positive sentiment, and values near 0 signifying neutral sentiment.

Real-World Applications of the SENTIMENT Function:

The SENTIMENT function delivers tangible business value through practical applications in various real-world scenarios. Here are some key examples of its impactful use cases.

Demo 1: Analyzing Customer Reviews from Customers for e-commerce retailer to determine Customer Sentiment

E-commerce retailers often rely on customer reviews to measure satisfaction, uncover product issues, and enhance their offerings. The SENTIMENT function simplifies this process by automatically categorizing reviews as Positive, Negative, or Neutral, based on sentiment scores.

By leveraging this function, businesses can:

· Quickly gauge customer sentiment across their product portfolio.

· Identify recurring pain points or emerging trends.

· Adjust marketing strategies, refine product designs, and improve overall customer experiences to foster brand loyalty.

A step by step guide for the demo 1:

Table Creation: DDL statement to create the table required for the demo.

CREATE OR REPLACE TABLE PRODUCT_REVIEWS (
    REVIEW_ID INT,
    PRODUCT_ID INT,
    CUSTOMER_ID INT,
    REVIEW_TEXT STRING,
    REVIEW_DATE DATE
);

2. Loading sample data : Insert statements to load data into the PRODUCT_REVIEWS table.

INSERT INTO PRODUCT_REVIEWS (REVIEW_ID, PRODUCT_ID, CUSTOMER_ID, REVIEW_TEXT, REVIEW_DATE)
VALUES 
(1, 101, 1, 'This product is absolutely amazing! It arrived on time and was exactly as described. The quality exceeded my expectations, and it’s been working perfectly. I’m thrilled with my purchase and will definitely buy from this brand again. Highly recommend it!', '2024-12-01'),
(2, 101, 2, 'I’m really disappointed with the product. It didn’t match the description, and after using it for a week, it broke. The customer service was also unhelpful, and I feel like I wasted my money. Definitely not satisfied with this purchase.', '2024-12-02'),
(3, 102, 3, 'The product is okay. It does what it says but nothing special. I was expecting better performance given the price. It’s fine for now, but I might look for alternatives next time.', '2024-12-03'),
(4, 103, 4, 'This blender is fantastic! It blends everything smoothly, even tough ingredients. The build quality is sturdy, and cleaning is a breeze. It’s exactly what I needed for my morning smoothies. I love it!', '2024-12-04'),
(5, 104, 5, 'I had high hopes for this jacket, but the fabric feels cheap, and it’s not as warm as advertised. The sizing was also off, and it didn’t fit me well. Overall, I wouldn’t recommend it.', '2024-12-05'),
(6, 105, 6, 'I am so happy with these headphones! The sound quality is top-notch, and they’re incredibly comfortable. I can wear them for hours without any discomfort. Worth every penny!', '2024-12-06'),
(7, 106, 7, 'The shoes are just okay. They look nice, but the comfort level isn’t what I expected. After wearing them for a few hours, my feet started to hurt. Not a bad product, but it could be better.', '2024-12-07'),
(8, 107, 8, 'I love this book! It’s well-written, informative, and engaging. I couldn’t put it down. A must-read for anyone interested in personal development. Highly recommend!', '2024-12-08'),
(9, 108, 9, 'I’m not happy with this camera. The picture quality is subpar, and the battery life is disappointing. It’s not worth the price, and I’ll be returning it. Definitely a letdown.', '2024-12-09'),
(10, 109, 10, 'This kitchen appliance is decent. It works well, but there’s nothing extraordinary about it. I was expecting more advanced features for the price, but it’s a functional product.', '2024-12-10');

3. Validating the data loaded:

4. Leveraging the SENTIMENT function to analyze the data in PRODUCT_REVIEWS table.

WITH SENTIMENT_SCORE AS (
    SELECT REVIEW_TEXT, 
           SNOWFLAKE.CORTEX.SENTIMENT(REVIEW_TEXT) AS SENTIMENT_SCORE
    FROM PRODUCT_REVIEWS
)
SELECT REVIEW_TEXT,
       SENTIMENT_SCORE,
       CASE
           WHEN SENTIMENT_SCORE > 0.3 THEN 'POSITIVE'
           WHEN SENTIMENT_SCORE < -0.3 THEN 'NEGATIVE'
           ELSE 'NEUTRAL'
       END AS SENTIMENT_GROUP
FROM SENTIMENT_SCORE;

The above query analyzes customer reviews in the PRODUCT_REVIEWS table by calculating the sentiment score for each review using the SNOWFLAKE.CORTEX.SENTIMENT function.

Each review is assigned a sentiment score, which is used to classify the sentiment:

· Scores greater than 0.3 are labeled as POSITIVE.

· Scores less than -0.3 are labeled as NEGATIVE.

· Scores between -0.3 and 0.3 are labeled as NEUTRAL.

The result includes the review text, sentiment score, and its corresponding sentiment category.

Query Output

Demo 2: Analyzing Semi Structured Text information from Customer Support Tickets Data

Businesses can analyze chat conversations to gauge the emotional tone of customer interactions by leveraging SENTIMENT function to analyze text data in semi-structured JSON format, specifically from customer support chat information to determine Customer Sentiment.

A step by step guide for the demo 2:

Table Creation: DDL statement to create the table required for the demo.

CREATE OR REPLACE TABLE CUSTOMER_SUPPORT_TICKETS (
    TICKET_ID INT,
    CUSTOMER_NAME VARCHAR(100),
    AGENT_NAME VARCHAR(100),
    TICKET_DATE DATE,
    CHAT_LOG VARIANT
);

2. Loading sample data : Insert statements to load data into the CUSTOMER_SUPPORT_TICKETS table.

INSERT INTO CUSTOMER_SUPPORT_TICKETS (TICKET_ID, CUSTOMER_NAME, AGENT_NAME, TICKET_DATE, CHAT_LOG)
SELECT
    4, 
    'David Harris', 
    'Olivia Brown', 
    '2023-12-04', 
    PARSE_JSON('[
        {"timestamp": "2023-12-04T11:10:00Z", "role": "customer", "message": "Can you help me track my shipment?"},
        {"timestamp": "2023-12-04T11:10:30Z", "role": "agent", "message": "Of course, David. Could you provide your tracking number?"},
        {"timestamp": "2023-12-04T11:11:00Z", "role": "customer", "message": "It''s 987654321."},
        {"timestamp": "2023-12-04T11:11:30Z", "role": "agent", "message": "Thank you! Your package is expected to arrive by December 6th."},
        {"timestamp": "2023-12-04T11:12:00Z", "role": "customer", "message": "Perfect! Thank you for your assistance!"}
    ]')
UNION ALL
SELECT
    5, 
    'Eve Martin', 
    'Lucas King', 
    '2023-12-05', 
    PARSE_JSON('[
        {"timestamp": "2023-12-05T15:30:00Z", "role": "customer", "message": "I want to cancel my recent order."},
        {"timestamp": "2023-12-05T15:30:30Z", "role": "agent", "message": "I''m sorry to hear that, Eve. May I know the order number?"},
        {"timestamp": "2023-12-05T15:31:00Z", "role": "customer", "message": "The order number is 56789."},
        {"timestamp": "2023-12-05T15:31:30Z", "role": "agent", "message": "I have canceled the order successfully. You will receive a refund soon."},
        {"timestamp": "2023-12-05T15:32:00Z", "role": "customer", "message": "Thanks! I appreciate your help."}
    ]')
UNION ALL
SELECT
    6, 
    'Grace Lee', 
    'Ethan Clark', 
    '2023-12-06', 
    PARSE_JSON('[
        {"timestamp": "2023-12-06T10:00:00Z", "role": "customer", "message": "Can I get a refund for my damaged item?"},
        {"timestamp": "2023-12-06T10:00:30Z", "role": "agent", "message": "I''m sorry for the inconvenience. Could you upload a photo of the damage?"},
        {"timestamp": "2023-12-06T10:01:00Z", "role": "customer", "message": "Sure, here is the photo."},
        {"timestamp": "2023-12-06T10:01:30Z", "role": "agent", "message": "I see the damage. I will process the refund and send a return label."},
        {"timestamp": "2023-12-06T10:02:00Z", "role": "customer", "message": "Great, thanks for the quick resolution."}
    ]')
UNION ALL
SELECT
    7, 
    'Harry Harris', 
    'Isla White', 
    '2023-12-07', 
    PARSE_JSON('[
        {"timestamp": "2023-12-07T14:00:00Z", "role": "customer", "message": "I am having issues with my internet connection."},
        {"timestamp": "2023-12-07T14:00:30Z", "role": "agent", "message": "Sorry to hear that, Harry. Can you confirm if all lights on your router are on?"},
        {"timestamp": "2023-12-07T14:01:00Z", "role": "customer", "message": "Yes, they are, but the internet is still not working."},
        {"timestamp": "2023-12-07T14:01:30Z", "role": "agent", "message": "It seems there might be a service outage in your area. Let me confirm."},
        {"timestamp": "2023-12-07T14:02:00Z", "role": "customer", "message": "Thanks for checking. I''ll wait for an update."}
    ]')
UNION ALL
SELECT
    8, 
    'Ivy Turner', 
    'William King', 
    '2023-12-08', 
    PARSE_JSON('[
        {"timestamp": "2023-12-08T09:00:00Z", "role": "customer", "message": "Can you assist me with updating my billing address?"},
        {"timestamp": "2023-12-08T09:00:30Z", "role": "agent", "message": "Certainly, Ivy. Please provide the new billing address."},
        {"timestamp": "2023-12-08T09:01:00Z", "role": "customer", "message": "The new address is 123 New St, Springfield, IL."},
        {"timestamp": "2023-12-08T09:01:30Z", "role": "agent", "message": "Thank you! The address has been updated successfully."},
        {"timestamp": "2023-12-08T09:02:00Z", "role": "customer", "message": "Awesome, thank you for the help!"}
    ]')
UNION ALL
SELECT
    9, 
    'John Davis', 
    'Lily Roberts', 
    '2023-12-09', 
    PARSE_JSON('[
        {"timestamp": "2023-12-09T13:20:00Z", "role": "customer", "message": "I''m unable to access my account. Can you help?"},
        {"timestamp": "2023-12-09T13:20:30Z", "role": "agent", "message": "I''m sorry for the trouble, John. Can you confirm your username?"},
        {"timestamp": "2023-12-09T13:21:00Z", "role": "customer", "message": "My username is johnd123."},
        {"timestamp": "2023-12-09T13:21:30Z", "role": "agent", "message": "Thank you! It seems there was an issue with your password. I''ll reset it for you."},
        {"timestamp": "2023-12-09T13:22:00Z", "role": "customer", "message": "Thanks for fixing it so quickly."}
    ]')
UNION ALL
SELECT
    10, 
    'Rachel Green', 
    'Mia Garcia', 
    '2023-12-10', 
    PARSE_JSON('[
        {"timestamp": "2023-12-10T08:45:00Z", "role": "customer", "message": "My order status says delivered, but I haven''t received it yet."},
        {"timestamp": "2023-12-10T08:45:30Z", "role": "agent", "message": "I''m sorry for the confusion, Rachel. Let me check the delivery details."},
        {"timestamp": "2023-12-10T08:46:00Z", "role": "customer", "message": "I appreciate it, Mia."},
        {"timestamp": "2023-12-10T08:46:30Z", "role": "agent", "message": "It looks like the package was mistakenly marked as delivered. I''ll arrange for it to be re-shipped immediately."},
        {"timestamp": "2023-12-10T08:47:00Z", "role": "customer", "message": "Thanks for your help. I''ll be waiting for the new shipment."}
    ]');

3. Validating the data loaded:

4. Leveraging the SENTIMENT function to analyze the semi-structured data in CUSTOMER_SUPPORT_TICKETS table.

WITH SENTIMENT_SCORE AS (
    SELECT CHAT_LOG, 
           SNOWFLAKE.CORTEX.SENTIMENT(CHAT_LOG) AS SENTIMENT_SCORE
    FROM CUSTOMER_SUPPORT_TICKETS
)
SELECT CHAT_LOG,
       SENTIMENT_SCORE,
       CASE
           WHEN SENTIMENT_SCORE > 0.3 THEN 'POSITIVE'
           WHEN SENTIMENT_SCORE < -0.3 THEN 'NEGATIVE'
           ELSE 'NEUTRAL'
       END AS SENTIMENT_GROUP
FROM SENTIMENT_SCORE;

The above query analyzes customer support chat logs from the CUSTOMER_SUPPORT_TICKETS table by calculating the sentiment score using the SNOWFLAKE.CORTEX.SENTIMENT function.

Each chat conversation is assigned a sentiment score, which is used to classify the sentiment:

· Scores greater than 0.3 are labeled as POSITIVE.

· Scores less than -0.3 are labeled as NEGATIVE.

· Scores between -0.3 and 0.3 are labeled as NEUTRAL.

The result includes the CHAT_LOG, sentiment score, and its corresponding sentiment category.

Query Output

Conclusion:

In conclusion, the Snowflake Cortex LLM function SENTIMENT empowers organizations across various industries to unlock valuable insights from their text data. By seamlessly integrating advanced NLP capabilities within the Snowflake ecosystem, SENTIMENT enables real-time, scalable sentiment analysis for a wide range of applications, including: identifying customer satisfaction and pain points through analysis of reviews, surveys, and support tickets; tracking brand reputation and detecting potential crises by evaluating sentiment in social media; assessing workplace morale and engagement through sentiment scoring of employee feedback; gauging customer reactions to new features or services to guide product development priorities; and gaining market insights by analyzing sentiment in competitor reviews, news articles, and social media to refine strategies. These applications empower businesses to make data-driven decisions, enhance customer experiences, and improve overall business outcomes.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

Karthik currently serves as the Head of Snowflake Practice at LTM. With over 21 years of expertise in the data sector, he has extensive experience in Data Architecture, Data Modeling, Data Modernization & Technology consulting for customers across the globe. In his leisure time, Karthik enjoys spending time with his family. He is also a passionate supporter of Manchester United Football Club, ensuring that his weekends are never complete without watching his beloved team play.

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Snowflake Cortex Series — Part 6 : AI-Powered Text Condensation using SUMMARIZE Function

Karthik S Raman — Mon, 06 Jan 2025 13:40:36 GMT

Snowflake Cortex Series — Part 6 : AI-Powered Text Condensation using SUMMARIZE Function

Welcome to Part 6 of our Snowflake Cortex Series! This series explores how Snowflake Cortex’s advanced functions are helping businesses effortlessly extract actionable insights from their data.

In previous parts, we introduced various Snowflake Cortex LLM functions:

In Part1: A high-level overview of the COMPLETE, task-specific, and helper functions.

In Part 2: The COMPLETE function, showcasing its ability to generate creative content, answer complex queries, and produce tailored summaries.

In Part 3: The CLASSIFY_TEXT function, highlighting how it categorizes unstructured data for better decision-making.

In Part 4: The EXTRACT_ANSWER function, enabling natural language answers from structured and semi-structured data.

In Part 5: The PARSE_DOCUMENT function, demonstrating how OCR and machine learning extract insights from documents to streamline operations.

Now in this Part 6 we turn our attention to how the SUMMARIZE function leverages advanced AI to condense large text data into concise summaries, preserving key information and context. It is ideal for applications like summarizing customer interactions, documents, or extensive text-based datasets for efficient analysis and decision-making.

In this installment, we’ll explore: How the SUMMARIZE function works, Its parameters and features, Best practices for effective implementation.

By the end, you’ll be equipped with the knowledge to transform data queries into valuable insights faster than ever.

SUMMARIZE Function:

The SUMMARIZE function in Snowflake Cortex is an advanced AI-powered feature that leverages the capabilities of Large Language Models (LLMs) to efficiently condense large volumes of text into concise summaries while maintaining accuracy and key information. This functionality significantly enhances productivity by automating the summarization process, enabling faster data analysis, and facilitating a deeper understanding of complex information within the Snowflake Data Cloud. Summarize can be applied to a wide range of text formats, including documents, articles, reports, customer feedback, and more, making it a valuable tool for various industries and use cases.

Key Applications:

The SUMMARIZE function unlocks a universe of possibilities across diverse domains:

Customer Service:

Summarizing customer feedback from surveys, reviews, and support tickets to quickly identify key issues and areas for improvement.
Condensing lengthy chat logs to extract crucial information for customer support representatives.

Market Research:

Summarizing market research reports, competitor analysis reports to gain a comprehensive understanding of market trends and customer preferences.

Financial Services:

Summarizing financial news articles, regulatory documents, and risk assessments to facilitate faster decision-making and risk mitigation.

Legal:

Summarizing legal documents, contracts, and case files to streamline legal processes and improve efficiency.

Content Creation:

Creating concise summaries of long-form articles, blog posts, and research papers for easier consumption and sharing.

Understanding how SUMMARIZE Function works:

Syntax:

The basic syntax for using the SUMMARIZE function is as follows:

SNOWFLAKE.CORTEX.SUMMARIZE()

Required Parameters:

text: A string containing the English text from which a summary should be generated.

Returns:

A string containing a summary of the original text.

Real-World Applications of the SUMMARIZE Function:

The SUMMARIZE function is not just a theoretical concept; it’s driving concrete business value in numerous real-world scenarios. Below are few key examples of its impactful applications.

· Summarizing Market Research articles

This example shows how the SUMMARIZE function summarizes long text content, specifically from research articles and papers, into concise summaries.

The below query retrieves data from the RESEARCH_PAPERS table, specifically focusing on summarizing the contents of a column named FULL_TEXT. It includes four key components in its output:

FULL_TEXT: Original content of the research paper.
ORIGINAL_TEXT_SIZE: Number of characters in the original text.
SUMMARY: version of the text generated using the SUMMARIZE function.
SUMMARIZED_TEXT_SIZE: Number of characters in the summary, illustrating the reduction in text size.

Original Text

Summarized Text

· Summarizing Semi-Structured Customer Support Data

The below example shows how the SUMMARIZE function summarizes text data in semi-structured JSON format, specifically from customer support chat information, into concise summaries.

The below query extracts the CHAT_LOG column from the CUSTOMER_SUPPORT_TICKETS table and uses the Snowflake Cortex SUMMARIZE function to generate a concise summary of each chat log. The results include both the original chat transcripts (CHAT_LOG) and their corresponding summaries (CHAT_SUMMARY), enabling quick insights into the key points of customer interactions without reviewing the full logs.

Conclusion:

In conclusion, the Snowflake Cortex SUMMARIZE function empowers data professionals with a powerful AI-driven feature for efficiently condensing large volumes of text data into concise summaries. By leveraging the capabilities of Large Language Models, SUMMARIZE unlocks valuable insights from diverse sources and enables to streamline data analysis, improves decision-making, and enhance productivity across various industries. As demonstrated through real-world examples, the SUMMARIZE function is not merely a theoretical concept but a practical solution with the potential to revolutionize how organizations interact with and understand their textual data.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Snowflake Cortex Series — Part 5 : Mastering Document Parsing with PARSE_DOCUMENT Function

Karthik S Raman — Tue, 31 Dec 2024 10:14:53 GMT

Snowflake Cortex Series — Part 5 : Mastering Document Parsing with PARSE_DOCUMENT Function

Welcome to Part 5 of our Snowflake Cortex Series! In this series, we are exploring how Snowflake Cortex empowers users with advanced functions.

Part1: Overview of Cortex LLM functions, including Complete, task-specific, and helper functions.

Part 2: The COMPLETE function, enabling creative content generation, complex queries, and tailored summaries.

Part 3: The CLASSIFY_TEXT function, which simplifies categorizing unstructured data and enhances decision-making.

Part 4: The EXTRACT_ANSWER function, enabling natural language answers from structured and semi-structured data.

Now in this Part 5 we turn our attention to the PARSE_DOCUMENT function — an innovative feature that allows users to query both structured and semi-structured data to retrieve natural language answers. It combines the power of Snowflake’s scalable data platform with the sophisticated text and data processing capabilities of LLMs to help businesses extract insights from their data in an intuitive way.

By the end, you’ll be equipped with the knowledge to transform data queries into valuable insights faster than ever.

PARSE_DOCUMENT Function:

The PARSE_DOCUMENT function in Snowflake Cortex is an advanced AI-powered feature that extracts and interprets text and layout data from documents stored in internal or external stages. By leveraging cutting-edge Optical Character Recognition (OCR) technology and sophisticated machine learning models, it accurately identifies text, tabular data, and structural elements within complex documents like PDFs.

This functionality unlocks the value of unstructured document archives by transforming them into actionable insights. With PARSE_DOCUMENT, you can streamline workflows, build robust information retrieval systems, and automate document processing. Extracted data can be seamlessly loaded into structured Snowflake tables, enabling effortless integration with downstream applications. Whether managing large-scale document repositories or optimizing data pipelines, PARSE_DOCUMENT delivers exceptional accuracy and scalability for document analytics.

Key Applications:

The PARSE_DOCUMENT function unlocks a universe of possibilities across diverse domains. By extracting actionable insights, it bridges the gap between raw document archives and meaningful analytics, driving efficiency and innovation.

Revolutionizing Information Retrieval: Construct sophisticated search engines that effortlessly navigate vast repositories of business documents, ensuring rapid access to critical information and accelerating decision-making.
Unleashing the Power of Data Enrichment: Transform raw, unstructured data into actionable insights by seamlessly integrating extracted information into existing data warehouses and analytical pipelines, enriching existing datasets with valuable context.
Comprehensive Document Analysis: Delve beyond surface-level analysis with insightful document comprehension. Uncover hidden patterns, identify key themes, and gain a holistic understanding of the content and structure of documents, empowering data-driven discoveries.
Automating the Data Pipeline: Streamline operations and liberate valuable human resources by automating tedious data extraction and processing tasks. Enhance operational efficiency, minimize human error, and unlock significant time and cost savings.

Understanding how PARSE_DOCUMENT Function works under the hood:

The PARSE_DOCUMENT function provides two powerful modes for processing PDF documents: OCR (default) and LAYOUT.

OCR Mode (Default):
Optimized for extracting text from text-heavy documents, this mode is ideal for quick and straightforward text extraction. It is recommended for documents without a complex semantic structure, offering an efficient way to retrieve textual information with minimal processing overhead.
LAYOUT Mode (Optional):
Designed to extract both text and layout elements, such as tables, this mode is ideal for enhancing the contextual understanding of documents. It is particularly suited for improving document knowledge bases, optimizing information retrieval systems, and supporting Large Language Model (LLM) inference tasks. By isolating text sections using layout elements, this mode enables more precise and targeted entity extraction, making it a valuable tool for advanced analytical workflows.

Syntax:

The basic syntax for using the PARSE_DOCUMENT function is as follows:

SNOWFLAKE.CORTEX.PARSE_DOCUMENT( ‘@’, ‘’, [ { ‘mode’: ‘’ }, ] )

Required Parameters:

stage: The name of the Snowflake stage where the document is stored.
path: The relative path to the document within the stage.

Optional Parameters:

MODE : Specifies the processing mode for the document:

· OCR (default): Extracts plain text content.

· LAYOUT: Extracts data as markdown, including structural elements like tables.

For example, with LAYOUT Mode, you can structured data to improve downstream tasks such as generating insights, enhancing search relevance, or enabling detailed content analysis.

Returns:

An OBJECT data type containing the extracted data as a JSON-encoded string under the key content. The format of the content depends on the selected mode:

· LAYOUT Mode: Provides markdown with structural details, including tables.

· OCR Mode: Provides plain text content.

Error Conditions

If the input arguments are invalid or the function encounters other issues, it will return an error message. Refer to the Snowflake documentation for a comprehensive list of potential errors and their corresponding error messages.

Real-World Applications of the PARSE_DOCUMENT Function:

The PARSE_DOCUMENT function goes beyond theory; it is actively generating significant business value in a wide array of real-world applications. Below are several key examples of how this powerful function is being leveraged.

Parsing of Documents using OCR Mode: — Parsing documents with Vehicle Service Estimates:

The below example illustrates how the PARSE_DOCUMENT function can be used in OCRM mode to perform parsing of the PDF documents containing Vehicle Service estimates. The PARSE_DOCUMENT function can be instrumental in automating the processing of such service estimates. By feeding the PDF into the function, it can intelligently extract key information fields like:

Customer Details: Name, GSTIN, Address, Phone Number.
Vehicle Information: Model, Registration Number, Chassis Number, Engine Number, Next Service Due Date.
Service Details: RO Number, RO Type, Visit Date, Mileage, Estimated Delivery Date.
Service Items: Description, Quantity, Job Value, Discount, Taxable Amount, Tax, Total Amount.
Service Categories: Periodic Maintenance, General Repair, Value Added Services.

Furthermore, the function can likely identify and categorize different sections of the document, such as “BILL TO,” “SHIP TO,” “VEHICLE INFO,” and “REPAIR ORDER INFO.” This structured output can then be loaded directly into a Snowflake table, enabling further analysis, reporting, and integration with other business systems.

This powerful use case demonstrates how PARSE_DOCUMENT transforms unstructured documents into actionable insights, reducing manual effort and driving smarter decision-making.

· Parsing of documents using LAYOUT Mode: — Information from Retail Store Purchase Receipt:

The below example illustrates how the PARSE_DOCUMENT function can be used in Layout mode to perform an analysis of the PDF documents containing Retail Store Purchase receipt, detailing purchased items, their quantities, prices, tax, and total payment information in a structured format. For this use case, PARSE_DOCUMENT can extract relevant data such as store details (address, manager name), transaction details (total, tax, change due), and individual item data (description, SKU, price, quantity). This structured data can then be used for further analytics, such as generating purchase insights, calculating total spending, or categorizing products. By leveraging PARSE_DOCUMENT, businesses can automate the process of transforming unstructured receipt data into actionable information.

Limitations & Considerations

While the PARSE_DOCUMENT function is a powerful tool, it’s important to be aware of its limitations:

File Size & Page Limits: Currently supports PDFs up to 100MB with a maximum of 100 pages.
Language Support: Optimized for English; support for other languages (French, Portuguese, etc.) is in preview.
Digital PDF Optimization: Best suited for digitally created PDFs, with limited support for scanned documents.
Stage Encryption: Server-Side Encryption is mandatory for stages used with PARSE_DOCUMENT.
Network Policy Compatibility: Currently incompatible with custom network policies.

Conclusion:

In conclusion, the Snowflake Cortex PARSE_DOCUMENT function empowers businesses to unlock the hidden value within their document repositories. By seamlessly integrating advanced OCR technology and sophisticated machine learning models, this function enables the extraction of valuable insights from unstructured data with unprecedented speed and accuracy. With its ability to process both text and layout information, PARSE_DOCUMENT unlocks a wide range of applications, from streamlining data entry and automating workflows to building sophisticated search engines and gaining deeper insights into document content. While limitations such as file size restrictions and language support considerations exist, Snowflake continues to enhance the capabilities of PARSE_DOCUMENT, pushing the boundaries of what’s possible in document analysis. As businesses increasingly rely on data-driven decision making, the PARSE_DOCUMENT function is poised to play a critical role in transforming raw documents into actionable intelligence.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Snowflake Cortex Series — Part 5 : Mastering Document Parsing with PARSE_DOCUMENT Function was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.

Snowflake Cortex Series — Part 4 : Supercharging Your Data Analysis with EXTRACT_ANSWER Function

Karthik S Raman — Mon, 11 Nov 2024 15:48:32 GMT

Snowflake Cortex Series — Part 4 : Supercharging Your Data Analysis with EXTRACT_ANSWER Function

Welcome to Part 4 of our Snowflake Cortex Series! In this series, we’re exploring how Snowflake Cortex’s advanced functions are empowering businesses to extract actionable insights from data with ease.

In Part 2, we looked at the COMPLETE function, demonstrating how it enables users to generate creative content, answer complex questions, and produce customized summaries effortlessly.

Part 3, focused on the CLASSIFY_TEXT function, showing how businesses can categorize unstructured text data, streamline workflows, and make data-driven decisions.

Now in this Part 4, we turn our attention to the EXTRACT_ANSWER function — an innovative feature that allows users to query both structured and semi-structured data to retrieve natural language answers. It combines the power of Snowflake’s scalable data platform with the sophisticated text and data processing capabilities of LLMs to help businesses extract insights from their data in an intuitive way.

We will delve into the details of the EXTRACT_ANSWER function, reviewing its parameters, features, and best practices for effective implementation. By the end, you’ll be equipped with the knowledge to transform data queries into valuable insights faster than ever.

EXTRACT_ANSWER Function:

Snowflake Cortex’s EXTRACT_ANSWER LLM function empowers users to extract specific information from text documents using natural language queries. By inputting a question and a source document (plain text or JSON), the function leverages advanced LLM technology to identify relevant text passages, process them, and return a concise answer. This capability significantly simplifies data analysis, enabling users to efficiently extract insights from unstructured data without complex SQL queries, accelerating data-driven decision-making.

Key Applications:

· Analyzing Customer Support Tickets: By leveraging the EXTRACT_ANSWER function, customer support teams can efficiently analyze ticket data to identify core issues, determine customer sentiment, and suggest relevant solutions from knowledge bases. This automation not only speeds up resolution times but also improves customer satisfaction by providing timely and accurate responses.

· Extracting Information from Product Reviews: The EXTRACT_ANSWER function can effectively analyze product reviews to extract valuable insights. By identifying key features, sentiments, and common issues, businesses can make data-driven decisions to improve product quality, marketing strategies, and customer experience.

· Extracting Information from News Articles: The EXTRACT_ANSWER function can be used to automate the process of extracting key information from news articles. By identifying the main topic, keywords, and summary of an article, businesses can stay updated on industry trends, monitor brand reputation, and identify potential opportunities or threats.

Understanding the parameters of the EXTRACT_ANSWER Function in Snowflake Cortex LLM

Syntax:

The basic syntax for using the EXTRACT_ANSWER function is as follows:

SNOWFLAKE.CORTEX.EXTRACT_ANSWER(, )

Required Parameters:

source_document: A string containing the plain-text or JSON document to be analyzed.
question: A string specifying the question to be answered based on the source document.

Return Value: A string containing the extracted answer to the given question, based on the content of the source_document.

Error Conditions

Real-World Applications of the EXTRACT_ANSWER Function:

The EXTRACT_ANSWER function goes beyond theory; it is actively generating significant business value in a wide array of real-world applications. Below are several key examples of how this powerful function is being leveraged.

· Analyzing Customer Support Tickets:

The below example illustrates how the EXTRACT_ANSWER function can be used to perform an analysis of the Customer Support Ticket data. This SQL query analyzes the structured data stored in the customer support ticket table to extract the main issue described by the customer. The SNOWFLAKE.CORTEX.EXTRACT_ANSWER function processes the ticket_description field to identify the core problem. The LATERAL FLATTEN clause is used to unpack the JSON output of the EXTRACT_ANSWER function, making the extracted issue accessible as the extracted_issue column. The final result includes the ticket ID, ticket description, and the identified main issue.

· Extracting Information from Product Reviews:

The following SQL query demonstrates how to use the EXTRACT_ANSWER function to analyze structured data from product reviews stored in a table. For each review record, it applies the EXTRACT_ANSWER function twice: once to identify the main feature and once to determine the product color. The LATERAL FLATTEN clause is used to unpack the JSON output of the EXTRACT_ANSWER function, allowing the extracted information to be joined back to the original product_reviews table. The final result provides valuable insights, displaying the product name, review text, the extracted main feature, and the identified product color, giving companies a better understanding of customer feedback on specific product attributes.

· Extracting Information from News Articles:

This SQL query analyses the semi-structured information stored in the news_articles table using the SNOWFLAKE.CORTEX.EXTRACT_ANSWER function. For each article, it applies the EXTRACT_ANSWER function multiple times to extract information like main topic, author, published date, summary, categories, and keywords. The LATERAL FLATTEN clause is used to unpack the JSON output of the EXTRACT_ANSWER function, making the extracted information accessible as separate columns. The final result includes the original article ID, article data, and the extracted information.

Guidelines and Best Practices

To maximize the effectiveness of the EXTRACT_ANSWER function, it’s crucial to adhere to these best practices:

Effective Query Crafting:

Clarity and Specificity: Frame clear, concise questions with precise keywords.
The More Specific, the Better: Detailed queries yield more accurate results.

Optimizing Source Documents:

Clear Structure: Ensure well-formatted documents with clear headings and paragraphs.
JSON for Structure: Consider JSON for structured data to enhance extraction.

Iterative Refinement and Contextual Clues:

Experimentation: Refine queries if initial results are suboptimal. Experiment with different parameters and phrasings to optimize results.
Contextual Understanding: Provide relevant context for improved accuracy, especially for complex queries.
Monitor and Fine-tune: Continuously monitor the performance of the EXTRACT_ANSWER function and make adjustments as needed.

By adhering to these guidelines and best practices, you can harness the full potential of the EXTRACT_ANSWER function to uncover valuable insights within your data. This approach empowers you to transform raw information into actionable intelligence, making data-driven decision-making more accessible and effective across your organization.

Conclusion:

Snowflake Cortex’s EXTRACT_ANSWER function is a powerful tool that revolutionizes data analysis by enabling users to extract meaningful insights from unstructured text data. By leveraging advanced natural language processing techniques, this function empowers businesses to streamline operations, improve decision-making, and uncover hidden patterns within their data. Whether it’s analyzing customer support tickets, product reviews, or news articles, the EXTRACT_ANSWER function simplifies complex tasks and accelerates the journey from raw data to actionable insights. As AI and ML continue to advance, we can expect even more innovative applications of this function in the future, making it an essential tool for data-driven organizations.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Snowflake Cortex Series — Part 3 : Classifying Text with Precision: A Comprehensive Guide to the…

Karthik S Raman — Mon, 04 Nov 2024 14:01:23 GMT

Snowflake Cortex Series -Part 3 : Classifying Text with Precision using CLASSIFY_TEXT Function

In Part 2 of our Snowflake Cortex series, we explored the transformative potential of the COMPLETE function, a core element of Snowflake Cortex LLM that redefines natural language processing tasks. This powerful tool enables users to effortlessly generate creative content, address complex queries, and provide concise summaries. From sentiment analysis and synthetic data generation to customized summaries, the COMPLETE function opens up a world of possibilities for businesses across industries.

Now, in Part 3, we continue our journey by diving deeper into another powerful function within the Snowflake Cortex arsenal: the CLASSIFY_TEXT function. This function empowers users to categorize free-form text data into predefined categories, unlocking valuable insights and streamlining workflows. It enables organizations to make data-driven decisions and uncover critical insights hidden in unstructured text.

We’ll explore the nuances of the CLASSIFY_TEXT function, examining its parameters, capabilities, and best practices for optimal implementation. By understanding the intricacies of this function, you can unlock new levels of innovation, efficiency, and data-driven insights.

CLASSIFY_TEXT Function: A Closer Look

The CLASSIFY_TEXT function is a powerful tool within Snowflake Cortex LLM that enables you to categorize free-form text data into predefined categories. This function leverages the power of natural language processing to accurately classify text, making it a valuable asset for a variety of applications.

Key Applications:

· Sentiment Analysis: By classifying text as positive, negative, or neutral, businesses can gain valuable insights into customer sentiment. This data-driven understanding can highlight areas for improvement, track brand reputation, and guide decisions to enhance customer satisfaction.

· Topic Classification: Categorizing text into specific topics allows organizations to efficiently organize and filter large volumes of information. This can help in tasks like news aggregation, document categorization, and social media monitoring. By understanding the topics discussed in text data, businesses can identify trends, extract relevant information, and make informed decisions.

· Intent Recognition: By accurately identifying the intent behind a user’s query, businesses can provide more relevant and efficient responses. This is particularly useful in chatbot and virtual assistant applications, While understanding user intent helps businesses deliver personalized, helpful assistance, creating a better user experience.

· Document Categorization: Automating the categorization of documents can save time and effort, especially for large organizations with extensive document repositories. By classifying documents into specific categories, businesses can improve search and retrieval capabilities, facilitate knowledge management, and streamline workflows.

Understanding the parameters of the CLASSIFY_TEXT Function in Snowflake Cortex LLM

Syntax:

The basic syntax for using the CLASSIFY_TEXT function is as follows:

SNOWFLAKE.CORTEX.CLASSIFY_TEXT(, )

Required Parameters:

Input:

A string representing the text data to be classified.
Case-sensitive, meaning that slight variations in capitalization can lead to different classification results.

List_of_categories:

An array of strings defining the potential categories for classification.
Must contain at least two and at most 100 unique categories.
Case-sensitive, so the exact capitalization of categories in the input text must match the defined categories.
If these requirements are not met, the function will return an error message.

Return Value

The function returns a JSON object containing the following information:

Category: The category that the input text was classified into.

Error Conditions

Live applications of CLASSIFY_TEXT Function:

The CLASSIFY_TEXT function isn’t just theoretical — it’s driving real business value across various real-world applications. Below are some examples of how this function is being utilized.

· Sentiment Analysis: Here is an example of how the CLASSIFY_TEXT function helps to uncover the nuances of customer sentiment by identifying and analyzing specific aspects of a text.

The below example illustrates how the CLASSIFY_TEXT function can be used to perform a sentiment analysis of the Review data stored in a table. The query classifies the review data stored in a table into one of the categories specified and returns a JSON object for each row of the table.

· Topic Classification: Here is an example of how the CLASSIFY_TEXT function helps to classify text data into categories and helps to organize the data and filter information. In the below example, The query uses CLASSIFY_TEXT function to classify and categorize mews articles stored in a table into different categories which can then help in creating a personalized news feed or analyzing trends in specific domains.

· Intent Recognition: Here is an example of how the CLASSIFY_TEXT function can be used to precisely identify the underlying meaning of a user’s query and classify then in to a relevant category, which can help businesses to offer more targeted and effective solutions. This is particularly beneficial for chatbot and virtual assistant technologies, where accurate intent recognition is essential for providing personalized and helpful support.

In the following example, customer queries and their corresponding categories are stored in separate table columns. The CLASSIFY_TEXT function categorizes each query into its most relevant category.

· Document Categorization: Here is an example of how the CLASSIFY_TEXT function can help to automate the process of classifying and sorting documents into specific categories significantly reducing manual effort, particularly for large organizations with vast document repositories.

In the following example, document name and their corresponding categories are stored in separate table columns. The CLASSIFY_TEXT function categorizes each document into its most relevant category.

Guidelines and Best Practices

To maximize the effectiveness of the CLASSIFY_TEXT function, it’s crucial to adhere to these best practices:

Input Text Quality:

Plain English: Use clear and concise language in the input text to ensure accurate classification.
Minimal Non-Text Content: Limit the inclusion of code snippets, logs, or other non-textual elements in the input to avoid misclassification.
Open-Source Formats: Avoid using proprietary or company-specific formats, as they may hinder the classification process.

Category Definition:

Descriptive Categories: Use clear and meaningful category labels that accurately reflect the content they represent.
Avoid Abbreviations and Jargon: Employ full words and phrases to prevent misinterpretation.
Mutual Exclusivity: Ensure that categories are distinct and non-overlapping to minimize confusion.

By following these guidelines, you can significantly improve the accuracy and reliability of the CLASSIFY_TEXT function, leading to better data categorization and analysis.

Conclusion:

The CLASSIFY_TEXT function is a powerful tool within Snowflake Cortex LLM that empowers businesses to harness the potential of natural language processing. By accurately categorizing text data, organizations can gain valuable insights, improve operational efficiency, and enhance customer experiences. Whether applied to sentiment analysis, topic classification, intent recognition, or document categorization, the potential of this function is extensive and impactful. By adhering to best practices and leveraging the capabilities of the CLASSIFY_TEXT function, you can unlock new levels of innovation and drive data-driven decision-making.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Cortex Series -Part 2 : Unleash the Power of Natural Language Processing with the COMPLETE Function

Karthik S Raman — Fri, 18 Oct 2024 14:29:07 GMT

Snowflake Cortex Series -Part 2 : Unleash the Power of Natural Language Processing with the COMPLETE Function

In Part 1 of this series, we explored the groundbreaking potential of Snowflake Cortex LLM Functions and how they are reshaping the way businesses interact with data. By integrating the power of Large Language Models (LLMs) directly into the Snowflake platform, organizations are revolutionizing data-driven operations, improving insights, and accelerating innovation. We discussed how Cortex empowers businesses to perform tasks like sentiment analysis, entity recognition, and real-time decision-making with ease.

Now, in Part 2, we continue our journey by diving deeper into one of Cortex’s most transformative functions: the COMPLETE function. This function is a cornerstone of LLM applications, enabling a wide range of use cases from text generation and summarization to question answering and creative writing.

We’ll explore the intricacies of the COMPLETE function, including its parameters, capabilities, and best practices for effective implementation. Understanding its functionality will enable you to unlock new levels of innovation, efficiency, and insight from your data.

COMPLETE Function: Your Customizable NLP Workhorse

The COMPLETE function is a versatile tool within Snowflake Cortex LLM that empowers you to perform a wide range of natural language processing tasks with unparalleled customization. From fine-tuning sentiment analysis to generating synthetic data, its capabilities are vast and adaptable.

Key Applications:

Aspect-Based Sentiment Analysis: Delve deeper than just the overall sentiment. Identify specific aspects of a text and analyze the associated feelings, providing valuable insights for businesses.
Synthetic Data Generation: Create realistic, artificial data to augment training datasets, improve model performance, and address data scarcity challenges.
Customized Summaries: Generate summaries that align with your specific needs, whether you require concise overviews or detailed breakdowns.

Safety and Reliability:

Cortex Guard ensures the safety and reliability of the COMPLETE function by filtering out potentially harmful or inappropriate responses. You can trust that the generated output will be aligned with your organization’s values and guidelines.

Fine-Tuning for Optimal Performance:

The COMPLETE function offers extensive flexibility, allowing you to fine-tune its parameters to better align with your business objectives. Whether you need more creative outputs, informative responses, or concise summaries, the COMPLETE function can be tailored to generate outputs that meet your specific needs.

Unlock the Versatility of the COMPLETE Function

With its powerful capabilities and customizable options, the COMPLETE function is a valuable asset for any organization looking to leverage the power of natural language processing. Discover how the COMPLETE function can enhance your business operations and drive innovation.

Understanding the parameters of the COMPLETE Function in Snowflake Cortex LLM

Required Parameters

Model: Specifies the LLM model to be used for generating the completion.

Options include gemma-7b, jamba-1.5-mini, llama2–70b-chat, llama3–8b, llama3–70b, llama3.1–8b, llama3.2–1b, llama3.2–3b, mistral-large, mistral-large2, mistral-7b, mixtral-8x7b, reka-core, reka-flash, snowflake-arctic etc

The choice of model will impact the quality and cost of the generated response.

Prompt_or_history: This is the input text or conversation history that the LLM will use to generate a completion.

If options is not specified, the prompt must be a string.
If options is specified, the prompt must be an array of objects representing a conversation in chronological order. Each object must contain a role key and a content key. The content value is the prompt or response, and the role must be one of system, user, or assistant.

Optional Parameters

options: An object containing zero or more of the following options that affect the LLM’s hyperparameters:

temperature: Controls the randomness of the output (default: 0). Higher values result in more diverse and random output, while lower values make the output more deterministic.
top_p: Controls randomness and diversity, similar to temperature, but restricts the set of possible tokens (default: 0).
max_tokens: Sets the maximum number of output tokens in the response. Adjust the max_tokens value to control the length of the generated response. Be mindful of the computational cost associated with longer responses.
guardrails: Filters potentially unsafe or harmful responses (default: False). If you’re concerned about potentially unsafe or harmful content, enable the guardrails option. However, note that this might limit the creativity and diversity of the generated text.

Output

The output of the COMPLETE function depends on whether the options argument is specified:

When options argument is not specified: A string representing the generated response.

When options argument is specified: A JSON object containing-

choices: An array of the model’s responses (usually only one).
created: UNIX timestamp of when the response was generated.
model: The name of the model used.
usage: An object recording the number of tokens consumed and generated.

Live applications of COMPLETE Functions:

The COMPLETE function isn’t just theoretical — it’s driving real business value in various live applications. Below are some examples of how this function is being utilized.

· Aspect-Based Sentiment Analysis: Here are a couple of examples of how the COMPLETE function helps to uncover the nuances of customer sentiment by identifying and analyzing specific aspects of a text.

Example 1: The below example illustrates how the COMPLETE function can be used to perform a sentiment analysis of the data stored in a table. The query returns a string response for each row of the table.

Example 2: The below example illustrates the use of a system prompt to perform a sentiment analysis of a food review. The prompt argument here is an array of objects, each having an appropriate role value. This query returns a JSON object containing the response information from the LLM Model.

· Synthetic Data Generation: Here is an example of how the COMPLETE function helps to create synthetic data that mirrors real-world patterns, enabling you to train more robust models and tackle challenges associated with limited data availability.

· Customized Summaries: Here is an example of how the COMPLETE function helps to generate summaries from your text data to suit your specific preferences, from concise summaries to detailed breakdowns.

Limitations and Considerations

While the COMPLETE function is a powerful tool, it’s important to be aware of its limitations:

Hallucinations: The model may sometimes generate information that is factually incorrect or misleading. This is known as a hallucination, and it’s important to validate outputs when accuracy is critical.
Bias: Since the model is trained on large datasets, it can sometimes reflect the biases present in that data. Users should be cautious of unintended bias in the outputs.
Computational Cost: Generating high-quality completions can be computationally expensive, especially for larger models.

Conclusion:

The COMPLETE function within Snowflake Cortex LLM Functions offers a powerful and versatile tool for natural language processing tasks. By understanding its parameters, capabilities, and best practices, you can effectively leverage this function to generate creative text, answer questions, summarize information, and more.

Whether you’re a data scientist, developer, or business analyst, the COMPLETE function provides a valuable resource for unlocking the potential of Large Language Models within your data ecosystem. Embrace this function as part of your toolkit to transform how you interact with data and scale your organization’s capabilities.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Snowflake Cortex Series -Part1: Unleashing the Power of Snowflake Cortex LLM Functions: A Deep Dive

Karthik S Raman — Mon, 07 Oct 2024 01:12:41 GMT

Introduction

Snowflake AI Data Cloud, continues to reshape how businesses manage and analyze their data by integrating the transformative capabilities of Large Language Models (LLMs). With Snowflake’s Cortex platform, organizations can seamlessly leverage these advanced models within Snowflake’s robust Data and AI ecosystem. This blog post will delve into the world of Snowflake Cortex LLM functions, providing a comprehensive overview of their capabilities, use cases, and the immense value they bring to organizations across various industries. By understanding how LLM functions can be harnessed within Snowflake’s powerful data platform, businesses can unlock new insights, streamline operations, and drive innovation.

Snowflake Cortex: Your Gateway to Cutting-Edge LLMs

Cortex provides businesses with the power to access industry-leading Large Language Models (LLMs) directly within the Snowflake platform. Cortex provides instant access to innovative models developed by renowned research teams at companies like Mistral, Reka, Meta, and Google. Plus, benefit from Snowflake Arctic, Snowflake’s own open enterprise-grade model designed to deliver exceptional performance and scalability.

Simplified and Seamless Integration:

One of the most compelling aspects of Snowflake Cortex is its ease of use. There’s no need for complex setup or management of infrastructure, as Cortex fully hosts and manages these LLMs. Your data remains securely within the Snowflake environment, guaranteeing the performance, scalability, and governance you’ve come to expect. You can immediately begin using LLMs without worrying about technical overhead or security risks, allowing your teams to focus on extracting valuable insights.

Snowflake Cortex: A Versatile Toolkit of LLM Functions

Snowflake Cortex offers a comprehensive suite of LLM functions, accessible through both SQL and Python, to cater to a wide range of natural language processing tasks. These functions can be categorized into:

COMPLETE Function: Your Customizable Workhorse

The COMPLETE function is an incredibly versatile tool, capable of executing a broad range of user-specified tasks. This flexibility makes it ideal for many natural language processing scenarios, including:

· Aspect-Based Sentiment Analysis: Pinpoint specific sentiments within a text, such as “product quality is excellent” or “customer service is poor.”

· Synthetic Data Generation: Create realistic, artificial data to augment training datasets or test models.

· Customized Summaries: Generate summaries that align with specific requirements or preferences.

Cortex Guard ensures the safety and reliability of the COMPLETE function by filtering out potentially harmful or inappropriate responses. Additionally, the COMPLETE function can be fine-tuned, giving you control over how it operates to better suit your business objectives.

Task-Specific Functions: Streamlined Solutions

For common NLP tasks, Snowflake Cortex provides pre-built, managed functions that streamline your workflows. These functions are ready-to-use, reducing the complexity of integrating advanced language models into your operations.

· CLASSIFY_TEXT: Categorize text into predefined classes.

· EXTRACT_ANSWER: Retrieve answers from unstructured data based on specific questions.

· PARSE_DOCUMENT: Extract text and layout elements from documents.

· SENTIMENT: Determine the overall sentiment of a text, helping businesses gauge customer feedback or market sentiment.

· SUMMARIZE: Generate concise summaries of longer texts.

· TRANSLATE: Translate text between supported languages, facilitating communication across diverse linguistic regions.

· EMBED_TEXT_768: Create 768-dimensional vector embeddings for text representation.

· EMBED_TEXT_1024: Create 1024-dimensional vector embeddings for text representation.

Helper Functions: Ensuring Smooth Operations

To enhance the reliability and efficiency of your LLM workflows, Snowflake Cortex offers helper functions:

· COUNT_TOKENS: Calculate the token count of an input text, ensuring compliance with model limits.

· TRY_COMPLETE: Run the COMPLETE function with built-in error handling, returning NULL in case of an error, ensuring your workflows continue uninterrupted.

By leveraging this diverse set of LLM functions, you can enhance your data analysis, automate tasks, and gain valuable insights from your unstructured data and unlock the full potential of natural language processing within your Snowflake environment.

Cost Considerations for Snowflake Cortex LLM Functions

Snowflake Cortex LLM functions are charged based on the number of tokens processed. Below is a breakdown of how token-based pricing works and how you can optimize costs for your workloads.

Token-Based Pricing:

A token is approximately equivalent to four characters.
Input and Output Tokens: Functions that generate new text (COMPLETE, CLASSIFY_TEXT, SUMMARIZE, TRANSLATE) count both input and output tokens.
Input Tokens Only: CORTEX GUARD, EMBED_TEXT_*, EXTRACT_ANSWER, and SENTIMENT count only input tokens.
Prompt Addition: SUMMARIZE, TRANSLATE, EXTRACT_ANSWER, CLASSIFY_TEXT, and SENTIMENT add a prompt to the input text, slightly increasing the input token count.

Cost Optimization:

Right Size Warehouses: Use smaller warehouses (no larger than MEDIUM) for Snowflake Cortex LLM functions and PARSE_DOCUMENT to optimize costs without sacrificing performance.
TRY_COMPLETE: Leverage TRY_COMPLETE to avoid unnecessary costs for error handling.
Token Management: Be mindful of token usage to minimize costs.
Function Selection: Choose the most appropriate function for your task to avoid unnecessary token processing.

By understanding these cost considerations and implementing optimization strategies, you can effectively manage your Snowflake Cortex LLM function expenses while maximizing the value of your applications.

Choosing the Ideal Model for Your Needs

Snowflake Cortex supports a diverse range of models, each tailored to specific use cases and performance requirements. Selecting the right model is essential to optimize performance, cost, and application outcomes. Below is a guide to help you make the right choice based on your needs.

Large Models:

Large models are designed for tasks that require extensive computational power, deeper reasoning capabilities, or multilingual support.

reka-core: Reka AI’s flagship model, excelling in reasoning, code generation, and multilingual tasks.
mistral-large2: Mistral AI’s advanced model, ideal for complex tasks requiring strong reasoning and multilingual capabilities.
llama3.1–405b: A powerful open-source model from Meta, well-suited for long document processing, multilingual support, and synthetic data generation.

Medium Models:

Medium models strike a balance between performance and efficiency, making them suitable for a wide range of applications.

llama3.1–70b: A versatile model for chat applications, content creation, and enterprise use cases.
snowflake-arctic: Snowflake’s enterprise-focused model, optimized for SQL generation, coding, and instruction following.
reka-flash: A high-performance multilingual model for fast-paced workloads requiring high-quality output.
mixtral-8x7b: Ideal for text generation, classification, and question answering, offering low latency and high throughput.
jamba-Instruct: An efficient model for long documents and extensive knowledge bases, supporting summarization, Q&A, and entity extraction.
jamba-1.5-mini/large: State-of-the-art models for structured output and grounded generation.

Small Models:

Small models in Snowflake Cortex are designed for simpler, more focused tasks where speed and cost-efficiency are priorities. These models offer sufficient power for basic operations while minimizing computational overhead.

llama3.2–1b/3b: Compact models for summarization, instruction following, and rewriting tasks, supporting multiple languages.
llama3.1–8b: A lightweight model for low to moderate reasoning tasks.
mistral-7b: Suitable for simple summarization, structuration, and question answering tasks.
gemma-7b: Optimized for simple code and text completion tasks.

Key Considerations:

Task Complexity: Evaluate the complexity of your task to determine the required model capabilities.
Performance Requirements: Consider latency, throughput, and cost constraints when selecting a model.
Data Characteristics: Assess the size, format, and content of your data to ensure compatibility with the chosen model.
Experimentation: Start with a suitable model and gradually refine your selection based on performance and results.

By carefully considering these factors, you can choose the most appropriate Snowflake Cortex LLM model to drive optimal results in your applications.

Benefits of Snowflake Cortex LLM Functions

Enhanced Data Insights: LLMs can analyze unstructured text, identify patterns, and generate meaningful summaries, providing a more comprehensive understanding of your information.
Improved Efficiency: Snowflake Cortex streamlines the process of integrating LLMs into data workflows. The platform’s user-friendly interface and SQL-based commands make it easy for data analysts and developers to implement LLM functions without extensive programming knowledge.
Scalability and Performance: Snowflake’s distributed architecture ensures that LLM functions can handle large datasets and complex queries efficiently. This scalability allows businesses to process vast amounts of data without compromising performance.
Security and Governance: Snowflake Cortex is built on a secure and compliant data platform, providing robust security measures to protect sensitive information. This ensures that LLM applications can be deployed with confidence, adhering to industry standards and regulations.
Seamless Workflow Integration: Cortex seamlessly integrates with Snowflake’s existing ecosystem, allowing businesses to incorporate LLM functions into their existing data pipelines and workflows. This minimizes disruption and maximizes the value of LLM applications.

Applications of Snowflake Cortex LLM Functions:

Unlock the potential of advanced natural language processing and data analysis capabilities to drive innovation and efficiency across diverse industries.

Natural Language Processing (NLP): Sentiment analysis, text classification, topic modeling, named entity recognition.
Data Analysis: Data summarization, data exploration, anomaly detection, predictive analytics.
Customer Service: Chatbots, virtual assistants, automated customer support.
Content Generation: Article writing, product descriptions, creative content.
Research and Development: Literature review, hypothesis generation, knowledge discovery.

Conclusion:

In today’s data-driven world, leveraging advanced tools like Snowflake Cortex LLM Functions is not just an option; it’s a necessity for staying competitive. By embracing these capabilities, organizations can transform their data strategies and lead the way in innovation. Snowflake Cortex LLM Functions offers a powerful and versatile toolkit for organizations seeking to leverage the capabilities of Large Language Models. By integrating LLMs directly into Snowflake’s data platform, businesses can unlock new insights, automate tasks, and drive innovation. The comprehensive suite of LLM functions, combined with Snowflake’s scalability, security, and ease of use, provides a compelling solution for a wide range of applications. Whether you’re looking to enhance data analysis, improve customer experiences, or streamline content creation, Snowflake Cortex LLM Functions offer the tools and flexibility you need to achieve your goals.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Maximize Snowflake Warehouse Utilization: A Deep Dive into Warehouse_Cluster_Utilization View

Karthik S Raman — Fri, 27 Sep 2024 08:26:32 GMT

In my earlier blog post, we explored how Snowflake Warehouse Utilization feature, helps organizations optimize resource consumption and reduce costs by offering detailed insights into warehouse usage patterns. Building on that foundation, this post will delve into the WAREHOUSE_CLUSTER_UTILIZATION feature (currently in private preview). We’ll examine how this feature further aids organizations in optimizing costs and managing resources efficiently.

WAREHOUSE_CLUSTER_UTILIZATION View: New Feature — Currently in Private Preview

The WAREHOUSE_CLUSTER_UTILIZATION view, now available in Snowflake’s Account Usage schema, offers a detailed look into the resource consumption of Snowflake warehouses and clusters. It presents a comprehensive breakdown of utilization metrics over the past 365 days, helping users optimize performance, manage costs, and allocate resources more efficiently.

The WAREHOUSE_CLUSTER_UTILIZATION view offers a new level of transparency into Snowflake’s resource consumption, making it easier for organizations to understand and optimize their warehouse clusters.

Let’s explore the key features this view provides

Key Features

Time-Based Analysis: The view presents Cluster utilization data for a specified time window, typically the last year, enabling users to track trends and identify patterns.

Granular Level: It provides metrics at the warehouse cluster level, allowing for detailed analysis of individual resource performance.

Comprehensive Metrics: The view includes the following key metrics:

Start Time: The beginning of the utilization time window.
End Time: The end of the utilization time window.
Warehouse ID: A unique identifier for the warehouse.
Warehouse Name: The user-friendly name of the warehouse.
Cluster Number: Unique identifier of the cluster.
Utilization: A percentage value indicating the level of resource utilization, ranging from 0 (idle) to 100 (fully utilized).
Number of Queries Running: The count of queries executing during the specified time period.

Benefits and Applications

The WAREHOUSE_CLUSTER_UTILIZATION view offers several significant advantages for Snowflake users, transforming raw utilization data into actionable insights. By leveraging this view, users can:

Optimize performance: Identify underutilized or overloaded clusters to improve query execution times and overall system efficiency.
Manage costs: Determine the cost-effectiveness of warehouse cluster sizing and usage patterns, helping to reduce unnecessary expenses.
Ensure efficient resource allocation: Make data-driven decisions about scaling warehouses up or down based on workload demands, preventing resource bottlenecks and maximizing utilization.
Monitor resource trends: Track historical utilization data to identify patterns, anticipate future needs, and proactively adjust resource allocations.
Identify resource anomalies: Detect unusual spikes or dips in utilization, which could indicate issues such as query performance problems or resource contention.

Considerations and Limitations

While the WAREHOUSE_CLUSTER_UTILIZATION view is a valuable tool, there are certain considerations and limitations that users should keep in mind to ensure effective utilization of this feature:

· Potential Latency: The view might have a maximum delay of six hours between the actual utilization and the reported data. This means that real-time insights may be slightly delayed.

· Reporting Interval : Utilization data is collected at a one-minute interval, which may not capture very short-lived query execution patterns.

· Inactive Clusters: The view does not provide utilization data for clusters that are currently inactive or have been inactive for an extended period.

· Short-Running Queries: Utilization metrics might be less reliable for clusters with a high proportion of very short-running queries (typically under 100 milliseconds). These queries can introduce noise into the overall utilization calculation.

· Data Availability: Data for the number of queries running is available starting from July 15, 2024.

Few live Examples:

By applying real-world scenarios, users can better understand how the WAREHOUSE_CLUSTER_UTILIZATION view provides actionable insights for optimizing their Snowflake environment. Here are some practical examples

Warehouse Cluster utilization by query concurrency: This can help you understand how warehouse Cluster utilization changes with the number of concurrent queries, which can be useful for optimizing number of clusters, warehouse sizing and query concurrency settings.

2. Warehouse cluster Idle time and active time for the specific time period: This can help you understand the idle time and percentage of Idle time for each warehouse cluster in a Snowflake account. It filters the data for the past 7 days and groups the results by warehouse name and cluster number. The output includes the warehouse name, cluster number, start date, idle minutes, total minutes, and idle percentage.

By utilizing these insights, organizations can proactively manage their Snowflake resources, ensuring optimal performance and cost-effectiveness. The WAREHOUSE_CLUSTER_UTILIZATION view serves as a powerful tool to guide strategic decisions related to warehouse scaling, query performance, and resource allocation.

Conclusion:

Snowflake’s Warehouse_cluster_utilization feature offer valuable insights for organizations seeking to optimize resource consumption and reduce costs. By providing detailed metrics on warehouse and cluster utilization, this feature enable users to make data-driven decisions about resource allocation, query optimization, and cost management. Through effective analysis and application of these features, organizations can enhance system performance, reduce expenses, and ensure efficient resource utilization within their Snowflake environments.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

Karthik currently serves as the Head of the Snowflake Tech Consulting group at LTIMindtree. With over 21 years of expertise in the data sector, he has extensive experience in Data Architecture, Data Modeling, Data Modernization & Technology consulting for customers across the globe. In his leisure time, Karthik enjoys spending time with his family. He is also a passionate supporter of Manchester United Football Club, ensuring that his weekends are never complete without watching his beloved team play.

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Harnessing the Power of QUERY_ATTRIBUTION_HISTORY: A Snowflake Deep Dive

Karthik S Raman — Thu, 19 Sep 2024 17:24:29 GMT

Introduction

Queries are the backbone of data processing and analysis in Snowflake. Understanding the cost associated with each query is essential for organizations to optimize their resource utilization and control expenses. By identifying Resource intensive queries, organizations can pinpoint areas where performance improvements can be made. This knowledge enables them to refine query execution plans, leverage indexing strategies, and optimize warehouse sizing, ultimately reducing costs and enhancing query performance. Moreover, cost per query analysis provides valuable insights into the resource consumption patterns of different workloads, facilitating informed decision-making regarding resource allocation and capacity planning.

Traditionally, calculating the exact cost of a single query in Snowflake has been a complex endeavour due to several factors. Snowflake’s pay-as-you-go pricing model, where users are charged based on resource consumption, introduces challenges in isolating the specific costs attributed to individual queries. The platform’s dynamic infrastructure, which scales resources up or down according to workload demands, further complicates cost attribution. Additionally, the potential for concurrent query execution and the sharing of resources among multiple queries can make it difficult to pinpoint the precise cost associated with a particular query. These complexities have necessitated the development of sophisticated methodologies and tools to estimate and approximate query costs, providing users with valuable insights for optimizing their Snowflake environments.

New Feature in GA — Per query cost attribution

Snowflake’s new feature in GA, per query cost attribution, addresses the longstanding challenge of calculating the cost per query by providing a granular and accurate method for attributing warehouse compute costs to individual SQL queries. This feature introduces the QUERY_ATTRIBUTION_HISTORY view, which tracks query-specific costs. This allows users to accurately measure and analyze the resource consumption of their queries, enabling more effective cost management and optimization.

Cost Per Query:

In the realm of Snowflake, understanding the cost per query is paramount for optimizing resource utilization and minimizing expenses. The cost per query represents the warehouse credits consumed specifically for executing a given SQL query. By providing a granular view of resource demands, it enables users to make informed decisions about query optimization and cost management.

Key Points to Note:

To effectively manage and optimize query costs, consider the following important aspects:

Granular Cost Attribution: The cost per query provides a precise measurement of the resources consumed by a specific query, allowing for targeted optimization efforts.
Exclusions: The cost per query does not include additional costs incurred during query execution, such as data transfer, storage, cloud services, or serverless feature costs. This ensures a focused view of query-specific expenses.
Concurrent Query Attribution: For concurrently executing queries, the cost is distributed among them based on their relative resource consumption, providing a fair allocation of costs.
Idle Time Exclusion: Warehouse idle time, when no queries are running, is not included in the cost per query calculation. This helps to isolate the costs directly attributable to query execution.

QUERY_ATTRIBUTION_HISTORY (New feature in GA): A Deep Dive into Snowflake Query Costs

The QUERY_ATTRIBUTION_HISTORY view in Snowflake is a valuable tool for gaining insights into the cost of individual queries executed within your account. It provides detailed information about the resource consumption of each query, enabling you to optimize performance and manage costs effectively.

Key Columns and Their Significance:

query_id: A unique identifier for the SQL statement.
parent_query_id: The ID of the parent query, if applicable.
root_query_id: The ID of the topmost query in the query chain.
warehouse_id: The internal ID of the warehouse where the query was executed.
warehouse_name: The name of the warehouse where the query was executed.
query_hash, query_parameterized_hash: Hash values derived from the SQL text, potentially used for caching or optimization.
query_tag: A user-defined label for the query.
user_name: The user who executed the query.
start_time: The time when query execution began.
end_time: The time when query execution ended.
credits_attributed_compute, credits_used_query_acceleration: Metrics related to the computational cost of the query, including credits consumed for query execution and acceleration services.

Leveraging the View for Cost Analysis

To fully benefit from the QUERY_ATTRIBUTION_HISTORY view, consider the following strategies for cost analysis:

Identify Expensive Queries: Pinpoint queries that consume significant resources and contribute to higher costs.
Analyze Query Performance: Evaluate the efficiency of queries and identify areas for optimization.
Track Cost Trends: Monitor changes in query costs over time to identify potential issues or improvements.
Optimize Resource Allocation: Make informed decisions about warehouse sizing and resource allocation based on query costs.

Important Considerations:

When using the QUERY_ATTRIBUTION_HISTORY view, keep the following factors in mind:

Latency: The QUERY_ATTRIBUTION_HISTORY view may have a latency of up to six hours.
Role Requirements: You must have the USAGE_VIEWER database role to access this view.
Short-Running Queries: Queries that are shorter than approximately 100 milliseconds may not be included in the view due to the current limitations of per query cost attribution.

By effectively utilizing the QUERY_ATTRIBUTION_HISTORY view, you can gain valuable insights into your Snowflake query costs and make data-driven decisions to optimize your environment.

Few live Examples:

By applying real-world scenarios, users can see how the QUERY_ATTRIBUTION_HISTORY view provides actionable insights. For instance:

Recurrent Query Costs: To track the costs associated with recurring or similar queries, you can leverage QUERY_PARAMETERIZED_HASH. For a detailed breakdown of costs over time, consider grouping your queries by QUERY_PARAMETERIZED_HASH. This will help you identify the top cost drivers and optimize your query performance.

2. Query Cost by User: To gain insights into user-specific costs, consider analyzing the QUERY_ATTRIBUTION_HISTORY view. By examining the USER_NAME column, you can allocate costs to individual users, facilitating responsibility, financial planning, and more precise predictions. This analysis can also identify users who are particularly adept at utilizing Snowflake.”

Conclusion

By leveraging the QUERY_ATTRIBUTION_HISTORY view, Snowflake users can now gain a deeper understanding of query costs and optimize their Snowflake environments. This powerful tool provides granular insights into resource consumption, enabling users to identify expensive queries, analyze query performance, track cost trends, and make informed decisions about warehouse sizing and resource allocation. With the ability to pinpoint areas for improvement, Snowflake users can effectively reduce costs and enhance query performance, ultimately maximizing the value of their Snowflake investments.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Mastering Snowflake Warehouse Utilization: A Deep Dive into the WAREHOUSE_UTILIZATION View

Karthik S Raman — Thu, 29 Aug 2024 15:58:37 GMT

Introduction

In the quest for optimizing resource management and cost efficiency, understanding warehouse utilization is paramount for organizations leveraging Snowflake. Accurate tracking provides valuable insights into resource consumption patterns, enabling effective cost optimization. By identifying periods of low utilization, organizations can adjust warehouse sizes or suspend them during off-peak hours, significantly reducing costs. Moreover, tracking warehouse utilization helps in capacity planning, ensuring that sufficient resources are available to meet future workload demands while avoiding unnecessary overprovisioning. Additionally, analyzing utilization data can reveal bottlenecks and inefficient query patterns, allowing for targeted optimization efforts and improved performance.

Warehouse Utilization Metrics in Snowflake

Snowflake provides comprehensive utilization metrics to help you monitor and optimize your warehouse performance. These metrics offer insights into warehouse idle time, query load, spillage to remote storage, and overall resource consumption. By analyzing these data points, you can make well-informed decisions regarding warehouse sizing, auto-suspend settings, and consolidation strategies, optimizing both performance and cost efficiency.

WAREHOUSE_UTILIZATION View: New Feature - Currently in Private Preview

The WAREHOUSE_UTILIZATION view, now available in Snowflake’s Account Usage schema, offers a detailed look into the resource consumption of individual warehouses or clusters. By offering a comprehensive breakdown of utilization metrics over the past 365 days, this view empowers users to optimize performance, manage costs, and ensure efficient resource allocation.

Key Features

Time-Based Analysis: The view presents utilization data for a specified time window, typically the last year, enabling users to track trends and identify patterns.

Granular Level: It provides metrics at the warehouse level, allowing for detailed analysis of individual resource performance.

Comprehensive Metrics: The view includes the following key metrics:

Start Time: The beginning of the utilization time window.
End Time: The end of the utilization time window.
Warehouse ID: A unique identifier for the warehouse.
Warehouse Name: The user-friendly name of the warehouse.
Utilization: A percentage value indicating the level of resource utilization, ranging from 0 (idle) to 100 (fully utilized).
Number of Queries Running: The count of queries executing during the specified time period.

Benefits and Applications

The WAREHOUSE_UTILIZATION view offers a range of benefits that enhance operational efficiency and cost-effectiveness:

Performance Optimization: By identifying underutilized or overloaded warehouses, users can optimize resource allocation and improve overall system performance.
Cost Management: Understanding warehouse utilization patterns can help organizations effectively manage their Snowflake costs by ensuring that resources are allocated efficiently.
Capacity Planning: The view provides valuable data for forecasting future resource needs and planning for capacity expansions.
Troubleshooting: It can be used to identify potential performance bottlenecks or issues related to warehouse utilization.
Compliance: The view can assist in compliance efforts by providing data on resource usage and utilization patterns.

Considerations and Limitations

While the WAREHOUSE_UTILIZATION view is a valuable tool, users should be aware of certain considerations and limitations:

Latency: The view might have a latency of up to 360 minutes (6 hours), meaning the data may be up to 6 hours old.
Reporting Interval: Utilization data is reported at a 60-second (1-minute) interval, which may not capture very short-lived query execution patterns.
Short-Running Queries: The utilization metric may not be reliable for warehouses with a significant proportion of short-running queries (<= ~100ms).
Warehouse State Changes: If a warehouse is resumed or suspended during a reporting period, the utilization value may not accurately reflect the overall usage for that period.
Data Availability: The number_of_queries_running column is available starting from July 15, 2024.

Few live Examples:

By applying real-world scenarios, users can see how the WAREHOUSE_UTILIZATION view provides actionable insights. For instance:

1. Warehouse utilization by query concurrency: This can help you understand how warehouse utilization changes with the number of concurrent queries, which can be useful for optimizing warehouse sizing and query concurrency settings.

2. Warehouse Idle time and active time for the specific time period: This can help you understand the idle time and percentage of Idle time for each warehouse in a Snowflake account. It filters the data for the past 7 days and groups the results by warehouse name. The output includes the warehouse name, start date, idle minutes, total minutes, and idle percentage.

How to Optimize Your Warehouses Using Utilization Metrics

Now, Using above metrics, you can make data-driven decisions about warehouse configurations and operations.

Adjusting Auto-Suspend Settings:

Idle Time: Calculate idle time using the provided query to determine if a warehouse is frequently suspended.
Auto-Suspend Property: Set the auto-suspend property to a low value (e.g., 60 seconds) if idle time is significant and query performance is not critical. Set it to a few minutes (e.g., 2–5 minutes) if query performance is important.

Resizing Warehouses:

Low Utilization: Consider downsizing or reducing clusters if utilization is consistently low (for example, < 1) and there’s no spillage.
High Utilization: Consider upsizing the warehouse if utilization is high (for example, > 75%) and there’s spillage to remote or local storage.

Consolidating Warehouses:

Outlier Queries: Move outlier queries to a new warehouse if they have similar resource needs.
Moderately Utilized Warehouses: Consolidate similar workloads into a single multi-cluster or standard warehouse.
Ad-Hoc Queries: Consider a common ad-hoc warehouse for multiple teams to improve efficiency.

Adjusting Multi-Cluster Warehouse Settings:

Scaling Policy: Adjust the scaling policy (economy or standard) based on median utilization and queuing.
If the median utilization is low: changing the scaling policy to economy
If utilization is high (for example, > 70%) : Snowflake recommends leaving it set to standard

Additional Considerations

Comprehensive Analysis: Use multiple metrics from QUERY_HISTORY and WAREHOUSE_LOAD_HISTORY for a deeper understanding of warehouse usage.
Optimize Queries: Improve query performance to reduce resource consumption and improve utilization.
Leverage Snowflake Features: Utilize features like Clustering, SOS, time travel, and Query Acceleration Service (QAS) for optimization.

Conclusion:

In Conclusion, Snowflake’s Warehouse Utilization feature (currently in Private Preview) offers a comprehensive solution for optimizing resource consumption and reducing costs. By providing detailed insights into warehouse usage patterns, it empowers users to make data-driven decisions regarding auto-suspend settings, warehouse sizing, consolidation, and multi-cluster scaling. This ultimately leads to improved efficiency and reduced expenses, making Warehouse Utilization an invaluable tool for organizations seeking to maximize the return on their Snowflake investment.

About the Author

Karthik Srinivasan Raman | Head of Snowflake Tech Consulting | LTIMindtree

www.linkedin.com/in/karthik-s-raman

https://medium.com/@karthiksraman

Mastering Snowflake Warehouse Utilization: A Deep Dive into the WAREHOUSE_UTILIZATION View was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.