SnowPro Advanced: MLOps Engineer free question 2 / 10 · Domain 1: Operationalize Data Preparation and Feature Engineering
A Snowpark Python feature job starts with this line, meant to load the day's files before features are computed: session.sql("copy into raw_events from @landing file_format=(type = csv)"). The job ends without an error, but RAW_EVENTS has no new rows. Which change makes the load actually happen?
- Call session.sql a second time with the same COPY statement.
- Call collect() on the DataFrame that session.sql returns.
- Build the load with session.table("raw_events") instead.
- Move the job to a Python worksheet, where session.sql runs at once.
Show the answer and explanation
Answer: B
Why this is correct
Under "Executing SQL Statements", session.sql takes the statement you pass in and returns a DataFrame, and the page adds that the SQL statement will not be executed until you call an action method. Every example on the page, including the copy into example, ends with collect(). The section on actions explains why: a DataFrame is lazily evaluated, and only an action such as collect, count, show or save_as_table sends the SQL to the server. Here the DataFrame was built and then dropped, so the COPY never ran and no error was raised.
Why the other options are wrong
"Build the load with session.table("raw_events") instead." … session.table builds a DataFrame over a table that already exists, so it reads rows rather than copying staged files into the table. It is also a DataFrame like any other: nothing is sent to the server for it until an action such as collect or count is performed.
"Call session.sql a second time with the same COPY statement." … Each call to session.sql returns a new DataFrame, and each of those is lazily evaluated in the same way. Calling it twice produces two unevaluated DataFrames, so the COPY statement is still never sent to the server.
"Move the job to a Python worksheet, where session.sql runs at once." … The page's Python worksheet examples call collect() as well; their comments say to prepend a return statement to return the collect() results. The worksheet supplies the database and schema context, but it does not change when the statement is executed.
Statements that do work, not queries
The trap is easiest to fall into with statements that change state, such as COPY, CREATE STAGE or ALTER WAREHOUSE, because nobody looks at their result. Make a habit of ending such calls with collect(), as the examples on the page do, and check the returned status row when the next step depends on the statement having succeeded.