# dbt-external-tables FAILS interacting with Snowflake

**URL:** <https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046>\
**Category:** Help\
**Tags:** snowflake, dbt-cloud, macros\
**Created:** [April 15, 2025, 5:45pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046 "2025-04-15T17:45:34Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![aleppek](https://avatars.discourse-cdn.com/v4/letter/a/8797f3/32.png) [@aleppek](https://discourse.getdbt.com/u/aleppek)\
**Post date:** [April 15, 2025, 5:45pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/1 "2025-04-15T17:45:35Z")

</div>

I’m trying to use the dbt-external-tables package with my Snowflake account, through dbt Cloud. This should create raw tables for me, using S3 as the source. Below is my source configuration:

```auto
version: 2

sources:
  - name: dc305_animals
    database: "{{env_var('DBT_DB_NAME')}}"
    schema: _raw
    loader: S3
    tables:
      - name: file91
        description: Data from the FILEOUT91 file
        identifier: raw_dc305_animals__file91
        external:
          location: "@{{env_var('DBT_DB_NAME')}}.external_stages.s3_staging_bucket"
          pattern: "owner=\\d+/datatype=management/provider=dc305_animals/year=\\d{4}/month=\\d{2}/day=\\d{2}/FILEOUT91.*[.]parquet"
          file_format: "(type = parquet)"
          infer_schema: true

```

When I run `dbt run-operation stage_external_sources` I get the following error (a sample of the full log):

```auto
17:34:40 Opening a new connection, currently in state init
17:34:41 Snowflake adapter: Snowflake query id: 01bbb5be-0304-f1f7-0004-2c6700109552
17:34:41 Snowflake adapter: Snowflake error: 002043 (02000): SQL compilation error:
Object does not exist, or operation cannot be performed.
17:34:41 Snowflake adapter: Error running SQL: macro list_relations_without_caching
17:34:41 Snowflake adapter: Rolling back transaction.
17:34:41 While listing relations in database=dev, schema=_raw, found: 
17:34:41 Using snowflake connection "macro_stage_external_sources"
17:34:41 On macro_stage_external_sources: /* {"app": "dbt", "dbt_version": "2025.4.7+8c17c1e", "profile_name": "user", "target_name": "default", "connection_name": "macro_stage_external_sources"} */
show terse schemas like '_raw' in database dev limit 1;
17:34:42 Snowflake adapter: Snowflake query id: 01bbb5be-0304-f8e9-0004-2c670010c0b6
17:34:42 Snowflake adapter: Snowflake error: 002043 (02000): SQL compilation error:
Object does not exist, or operation cannot be performed.
17:34:42 Snowflake adapter: Error running SQL: macro stage_external_sources
17:34:42 Snowflake adapter: Rolling back transaction.
17:34:42 Encountered an error while running operation: Database Error
  002043 (02000): SQL compilation error:
  Object does not exist, or operation cannot be performed.

```

The environment variable resolves to the name “dev” for the database. I have confirmed in Snowflake that I have the proper privileges and grants to select from this database and external stages within it.

My personal credentials are using a role granted to my user that has full access to the database and all its schemas.

I’ve tried:

- Adding the database and warehouse to my personal credentials
- Removing the environment variable and directly using the database name in the source config

Any idea why dbt-external-tables can’t correctly connect to the database in Snowflake?

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:02pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/2 "2025-04-15T18:02:18Z")

</div>

I joined the Slack too, happy to be here

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744740121789249?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:43pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/3 "2025-04-15T18:43:28Z")

</div>

There’s a specific privilege in Snowflake to create external tables (different from just create table) did you check that the user has that permission inside the schema that it’s working on?

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744742591324429?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:46pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/4 "2025-04-15T18:46:15Z")

</div>

Hey Mark, I checked and the role that my user is assigned has that privilege inside the schema I want to create external tables in.

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744742761140389?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:47pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/5 "2025-04-15T18:47:32Z")

