Why Snowflake SQL extends standard SQL - Why It Works

Introduction

Standard SQL is a common language to work with databases, but it has limits. Snowflake SQL adds extra features to solve real problems like handling big data, working with cloud storage, and making queries faster and easier.

When you want to analyze large amounts of data stored in the cloud quickly and easily.

When you need to combine data from different sources like files and databases in one query.

When you want to use built-in functions for semi-structured data like JSON without extra coding.

When you want to scale your data warehouse automatically without managing hardware.

When you want to share data securely with others without copying it.

Commands

This command shows the current Snowflake version, confirming you are connected to Snowflake and ready to use its extended SQL features.

Terminal

SELECT CURRENT_VERSION();

Expected Output

CURRENT_VERSION()

8.50.0

This command uses Snowflake's extended function PARSE_JSON to convert a JSON string into a queryable object, which standard SQL does not support.

Terminal

SELECT PARSE_JSON('{"name":"Alice","age":30}') AS data;

Expected Output

DATA

{"name":"Alice","age":30}

This command extracts values from the JSON object using Snowflake's extended syntax for semi-structured data, showing how Snowflake SQL extends standard SQL to handle JSON easily.

Terminal

SELECT data:name::string AS name, data:age::int AS age FROM (SELECT PARSE_JSON('{"name":"Alice","age":30}') AS data);

Expected Output

NAME  AGE

Alice 30

This command creates a table with a VARIANT column type, which is unique to Snowflake and allows storing semi-structured data like JSON, extending standard SQL capabilities.

Terminal

CREATE OR REPLACE TABLE example_table (id INT, info VARIANT);

Expected Output

Table EXAMPLE_TABLE successfully created.

This command inserts JSON data into the VARIANT column, demonstrating Snowflake's ability to store and query semi-structured data directly.

Terminal

INSERT INTO example_table VALUES (1, PARSE_JSON('{"city":"Seattle","population":750000}'));

Expected Output

1 row inserted.

This command queries the JSON data inside the VARIANT column using Snowflake's extended SQL syntax, showing how it extends standard SQL for flexible data querying.

Terminal

SELECT info:city::string AS city FROM example_table WHERE id = 1;

Expected Output

CITY

Seattle

Key Concept

If you remember nothing else from this pattern, remember: Snowflake SQL adds special features to standard SQL to handle cloud data, semi-structured data, and scaling easily.

Common Mistakes

Trying to query JSON data using standard SQL syntax without Snowflake's extended functions.

Standard SQL does not understand JSON data types or the VARIANT column, so the query fails or returns errors.

Use Snowflake's PARSE_JSON function and colon notation (:) to access JSON fields inside VARIANT columns.

Assuming Snowflake SQL is exactly the same as standard SQL and ignoring its unique data types like VARIANT.

This causes confusion and errors when working with semi-structured data or cloud-specific features.

Learn and use Snowflake's extended data types and functions designed for cloud and semi-structured data.

Summary

Snowflake SQL extends standard SQL by adding support for semi-structured data like JSON using VARIANT columns and special functions.

It includes cloud-friendly features that make querying large and diverse data easier and faster.

Using Snowflake's extended SQL syntax allows you to work with modern data types and scale your data warehouse without extra complexity.

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