Why Snowflake SQL extends standard SQL - See It in Action

Why Snowflake SQL Extends Standard SQL

📖 Scenario: You are working as a cloud data engineer. Your company uses Snowflake as its cloud data warehouse. You want to understand how Snowflake SQL builds on standard SQL to handle cloud data tasks better.

🎯 Goal: Learn how Snowflake SQL extends standard SQL by creating a simple table, adding a configuration for clustering, querying with a Snowflake-specific function, and completing the table with a clustering key.

📋 What You'll Learn

Create a table with specific columns using Snowflake SQL syntax

Add a clustering key configuration variable

Use a Snowflake-specific function in a SELECT query

Complete the table definition by adding the clustering key

💡 Why This Matters

🌍 Real World

Cloud data warehouses like Snowflake extend SQL to handle large-scale, cloud-native data storage and querying efficiently.

💼 Career

Understanding Snowflake SQL extensions is essential for cloud data engineers and analysts working with modern cloud data platforms.

1

Create a table with columns id and name

Write a Snowflake SQL statement to create a table called employees with two columns: id as INTEGER and name as VARCHAR(100).

Snowflake

-- Write the CREATE TABLE statement for employees with id and name columns
-- Your code here

Need a hint?

Use CREATE TABLE employees (id INTEGER, name VARCHAR(100));

2

Add a clustering key configuration variable

Add a variable called clustering_key and set it to the string 'id' to represent the clustering column.

Snowflake

CREATE TABLE employees (
  id INTEGER,
  name VARCHAR(100)
);
-- Define clustering_key variable
-- Your code here

Need a hint?

Use SET clustering_key = 'id'; to define the clustering key variable.

3

Use a Snowflake-specific function in a SELECT query

Write a SELECT statement that retrieves id, name, and the current timestamp using Snowflake's CURRENT_TIMESTAMP() function from the employees table.

Snowflake

CREATE TABLE employees (
  id INTEGER,
  name VARCHAR(100)
);

SET clustering_key = 'id';
-- Write SELECT statement with CURRENT_TIMESTAMP() function
-- Your code here

Need a hint?

Use CURRENT_TIMESTAMP() to get the current time in Snowflake SQL.

4

Complete the table by adding the clustering key

Alter the employees table to add a clustering key on the id column using the variable clustering_key.

Snowflake

CREATE TABLE employees (
  id INTEGER,
  name VARCHAR(100)
);

SET clustering_key = 'id';

SELECT id, name, CURRENT_TIMESTAMP() AS current_time FROM employees;
-- Add clustering key to employees table
-- Your code here

Need a hint?

Use CLUSTER BY (id) in the CREATE TABLE statement to add clustering.