</div>

It’s strange, when I do queries directly in Snowflake, quoting the various parts of the fully qualified namespace, like “db”.“schema”, things seem to work. So maybe there’s something with how quoting works in the yml source config?

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744742838382649?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:47pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/6 "2025-04-15T18:47:58Z")

</div>

Are you seeing the failed query in Snowflake’s query history? That could also help if you see it’s not being rendered with correct names

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744742862846329?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:50pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/7 "2025-04-15T18:50:44Z")

</div>

Normally you don’t need quoted identifiers in Snowflake unless the object was created with them originally, so if you created that database/schema using something like “\_raw” then it shouldn’t matter

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743013773809?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:53pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/8 "2025-04-15T18:53:10Z")

</div>

If you can’t run normal statements on that same database and schema without quotes in Snowflake’s on interface, then that would be a sign that the database name or schema name was quoted on creation

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743174192189?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:55pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/9 "2025-04-15T18:55:17Z")

</div>

If you want to check, see this doc on enabling quoted identifiers on sources: [https://docs.getdbt.com/reference/resource-properties/quoting](https://docs.getdbt.com/reference/resource-properties/quoting) but my recommendation to save yourself a lot of headaches if you can is to rename the database and/or schema to not use quoted identifiers

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743301485509?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:56pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/10 "2025-04-15T18:56:15Z")

</div>

I actually made this database and its schemas using terraform, so that could have something to do with it

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743364180909?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 6:59pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/11 "2025-04-15T18:59:27Z")

</div>

You can check by just trying to run `use database.schema` in the Snowflake UI. If it only works with quotes around the database and/or schema name, then that’s what’s happening.

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743548495549?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 7:01pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/12 "2025-04-15T19:01:48Z")

</div>

They’re a pain because not all tools support/understand quoted identifiers so even though you can set dbt to use them on a source by source basis, it can become a headache with other tools downstream

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743688061149?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 7:03pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/13 "2025-04-15T19:03:21Z")

</div>

show objects in dev.\_raw limit 10000;  
show terse schemas like ‘\_raw’ in database dev limit 1;  
show objects in dev.\_raw limit 10000;  
show terse schemas like ‘\_raw’ in database dev limit 1;

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743785856859?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 7:03pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/14 "2025-04-15T19:03:43Z")

</div>

That was the query history for the commands run by dbt cloud that failed with the sql compilation error

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743804425839?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 7:04pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/15 "2025-04-15T19:04:04Z")

</div>

I think you’re onto something with these quotes

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743825046349?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 7:04pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/16 "2025-04-15T19:04:31Z")

</div>

And these definitely won’t work in the Snowflake UI

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743853181439?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 7:05pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/17 "2025-04-15T19:05:28Z")

</div>

Basically if you don’t use quotes in Snowflake converts all object names to uppercase, but if you use quotes it’s case sensitive so dev.\_raw matches “DEV”.“\_RAW” but not “dev”.“\_raw”. Because the latter contains lowercase letters, it can only be referenced using quotes

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744743915384269?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 15, 2025, 7:07pm UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/18 "2025-04-15T19:07:28Z")

</div>

If Terraform created the database/schema as “dev”.“\_raw” then you either get Terraform to drop the quotes, always use uppercase, or always use quoted identifiers and lowercase names in Snowflake

Note: `@Mark Estey` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744744026686439?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [April 17, 2025, 12:23am UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/19 "2025-04-17T00:23:21Z")

</div>

Mark, apologies for the delay as I fought my way through this. Yes indeed, using all caps worked, and is the more extensible solution for the future. Really appreciate the help. 🙏

Note: `@adam.c.leppek` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1744849390320389?thread_ts=1744739161.556219&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [April 24, 2025, 12:23am UTC](https://discourse.getdbt.com/t/dbt-external-tables-fails-interacting-with-snowflake/19046/20 "2025-04-24T00:23:27Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.