dbt persist_docs - please persist view and view column descriptions to Snowflake when a model is a view AND persist_docs = True

[randypitcherii]

Describe the feature

When a model is materialized as a view in snowflake and persist_docs for that model is true, descriptions for that model and that model's columns are persisted in Snowflake as view and column comments.

Describe alternatives you've considered

None

Additional context

I believe there was a time when Views in Snowflake did not support commenting (or their columns didn't?), but a dbt Cloud client mentioned to me that Snowflake does now support this and asked that we look into supporting this functionality. Also I think it's a good idea.

Who will this benefit?

Snowflake users that leverage persist_docs and have views in their projects.

Are you interested in contributing this feature?

Yes!!! I'm guessing we have a check somewhere in the persist_docs handler that just doesn't attempt to alter the view to avoid errors and we can just remove that check for Snowflake.

[jtcohen6]

@randypitcherii Thanks for opening! dbt has always supported persisting model-column descriptions for Snowflake views, but it historically did not support persisting column-level descriptions, because Snowflake requires column comments to be included in the initial create or replace view statement.

The good news is: We added support for this, as a last-minute addition, in dbt-snowflake==1.0.0! (#53) The way we did it was a bit unorthodox:

• Run the model SQL with limit 0 where false, just to get column names and types
• Combine those columns with provided descriptions (from .yml file) to template out create or replace view

Try it out:

version: 2

models:
  - name: my_cool_view_model
    description: this is a cool model
    config:
      materialized: view
      persist_docs:
        relation: true
        columns: true
    columns:
      - name: ID
        description: this is a cool column

dbt will compile and run three bits of SQL:

select * from (
            select 1 as id
        ) as __dbt_sbq
        where false
        limit 0
;

create or replace  view analytics.dbt_jcohen.my_cool_view_model 
(      
  ID COMMENT $$this is a cool column$$
)
   as (
    select 1 as id
  );

comment on view analytics.dbt_jcohen.my_model IS $$this is a cool model$$;

With this result:

While testing, I did realize that the column name checker is case-sensitive, because we didn't do the logic here quite right:

dbt-snowflake/dbt/include/snowflake/macros/adapters.sql

Lines 48 to 50 in e75986f

	{% if (column_name|upper in model_columns) or (column_name in model_columns) %}
	{{ get_column_comment_sql(column_name, model_columns) }}
	{% else %}

Snowflake columns are almost always uppercase! So this should really be column_name|lower in model_columns. I'm going to open a new bug / good first issue to set that straight.