DataFrame API in Snowpark in Snowflake - Commands & Configuration

Introduction

Working with large data sets inside Snowflake can be slow and complex if you use SQL alone. The DataFrame API in Snowpark lets you write simple code to manipulate data like a spreadsheet, but it runs fast inside Snowflake without moving data around.

When you want to process data inside Snowflake using simple code instead of complex SQL queries.

When you need to filter, group, or transform large tables without exporting data.

When you want to build data pipelines that run close to your data for speed and security.

When you prefer programming with familiar DataFrame operations like select, filter, and join.

When you want to chain multiple data operations in a clear and readable way.

Commands

Connect to Snowflake using SnowSQL CLI with your account, user, warehouse, database, and schema to run commands.

Terminal

snowsql -a myaccount -u myuser -w mywarehouse -d mydatabase -s public

Expected Output

Welcome to SnowSQL!

You are now connected to Snowflake.

→

-a - Specifies the Snowflake account name

→

-u - Specifies the username

→

-w - Specifies the warehouse to use

→

-d - Specifies the database to use

→

-s - Specifies the schema to use

Run a Python script that uses Snowpark DataFrame API to load a table, filter rows, and show results.

Terminal

python3 snowpark_dataframe_example.py

Expected Output

id | name    | age

---+---------+----

3  | Charlie | 35

Key Concept

If you remember nothing else from this pattern, remember: Snowpark DataFrame API lets you write simple code to process data inside Snowflake efficiently without moving data out.

Code Example

Snowflake

from snowflake.snowpark import Session

connection_parameters = {
    "account": "myaccount",
    "user": "myuser",
    "password": "mypassword",
    "warehouse": "mywarehouse",
    "database": "mydatabase",
    "schema": "public"
}

session = Session.builder.configs(connection_parameters).create()

# Load table as DataFrame
people_df = session.table("people")

# Filter rows where age > 30
filtered_df = people_df.filter(people_df["age"] > 30)

# Show results
filtered_df.show()

session.close()

Common Mistakes

Trying to run Snowpark DataFrame code without setting up the Snowflake connection properly.

The code cannot connect to Snowflake, so it fails to load or process data.

Always configure your Snowflake account, user, warehouse, database, and schema correctly before running Snowpark code.

Using DataFrame operations without calling collect() or show() to get results.

DataFrame operations are lazy and do not execute until you ask for results, so no output appears.

Use collect() or show() methods to trigger execution and see data.

Summary

Connect to Snowflake using SnowSQL or Snowpark session with correct credentials.

Use Snowpark DataFrame API to load tables and apply filters or transformations.

Call show() or collect() to execute and view the results inside Snowflake.

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