DataFrame API in Snowpark in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding DataFrame Lazy Evaluation in Snowpark

Consider the following Snowpark code snippet:

df = session.table("EMPLOYEES").filter(col("SALARY") > 50000)
df.count()

What happens when df.count() is called?

Snowflake

df = session.table("EMPLOYEES").filter(col("SALARY") > 50000)
df.count()

A The filter operation runs immediately and returns the count of employees with salary over 50000.

B No query runs until an action like count() is called; count() triggers the query execution and returns the number of matching rows.

C The count() method returns the total number of rows in the EMPLOYEES table ignoring the filter.

D The filter operation runs immediately but count() only returns a DataFrame object without executing the query.

💡 Hint

Think about when Snowpark sends queries to Snowflake.

Snowpark uses lazy evaluation. The filter builds the query but does not run it. The count() method triggers execution and returns the count of rows matching the filter.

❓ Configuration

intermediate

Correctly Creating a Snowpark DataFrame from a SQL Query

You want to create a DataFrame from a SQL query string in Snowpark. Which code snippet correctly creates the DataFrame?

A df = session.sql("SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'SALES'")

B df = session.table("SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'SALES'")

C df = session.execute("SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'SALES'")

D df = session.query("SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'SALES'")

💡 Hint

Check which method accepts raw SQL strings.

The session.sql() method runs a SQL query and returns a DataFrame. The table() method expects a table name, not a query string.

❓ Architecture

advanced

Optimizing Snowpark DataFrame Operations for Large Datasets

You have a large dataset and want to optimize your Snowpark DataFrame transformations to minimize data scanned and improve performance. Which approach is best?

A Use multiple collect() calls to fetch intermediate results to speed up processing.

B Load the entire dataset into memory before applying any filters or transformations.

C Apply filters early in the DataFrame chain to reduce data processed in later steps.

D Avoid using filters and rely on Snowflake's automatic optimization only.

💡 Hint

Think about reducing data volume early.

Applying filters early reduces the amount of data processed downstream, improving query performance and reducing costs.

❓ security

advanced

Handling Sensitive Data with Snowpark DataFrames

You need to process sensitive customer data using Snowpark DataFrames. Which practice ensures data security during processing?

A Use Snowflake's masking policies and restrict DataFrame access to authorized roles only.

B Export the data to local files, mask it there, then reload into Snowpark DataFrames.

C Disable all Snowflake security features and rely on Snowpark's client-side encryption.

D Share the DataFrame with all users to speed up processing and mask data manually.

💡 Hint

Consider built-in Snowflake security features.

Snowflake masking policies protect sensitive data at query time. Restricting DataFrame access to authorized roles ensures security during processing.

✅ Best Practice

expert

Ensuring Efficient Resource Usage with Snowpark Sessions

You have multiple Snowpark DataFrame operations running in parallel in your application. What is the best practice to manage Snowpark sessions to optimize resource usage?

A Avoid using Snowpark sessions and run raw SQL queries instead.

B Create a new Snowpark session for each DataFrame operation to isolate workloads.

C Close the Snowpark session immediately after creating each DataFrame to free resources.

D Create a single shared Snowpark session and reuse it across all operations to reduce overhead.

💡 Hint

Think about session overhead and resource management.

Reusing a single Snowpark session reduces connection overhead and optimizes resource usage. Creating many sessions can cause unnecessary load.

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