[Bug] Using --empty Flag with dynamic_table Materialization Results in Snowflake Error

[lloydbranch]

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

When attempting to execute dbt with the run/build commands with the --empty flag, the following error is received from Snowflake:

002728 (23001): SQL compilation error: Dynamic Tables must have at least one base table.

Impact: This is preventing us from fully testing projects in our CI pipeline

Expected Behavior

When attempting to execute dbt with the run/build commands with the --empty flag, the dynamic table builds successfully containing 0 records without Snowflake throwing an error.

Steps To Reproduce

Leveraging the jaffle_shop dbt project...

1. Create a dbt profile of type snowflake

2. Create a Snowflake Dynamic table model.
   Name: customers__dynamic_table__example__error_on_empty.sql
   Body:

   {{
       config({
           "materialized": "dynamic_table",
           "schema": "public",
           "alias": "customers__dynamic_table__example__error_on_empty",
           "target_lag": "10 minutes",
           "snowflake_warehouse": "DYNAMIC_TABLES_SMALL",
       })
   }}
   
   select
       *
   from {{ ref('customers') }}

3. Attempt to run the model and receive the following output:

   root@:/usr/project/dbt# dbt run --models customers__dynamic_table__example__error_on_empty --full-refresh --empty
   23:44:21  Running with dbt=1.8.7
   23:44:21  [WARNING]: Deprecated functionality
   The `tests` config has been renamed to `data_tests`. Please see
   https://docs.getdbt.com/docs/build/data-tests#new-data_tests-syntax for more
   information.
   23:44:21  Registered adapter: snowflake=1.8.3
   23:44:21  Unable to do partial parsing because of a version mismatch
   23:44:25  Found 6 models, 3 seeds, 1 operation, 20 data tests, 1060 macros
   23:44:25  
   23:44:26  Concurrency: 3 threads (target='*****')
   23:44:26  
   23:44:26  1 of 1 START sql dynamic_table model *****.customers__dynamic_table__example__error_on_empty  [RUN]
   23:44:27  1 of 1 ERROR creating sql dynamic_table model *****.customers__dynamic_table__example__error_on_empty  [ERROR in 1.22s]
   23:44:27  
   23:44:27  Running 1 on-run-end hook
   23:44:27  1 of 1 START hook: jaffle_shop.on-run-end.0 .................................... [RUN]
   23:44:27  1 of 1 OK hook: jaffle_shop.on-run-end.0 ....................................... [OK in 0.00s]
   23:44:27  
   23:44:27  
   23:44:27  Finished running 1 dynamic table model, 1 project hook in 0 hours 0 minutes and 2.22 seconds (2.22s).
   23:44:28  
   23:44:28  Completed with 1 error and 0 warnings:
   23:44:28  
   23:44:28    Database Error in model customers__dynamic_table__example__error_on_empty (models/dynamic_table/customers__dynamic_table__example__error_on_empty.sql)
     002728 (23001): SQL compilation error: Dynamic Tables must have at least one base table.
     compiled code at target/run/jaffle_shop/models/dynamic_table/customers__dynamic_table__example__error_on_empty.sql
   23:44:28  
   23:44:28  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1
   root@:/usr/project/dbt# 
   

Relevant log output

23:56:10.160639 [debug] [Thread-1 (]: Using snowflake connection "model.jaffle_shop.customers__dynamic_table__example__error_on_empty"
23:56:10.161183 [debug] [Thread-1 (]: On model.jaffle_shop.customers__dynamic_table__example__error_on_empty: /* {"app": "dbt", "dbt_version": "1.8.7", "profile_name": "jaffle_shop", "target_name": "*****", "node_id": "model.jaffle_shop.customers__dynamic_table__example__error_on_empty"} */
create dynamic table JAFFLE_SHOP_DEV.*****.customers__dynamic_table__example__error_on_empty
        target_lag = '10 minutes'
        warehouse = DYNAMIC_TABLES_SMALL
        as (
            

select
    *
from (select * from JAFFLE_SHOP_DEV.*****.customers where false limit 0)
        )
23:56:10.517388 [debug] [Thread-1 (]: Snowflake adapter: Snowflake query id: x-x-x-x

Environment

- OS: Linux-6.10.4-linuxkit-x86_64-with-glibc2.31
- Python: 3.11.2
- dbt-core: 1.8.7
- dbt-snowflake: 1.8.3

Additional Context

The issue appears to be related to the syntax where false.

SQL Causing Failure

create dynamic table JAFFLE_SHOP_DEV.public.customers__dynamic_table__example__error_on_empty
        target_lag = '10 minutes'
        warehouse = DYNAMIC_TABLES_SMALL
        as (
            

select
    *
from (select * from JAFFLE_SHOP_DEV.public.customers where false limit 0)

Message: SQL compilation error: Dynamic Tables must have at least one base table.

SQL Executing Successfully (where false -> where true=false)

create dynamic table JAFFLE_SHOP_DEV.public.customers__dynamic_table__example__error_on_empty
        target_lag = '10 minutes'
        warehouse = DYNAMIC_TABLES_SMALL
        as (
            

select
    *
from (select * from JAFFLE_SHOP_DEV.public.customers where true=false limit 0)
        );

Message: Dynamic table CUSTOMERS__DYNAMIC_TABLE__EXAMPLE__ERROR_ON_EMPTY successfully created.