Snowflake Copilot is an AI-powered SQL assistant that simplifies data exploration, query generation, and optimization. It seamlessly integrates with your Snowflake environment, enabling you to interact with your data using natural language, making complex analysis accessible even for non-SQL experts.

A Practical Example: Sales Data Analysis

Let's explore how to use Snowflake Co-pilot in a practical scenario.

⛁ Sample Data Model: salesdb-data-model

Setup the SalesDB sample data model and proceed with the rest of the exercises. This model provides a robust foundation for tracking customers, buyers, clients, and sales opportunities.

>_ SQL

// Assign the databaseschema used co-pilot
use schema

Let's delve into how Snowflake Copilot can supercharge your data workflow:

1.0 Data Exploration

• Prompt: "What is in the table Customer?"

  • Copilot Response: A description of the Customer table's columns, data types, and purpose will be presented. In this case, you'll see details about CustomerID, FirstName, LastName, etc.

• Prompt: "What type of events can I filter by in the Opportunities table?"

  • Copilot Response: Copilot might identify the SalesStage and LeadSource columns as suitable for filtering, explaining their possible values (e.g., 'Closed Won,' 'Partner Referral').

• Prompt: "Are there any tables joinable to Customer?"

  • Copilot Response: It will highlight that the Buyer and Opportunities tables can be joined to Customer through the CustomerID foreign key.

SQL Generation and Refinement

• Prompt: "What are all the customer names that start with 'F'?"

  • Copilot Response:

    SELECT FirstName, LastName FROM Customer WHERE FirstName LIKE 'F%'

    
    

• Prompt: "How many customers do we have that have placed an order in the last 90 days?"

  • Copilot Response: This will involve a join between Customer and Opportunities, filtering on ExpectedCloseDate within the last 90 days, and then counting distinct CustomerID.

  
  

• Prompt: "Give a view to show the name of the Device and status of all the orders sorted by order date in ascending order." (Assuming you meant "Customer Name" instead of "Device")

  • Copilot Response:

CREATE OR REPLACE VIEW customer_orders AS
SELECT c.FirstName, c.LastName, o.SalesStage, o.ExpectedCloseDate 
FROM Customer c
JOIN Opportunities o ON c.CustomerID = o.CustomerID
ORDER BY o.ExpectedCloseDate ASC

• Follow-up Prompt: "Good. Please include the opportunity amount too."

  • Copilot Response: (The view will be modified to include the Amount column)

• Follow-up Prompt: "Bravo. What name should I call that view?"

  • Copilot Response: (Suggests potential view names like CustomerOrdersWithAmount or SalesPipelineByCustomer)

Snowflake Knowledge and Query Improvement

• Prompt: "How do I write a SQL join?"

  • Copilot Response: A brief explanation of SQL joins, with links to relevant Snowflake documentation.

• Prompt: "What is Snowpark Cortex?"

  • Copilot Response: An overview of Snowpark and its capabilities, including the Cortex framework for machine learning.

• Prompt: "Help improve this SQL query..." (Followed by your example query)

  • Copilot Response: Suggestions on how to rewrite the query using CASE expressions or other optimizations to improve its efficiency.

2.0 Advanced Use Cases with Copilot

With your refined sales data model, you can leverage Snowflake Copilot's capabilities to dive deeper into your data analysis:

Basic Exploration:

• Prompt: "Who are my top 10 customers by total contract value?"

  • Copilot Response:

SELECT c.CustomerID, c.FirstName, c.LastName, SUM(cl.ContractValue) AS TotalContractValue
FROM Customer c
JOIN Buyer b ON c.CustomerID = b.CustomerID
JOIN Client cl ON b.BuyerID = cl.BuyerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
ORDER BY TotalContractValue DESC
LIMIT 10

• Prompt: "What are the most common lead sources for our opportunities?"

  • Copilot Response:

SELECT LeadSource, COUNT(*) AS LeadCount
FROM Opportunities
GROUP BY LeadSource
ORDER BY LeadCount DESC

Relationship Analysis:

• Prompt: "Is there a relationship between lead source and average opportunity value?"

  • Copilot Response:

