ps_oph

Did Snowsight need a revamped user interface for cost management? Absolutely! That’s why Snowflake has officially (general availability) launched BUDGETS!

A budget establishes a monthly cap on compute expenses for either a Snowflake account or a specific collection of Snowflake objects. If the projected spending exceeds this limit, an email notification is sent daily to the designated recipients for budget alerts.

How do we go about setting it up? That’s the task for today!

• Create a budget for the entire account to oversee all computing resources throughout the month, comparing their spending to the set limit.
• Generate a budget tailored to a particular set of resources (Snowflake objects) within your account, potentially corresponding to a business unit, department, or application. Afterwards, keep tabs on their expenditures compared to the allocated limit for the month.

Some hints in the picture below:

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‘
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

Welcome to the Frosty_Friday Aggregation Policy challenge! You are tasked with creating SQL queries that comply with Snowflake’s data aggregation policy. Your queries should aggregate data from a given table or view, ensuring that each group meets the minimum size requirement specified in the aggregation policy.

About aggregation policy:
Snowflake offers a feature to share datasets while maintaining control over data usage through aggregation policies. These policies allow data owners to specify minimum group sizes for aggregation, enhancing data privacy and security.

Requirements:

1. Utilize SQL queries to aggregate data from the specified table or view.
2. Adhere to the allowed aggregation functions permitted by the aggregation policy.
3. Ensure that each group formed by the query contains at least the specified minimum number of records as per the aggregation policy.

Instructions:

1. Use the code below to create a sample table named Sales_Records
2. Define an aggregation policy with a minimum group size (X).
3. Create SQL queries that aggregate data from the chosen dataset, adhering to the aggregation policy requirements.
4. Test your queries to ensure compliance with the policy.

-- Create the Sales_Records table
CREATE TABLE Sales_Records (
    Order_ID INT,
    Product_Name VARCHAR(50),
    Product_Category VARCHAR(50),
    Quantity INT,
    Unit_Price DECIMAL(10,2),
    Customer_ID INT
);

-- Insert sample data into the Sales_Records table
INSERT INTO Sales_Records (Order_ID, Product_Name, Product_Category, Quantity, Unit_Price, Customer_ID) VALUES
(1, 'Laptop', 'Electronics', 2, 1200.00, 101),
(2, 'Smartphone', 'Electronics', 1, 800.00, 102),
(3, 'Headphones', 'Electronics', 5, 50.00, 103),
(4, 'T-shirt', 'Apparel', 3, 20.00, 104),
(5, 'Jeans', 'Apparel', 2, 30.00, 105),
(6, 'Sneakers', 'Footwear', 1, 80.00, 106),
(7, 'Backpack', 'Accessories', 4, 40.00, 107),
(8, 'Sunglasses', 'Accessories', 2, 50.00, 108),
(9, 'Watch', 'Accessories', 1, 150.00, 109),
(10, 'Tablet', 'Electronics', 3, 500.00, 110),
(11, 'Jacket', 'Apparel', 2, 70.00, 111),
(12, 'Dress', 'Apparel', 1, 60.00, 112),
(13, 'Sandals', 'Footwear', 4, 25.00, 113),
(14, 'Belt', 'Accessories', 2, 30.00, 114),
(15, 'Speaker', 'Electronics', 1, 150.00, 115),
(16, 'Wallet', 'Accessories', 3, 20.00, 116),
(17, 'Hoodie', 'Apparel', 2, 40.00, 117),
(18, 'Running Shoes', 'Footwear', 1, 90.00, 118),
(19, 'Earrings', 'Accessories', 4, 15.00, 119),
(20, 'Ring', 'Accessories', 2, 50.00, 120);

May the aggregation be with you!

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.

You are tasked with optimizing a query in Snowflake that extracts information from a table called sales_data. The sales_data table contains information about sales transactions, including columns like product_id, quantity_sold, price, and transaction_date.

Your goal? Retrieving the top 10 products with the highest total revenue, where the total revenue is calculated as the sum of the product of quantity_sold and price for each transaction.

Hint: In a SELECT statement, the QUALIFY clause filters the results of window functions.

-- Create sales_data table
CREATE TABLE sales_data (
  product_id INT,
  quantity_sold INT,
  price DECIMAL(10,2),
  transaction_date DATE
);

