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.