Archives for August 2024

DYNAMIC SQL!

Another new feature that, in my opinion, hasn’t had enough hype around it. Your task is to create the following table:

CREATE TABLE week_108 AS
SELECT * FROM VALUES
    (1, 'Alice', 'Laptop', 1, 1200.50),
    (2, 'Bob', 'Smartphone', 2, 800.00),
    (3, 'Charlie', 'Tablet', 1, 300.00),
    (4, 'David', 'Smartwatch', 3, 150.00),
    (5, 'Eva', 'Headphones', 2, 100.00),
    (6, 'Frank', 'Laptop', 1, 1300.00),
    (7, 'Grace', 'Smartphone', 1, 900.00),
    (8, 'Hank', 'Tablet', 4, 320.00),
    (9, 'Ivy', 'Smartwatch', 2, 180.00),
    (10, 'Jack', 'Headphones', 3, 110.00),
    (11, 'Karen', 'Laptop', 1, 1250.75),
    (12, 'Leo', 'Smartphone', 2, 850.00),
    (13, 'Mona', 'Tablet', 1, 350.00),
    (14, 'Nina', 'Smartwatch', 3, 160.00),
    (15, 'Oscar', 'Headphones', 2, 105.00),
    (16, 'Paul', 'Laptop', 1, 1350.00),
    (17, 'Quincy', 'Smartphone', 1, 950.00),
    (18, 'Rita', 'Tablet', 4, 330.00),
    (19, 'Sam', 'Smartwatch', 2, 200.00),
    (20, 'Tina', 'Headphones', 3, 115.00)
    AS sales(sale_id, customer_name, product_name, quantity, sale_amount);

And complete the following query:

SELECT *
FROM TABLE(
    TO_QUERY(
        SQL => '',
        <VARIABLE> => $<SOME_SESSION_VARIABLE_YOU_OUGHT_TO_CREATE>
    )
)

If the concept of session variables is a bit intimidating, read this page.

Hey there, you salty sea dog! As the mighty captain of this pirate crew, you have amassed a treasure trove worthy of legend! Your booty, gathered from distant islands and fierce naval skirmishes, is a veritable mountain of riches, including a fine assortment of valuable trinkets and captured ships. But the time has come to make it easier for your crew to locate all the precious loot hidden in our vault.

To do this job, you’ll need to set up a search system using Snowflake’s Cortex search service so that your crew can quickly find any gems or pieces of gold they’re looking for among the pile.

CREATE OR REPLACE TABLE Treasures_Collected (
    Treasure_ID INT AUTOINCREMENT,
    Treasure_Name VARCHAR(255),
    Treasure_Type VARCHAR(255),
    Estimated_Value DECIMAL(10, 2),
    Discovery_Date DATE,
    Discovery_Island VARCHAR(255)
);

INSERT INTO Treasures_Collected (Treasure_Name, Treasure_Type, Estimated_Value, Discovery_Date, Discovery_Island) VALUES
('Golden Chest', 'Jewels', 5000.00, '1721-04-20', 'Tortuga'),
('Pearl Necklace', 'Jewels', 3000.00, '1720-08-15', 'Nassau'),
('Silver Coins', 'Coins', 1500.00, '1722-06-01', 'Cayman'),
('Royal Scepter', 'Relics', 7500.00, '1723-02-25', 'Bermuda'),
('King’s Sapphire', 'Precious Stones', 12000.00, '1723-09-20', 'Havana'),
('Ancient Map', 'Artifacts', 1800.00, '1721-11-12', 'Tortuga'),
('Emerald Ring', 'Jewels', 2500.00, '1721-06-25', 'Nassau'),
('Gold Goblet', 'Relics', 3000.00, '1722-07-18', 'Cayman'),
('Ivory Dagger', 'Weapons', 4000.00, '1723-05-30', 'Bermuda'),
('Opal Necklace', 'Jewels', 13000.00, '1723-12-15', 'Havana'),
('Bronze Statue', 'Artifacts', 2200.00, '1722-09-01', 'Tortuga'),
('Diamond Earrings', 'Jewels', 4000.00, '1721-10-10', 'Nassau'),
('Silver Chalice', 'Relics', 2800.00, '1722-04-12', 'Cayman'),
('Ancient Sword', 'Weapons', 5000.00, '1723-01-05', 'Bermuda'),
('Ruby Pendant', 'Jewels', 15000.00, '1723-08-22', 'Havana'),
('Ceremonial Mask', 'Artifacts', 1900.00, '1721-03-19', 'Tortuga'),
('Gold Bracelets', 'Jewels', 3500.00, '1722-02-10', 'Nassau'),
('Platinum Coins', 'Coins', 1800.00, '1722-11-14', 'Cayman'),
('Sapphire Ring', 'Jewels', 8000.00, '1723-07-07', 'Bermuda'),
('Emerald Necklace', 'Jewels', 16000.00, '1723-11-30', 'Havana');

