default__get_relations_by_pattern fails due to agate.row value selection by column name

[TerjeRusska]

Describe the bug

default__get_relations_by_pattern is failing due to agate.row column selection by name being case sensitive. The relation creation process does not receive row values.

Steps to reproduce

{{ dbt_utils.get_relations_by_pattern('%', '%', '', 'RAW') }}

Expected results

List of snowflake relations in the target sql file

Actual results

Runtime Error
  Field "path" of type Path in SnowflakeRelation has invalid value {'database': 'RAW', 'schema': Undefined, 'identifier': Undefined}

Screenshots and log output

In default__get_relations_by_pattern

{% do log(table_list['table'], info=True) %}

column	data_type
TABLE_SCHEMA	Text
TABLE_NAME	Text
TABLE_TYPE	Text

agate.row value selection with row.table_schema will not give any results, however it does work it row.TABLE_SCHEMA

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=0.8.0", "<0.9.0"]
  - package: brooklyn-data/dbt_artifacts
    version: [">=0.6.0", "<0.7.0"]
  - package: dbt-labs/codegen
    version: [">=0.5.0", "<0.6.0"]

Which database are you using dbt with?

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

The output of dbt --version:

installed version: 1.0.1
   latest version: 1.0.1

Up to date!

Plugins:
  - snowflake: 1.0.0

Additional context

Are you interested in contributing the fix?

I can work on a fix that covers both lower and uppercase column names.

[joellabes]

Hi @TerjeRusska, thanks for opening the issue - I'm sorry to say I'm having trouble reproducing your experience with the sample you provided, can you share a more complete code sample?

When I run
{{ dbt_utils.get_relations_by_pattern('%', '%', database='RAW') }},
I get all of our tables listed out:

The error that you pasted (Field "path" of type Path in SnowflakeRelation has invalid value) sounds as though you were trying to operate on the list results, but I don't think you provided that code.

Thank you!

[TerjeRusska]

If I try to run the line
{{ dbt_utils.get_relations_by_pattern('test', '%', '', 'RAW') }}

The compilation will return

Runtime Error
  Field "path" of type Path in SnowflakeRelation has invalid value {'database': 'RAW', 'schema': Undefined, 'identifier': Undefined}

No additional operations are added to the sql file

The default__get_relations_by_pattern macro receives table_list from load_result('get_tables') so the data is there, but the table_list['table'] value contains capitalized table column names (TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE).

            {%- set tbl_relation = api.Relation.create(
                database=database,
                schema=row.table_schema,
                identifier=row.table_name,
                type=row.table_type
            ) -%}

Current code above in the marco will throw the Runtime Error because row.table_schema does not return any value (hence schema Undefined). If I change it to row.TABLE_SCHEMA then the correct value test is received.

            {%- set tbl_relation = api.Relation.create(
                database=database,
                schema=row.TABLE_SCHEMA,
                identifier=row.TABLE_NAME,
                type=row.TABLE_TYPE
            ) -%}

I hope this makes it more clear 😅 If the DB returns column names that are not capitalized then no error is thrown.

[jtcohen6]

@TerjeRusska We have a hunch — any chance you have enabled the QUOTED_IDENTIFIERS_IGNORE_CASE parameter? https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html#controlling-case-using-the-quoted-identifiers-ignore-case-parameter

The query running in default__get_tables_by_pattern_sql double-quotes its return columns, so these should be accessible in lower-case in the resulting agate table:

dbt-utils/macros/sql/get_tables_by_pattern_sql.sql

Lines 8 to 10 in d279542

	select distinct
	table_schema as "table_schema",
	table_name as "table_name",

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

[preeti-taneja]

I also got the same issue and would love this to be resolved. I can contribute to the fix.

[eugenekim-orrum]

Ran into this same issue today. I have an automated feed that creates new sources as they are ingested. I tried using this macro to get all the source tables but it fails to compile. I am also using QUOTED_IDENTIFIERS_IGNORE_CASE as case-sensitive identifiers is an evil that needs to be irradicated.

[smallery]

I was getting this same error today, I was able to resolve it by changing the code of the get_relations_by_pattern macro to use UPPERCASE like so:
{%- set tbl_relation = api.Relation.create( database=database, schema=row.TABLE_SCHEMA, identifier=row.TABLE_NAME, type=row.TABLE_TYPE ) -%}
I have QUOTED_IDENTIFIERS_IGNORE_CASE = true in my current session