Model: Tech Support Advisor

Prompt:

I'm working with dbt 1.8.4 and starting to do some dynamic table work, and now I'm getting this error any ideas?

22:58:39   Database Error in model stg_mi9_bis_dwh_mat__ft_fee_e0_c0_f0_t1_s0 (models/staging/mi9_bis_dwh_mat/stg_mi9_bis_dwh_mat__ft_fee_e0_c0_f0_t1_s0.sql)

 090106 (22000): Cannot perform CREATE TABLE. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.

compiled Code at target/run/my_project/models/staging/mi9_bis_dwh_mat/stg_mi9_bis_dwh_mat__ft_fee_e0_c0_f0_t1_s0.sql

Response:

I'm not going to quote the response because ChatGPT took me on a wild goose chase.

I eventually figured out that this is the error we get if there is an existing view since dbt does not realize its there and drop it. So when it tries to create the table we get the above error.

Analysis:

This is a great example of how sometimes the most complex-sounding errors have simple root causes. The error message "Cannot perform CREATE TABLE. This session does not have a current schema" is misleading because it suggests a schema context issue, when the real problem is that dbt is trying to create a table where a view already exists.

The Real Issue:

  • dbt's state tracking didn't recognize an existing view in the target location
  • When dbt tries to create a dynamic table (or regular table), it conflicts with the existing view
  • Snowflake throws the cryptic schema error instead of a more helpful "object already exists" message

Solutions:

  1. Manual cleanup: Drop the existing view manually before running dbt
  2. dbt fresh start: Use dbt run --full-refresh to force recreation of models
  3. Schema management: Ensure proper pre-hook or on-schema-change configurations
  4. State file cleanup: Sometimes clearing dbt's target directory helps reset state tracking

Prevention:

This type of issue often occurs when:

  • Switching between different materialization types (view → table → dynamic table)
  • Running dbt in different environments without proper state management
  • Manual database changes outside of dbt's control

The lesson here: don't always trust the first error message you see. Sometimes you need to dig deeper and think about what dbt is actually trying to do behind the scenes.