DBT to add comments to snowflakes columns

Question

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?

Agreed with @Kay. [persist_docs](https://docs.getdbt.com/reference/resource-configs/persist_docs) is the correct way to do this. Write your descriptions in the `.yml` schema files and then persist to db automatically. — sgdata, Aug 09 '21 at 17:48

Kay · Answer 1 · 2021-08-06T18:46:49.080

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.

score 2 · Answer 2 · answered Oct 28 '22 at 00:16

I am pretty new to DBT myself but I think I found a simpler way that works at least on Snowflake, and which doesn't involve defining macros.

It's described in full detail here; https://docs.getdbt.com/reference/resource-configs/persist_docs

Essentially, you write a models/schema.yml containing descriptions for your relations (tables or views) and each of their columns. And then in a model's own config block you add persist_docs={"relation"=true, "columns"=true}

DBT to add comments to snowflakes columns

2 Answers2