get_relations_by_prefix not working with snowflake external table

[upjohnc]

Describe the bug

I am working on updating from 0.6.2 to 0.6.4 so that we can update dbt version from 0.18.1 to 0.19.1. I am getting an error when compiling a model that get_relations_by_prefix and the tables in the schema are external tables in snowflake.

Steps to reproduce

In the model:
{% set raw_tables = dbt_utils.get_relations_by_prefix( database = 'raw', schema = 'external_mra', prefix = 'ASC_RATES_' ) %}
dbt: 0.19.1
dbt-utils: 0.6.4

presently works on
dbt: 0.18.1
dbt-utils 0.6.2

cli: dbt compile -m <model>

Expected results

that variable can then be used in this portion of the model

WITH combine AS (--
{% for table in raw_tables %}

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

Screenshots and log output

error message

Field "type" of type typing.Union[dbt.contracts.relation.RelationType, NoneType] in dbt.adapters.snowflake.relation.SnowflakeRelation has invalid value 'external table'
Encountered an error:
Runtime Error
  Field "type" of type typing.Union[dbt.contracts.relation.RelationType, NoneType] in dbt.adapters.snowflake.relation.SnowflakeRelation has invalid value 'external table'

System information

packages.yml

packages:
  - package: fishtown-analytics/dbt_utils
    version: 0.6.4
  - package: fishtown-analytics/dbt_external_tables
    version: 0.6.2

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:
dbt version

   latest version: 0.19.1

Your version of dbt is out of date! You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 0.18.1
  - snowflake: 0.18.1
  - redshift: 0.18.1
  - postgres: 0.18.1

Are you interested in contributing the fix?

I am up for learning and helping how I can

[jtcohen6]

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"

[upjohnc]

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).

[jtcohen6]

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'?).

[upjohnc]

Created pr: pr url

[github-actions]

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.

[github-actions]

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.