Retrieving table name from snowflake information_schema using dbt

user_13138455 0
Viewed 1354

I have created a macro to returns a table name from the INFORMATION_SCHEMA in Snowflake.

I have tables in snowflake as follows

------------
|  TABLES  |
------------
|   ~one   |
|   ~two   |
|  ~three  |
------------

I want to pass the table type i.e. one into the macro and get the actual table name i.e. ~one

Here is my macro(get_table.sql) in DBT that takes in parameter and returns the table name

{%- macro get_table(table_type) -%}
    
    {%- set table_result -%}
        select distinct TABLE_NAME from "DEMO_DB"."INFORMATION_SCHEMA"."TABLES" where TABLE_NAME like '\~%{{table_type}}%'
    {%- endset -%}
    
    {%- set table_name = run_query(table_result).columns[0].values() -%}
  
  {{ return(table_name) }}
{%- endmacro -%}

Here is my DBT Model that calls the above macro

{{ config(materialized='table',full_refresh=true) }}

select * from {{get_table("one")}}

But I am getting an error:

Compilation Error in model

'None' has no attribute 'table'

> in macro get_table (macros\get_table.sql)

I don't understand where the error is

dbt snowflake-cloud-data-platform sql
1 Answers

You need to use the execute context variable to prevent this error, as it is described here:

https://discourse.getdbt.com/t/help-with-call-statement-error-none-has-no-attribute-table/602

You also be careful about your query, that the table names are uppercase. So you better use "ilike" instead of "like".

Another important point is, "run_query(table_result).columns[0].values()" returns an array, so I added index to the end.

So here's the modified version of your module, which I successfully run it on my test environment:

{% macro get_table(table_name) %}
    
    {% set table_query %}
        select distinct TABLE_NAME from "DEMO_DB"."INFORMATION_SCHEMA"."TABLES" where TABLE_NAME ilike '%{{ table_name }}%'
    {% endset %}

    {% if execute %}
        {%- set result = run_query(table_query).columns[0].values()[0] -%}
        {{return( result )}}
    {% else %}
        {{return( false ) }}
    {% endif %}
    
{% endmacro %}
user_125509650
Related