SELECT LeadSource, AVG(Amount) AS AvgOpportunityValue
FROM Opportunities
GROUP BY

• Prompt: "Which zip codes have the highest number of closed deals?"

  • Copilot Response:

SELECT c.ZipCode, COUNT(o.OpportunityID) AS ClosedDeals
FROM Customer c
JOIN Opportunities o ON c.CustomerID = o.CustomerID
WHERE o.SalesStage = 'Closed Won'
GROUP BY c.ZipCode
ORDER BY ClosedDeals DESC

Trend Spotting:

• Prompt: "How has the number of opportunities changed over the last 6 months, broken down by month?"

  • Copilot Response:

SELECT DATE_TRUNC('month', LoadDate) AS Month, COUNT(*) AS OpportunityCount
FROM Opportunities
WHERE LoadDate >= DATEADD(month, -6, CURRENT_DATE)
GROUP BY Month
ORDER BY Month

Issue Identification:

• Prompt: "Show me customers who have become buyers but haven't yet signed a contract."

  • Copilot Response:

SELECT c.*
FROM Customer c
JOIN Buyer b ON c.CustomerID = b.CustomerID
LEFT JOIN Client cl ON b.BuyerID = cl.BuyerID
WHERE cl.ClientID IS NULL

• Prompt: "Are there any opportunities with negative amounts?"

  • Copilot Response:

SELECT *
FROM Opportunities
WHERE Amount < 0

Predictive Modeling:

• Prompt: "Can you predict the likelihood of an opportunity closing based on its lead source and sales stage?"

  • Copilot Response:

    • Copilot would ideally guide you through the necessary steps to create a predictive model.

    • It might suggest using Snowflake's ML capabilities, Snowpark, or integration with external ML libraries.

3.0 Enhancing Your Data Journey with Copilot

By leveraging Snowflake Copilot's capabilities, you can:

• Accelerate insights: Quickly get answers to your questions without writing complex SQL queries.

• Democratize data access: Enable non-technical users to explore and analyze data independently.

• Improve SQL proficiency: Learn from Copilot's suggestions and explanations.

• Enhance productivity: Free up your time for strategic analysis by automating routine tasks.

Co-pilot Feedback:

Remember, Snowflake Copilot is a tool that learns and improves over time. Don't hesitate to provide feedback (thumbs up/down) to help it refine its suggestions and become an even more valuable asset in your data journey.

Key Points:

1. Custom Instructions: You can provide Copilot with specific instructions to tailor its behavior. For instance, you can guide it to prioritize query performance over readability or request detailed explanations alongside SQL queries.

2. Feedback Mechanism: Copilot features thumbs-up and thumbs-down buttons to allow you to rate its responses. This feedback helps Snowflake refine and improve the tool over time.

3. Contextual Awareness: Copilot leverages the context of your current worksheet, including the database and schema you're using. This enables it to generate more relevant and accurate suggestions.

4. Data Privacy: Copilot doesn't have direct access to the data in your tables, ensuring the privacy and security of your information.

   pen_spark

Summary:

These are just a few examples, and Copilot's potential expands as your data model grows in complexity. The quality of Copilot's responses depends on the clarity of your prompts and the structure of your data. Feel free to experiment with different questions and explore the full capabilities of Snowflake Copilot.

Reset Environment:

USE ROLE ACCOUNTADMIN;  -- Or a role with sufficient privileges

-- Drop the database
DROP DATABASE IF EXISTS invoicesdb CASCADE;

-- Revoke the role from the user (if applicable)
REVOKE ROLE IF EXISTS doc_ai_role FROM USER fru; -- Replace 'fru' with your username
-- Drop the role
DROP ROLE IF EXISTS

Resources:

• Snowflake Copilot User Guide: Get the official lowdown on Snowflake Copilot's features, use cases, and limitations. Find it here: https://docs.snowflake.com/en/user-guide/snowflake-copilot

• Snowflake SQL Reference: Brush up on your SQL skills and explore the full capabilities of Snowflake's query language: https://docs.snowflake.com/en/sql-reference/

• Snowflake Community: Connect with other Snowflake users, ask questions, and share knowledge: https://community.snowflake.com/