The issue here is that dbt supports a relation type called 'external', whereas Snowflake's information_schema.tables calls these EXTERNAL TABLE.

In fact, there's some logic in Snowflake's adapter cache such to handle exactly this error, and infer the type as external instead of external table.

It's a bit weird to see this cropping up in v0.19.1 for the very first time, though it makes sense: as part of core performance improvements, we've been much stricter about type checking internal objects, so I'm guessing that's the cause. Suffice to say, this ought to have been a type error in earlier versions, too. I'm hopeful that the resolution is as simple as extending the logic already here:

https://github.com/fishtown-analytics/dbt-utils/blob/ceb28497769c642cae7e3d5d18f1fe6bb253ef59/macros/sql/get_tables_by_pattern_sql.sql#L11-L14

To also read:

            case table_type
                when 'BASE TABLE' then 'table'
                when 'EXTERNAL TABLE' then 'external'
                else lower(table_type)
            end as "table_type"

First, thank you for walking through the explanation. That makes a ton of sense.

Second, I went into the file and adding the line for external table as you recommended. I can confirm that it did compile the model.

{% set raw_tables = dbt_utils.get_relations_by_prefix(
    database = 'raw',
    schema = 'external_mra',
    prefix = 'ASC_RATES_'
) %}
WITH combine AS (--
{% for table in raw_tables %}

    SELECT
        *
    FROM
        {{ table }}
        {{ "UNION ALL" if not loop.last }}
    {% endfor %})
SELECT
    *
FROM
    combine

result

I assume that it'll be easier for you to create a pr because I'd have to become familiar with the contribution page and create the pr. I am happy to do so if it helps. Let me know if that is a better route (me creating a pr).

I am happy to do so if it helps. Let me know if that is a better route (me creating a pr).

I'd love if you could contribute the fix for this! The process looks like fork, change, commit, open PR :)

We have CI tests that run on all PRs from forks, though (clearly) the CI tests weren't catching this because we don't have external tables in a schema pointed to by get_relations_by_prefix. In the PR, we can discuss if we want to add a test for that, or if there are other edge cases we need to consider (perhaps `when 'MATERIALIZED VIEW' then 'materializedview'?).

Created pr: pr url

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.