Here’s what you need to do:

1. Create the search index: First, set up the treasure_search_service to catalog all our loot. This will help the crew quickly find every shiny piece and sparkling gem.
2. Check the search service: Once the index is ready, make sure the search service is in Bristol shape and fashion. (Here the author has gotten into character a bit too much). Run a preview query to locate all treasures of the type “jewelry.” Use the SNOWFLAKE.CORTEX.SEARCH_PREVIEW function to make sure everything is working properly.
3. Get specific: For a more precise search, run a preview query to find all the jewels looted from the island of “Nassau.” This will help your crew reach the right cache without any problems. (The intended result in the picture below).

Set sail on this quest and soon your crew will find its treasure like child’s play!

Challenge: Implementing Dynamic Data Masking in Snowflake

This week’s focus centers around leveraging Snowflake’s Dynamic Data Masking feature to protect sensitive information based on user roles. Imagine you are a data engineer at a company where data privacy and role-based access control are crucial for compliance and security. Your mission is to implement dynamic data masking to ensure that sensitive data is properly masked based on the user’s role while allowing necessary data access for different business functions.

Story Scenario:

Your company, SecureDataCorp, recently underwent a security audit that highlighted the need for better data masking practices to protect sensitive customer information. The audit revealed that sensitive data such as credit card numbers, email addresses, and account balances were accessible to users without appropriate clearance. Your task is to implement Snowflake’s Dynamic Data Masking to ensure that only authorized users can view sensitive information while others see masked data.

As you engage with this challenge, consider the following tasks:

Detailed Tasks:

Initial Setup:

Task: Create a sample database and a table named customer_data containing columns for customer ID, name, email, phone, address, credit card number, and account balance. Populate this table with sample data.

CREATE DATABASE dynamic_data_masking_db;
USE DATABASE dynamic_data_masking_db;

CREATE TABLE customer_data (
    customer_id INTEGER,
    name STRING,
    email STRING,
    phone STRING,
    address STRING,
    credit_card_number STRING,
    account_balance FLOAT
);

INSERT INTO customer_data (customer_id, name, email, phone, address, credit_card_number, account_balance) VALUES
(1, 'John Doe', 'john.doe@example.com', '123-456-7890', '123 Main St', '4111111111111111', 15000.00),
(2, 'Jane Smith', 'jane.smith@example.com', '234-567-8901', '456 Elm St', '4222222222222222', 8500.00),
(3, 'Alice Johnson', 'alice.johnson@example.com', '345-678-9012', '789 Oak St', '4333333333333333', 3000.00),
(4, 'Bob Brown', 'bob.brown@example.com', '456-789-0123', '101 Pine St', '4444444444444444', 500.00),
(5, 'Charlie Davis', 'charlie.davis@example.com', '567-890-1234', '202 Maple St', '4555555555555555', 12000.00),
(6, 'Diana Evans', 'diana.evans@example.com', '678-901-2345', '303 Cedar St', '4666666666666666', 2000.00),
(7, 'Frank Green', 'frank.green@example.com', '789-012-3456', '404 Birch St', '4777777777777777', 30000.00),
(8, 'Hannah White', 'hannah.white@example.com', '890-123-4567', '505 Willow St', '4888888888888888', 4500.00),
(9, 'Ian Black', 'ian.black@example.com', '901-234-5678', '606 Aspen St', '4999999999999999', 7500.00),
(10, 'Jill Blue', 'jill.blue@example.com', '012-345-6789', '707 Cherry St', '4000000000000000', 500.00);

Define Roles and Users:

Task: Create three roles: admin, manager, and analyst. Assign them to these roles to simulate different levels of data access within the organization (You don’t have to create new users as long as you don’t have secondary_roles enabled).

Implement Masking Policies:

Task: Implement masking policies for the credit_card_number, email, and account_balance columns with the following logic:

• For analyst role: Fully mask the credit card number, mask the domain part of the email, and mask the account balance.
• For manager role: Partially mask the credit card number (showing only the last 4 digits), mask the domain part of the email, and partially mask the account balance by rounding it.
• For admin role: No masking is applied; the admin should see the full data.

