[Bug] Python Model Error Upgrading to dbt Cloud Versionless: Using snowflake.snowpark.session.create_dataframe() method

[jha-aurora]

Is this a new bug in dbt-snowflake?

• I believe this is a new bug in dbt-snowflake
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Recommended by dbt support to raise an issue here:

1. We were running on dbt Cloud 1.7 and testing our upgrade to dbt Cloud versionless
2. No changes were to our dbt project's repo, and we encountered errors on ALL our python models (roughly 10 models)

The error states as follows: "raise error_class(
snowflake.snowpark.exceptions.SnowparkSQLException: (1304): 01b7d7e3-0611-d6d2-0028-fb832aac9de2: 090106 (22000): Cannot perform DROP. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name."

I found a workaround, but I wanted to highlight this to you all to see if this is a bug. My workaround was to change the method we use in all our python models, that generates the output dataframe.

• We were using snowflake.snowpark.session.create_dataframe() method
• We switched to pandas.Dataframe() -- which resolved all our errors across all our python models

Expected Behavior

Expected behavior is for no error to occur, since we were only changing our dbt version from 1.7 to versionless

Steps To Reproduce

1. Change our environment in dbt Cloud to go from version 1.7 to versionless
2. Run our entire project's DAG (or just the set of python models, to be specific)
3. Errors occur on all our python models with the message

The error states as follows: "raise error_class(
snowflake.snowpark.exceptions.SnowparkSQLException: (1304): 01b7d7e3-0611-d6d2-0028-fb832aac9de2: 090106 (22000): Cannot perform DROP. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name."

Relevant log output

Here's an output of one of the python models that failed. Error message at bottom of the log.


18:17:42
18:17:42    Database Error in model [redacted_model_name] (models/intermediate/core/contracts/transformed/[redacted_model_name].py)
  100357 (P0000): Python Interpreter Error:
  Traceback (most recent call last):
    File "_udf_code.py", line 303, in main
      materialize(session, df, dbt.this)
    File "_udf_code.py", line 298, in materialize
      df.write.mode("overwrite").save_as_table('dbt_dev.STAGING."[redacted_model_name]"', table_type='')
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/telemetry.py", line 195, in wrap
      result = func(*args, **kwargs)
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/dataframe_writer.py", line 276, in save_as_table
      result = session._conn.execute(
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/server_connection.py", line 526, in execute
      result_set, result_meta = self.get_result_set(
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/analyzer/snowflake_plan.py", line 207, in wrap
      raise ne.with_traceback(tb) from None
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/analyzer/snowflake_plan.py", line 138, in wrap
      return func(*args, **kwargs)
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/server_connection.py", line 657, in get_result_set
      self.run_query(
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/server_connection.py", line 125, in wrap
      raise ex
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/server_connection.py", line 119, in wrap
      return func(*args, **kwargs)
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/server_connection.py", line 433, in run_query
      raise ex
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/server_connection.py", line 418, in run_query
      results_cursor = self.execute_and_notify_query_listener(
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/snowpark/_internal/server_connection.py", line 369, in execute_and_notify_query_listener
      results_cursor = self._cursor.execute(query, **kwargs)
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/connector/cursor.py", line 1015, in execute
      Error.errorhandler_wrapper(
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/connector/errors.py", line 232, in errorhandler_wrapper
      handed_over = Error.hand_to_other_handler(
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/connector/errors.py", line 287, in hand_to_other_handler
      cursor.errorhandler(connection, cursor, error_class, error_value)
    File "/usr/lib/python_udf/6d863adfdaffa5e0fdef55e2417de4099289e1d46420e02c249458438fbcbd66/lib/python3.8/site-packages/snowflake/connector/errors.py", line 165, in default_errorhandler
      raise error_class(
  snowflake.snowpark.exceptions.SnowparkSQLException: (1304): 01b7d7e3-0611-da5c-0028-fb832aacc2de: 090106 (22000): Cannot perform DROP. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.
   in function [redacted_model_name]_DBT_SP with handler main
  compiled code at target/run/windblade/models/intermediate/core/contracts/transformed/[redacted_model_name].py

Environment

- OS: N/A
- Python: 3.8 (per the log output)
- dbt-core: N/A
- dbt-snowflake: Unsure, b/c using dbt Cloud
- Snowflake Bundle: 2024_07

Additional Context

No response

[colin-rogers-dbt]

@jha-aurora a couple questions:

1. Does this project use any plugins?
2. Are there any project or model config overrides of the schema or warehouse?
3. Can you share the dbt and adapter version from the logs?

[jha-aurora]

hey @colin-rogers-dbt apologies on the late response! I don't think I got a notification for the comment

1. Does this project use any plugins?
2. Are there any project or model config overrides of the schema or warehouse?
3. Can you share the dbt and adapter version from the logs?

Responses:

1. No, I don't believe so; but just to be certain, where can I verify this in our project repo?
2. No config overrides made on these models; we have the schema and warehouse defined just in our project yaml file, for these models

• dbt version: 2024.10.258
• Registered adapter: snowflake=1.9.0-post8+eea98443b21f6f3bdc3d297b727a0a9424d54d7b

[Dikootje]

I'm experiencing the same issue in dbt Cloud. But cannot reproduce in Snowflake UI.

Steps I take trying to reproduce the error:

• When the model run has failed take a look at the Snowflake query history and lookup the failed query.
• Open the query in a new worksheet and execute.
• Executing exactly the same code as produced by dbt I cannot reproduce the error using the Snowflake UI.

Role, user, warehouse is exactly the same. Only difference I can see is the Snowflake connector (Go in SF UI vs Python for dbt)

[Dikootje]

Just figured out another workaround. Set the snowflake context manually within the model method:

def model(dbt, session): 
    session.use_database(dbt.this.database)
    session.use_schema(dbt.this.schema)