DataFrame API in Snowpark in Snowflake - Step-by-Step Execution

Process Flow - DataFrame API in Snowpark

Create Snowpark Session

↓

Load Data into DataFrame

↓

Apply Transformations

↓

Execute Actions (collect/show)

↓

Get Results or Write Back

This flow shows how you start a Snowpark session, load data into a DataFrame, transform it, then execute actions to get results or save data.

Execution Sample

Snowflake

session = Session.builder.configs(connection_params).create()
df = session.table('EMPLOYEES')
df_filtered = df.filter(df['DEPARTMENT'] == 'SALES')
df_filtered.show()

This code creates a session, loads the EMPLOYEES table into a DataFrame, filters rows where DEPARTMENT is SALES, then shows the filtered data.

Process Table

Step	Action	DataFrame State	Result/Output
1	Create Snowpark Session	No DataFrame yet	Session object ready
2	Load EMPLOYEES table into df	df contains all EMPLOYEES rows	DataFrame with all rows
3	Filter df where DEPARTMENT == 'SALES'	df_filtered contains only SALES rows	Lazy transformation, no data fetched yet
4	Call df_filtered.show()	Triggers execution	Displays filtered rows on screen
5	Execution completes	DataFrame state unchanged	Filtered data visible to user

💡 Execution stops after show() displays filtered data; transformations are lazy until action called

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 4
session	null	Session object created	Session object created	Session object created
df	null	DataFrame with all EMPLOYEES rows	DataFrame unchanged	DataFrame unchanged
df_filtered	null	null	DataFrame filtered to SALES rows	DataFrame unchanged

Key Moments - 2 Insights

Why does filtering the DataFrame not immediately show results?

Filtering is a lazy operation; it only builds the plan but does not run it until an action like show() is called, as seen in step 3 and 4 of the execution_table.

What happens when show() is called on a DataFrame?

show() triggers execution of all prior transformations and fetches the data to display, as shown in step 4 where the filtered data is output.

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of df_filtered after step 3?

A Contains only SALES department rows

B Empty DataFrame

C Contains all EMPLOYEES rows

D DataFrame with aggregated data

💡 Hint

Refer to the 'DataFrame State' column in row for step 3 in execution_table

At which step does the DataFrame actually fetch data from Snowflake?

A Step 2: Loading table

B Step 4: Calling show()

C Step 3: Filtering

D Step 1: Creating session

💡 Hint

Check the 'Result/Output' column in execution_table for when data is displayed

If you remove the show() call, what happens to the filtered DataFrame?

A DataFrame becomes empty

B Data is fetched and displayed automatically

C No data is fetched or displayed

D Session closes immediately

💡 Hint

Look at the lazy transformation explanation in key_moments and execution_table step 3

Concept Snapshot

DataFrame API in Snowpark:
- Create a session to connect to Snowflake
- Load data into DataFrame (lazy, no data fetched yet)
- Apply transformations (filter, select, etc.) lazily
- Call actions (show, collect) to execute and fetch data
- Results can be displayed or written back
- Transformations build a plan, actions run it

Full Transcript

In Snowpark, you start by creating a session to connect to Snowflake. Then you load data into a DataFrame, which is like a table in memory but no data is fetched yet. You can apply transformations like filtering rows, but these are lazy and only build a plan. When you call an action like show(), Snowpark runs the plan, fetches the data, and displays the results. This approach helps optimize queries and manage large data efficiently.

Practice

1. What does the filter() method do in Snowpark's DataFrame API?

easy

A. It adds new columns to the DataFrame.

B. It changes the column names of the DataFrame.

C. It selects rows that meet a specified condition.

D. It sorts the DataFrame by a column.

Solution

1. Step 1: Understand the purpose of filter()

   The filter() method is used to keep only rows that satisfy a condition.

2. Step 2: Compare with other methods

   Changing column names is done by withColumnRenamed(), adding columns by withColumn(), and sorting by orderBy().

3. Final Answer:

   It selects rows that meet a specified condition. -> Option C

4. Quick Check:

   filter() = select rows [OK]

