SELECT with Snowflake functions - Step-by-Step Execution

Process Flow - SELECT with Snowflake functions

Start Query

↓

Parse SELECT statement

↓

Evaluate each function in SELECT

↓

Fetch data rows

↓

Apply functions to each row

↓

Return transformed results

↓

End Query

The query starts by parsing the SELECT statement, then evaluates each Snowflake function on the data rows, and finally returns the transformed results.

Execution Sample

Snowflake

SELECT UPPER(name), LENGTH(name) FROM users;

This query selects the uppercase version of 'name' and its length from the 'users' table.

Process Table

Step	Action	Input	Function Evaluated	Output
1	Parse SELECT statement	SELECT UPPER(name), LENGTH(name) FROM users;	N/A	Parsed successfully
2	Fetch first row	name='alice'	N/A	Row fetched
3	Apply UPPER(name)	alice	UPPER	ALICE
4	Apply LENGTH(name)	alice	LENGTH	5
5	Return transformed row	N/A	N/A	['ALICE', 5]
6	Fetch second row	name='bob'	N/A	Row fetched
7	Apply UPPER(name)	bob	UPPER	BOB
8	Apply LENGTH(name)	bob	LENGTH	3
9	Return transformed row	N/A	N/A	['BOB', 3]
10	No more rows	N/A	N/A	Query ends

💡 All rows processed, query execution complete

Status Tracker

Variable	Start	After 1	After 2	Final
name	N/A	alice	bob	N/A
UPPER(name)	N/A	ALICE	BOB	N/A
LENGTH(name)	N/A	5	3	N/A

Key Moments - 3 Insights

Why does the function UPPER(name) output 'ALICE' when the input is 'alice'?

Because the UPPER function converts all letters in the input string to uppercase, as shown in execution_table rows 3 and 7.

What happens when there are no more rows to process?

The query ends as indicated in execution_table row 10, stopping further function evaluations.

Are functions applied before or after fetching each row?

Functions are applied after fetching each row, as seen in execution_table where row fetching happens before function application.

Visual Quiz - 3 Questions

Look at the execution table, what is the output of LENGTH(name) at step 8?

A 5

B 3

C BOB

D alice

💡 Hint

Check the 'Output' column at step 8 in the execution_table.

At which step does the query finish processing all rows?

A Step 5

B Step 9

C Step 10

D Step 3

💡 Hint

Look for the step with 'Query ends' in the 'Output' column.

If the input name was 'charlie', what would UPPER(name) output?

A CHARLIE

B charlie

C 7

D char

💡 Hint

Refer to how UPPER transforms 'alice' to 'ALICE' in the variable_tracker.

Concept Snapshot

SELECT with Snowflake functions:
- Use SELECT to choose columns and apply functions.
- Functions like UPPER() and LENGTH() transform data per row.
- Query fetches rows, applies functions, returns results.
- Functions run after data retrieval, per row.
- Query ends when no rows remain.

Full Transcript

This visual execution traces a Snowflake SELECT query using functions. The query selects the uppercase version and length of the 'name' column from the 'users' table. First, the query is parsed. Then each row is fetched one by one. For each row, the UPPER function converts the name to uppercase, and LENGTH calculates the string length. The transformed row is returned. This repeats until all rows are processed, then the query ends. Variables like 'name', 'UPPER(name)', and 'LENGTH(name)' change values as rows are processed. Key moments include understanding when functions apply and when the query stops. The quiz tests understanding of outputs at specific steps and function behavior.