Synchronising metadata (e.g., tags) across schemas under Unity Catalog (Azure)

[AntonisCh]

Hello all,

I hope you are doing great!

I want to synchronise metadata (e.g., description, comments, tags) across schemas under the Unity Catalog (e.g., test.dev, test.uat). For example, under the schema test.dev, there is a sales table with multiple columns. For each column, a comment and a tag are added. Also, the sales table exists in test.uat schema. How can I clone/merge the tags, comments to the test.uat schema? Is there any standard functionality to achieve this? Or is there a workaround solution using APIs?

Thank you in advance!

[Coffee77 · Accepted solution]

I think there is no automatic way of achieving this BUT try (and customize if needed) this script 😎. Change "source_schema" and "table_schema" as per your needs, and then run it as a query in a SQL Warehouse cluster:

WITH table_tags AS (
    SELECT
        catalog_name,
        schema_name,
        table_name,
        NULL AS column_name,
        tag_name,
        tag_value
    FROM INFORMATION_SCHEMA.TABLE_TAGS
    WHERE schema_name = 'source_schema'

),
column_tags AS (
    SELECT
        catalog_name,
        schema_name,
        table_name,
        column_name,
        tag_name,
        tag_value    
    FROM INFORMATION_SCHEMA.COLUMN_TAGS
    WHERE schema_name = 'source_schema'
)
SELECT 
    'ALTER TABLE target_schema.' || table_name ||
    ' SET TAGS ("' || tag_name || '"="' || tag_value || '");' AS generated_sql
FROM table_tags
UNION ALL
SELECT            
    'ALTER TABLE target_schema.' || table_name ||
    ' ALTER COLUMN ' || column_name ||
    ' SET TAGS ("' || tag_name || '"="' || tag_value || '");' AS generated_sql
FROM column_tags

[AntonisCh]

Thank you a lot! Much appreciated your response. I will try for it and let you know. Though a gap/risk I am thinking is the following. What if the sales table does not have the same number of columns or the same column names across schemas? Do you believe that there is a cross-check for this? I am not sure it can be validated

[Coffee77]

You can improve that script indeed. I created that in an "ad-hoc" way to help you and give insight on possible workaround. At least, I tested a little bit in a very simple scenario working fine BUT it's true it is very likely is not covering all cases. I would need hours instead of minutes to cover broader scopes.

[AntonisCh]

It's completely fine, and I do understand. Thank you for your time and effort here! 

[Coffee77]

Happy to hear that, I also stored in my personal repo to use it onwards 😀