Hint: Filter keeps rows matching condition, like a sieve [OK]

Common Mistakes:

• Confusing filter() with select()
• Thinking filter() changes columns
• Using filter() to sort data

2. Which of the following is the correct way to select columns name and age from a DataFrame df in Snowpark?

easy

A. df.select('name', 'age')

B. df.filter('name', 'age')

C. df.orderBy('name', 'age')

D. df.withColumn('name', 'age')

Solution

1. Step 1: Identify method to pick columns

   The select() method is used to choose specific columns from a DataFrame.

2. Step 2: Check other methods

   filter() is for rows, orderBy() sorts rows, and withColumn() adds or modifies columns.

3. Final Answer:

   df.select('name', 'age') -> Option A

4. Quick Check:

   Select columns = select() [OK]

Hint: Use select() to pick columns, not filter() or orderBy() [OK]

Common Mistakes:

• Using filter() to select columns
• Trying to order columns instead of rows
• Confusing withColumn() with select()

3. Given the following Snowpark code:

df = session.create_dataframe([(1, 'Alice'), (2, 'Bob'), (3, 'Carol')], schema=['id', 'name'])
df_filtered = df.filter(df['id'] > 1).select('name')
df_filtered.show()

What will be the output?

medium

A. Rows with names 'Bob' and 'Carol'

B. Rows with names 'Alice' only

C. Rows with ids 1, 2, and 3

D. Empty DataFrame

Solution

1. Step 1: Understand the filter condition

   The filter keeps rows where id > 1, so rows with id 2 and 3 remain.

2. Step 2: Select the 'name' column

   After filtering, only the 'name' column is selected, so output shows 'Bob' and 'Carol'.

3. Final Answer:

   Rows with names 'Bob' and 'Carol' -> Option A

4. Quick Check:

   Filter id>1 then select name = Bob, Carol [OK]

Hint: Filter first, then select columns to get desired rows [OK]

Common Mistakes:

• Including 'Alice' who has id 1
• Showing all columns instead of just 'name'
• Expecting empty output

4. What is wrong with this Snowpark code snippet?

df = session.create_dataframe([(10, 'X'), (20, 'Y')], schema=['score', 'label'])
df_filtered = df.filter(df['score'] = 20)
df_filtered.show()

medium

A. Using show() instead of collect()

B. Missing parentheses around filter condition

C. Incorrect schema definition

D. Using single equals (=) instead of double equals (==) in filter condition

Solution

1. Step 1: Check filter condition syntax

   The filter condition uses = which is assignment, not comparison. It should be ==.

2. Step 2: Verify other parts

   Schema is correct, parentheses are fine, and show() is valid to display results.

3. Final Answer:

   Using single equals (=) instead of double equals (==) in filter condition -> Option D

4. Quick Check:

   Use == for comparison in filter [OK]

Hint: Use == for conditions, not = [OK]

Common Mistakes:

• Using = instead of == in conditions
• Thinking show() is invalid
• Confusing schema errors with syntax errors

5. You want to create a new column age_group in a Snowpark DataFrame df that labels ages below 18 as 'minor' and 18 or above as 'adult'. Which code snippet correctly achieves this?

hard

A. df.select('age_group', F.when(df['age'] < 18, 'minor').otherwise('adult'))

B. df.withColumn('age_group', F.when(df['age'] < 18, 'minor').otherwise('adult'))

C. df.filter(df['age_group'] == 'minor')

D. df.orderBy('age_group')

Solution

1. Step 1: Understand how to add a conditional column

   The withColumn() method adds or replaces a column. Using F.when().otherwise() sets values conditionally.

2. Step 2: Check other options

   select() chooses columns but does not add new ones, filter() filters rows, and orderBy() sorts rows.

3. Final Answer:

   df.withColumn('age_group', F.when(df['age'] < 18, 'minor').otherwise('adult')) -> Option B

4. Quick Check:

   Add conditional column with withColumn + when [OK]

Hint: Use withColumn and when() to add conditional columns [OK]

Common Mistakes:

• Using select() to add columns
• Filtering instead of adding columns
• Ordering instead of creating new columns