DBT to add comments to snowflakes columns

user_4277485 0
Viewed 1279

We use DBT for ELT in snowflake. Want to add comments to each column in Snowflake. Either using COMMENT or ALTER command after every full refresh.

Decided to add macros with commands and call it under on-run-end hook.

{​​​​​​​% macro comment_transactions_master() %}​​​​​​​

    {% if execute %}
        (COMMENT ON COLUMN 
        "DEV_SCHEMA"."DBT_TEMP"."TR_MASTER"."TR_ID" IS 'testing comment';​​​​​​​)
    {% endif %}

{​​​​​​​% endmacro %}​​​​​​​

Since there are 100+ columns and I am new to DBT, is there a better way to perform this operation?

snowflake-schema dbt snowflake-cloud-data-platform
1 Answers

I don't know about snowflake but I know in other databases, you can add comments to multiple columns in a table like so:

comment on column schema.table (
   a is 'just a comment',
   b is 'just another comment'
)

So for this you can use this macro:

{% macro snowflake__alter_column_comment(relation, column_dict) %}

    COMMENT on COLUMN {{ relation }} (
      {% for column_name in column_dict %}
        {% set comment = column_dict[column_name]['description'] %}
        {{ column_name }} is '{{ comment }}'{%- if not loop.last %}, {% endif -%}
      {% endfor %}
    )
  
{% endmacro %}

And add this to snowflakes persist_docs macro:

{% macro snowflake__persist_docs(relation, model, for_relation, for_columns) -%}
  {# -- Override the persist_docs default behaviour to add the short descriptions --#}

.........


{# Add the new macro here #}
  {% if for_columns and config.persist_column_docs() and model.columns %}
    {% do run_query(alter_column_comment(relation, model.columns)) %}
  {% endif %}

{% endmacro %}

Persist_docs is in almost every materialization so you should be fine. Let me know if this helps.

user_43510390
Related