dbt-labs / dbt-labs/dbt

[Snowflake] `--empty` breaks identifier resolution on a case-insensitive target (CLDs)

Open
#15,744 1 comment 0 reactions 1 assignee Claimed by @TIHan View on GitHub
area:catalog-v2 bug engine:v2 proj:catalog-v2 snowflake status:needs-repro status:triage triage type:bug v2
Dominant language
Rust
Stars
13.8k
Forks
2.6k
Avg merge
21h 31m
Merged PRs (30d)
56

Description

### Is this a new bug in dbt v2.x compared to the latest version of dbt 1.x?

- [x] I believe this is a new bug in dbt v2.x
- [x] I have searched the existing issues and could not find a duplicate

### Current Behavior

On a Snowflake target that resolves identifiers case-insensitively (lower-cased delimited identifiers / CLDs, i.e. `QUOTED_IDENTIFIERS_IGNORE_CASE=TRUE`), `dbt build --empty` fails with an unquoted-field / identifier-resolution error. The same models on the same target build fine **without** `--empty`.

`--empty` wraps each upstream `ref()` in a zero-row subquery, and the downstream model then selects its columns by name out of that wrapper. That extra `select *` layer is where the casing mismatch surfaces. The compiled SQL also differs between distributions:

dbt 1.x:

```sql
from (select * from "memory"."main"."stg_customers" where false limit 0)
```

dbt v2.x:

```sql
from (select * from "main"."stg_customers" limit 0)
```

### Expected Behavior

`dbt build` and `dbt build --empty` should both succeed against a Snowflake CLD target and produce the same relations — the `--empty` run just populates them with zero rows. This works in dbt 1.x.

### Steps To Reproduce

Minimal project (2 models + 1 seed):

- `seeds/raw_customers.csv` — mixed-case columns `Id`, `CustomerName`, `Region`
- `models/stg_customers.sql` — selects `"Id"` / `"CustomerName"` / `"Region"`, aliased to `id`, `customer_name`, `region`
- `models/dim_customers.sql` — references those columns **unquoted**, plus `count(*) over (partition by region)` and a `group by`, so the references must resolve through the `--empty` wrapper

Against a Snowflake target with case-insensitive (CLD) identifier resolution:

```bash
# dbt 1.x — both succeed
dbt build --target snowflake
dbt build --empty --target snowflake

# dbt v2.x — first succeeds, second fails on dim_customers
dbt build --target snowflake
dbt build --empty --target snowflake
```

Only the `--empty` variant fails, so `--empty` is the trigger, not the models themselves.

### Relevant log output

```shell
Pending — see caveat in Additional Context.
```

### Environment

- dbt 1.x: dbt-core 1.12.0 (adapter: dbt-snowflake)
- dbt v2.x: dbt-fusion 2.0.0-preview.202
- Snowflake account/session with `QUOTED_IDENTIFIERS_IGNORE_CASE=TRUE`

### Which database adapter are you using?

snowflake

### Is this a discrepancy vs. dbt 1.x?

- [x] Yes — this works in dbt 1.x but not in dbt v2.x

### Additional Context

Likely related to `quoting.snowflake_ignore_case`, added in #13533 — the `--empty` wrapper may not be honoring that setting.

DuckDB is not a substitute for reproducing this: it folds identifiers differently, and both distributions compile the `--empty` SQL there without error.

**Caveat:** the Snowflake run above has not yet been executed — no live credentials were available. What is confirmed is the compiled-SQL divergence shown above and that the project is valid end-to-end on dbt 1.x. The Snowflake failure is the expected-but-unconfirmed half of this report.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.