-- Insert sample values
INSERT INTO sales_data (product_id, quantity_sold, price, transaction_date)
VALUES
  (1, 10, 15.99, '2024-02-01'),
  (1, 8, 15.99, '2024-02-05'),
  (2, 15, 22.50, '2024-02-02'),
  (2, 20, 22.50, '2024-02-07'),
  (3, 12, 10.75, '2024-02-03'),
  (3, 18, 10.75, '2024-02-08'),
  (4, 5, 30.25, '2024-02-04'),
  (4, 10, 30.25, '2024-02-09'),
  (5, 25, 18.50, '2024-02-06'),
  (5, 30, 18.50, '2024-02-10');

Be sure to share your coded answers in the comments!

Thank you so much for the great advice Can Höbek! Your contribution was very helpful, how would I do without you.

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.

Just ten days ago Snowflake announced the general availability of creating a new table when loading a file in Snowsight. How? By using the INFER_SCHEMA table function to automatically detect the file metadata schema, retrieve the column definitions, and generate a new table.

All you need to do is:

1. Log on to Snowsight.
2. Go to the “Data” section in the navigation menu, then select “Databases.”
3. Choose a specific database and schema you want to work with.
4. Click “Create.”
5. In the drop-down menu, select “Table” and then choose “From File.”

And voila, you’ll discover how cool this new feature is! It will certainly save you time!

Shapefiles play a crucial role in BI by providing a geospatial foundation for data analysis. These files, comprising geometric and attribute information, enable businesses to integrate location-based data seamlessly into their analytical workflows. Recognizing the importance of location-based information, Snowflake allows users to store, query, and analyze geospatial data. How to load geospatial data that is in a shapefile into a Snowflake table?

Some previous steps before starting the challenge:

• Download shapefile as a .zip archive from here: https://mapcruzin.com/free-italy-arcgis-maps-shapefiles.htm#google_vignette (feel free to choose any other type of shapefile from any other platform, I really like trains so I chose this one🚆).
• Open this file. You can do it online here: https://mapshaper.org/. Below is a screenshot of the picked file opened by Mapshaper.

At this point, let the challenge begin:

• Load the .zip file into a Snowflake stage. (There are at least three ways to do that!).
• Find out how to convert the shapefile into a Snowflake table with geospatial features.
• Review the results (as in the picture below).

Can’t wait to see your ideas!

We all know about Snowflake’s great care for applications (native or not). So let’s imagine the scenario where we have developed an app. It allows customers of FrostyFriday Inc. to upload files to a database, in a raw Snowflake schema and to initiate all the needed transformations to have a final view (into another schema) ready to be presented, just by clicking a button in the app.

File management is the total responsibility of the client. In this specific case, in the ‘birthdate’ column, we notice that some rows are in the format ‘YYYY-MM-DD’, some in ‘DD/MM/YYYY’, and even in some lines there are values like ‘453921’ that are completely wrong.

The goal of this challenge is to have all the dates in the right format in the final view. How would you do it?

P.S: there is a clue about the type of function to use, it’s up to you to find it!

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.

In a BI scenario, arrays of multisets can be useful for representing and analyzing data with multiple values for a single attribute. Multiset arrays can be particularly relevant in scenarios where you have multivalued or hierarchical data. Here we deal with a common use case, namely product category management, where a product may belong to multiple categories.

-- Create the Sales table
CREATE OR REPLACE TABLE Sales (
    Sale_ID INT PRIMARY KEY,
    Product_IDs VARIANT --INT
);

-- Inserting sample sales data
INSERT INTO Sales (Sale_ID, Product_IDs) SELECT 1, PARSE_JSON('[1, 3]');-- Products A and C in the same sale
INSERT INTO Sales (Sale_ID, Product_IDs) SELECT 2, PARSE_JSON('[2, 4]');-- Products B and D in the same sale


-- Create the Products table
CREATE OR REPLACE TABLE Products (
    Product_ID INT PRIMARY KEY,
    Product_Name VARCHAR,
    Product_Categories VARIANT --VARCHAR
);

-- Inserting sample data into Products
INSERT INTO Products (Product_ID, Product_Name, Product_Categories) SELECT 1, 'Product A', ARRAY_CONSTRUCT('Electronics', 'Gadgets');
INSERT INTO Products (Product_ID, Product_Name, Product_Categories) SELECT 2, 'Product B', ARRAY_CONSTRUCT('Clothing', 'Accessories');
INSERT INTO Products (Product_ID, Product_Name, Product_Categories) SELECT 3, 'Product C', ARRAY_CONSTRUCT('Electronics', 'Appliances');
INSERT INTO Products (Product_ID, Product_Name, Product_Categories) SELECT 4, 'Product D', ARRAY_CONSTRUCT('Clothing');

Using the tables that we have just created, how can we find common categories among products sold together in a single transaction? Like in the example shown below.