CODEWITHSUNDEEP
snowflake-cloud-data-platform
Gavin Wilson
Gavin Wilson

Reputation: 522

Does someone have a data dictionary based off the Snowflake Infoschema system tables?

(Submitting the following on behalf of a Snowflake user)

In SQL Server we were able to use Extended Properties to add descriptions and other items directly to an attribute in the system tables. This allowed us to fill out a data dictionary directly in the database. I'm looking to do something similar using the InfoSchema tables, but I will need to create new tables to hold the column/table descriptions and then relate these back to the data in Snowflake's InfoSchema tables. I was hoping someone has already done something like this so I don't have to reinvent the wheel. If someone has (or has a similar/better way of documenting table/column information), could you please send me the structure that you have used to do this?

For clarification, I'm aware of the views that have been created for the Infoschema, and those are very valuable for gathering technical data about tables/columns, but I'm looking to add fields where we can put in text descriptions of what the table/columns are.

Thanks

Any recommendations?

Upvotes: 2

Views: 331

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

In SQL Server we were able to use Extended Properties to add descriptions and other items directly to an attribute in the system tables

A similar effect could be achieved with OBJECT TAGGING:

A tag is a schema-level object that can be associated to another Snowflake object. A tag can be assigned an arbitrary string value upon associating the tag to a Snowflake object. Snowflake stores the tag and its string value as a key-value pair in the form key = 'value'. In this example, cost_center = 'sales', cost_center is the tag and 'sales' is the string value. The tag must be unique for your schema and the tag value is always a string.

-- For a table or external table column

alter table <table_name> modify column <column_name> 
                         set tag <tag_key> = '<tag_value>' 
                                 [ , <tag_key> = ’<tag_value>’ , ... ];

Upvotes: 0

Mike Walton
Mike Walton

Reputation: 7339

If you are looking for recommendations on how to store descriptions in tables and columns, Snowflake offers the ability to store that information in Comments. These can be added during the creation of the table or by leveraging an ALTER TABLE...ALTER COLUMN statement.

Upvotes: 1

Related Questions