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.