Snowpark DataFrames for Data Cleaning: Lazy Evaluation, Selecting Fields and Casting Types

1.Why cleaning in Snowpark is really writing a query

In this subdomain, every cleaning task can be done two ways: as SQL, or as Snowpark for Python. They are less different than they look. The Snowpark library is built so that your Python code can process data in Snowflake without moving data to the system where your application code runs. You work through a DataFrame, which the documentation compares to a query that still has to be evaluated.

That comparison explains how a Snowpark cleaning pipeline behaves. Using a DataFrame takes three steps. First you construct it and name the data source: a table, a staged file, local values, or a SQL statement. Then you say how to transform it: which columns to select, how to filter rows, how to sort and group them. Last, you call a method that performs an action, such as collect(). Only that final step sends work to Snowflake.

SQL and Python meet in session.sql(). This method returns a DataFrame, and the documentation notes that the SQL statement won’t be executed until you call an action method. There is one catch. Transformation methods such as filter and select work only when the underlying statement is a SELECT. If you wrap a command like ls @my_stage in session.sql() and then call filter on it, the call fails. In the documented example the error is a SQL compilation error reporting invalid identifier 'SIZE'.

df = session.sql("select id, parent_id from sample_product_data where id < 10")
# Because the underlying SQL statement for the DataFrame is a SELECT statement,
# you can call the filter method to transform this DataFrame.
results = df.filter(col("id") < 3).select(col("id")).collect()

Sources12

2.Keeping the critical rows and dropping irrelevant fields

The exam guide asks you to identify critical data and remove irrelevant fields. None of the sources here defines "critical data" as a formal term, so treat it as a practical job. Decide which rows and which columns the analysis actually needs, and cut the rest before anything else runs. In Snowpark you do that with the DataFrame transformation methods. The documentation's guidance is to call the DataFrame methods that transform the dataset to specify which columns to select and how to filter, sort, group, etc. results. filter keeps rows, select keeps columns, and you name columns with the col function. The example in the previous section does both: it filters to id < 3 and keeps only the id column.

In SQL, select only the columns you need. When the table is wide and only a few fields are irrelevant, SELECT * EXCLUDE is shorter: it returns every column except the ones you list. Use the parenthesised form to exclude several at once.

SELECT * EXCLUDE (department_id, employee_id) FROM employee_table;

Removing duplicate rows is another way of keeping only the rows that matter. In SQL, SELECT DISTINCT eliminates duplicate values from the result set, which only removes rows that are identical in every selected column. When duplicates share a key but differ in other columns, such as several versions of the same customer, you must choose which one to keep. The documentation's pattern for this is QUALIFY with ROW_NUMBER(): a window function numbers the rows inside each PARTITION BY group, the ORDER BY decides which row comes first, and keeping row number 1 keeps one row per key. The documentation describes this version as explicit about which duplicate to keep. If you need only one value per key, such as the latest updated_at, a GROUP BY with MAX or MIN does that instead.

QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

Sources234

3.Casting types: Column.cast in Snowpark, TRY_CAST in SQL

Raw data often arrives with the wrong types, for example numbers stored as text or values inside a VARIANT. In Snowpark you cast a column expression with .cast() and a type from snowflake.snowpark.types. The documentation shows this on JSON values pulled out with FLATTEN. The code below adds casting the values to a specific type and changing the names of the columns to the previous example. In the output, the quoted JSON strings become plain strings under readable headers.

df.join_table_function("flatten", col("src")["customer"]).select(col("value")["name"].cast(StringType()).as_("Customer Name"), col("value")["address"].cast(StringType()).as_("Customer Address")).show()

In SQL, CAST (or ::) raises an error on a value it cannot convert, and one bad row is enough to stop the whole statement. TRY_CAST is a special version of CAST for a subset of conversions. A failed conversion becomes NULL instead of an error, so you can find and handle those rows later as missing values. It has two limits. It only works for string expressions. Its target must be VARCHAR, NUMBER, DOUBLE, BOOLEAN, DATE, an interval variation, TIME, or one of the TIMESTAMP types.

SELECT TRY_CAST('05/16' AS TIMESTAMP);

Sources25

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.TRY_CAST is a drop-in, error-free replacement for CAST on any column type.Why is that wrong?

   TRY_CAST accepts only string expressions and converts only to a listed set of target types. For other sources you still need CAST.

   Covered in Casting types: Column.cast in Snowpark, TRY_CAST in SQL

2. 2.Any statement passed to session.sql() returns a DataFrame you can keep cleaning with filter and select.Why is that wrong?

   session.sql() always returns a DataFrame, but the transformation methods work only when the underlying statement is a SELECT. Other commands raise an error when transformed.

   Covered in Why cleaning in Snowpark is really writing a query