CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schemadbt
Kavya shree
Kavya shree

Reputation: 448

DBT to add comments to snowflakes columns

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?

Upvotes: 3

Views: 7020

Answers (2)

Mateo
Mateo

Reputation: 1604

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}

Upvotes: 5

Kay
Kay

Reputation: 2332

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.

Upvotes: 5

Related Questions