Why Snowflake SQL extends standard SQL - Quick Recap

Recall & Review

beginner

What is the main reason Snowflake SQL extends standard SQL?

Snowflake SQL adds features to handle cloud data warehousing needs like scalability, semi-structured data, and performance optimization.

intermediate

How does Snowflake SQL support semi-structured data differently from standard SQL?

Snowflake SQL includes native support for JSON, XML, and Avro formats with special functions to query and manipulate them easily.

intermediate

What feature in Snowflake SQL helps with automatic scaling and performance?

Snowflake SQL supports automatic clustering and micro-partitioning to optimize query speed without manual tuning.

advanced

Why does Snowflake SQL include extensions for time travel and data cloning?

These features allow users to access historical data easily and create zero-copy clones for testing or backup, which standard SQL does not provide.

intermediate

Name one way Snowflake SQL improves security compared to standard SQL.

Snowflake SQL integrates with cloud identity providers and supports dynamic data masking and role-based access control for better security.

What type of data does Snowflake SQL natively support that standard SQL often struggles with?

A Unstructured video files

B Only structured tables

C Semi-structured data like JSON

D Encrypted binary blobs

Snowflake SQL has built-in functions to handle semi-structured data formats such as JSON, which standard SQL does not natively support.

Which Snowflake feature allows querying past data states without restoring backups?

A Time Travel

B Data Cloning

C Micro-partitioning

D Auto-scaling

Time Travel lets users query historical data easily, a feature unique to Snowflake SQL.

How does Snowflake SQL improve query performance automatically?

A By manual index creation

B Through micro-partitioning and automatic clustering

C By limiting query size

D Using external caching only

Snowflake uses micro-partitioning and automatic clustering to optimize queries without manual tuning.

What is a benefit of Snowflake's zero-copy cloning?

A It duplicates data physically

B It compresses data permanently

C It deletes original data

D It creates instant copies without extra storage

Zero-copy cloning creates instant copies of data without duplicating storage, saving space and time.

Which security feature is enhanced in Snowflake SQL compared to standard SQL?

A Role-based access control

B Plain text passwords

C No encryption

D Open network access

Snowflake supports role-based access control and integrates with cloud identity providers for better security.

Explain why Snowflake SQL extends standard SQL and name two key features it adds.

Think about what cloud data needs that traditional SQL does not cover.

Describe how Snowflake SQL improves performance and security compared to standard SQL.

Consider both how queries run faster and how data is protected.

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