dbt-external-tables CAN'T FIND external stage

Help
,
1

I have a job in a Staging environment in dbt Cloud that currently only runs one command, invoking the dbt-external-tables library:

dbt run-operation stage_external_sources

The sources.yml file looks like this:

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=.*/datatype=management/provider=dc305_animals/year=.*/month=.*/day=.*/FILEOUT91.*[.]parquet"
          file_format: "{{env_var('DBT_DB_NAME')}}.external_stages.parquet"
        columns:
          - name: TODAY
            data_type: varchar
          - ...

The connection to Snowflake seems to be successful, but I get an error that the stage doesn’t exist or isn’t authorized. Here is a snippet of the debug logs:

2025-04-19 00:54:47.069762 (MainThread): 00:54:47  1 of 3 (1) select 'Schema _RAW exists' from dual;
2025-04-19 00:54:47.072268 (MainThread): 00:54:47  Using snowflake connection "macro_stage_external_sources"
2025-04-19 00:54:47.072751 (MainThread): 00:54:47  On macro_stage_external_sources: /* {"app": "dbt", "dbt_version": "2025.4.15+7a72e82", "profile_name": "user", "target_name": "default", "connection_name": "macro_stage_external_sources"} */
select 'Schema _RAW exists' from dual;
2025-04-19 00:54:47.190124 (MainThread): 00:54:47  SQL status: SUCCESS 1 in 0.117 seconds
2025-04-19 00:54:47.191617 (MainThread): 00:54:47  1 of 3 (1) SUCCESS 1
2025-04-19 00:54:47.192161 (MainThread): 00:54:47  1 of 3 (2) create or replace external table TEST._RAW.raw_dc305_animals__file91(           ...  
2025-04-19 00:54:47.194876 (MainThread): 00:54:47  Using snowflake connection "macro_stage_external_sources"
2025-04-19 00:54:47.195444 (MainThread): 00:54:47  On macro_stage_external_sources: /* {"app": "dbt", "dbt_version": "2025.4.15+7a72e82", "profile_name": "user", "target_name": "default", "connection_name": "macro_stage_external_sources"} */
create or replace external table TEST._RAW.raw_dc305_animals__file91(
                TODAY varchar as ((case when is_null_value(value:TODAY) or lower(value:TODAY) = 'null' then null else value:TODAY end)::varchar),
                ID varchar as ((case when is_null_value(value:ID) or lower(value:ID) = 'null' then null else value:ID end)::varchar),
                BNAME varchar as ((case when is_null_value(value:BNAME) or lower(value:BNAME) = 'null' then null else value:BNAME end)::varchar),
                BDAT varchar as ((case when is_null_value(value:BDAT) or lower(value:BDAT) = 'null' then null else value:BDAT end)::varchar),
                REG varchar as ((case when is_null_value(value:REG) or lower(value:REG) = 'null' then null else value:REG end)::varchar),
                EID varchar as ((case when is_null_value(value:EID) or lower(value:EID) = 'null' then null else value:EID end)::varchar),
                CNTL varchar as ((case when is_null_value(value:CNTL) or lower(value:CNTL) = 'null' then null else value:CNTL end)::varchar),
                CBRD varchar as ((case when is_null_value(value:CBRD) or lower(value:CBRD) = 'null' then null else value:CBRD end)::varchar),
                EDAT varchar as ((case when is_null_value(value:EDAT) or lower(value:EDAT) = 'null' then null else value:EDAT end)::varchar),
                PSTRG varchar as ((case when is_null_value(value:PSTRG) or lower(value:PSTRG) = 'null' then null else value:PSTRG end)::varchar),
                DID varchar as ((case when is_null_value(value:DID) or lower(value:DID) = 'null' then null else value:DID end)::varchar),
                DREG varchar as ((case when is_null_value(value:DREG) or lower(value:DREG) = 'null' then null else value:DREG end)::varchar),
                DBRD varchar as ((case when is_null_value(value:DBRD) or lower(value:DBRD) = 'null' then null else value:DBRD end)::varchar),
                SID varchar as ((case when is_null_value(value:SID) or lower(value:SID) = 'null' then null else value:SID end)::varchar),
                SIRC varchar as ((case when is_null_value(value:SIRC) or lower(value:SIRC) = 'null' then null else value:SIRC end)::varchar),
                SREG varchar as ((case when is_null_value(value:SREG) or lower(value:SREG) = 'null' then null else value:SREG end)::varchar),
                PEN varchar as ((case when is_null_value(value:PEN) or lower(value:PEN) = 'null' then null else value:PEN end)::varchar),
                LACT varchar as ((case when is_null_value(value:LACT) or lower(value:LACT) = 'null' then null else value:LACT end)::varchar),
                RPRO varchar as ((case when is_null_value(value:RPRO) or lower(value:RPRO) = 'null' then null else value:RPRO end)::varchar),
                STAT varchar as ((case when is_null_value(value:STAT) or lower(value:STAT) = 'null' then null else value:STAT end)::varchar),
                CAR varchar as ((case when is_null_value(value:CAR) or lower(value:CAR) = 'null' then null else value:CAR end)::varchar),
                VC varchar as ((case when is_null_value(value:VC) or lower(value:VC) = 'null' then null else value:VC end)::varchar)
        )
    location = @TEST.EXTERNAL_STAGES.S3_STAGING_BUCKET 
    
    
    
    pattern = 'owner=.*/datatype=management/provider=dc305_animals/year=.*/month=.*/day=.*/FILEOUT91.*[.]parquet'
    
    file_format = TEST.external_stages.parquet
    
;
2025-04-19 00:54:47.315988 (MainThread): 00:54:47  Snowflake adapter: Snowflake query id: 01bbc856-0304-fca5-0004-2c67001339e6
2025-04-19 00:54:47.316622 (MainThread): 00:54:47  Snowflake adapter: Snowflake error: 002003 (02000): SQL compilation error:
Stage 'TEST.EXTERNAL_STAGES.S3_STAGING_BUCKET' does not exist or not authorized.
2025-04-19 00:54:47.317178 (MainThread): 00:54:47  Snowflake adapter: Error running SQL: macro stage_external_sources
2025-04-19 00:54:47.317644 (MainThread): 00:54:47  Snowflake adapter: Rolling back transaction.
2025-04-19 00:54:47.318265 (MainThread): 00:54:47  Encountered an error while running operation: Database Error
  002003 (02000): SQL compilation error:
  Stage 'TEST.EXTERNAL_STAGES.S3_STAGING_BUCKET' does not exist or not authorized.

Condition of Environment

  • The environment variable DBT_DB_NAME is set to TEST in this environment, which is the actual name of the database in Snowflake.
  • The schema and stage do indeed exist in Snowflake.

Things Tried

  • I ensured my deployment credential to Snowflake for the Staging environment in dbt works. It uses keypair auth, and Test Connection succeeds. Other queries are also able to return successfully, as the debug log shows.
  • The schema in the deployment credential is set to EXTERNAL_STAGES, but I also tried _RAW (the destination), with no further luck.
  • The role I’m using has all the privileges required (all privileges on all schemas and tables in the database). This role is the default role for the user whose credentials were set, and I additionally set the role in both the environment and the connection.
  • I tried capitalizing all references in the sources.yml file, but since the entities were created capitalized, a lower case definition should default to upper case in Snowflake. No difference in results when capitalization is used.
  • This command successfully runs in the dbt Cloud Development environment. The sources.yml is the same. The database is different, but the privileges are the same.
  • The failed command printed out in the debug log works as expected in a worksheet in the Snowflake UI, when run as the same role.

Observations

  • The user set up for the environment credentials is a Snowflake service user. In the Development environment, I’m using my own personal user (a “person” type user in Snowflake).
  • The stage is printed out in the debug log error message with single quotes surrounding it, which is invalid syntax for Snowflake. I wonder if the debug log is just single-quoting it for readability purposes.

Any idea why this command works in the Development environment and in the Snowflake UI, but not in the Staging environment?

2

Wondering if I can get a hand on this. It’s a blocking issue and may be something strange with the dbt-external-tables library.

Note: @adam.c.leppek originally posted this reply in Slack. It might not have transferred perfectly.

3

I ended up adding privileges for the role on the external stage. I didn’t need this explicitly in the dbt Development environment or the Snowflake UI because my own personal user also had higher level permissions from other roles.