Snowflake Persist Docs (Materialization == Views, column level descriptions)

[kevnoo]

Describe the feature

Given the database adapter is Snowflake, materialization type is View and persist docs is set true - create the view using the following snowflake syntax so that column descriptions from dbt schema.yml files are include:

CREATE OR REPLACE VIEW (COL1 COMMENT 'COMMENT', COLN COMMENT 'COMMENT') AS SELECT COL1, COLN FROM TABLE;

Describe alternatives you've considered

I am looking into submitting a PR myself but would benefit from oversight. We have also considered contacting Snowflake to include alter capabilities for columns within a view but believe this to be another alternative.

Additional context

Snowflake

Who will this benefit?

All users of Snowflake who heavily rely on views and would like to use persist docs to capture doc details within the database itself for external dependencies

Are you interested in contributing this feature?

Yes - more than happy to help - just require a push in the right direction

[jtcohen6]

@kevnoo Thanks for opening! See also: dbt-labs/dbt-core#2334, dbt-labs/dbt-core#2321

To put it simply, dbt's view materialization on Snowflake uses create or replace view <view name> as <select>; Snowflake infers the view's columns from the query itself. That DDL does not include a full column list, which is required if we want to set column comments at the time of view creation. Snowflake does not support commenting on view columns after a view is created, whether via alter view alter column or comment on column:

SQL compilation error: Object found is of type 'VIEW', not specified type 'TABLE'.

In order for dbt to include a full column list at time of view creation, we'd need to pursue one of two potential routes:

1. A new feature (a la strict field validation for schema.yml dbt-core#1570) whereby a user could optionally specify all column names in the model's YAML properties, and then pass a strict / declarative flag to tell dbt to use it. If there's a mismatch between the query and the specified column list, Snowflake (and therefore dbt) will return an error. Hence "strict".
2. dbt could try to be very clever: If persist_docs is enabled, it would use the get_columns_in_query macro to run the view's query + limit 0, grab the list of columns, and pass that list (plus descriptions as comments, and the query again, this time no limit 0) as create or replace view <view name> <column list> as <select>.

If I recall correctly, when we initially implemented persist_docs, we were hopeful about the possibilities of option 2, which should be fairly straightforward to implement. (We just didn't get there at the time.) Option 1 would be more complex, but there are many other reasons to want it. And, of course, there may be more options than just those two.

Does any of the above speak to you?

[kevnoo]

Thanks - this is helpful @jtcohen6. Let me think it over and try to take a stab at a solution. Leaning towards dbt-labs/dbt-core#2 right now but understand the additional benefits that would come from dbt-labs/dbt-core#1.

[gthanvi]

@kevnoo we have a similar requirement for persisting column level comments for Snowflake views. Is there something we can use currently as workaround to persist comments for views columns. Or we need to wait for this feature in next release?

[spencer-taylor-workrise]

Hey @jtcohen6 -- I also lean toward option 2 (I think both are good, though!). Would you mind if I gave this a try (unless @kevnoo has something in flight)?

[spencer-taylor-workrise]

@jtcohen6 Opened PR #53 to resolve this (not sure if it notifies you when I tag the PR to this issue, my open source github chops are a little rusty 😅 )!