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

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

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

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

Hint

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

Practice

1. Why does Snowflake SQL extend standard SQL?

easy

A. To remove complex SQL commands

B. To only support basic SQL queries

C. To add cloud-specific features and simplify data handling

D. To limit data types available

Solution

1. Step 1: Understand Snowflake's purpose

   Snowflake is designed for cloud data platforms, so it adds features that help with cloud data management.

2. Step 2: Compare with standard SQL

   Standard SQL lacks some cloud-specific functions and data types that Snowflake provides to make data handling easier.

3. Final Answer:

   To add cloud-specific features and simplify data handling -> Option C

4. Quick Check:

   Snowflake extends SQL for cloud features = B [OK]

Hint: Snowflake adds cloud tools to standard SQL [OK]

Common Mistakes:

• Thinking Snowflake removes SQL commands
• Believing Snowflake limits data types
• Assuming Snowflake only supports basic queries

2. Which of the following is a valid Snowflake SQL syntax extension?

easy

A. SELECT * FROM table WHERE ARRAY_CONTAINS(column, 'value');

B. SELECT * FROM table WHERE column IN ('value1', 'value2');

C. SELECT * FROM table WHERE column = 'value';

D. SELECT * FROM table WHERE column LIKE '%value%';

Solution

1. Step 1: Identify Snowflake-specific functions

   ARRAY_CONTAINS is a Snowflake extension to check if an array contains a value, not standard SQL.

2. Step 2: Compare other options

   Options A, B, and D use standard SQL syntax and functions.

3. Final Answer:

   SELECT * FROM table WHERE ARRAY_CONTAINS(column, 'value'); -> Option A

4. Quick Check:

   ARRAY_CONTAINS is Snowflake extension = C [OK]

Hint: Look for functions not in standard SQL like ARRAY_CONTAINS [OK]

Common Mistakes:

• Confusing standard SQL IN with Snowflake extensions
• Thinking LIKE is a Snowflake extension
• Assuming all functions are standard SQL

3. What will this Snowflake SQL query return?

SELECT ARRAY_SIZE(ARRAY_CONSTRUCT(1, 2, 3)) AS size;

medium

A. NULL

B. Error: ARRAY_SIZE not supported

C. 1, 2, 3

D. 3

Solution

1. Step 1: Understand ARRAY_CONSTRUCT

   ARRAY_CONSTRUCT creates an array with elements 1, 2, and 3.

2. Step 2: Understand ARRAY_SIZE

   ARRAY_SIZE returns the number of elements in the array, which is 3.

3. Final Answer:

   3 -> Option D

4. Quick Check:

   ARRAY_SIZE of 3-element array = 3 [OK]

Hint: ARRAY_SIZE counts elements in Snowflake arrays [OK]

Common Mistakes:

• Expecting a list instead of count
• Thinking ARRAY_SIZE is unsupported
• Confusing ARRAY_CONSTRUCT output

4. Identify the error in this Snowflake SQL query:

SELECT OBJECT_KEYS('key1', 'key2') FROM table;

medium

A. Missing WHERE clause

B. OBJECT_KEYS expects a single OBJECT, not multiple strings

C. Incorrect table name

D. OBJECT_KEYS is not a Snowflake function

Solution

1. Step 1: Check OBJECT_KEYS usage

   OBJECT_KEYS requires one OBJECT argument, not multiple string arguments.

2. Step 2: Analyze query structure

   The query passes two strings instead of one object, causing an error.

3. Final Answer:

   OBJECT_KEYS expects a single OBJECT, not multiple strings -> Option B

4. Quick Check:

   OBJECT_KEYS needs one object argument = A [OK]

Hint: OBJECT_KEYS takes one object, not multiple strings [OK]

Common Mistakes:

• Thinking multiple strings are valid arguments
• Assuming missing WHERE causes error here
• Believing OBJECT_KEYS is unsupported

5. How can Snowflake SQL extensions help when working with semi-structured data like JSON?

hard

A. By providing functions to parse and query JSON directly

B. By converting JSON to plain text only

C. By disallowing JSON data types

D. By requiring external tools to handle JSON

Solution

1. Step 1: Recognize Snowflake's JSON support

   Snowflake extends SQL with functions and data types to handle JSON and other semi-structured data directly.

2. Step 2: Compare other options

   Options A, B, and C contradict Snowflake's built-in JSON capabilities.

3. Final Answer:

   By providing functions to parse and query JSON directly -> Option A

4. Quick Check:

   Snowflake supports JSON parsing natively = D [OK]

Hint: Snowflake parses JSON inside SQL without extra tools [OK]

Common Mistakes:

• Thinking JSON must be converted to text first
• Believing JSON is unsupported
• Assuming external tools are always needed