Code Bundles: EXECUTE CODE BUNDLE and EXECUTE NOTEBOOK PROJECT return RETURN_ VALUE instead of status or result¶
EXECUTE CODE BUNDLE and EXECUTE NOTEBOOK PROJECT
no longer return a success message in a column named status or result. A successful run returns one row with one nullable VARCHAR
column named RETURN_VALUE, which holds the value that the run returned, or NULL if it returned nothing. The status and result columns
are removed for every bundle type and compute type. The Code Bundles REST API and Python API change in the same way.
This change affects anything that reads the output of these commands, including:
- SQL scripts, stored procedures, and tasks that read the output with
RESULT_SCAN,$1, or a column name. - Applications that use a Snowflake driver or connector, such as JDBC, ODBC, or the Python connector, and read the result set.
- Orchestrators, CI/CD pipelines, and other third-party tools that run these commands and inspect the
statusorresultcolumn or the success message to decide whether a run succeeded. - Clients of the Code Bundles REST API and the Snowflake Python API.
The following table summarizes the change:
| Surface | Before the change | After the change |
|---|---|---|
| SQL result set | One column, status or result, that contains a success message. | One nullable VARCHAR column, RETURN_VALUE, that contains the run’s return value or NULL. |
| REST API response body | {"status": "..."} or {"result": "..."} | {"return_value": "..."}, or {"return_value": null} when the run returns nothing. |
Python API (CodeBundleResource.execute()) | Returns a SuccessResponse whose status holds the success message. | Returns an ExecuteCodeBundleResponse. Its return_value attribute holds the run’s return value (str or None). It isn’t a SuccessResponse, and its status is None. |
| Failed run | The statement raises an error and returns no row. | Unchanged: the statement raises an error and returns no row. |
- Before the change:
A successful run returns one row with one column that contains a success message. The column name and the message depend on how the run executes:
How the run executes Column Value of a successful run Notebook projects, and customCode Bundles on a compute poolstatusStatement executed successfully.mlCode BundlesstatusStatement executed successfully.customPython Code Bundles on a warehouseresultProject <name> executed successfullycustomJava or Scala Code Bundles on a warehouseresultAn empty string sparkCode Bundlesstatusorresult, depending on the Spark runtimeSpark Application completed successfully.or an empty stringFor example:
- After the change:
A successful run returns one row with one nullable
VARCHARcolumn namedRETURN_VALUE, for every bundle type and compute type. The column contains the value that the run returned:Entrypoint RETURN_VALUEA notebook ( .ipynb) on a compute pool that callsnotebook.exit(value)The value passed to notebook.exit().A Python method entrypoint in the form file.py:methodThe value that the method returns. Any other run, including a Python script entrypoint such as main.pyand Java or Scala Code BundlesNULL A Python
stris returned as is. Any other value is returned as JSON text, as produced byjson.dumps()with its default settings, so{"rows_loaded": 42}keeps the space after the colon and non-ASCII characters are escaped. A value that can’t be encoded as JSON fails the run with aReturnValueError. On a compute pool, including notebook runs, the encoded value can be at most 16 MiB; a larger value fails the run with aReturnValueError. On a warehouse, the size limits for stored procedure return values apply.For example, if
main.pydefines arunmethod that returns{"rows_loaded": 42}:A run that returns nothing produces one row in which
RETURN_VALUEis NULL:You can read the column as
$1,RETURN_VALUE, or"RETURN_VALUE". In Snowflake Scripting, useINTOto assign the value to a variable:
Impact on existing code¶
- Code that reads the column by name fails. A query or client that references the
statusorresultcolumn fails with an invalid identifier error, or gets no value from a driver’s column lookup. - Code that reads the column by position keeps running, but gets a different value. Code that uses
$1,fetchone()[0],rows[0][0], or a similar positional read now receives the run’s return value or NULL instead of the success message. - Code that compares the output to a success message stops matching. For example, a check such as
status = 'Statement executed successfully.'is never true after the change. A tool that treats a missing or different success message as a failure reports successful runs as failed. - REST API responses change.
POST /api/v2/databases/{database}/schemas/{schema}/code-bundles/{name}:executeandPOST /api/v2/code-bundle-executionsreturn the result row as JSON with lowercase column names. The body changes from{"status": ...}or{"result": ...}to{"return_value": ...}. - Python API checks change. Code that checks
isinstance(response, SuccessResponse)or readsresponse.statusafterCodeBundleResource.execute()no longer detects success. EXECUTE NOTEBOOK PROJECTchanges in the same way. Existing schedules and tasks that use theNOTEBOOK PROJECTgrammar keep running. Only code that reads their output is affected.
Actions required¶
Decide whether a run succeeded from whether the statement or request succeeded, not from its output. This approach works both before and after the change.
-
SQL and Snowflake Scripting: A run that completes without raising an error succeeded. Catch the error to handle a failure:
-
Drivers and connectors: Treat a statement that completes without an exception as a successful run. Don’t compare the first column to a success message. Read
RETURN_VALUEonly if your entrypoint returns a value: -
REST API: Use the HTTP status code to decide whether a run succeeded. Read
return_valuefrom the response body only if your entrypoint returns a value. -
Python API: Treat
CodeBundleResource.execute()returning without raising an exception as a successful run. Remove checks forSuccessResponseandresponse.status, and readresponse.return_valueif your entrypoint returns a value. -
Third-party tools and orchestrators: If a tool checks for a
statusorresultcolumn or a success message, update its configuration, or upgrade it to a version that decides success from whether the statement succeeds. -
Review past runs: To check the status of a run after it finishes, query the CODE_BUNDLE_HISTORY table function by the run’s query ID:
When RETURN_ VALUE is NULL after a successful run¶
In addition to runs that don’t return a value, RETURN_VALUE is NULL in the following cases, even when the notebook calls
notebook.exit(value) or the method returns a value:
- The statement returns before the run finishes, such as when the run is executed asynchronously.
- The stages that Snowflake uses to cache notebook results in your account aren’t encrypted with
SNOWFLAKE_FULLencryption.
Additional notes¶
- If you have questions or concerns about this change, contact Snowflake Support.
- For the complete output reference, see EXECUTE CODE BUNDLE.
- For how to return a value from a notebook or a Python method, see Return a value from a run.
Ref: n/a