Why Snowflake SQL extends standard SQL - Visual Breakdown

Process Flow - Why Snowflake SQL extends standard SQL

Start with Standard SQL

↓

Add Cloud Data Warehouse Features

↓

Support Semi-Structured Data Types

↓

Enable Scalable Performance Optimizations

↓

Provide Advanced Analytical Functions

↓

Result: Snowflake SQL Extends Standard SQL

Snowflake SQL starts with standard SQL and adds cloud-specific features, semi-structured data support, performance improvements, and advanced analytics.

Execution Sample

Snowflake

SELECT
  customer_id,
  COUNT(*) AS order_count,
  AVG(order_total) AS avg_order,
  PARSE_JSON(order_details) AS details
FROM orders
GROUP BY customer_id;

This query uses standard SQL aggregation and Snowflake's PARSE_JSON to handle semi-structured data.

Process Table

Step	SQL Feature	Standard or Extended	Action	Result
1	SELECT customer_id, COUNT(*), AVG(order_total)	Standard SQL	Aggregate orders by customer	Groups rows by customer_id with counts and averages
2	PARSE_JSON(order_details)	Snowflake Extension	Parse semi-structured JSON data	Converts order_details string into JSON object
3	GROUP BY customer_id	Standard SQL	Group rows for aggregation	Ensures aggregation per customer
4	Execution in Cloud	Snowflake Extension	Leverage scalable compute	Query runs efficiently on cloud infrastructure
5	Advanced Functions (e.g., window functions)	Snowflake Extension	Enable complex analytics	Supports advanced data analysis
6	End	-	-	Query returns aggregated and parsed data per customer

💡 Query completes after aggregating and parsing data using both standard and extended SQL features.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
customer_id	N/A	Grouped values	Grouped values	Grouped values	Grouped values
order_count	N/A	Count per customer	Count per customer	Count per customer	Count per customer
avg_order	N/A	Average per customer	Average per customer	Average per customer	Average per customer
details	Raw JSON string	Raw JSON string	Parsed JSON object	Parsed JSON object	Parsed JSON object

Key Moments - 3 Insights

Why does Snowflake SQL include PARSE_JSON when standard SQL does not?

Because Snowflake supports semi-structured data natively, PARSE_JSON lets you convert JSON strings into queryable objects, as shown in execution_table step 2.

How does Snowflake SQL improve query performance compared to standard SQL?

Snowflake runs queries on scalable cloud compute resources (step 4), allowing faster execution than traditional databases.

Are standard SQL aggregation functions like COUNT and AVG still used in Snowflake SQL?

Yes, standard SQL functions like COUNT and AVG are fully supported and used for grouping and aggregation (steps 1 and 3).

Visual Quiz - 3 Questions

Look at the execution_table, at which step is semi-structured data handled?

A Step 1

B Step 4

C Step 2

D Step 5

💡 Hint

Check the 'SQL Feature' column for PARSE_JSON in step 2.

According to variable_tracker, what is the state of 'details' after Step 3?

A Raw JSON string

B Parsed JSON object

C Null

D Aggregated count

💡 Hint

Look at the 'details' row under 'After Step 3' in variable_tracker.

If Snowflake did not extend SQL with cloud features, which step would be missing?

A Step 4

B Step 2

C Step 1

D Step 3

💡 Hint

Step 4 mentions execution in cloud leveraging scalable compute.

Concept Snapshot

Snowflake SQL builds on standard SQL by adding:
- Native support for semi-structured data (e.g., JSON via PARSE_JSON)
- Cloud-native scalable execution for performance
- Advanced analytical functions beyond standard SQL
This lets you query diverse data types efficiently in the cloud.

Full Transcript

Snowflake SQL extends standard SQL by adding features that support cloud data warehousing. It includes functions like PARSE_JSON to handle semi-structured data, which standard SQL does not support. Snowflake also runs queries on scalable cloud infrastructure, improving performance. Standard SQL features like aggregation remain fully supported. This combination allows users to run powerful, flexible queries on both structured and semi-structured data efficiently.

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