Snowflake editions and pricing - Mini Project: Build & Apply

Snowflake Editions and Pricing Setup

📖 Scenario: You are working as a cloud data engineer for a company that wants to understand Snowflake's editions and pricing structure. Your task is to create a simple data structure that holds the different Snowflake editions and their pricing per credit. This will help the team decide which edition fits their budget and needs.

🎯 Goal: Create a Snowflake-compatible table structure that lists Snowflake editions and their pricing per credit. Then, add a configuration variable for a budget threshold. Finally, write a query to select editions that fit within the budget and complete the setup by adding a comment describing the pricing model.

📋 What You'll Learn

Create a table called snowflake_editions with columns edition (string) and price_per_credit (number).

Insert exact rows for editions: 'Standard' at 2.0, 'Enterprise' at 3.5, 'Business Critical' at 4.5, and 'Virtual Private Snowflake' at 5.5.

Create a variable called budget_threshold set to 4.0 to represent the maximum price per credit allowed.

Write a query to select all editions from snowflake_editions where price_per_credit is less than or equal to budget_threshold.

Add a comment to the table snowflake_editions describing that it holds Snowflake editions and their pricing per credit.

💡 Why This Matters

🌍 Real World

Companies use Snowflake editions and pricing data to plan budgets and choose the right service level for their data needs.

💼 Career

Understanding how to create tables, use variables, and query data in Snowflake is essential for cloud data engineers and analysts working with Snowflake.

1

Create the Snowflake editions table with pricing

Create a table called snowflake_editions with columns edition as VARCHAR and price_per_credit as FLOAT. Insert these exact rows: ('Standard', 2.0), ('Enterprise', 3.5), ('Business Critical', 4.5), and ('Virtual Private Snowflake', 5.5).

Snowflake

-- Create table snowflake_editions and insert rows
-- Your code here

Need a hint?

Use CREATE OR REPLACE TABLE to create the table. Use INSERT INTO with multiple rows to add the editions and prices.

2

Add a budget threshold variable

Create a variable called budget_threshold and set it to 4.0 to represent the maximum price per credit allowed.

Snowflake

CREATE OR REPLACE TABLE snowflake_editions (
    edition VARCHAR,
    price_per_credit FLOAT
);

INSERT INTO snowflake_editions (edition, price_per_credit) VALUES
('Standard', 2.0),
('Enterprise', 3.5),
('Business Critical', 4.5),
('Virtual Private Snowflake', 5.5);

-- Create variable budget_threshold
-- Your code here

Need a hint?

Use SET to create a session variable in Snowflake.

3

Query editions within the budget threshold

Write a query to select all columns from snowflake_editions where price_per_credit is less than or equal to the variable budget_threshold.

Snowflake

CREATE OR REPLACE TABLE snowflake_editions (
    edition VARCHAR,
    price_per_credit FLOAT
);

INSERT INTO snowflake_editions (edition, price_per_credit) VALUES
('Standard', 2.0),
('Enterprise', 3.5),
('Business Critical', 4.5),
('Virtual Private Snowflake', 5.5);

SET budget_threshold = 4.0;

-- Query editions within budget_threshold
-- Your code here

Need a hint?

Use $budget_threshold to reference the variable in the query.

4

Add a comment describing the pricing table

Add a comment to the table snowflake_editions that says: 'This table holds Snowflake editions and their pricing per credit.'

Snowflake

CREATE OR REPLACE TABLE snowflake_editions (
    edition VARCHAR,
    price_per_credit FLOAT
);

INSERT INTO snowflake_editions (edition, price_per_credit) VALUES
('Standard', 2.0),
('Enterprise', 3.5),
('Business Critical', 4.5),
('Virtual Private Snowflake', 5.5);

SET budget_threshold = 4.0;

SELECT * FROM snowflake_editions
WHERE price_per_credit <= $budget_threshold;

-- Add comment to snowflake_editions table
-- Your code here

Need a hint?

Use COMMENT ON TABLE to add a description to the table.