Apply and Test Masking Policies:

Task: Apply the masking policies to the customer_data table. Test the implementation by querying the table as each role (admin, manager, analyst) and verify that the masking policies are applied correctly.

Steps to Complete the Challenge:

1. Initial Setup:
   • Use the provided start-up code to create the customer_data table and insert sample data.
2. Define Roles and Users:
   • Create roles: admin, manager, analyst.
   • Create users (optional) and assign them to respective roles.
3. Implement Masking Policies:
   • Create and apply masking policies for the credit_card_number, email, and account_balance columns as per the specified logic.
4. Apply and Test Masking Policies:
   • Verify that the masking policies are correctly applied by querying the customer_data table as different users with admin, manager, and analyst roles.

By the end of this challenge, you will have implemented role-based dynamic data masking in Snowflake, ensuring that sensitive data is protected and only visible to users with appropriate access.

Summit was nearly two months ago, and we’re still not through creating challenges on features put into GA and PuPr. This one is a pretty cool one, this week, we’re using Cortex Fine Tuning.

Before we begin, as of time of publishing, this feature is only available in:

• AWS US West 2 (Oregon)
• AWS US East 1 (N. Virginia)
• AWS Europe Central 1 (Frankfurt)

If you don’t have an account in any of those regions, get a trial account here (I’m sure one day the dream of a free tier will come).

THE CHALLENGE

You have two datasets:

• Processed Data
• Unprocessed Data

You need an LLM to help you classify which line items belong to which account code. This is the prompt you are to use:

Load the PROCESSED DATA into an table and use the following query to see what COMPLETE with mistral-7b can do:

with data_prep as (
    select
        -- Turn invoice number into just a number
        replace(invoice_number, 'INV-')::int as invoice_number,
        -- Extract line item valies
        value:"AccountCode"::string as account_code,
        value:"AccountName"::string as account_name,
        value:"Item"::string as item,
        vendor_name
    from accpay,
    -- flatten line items
    lateral flatten(input=>parse_json(line_items))
)
select
    -- Create prompt
    concat(
        'You are an agent that helps categorise invoices for our bookkeeping team.
        They need to categories each line item per account code and account name,
        the following account codes, and their account names:\n\n' || 
        (
            select array_to_string(array_distinct(array_agg(
                account_code || ' (' || account_name || ')')), ',\n'
            ) from data_prep
        ) 
        || '\n\nTry doing it for this invoice line item.'
        || '\n\nThe line item is: `' || item 
        || '` and the vendor is: `'|| vendor_name || '`.'
    ) as prompt,
    -- Assert the real account_code as completion
    account_code as completion,
    -- Test the model
    snowflake.cortex.complete(
        'mistral-7b',
        prompt
    ) as llm_response,
    -- Get the account code from the response
    regexp_substr(llm_response, '\\d{4}', 1, 1, 'e') as llm_account_code,
    -- Check whether the model got the answer right
    completion = llm_account_code as verification
from data_prep

Then:

• Create training and validation datasets with the PROCESSED DATA
• Create a fine tuning job on those datasets
• Load in the UNPROCESSED DATA
• Query the unprocessed data using your fine tuned model, which should return a result that looks a little like:

How fascinating are mermaid and pirate stories? Some would spend their entire summer vacations reading every word of these tales, while others would rather be swimming in the sea. For those who prefer the latter, having a summary of the story is a great reason to sit in front of the computer and use Cortex!

The goal of this challenge is to create a complete pipeline in Snowflake for processing PDF files, extracting and chunking their text content, embedding the text for further analysis, and generating summaries of the text chunks.

Setup steps:

• Create the environement: database, schema, warehouse.
• Define a Python function that given the file_url as parameter, it returns a table with a single column chunk of type VARCHAR. (Hint: check here , very carefully xd ).
• Create a stage with a Directory Table. (You can do that directly from Snowsight).
• Upload into the stage the three PDF files (you can find them here).

The goal is to have a table STORIES_SUMMARY_TABLE with three rows (as shown in the figure below), where for each story, you have the relative_path as story_title and the summary of the text that is produced by using a specific Cortex function (guess which one? The challenge’s title could be useful).

Stories about mermaids and pirates are undeniably fascinating. But can we say that it is equally amazing to create summaries of them in this way? Once you solve this challenge, the sea awaits, maybe you will find treasure!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.