[BUG] Can't debug invalid Snapshot DML

[dataders]

Describe the bug

what i wish was different

I wish Fusion could better direct users to offending DML queries either by:

1. surfacing a clickable path a .sql file containing the broken query
2. emitting the SQL query to STDINFO when they fail

Expected Core behavior

For most materializations, Core will create target/run folder for DDL and that is very helpful when debugging materialization errors that don't have to do with the model's SELECT statement.

However, TIL that while it does do this for snapshots's initial DDL, it does not save the DML anywhere so far as I can tell!

So if you have a snapshot that results in invalid DDL, you get an error like this

19:07:49  Failure in snapshot snapshot_raw_orders3 (snapshots/snapshot_raw_orders.yml)
19:07:49    Database Error in snapshot snapshot_raw_orders3 (snapshots/snapshot_raw_orders.yml)
  000904 (42000): SQL compilation error: error line 2 at position 15
  invalid identifier 'FAKE_COL'

I went to look for the DML, but that only contained the original DDL

/Users/dataders/Developer/jaffle-sandbox/target/run/jaffle_shop/snapshots/snapshot_raw_orders.yml/snapshot_raw_orders.yml/snapshot_raw_orders4.sql

create or replace transient table analytics_dev.dbt_dataders.snapshot_raw_orders4
[...]

Fortunately, the failed query does show up in logs/dbt.log

�[0m15:21:55.247024 [debug] [Thread-1 (]: On snapshot.jaffle_shop.snapshot_raw_orders3: select * from (
        select fake_col, order_date from (
                select * from analytics_dev.dbt_dataders.raw_orders
            ) subq
    ) as __dbt_sbq
    where false
    limit 0
/* {"app": "dbt", "dbt_version": "1.10.10", "profile_name": "default", "target_name": "default", "node_id": "snapshot.jaffle_shop.snapshot_raw_orders3"} */
�[0m15:21:55.517684 [debug] [Thread-1 (]: Snowflake adapter: Snowflake query id: 01bf9f69-0911-e771-000e-69011dbb8187
�[0m15:21:55.518266 [debug] [Thread-1 (]: Snowflake adapter: Snowflake error: 000904 (42000): SQL compilation error: error line 2 at position 15
invalid identifier 'FAKE_COL'

Fusion world

Fusion, actually sets me up to be disappointed because of the stack trace of the error message in the same scenario.

A few observations:

1. CMD clicking the below path doesn't work bc the path is wrong -- it needs to be prefixed with target/

compiled/snapshots/snapshot_raw_orders3.sql:1:14

2. if you find your own way to that file, it only contains the SELECT statement not the DML that is erroring

-- target/compiled/snapshots/snapshot_raw_orders.sql

select * from analytics_dev.dbt_dataders.raw_orders

Failed [  1.74s] snapshot dbt_dataders.snapshot_raw_orders3 (table)
error: dbt1014: Error executing materialization macro 'dbt_snowflake.materialization_snapshot_snowflake' for snapshot snapshot.jaffle_shop.snapshot_raw_orders3: [Snowflake] 000904 (42000): SQL compilation error: error line 2 at position 15
invalid identifier 'FAKE_COL'
(in compiled/snapshots/snapshot_raw_orders3.sql:1:14)
(in dbt_internal_packages/dbt-snowflake/macros/materializations/snapshot.sql:3:24)
(in dbt_internal_packages/dbt-adapters/macros/materializations/snapshots/snapshot.sql:30:21)
(in dbt_internal_packages/dbt-adapters/macros/materializations/snapshots/strategies.sql:155:39)
(in dbt_internal_packages/dbt-adapters/macros/materializations/snapshots/strategies.sql:124:32)
(in dbt_internal_packages/dbt-adapters/macros/adapters/columns.sql:84:29)
(in dbt_internal_packages/dbt-adapters/macros/adapters/columns.sql:89:13)
(in dbt_internal_packages/dbt-adapters/macros/etc/statement.sql:14:35)
  --> compiled/snapshots/snapshot_raw_orders3.sql:1:14

Fortunately, just like Core, it contains the offending query

# logs/query_log.sql
-- created_at: 2025-10-10T19:09:13.508195+00:00
-- finished_at: 2025-10-10T19:09:13.647152+00:00
-- outcome: error
-- error vendor code: 904
-- error message: Internal: [Snowflake] 000904 (42000): SQL compilation error: error line 2 at position 15
invalid identifier 'FAKE_COL'
-- dialect: snowflake
-- node_id: snapshot.jaffle_shop.snapshot_raw_orders3
-- query_id: not available
-- desc: execute adapter call
select * from (
        select fake_col, order_date from (
                select * from analytics_dev.dbt_dataders.raw_orders
            ) subq
    ) as __dbt_sbq
    where false
    limit 0
/* {"app":"dbt","dbt_version":"2.0.0","profile_name":"default","target_name":"default","node_id":"snapshot.jaffle_shop.snapshot_raw_orders3"} */;

What version of dbt Fusion is this bug in? (find out by running dbt --version)
2.0.0-preview.45

Is this a discrepancy between the dbt Fusion Engine and dbt Core? Check one.

• YES
• NO
• SORT OF

To Reproduce

Using jaffle-sandbox:

1. dbtf seed -s raw_orders to create the initial table
2. define a snapshot like below

# snapshots/snapshot_raw_orders.yml
snapshots:
  - name: orders_snapshot
    relation: ref('raw_orders')
    config:
      unique_key: id
      strategy: check
      check_cols:
        - user_id
        - order_date

3. dbtf snapshot more than once