Snowflake query for a specific tag value against an object

Question

In Snowflake I hold tag definitions in a central DB/schema (in this case SF_UTILS/GLOBAL_TAGS) and then apply these tags to objects - for example, I might tag a database with an owner using the tag GT_OWNER and giving it the owner's email address as the value.

I can retrieve a specific tag held against a specific object by querying the views in the ACCOUNT_USAGE schema e.g. this retrieves the GT_OWNER tag value held against a DB named SL_DEMO_DB:

SELECT 
DB.DATABASE_NAME
,TR.TAG_VALUE
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES DB
LEFT OUTER JOIN SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES TR ON
    DB.DATABASE_NAME = TR.OBJECT_NAME
    AND TR.TAG_DATABASE = 'SF_UTILS'
    AND TR.TAG_SCHEMA = 'GLOBAL_TAGS'
    AND TR.TAG_NAME = 'GT_OWNER'
    AND TR.DOMAIN = 'DATABASE'
WHERE DB.DATABASE_NAME = 'SL_DEMO_DB'
;

However, because the ACCOUNT_USAGE views have a latency, I'd prefer to query the INFORMATION_SCHEMA - but I'm not sure if this is possible.

I can get the DB name easily enough e.g.

SELECT 
DATABASE_NAME
FROM SF_UTILS.INFORMATION_SCHEMA.DATABASES DB
WHERE DATABASE_NAME = 'SL_DEMO_DB'
;

but the only way I'm aware of for getting tags is to use this table function:

select *
  from table(SF_UTILS.information_schema.tag_references('SL_DEMO_DB', 'database'));

but this requires the DB name to be hard-coded into the SQL. What I'd like to be able to is join the Database select to the tag select.

Does anyone know if it is possible to query for an object and its tags without using the ACCOUNT_USAGE views (or anything that has significant latency)?

Answer 1

Using Snowflake Scripting to workaround the "static" database name:

DECLARE 
   res RESULTSET;
   sql TEXT;
BEGIN
    SELECT LISTAGG(
         REPLACE($$SELECT * FROM TABLE(information_schema.tag_references('<db_name>', 'DATABASE'))$$
           ,'<db_name>', DB.DATABASE_NAME)
          , ' UNION ALL ') 
          WITHIN GROUP(ORDER BY DB.DATABASE_NAME)
    INTO :sql
    FROM INFORMATION_SCHEMA.DATABASES AS DB;

    res := (EXECUTE IMMEDIATE :sql);
    
    RETURN TABLE(res);
END;