[CT-673] [Feature] Drop create should keep table metadata

[ismailsimsek]

Is this your first time opening an issue?

• I have read the expectations for open source contributors

Describe the Feature

dbt running table metadata gets lost, because dbt doing drop create.
DBT should keep table metadata and add previous metadata to new table. at least "table grants, table comment, column comments".

Describe alternatives you've considered

this could be achieved by improving drop create logic

• before drop: read table metadata to variables
• after create apply metadata to new table

Who will this benefit?

all dbt users

Are you interested in contributing this feature?

No response

Anything else?

No response

[jtcohen6]

@ismailsimsek Thanks for opening! This is a good topic. I disagree that dbt should do this, and I'll do my best to explain why.

It's really important that dbt builds are idempotent. This is an opinion that we hold quite strongly. There are dbt features that flirt with statefulness (incremental models, state:modified), but those exist to offer performance optimizations only. You can always rebuild, from scratch, from the same raw data and return the same result. It will take a lot longer, but it will get you to the same place.

There are features for which this is definitely not possible, namely snapshots—which is why we relegate those to being their own standalone resource type, separate from models, and stress simplicity for all snapshot logic. They're the exception that proves the rule.

table grants, table comment, column comments

I believe all of these must be fully reproducible from scratch, in order to power reproducible and idempotent builds.

Grants: We're adding support for these to be defined right within dbt! (#5090) Some databases do support copying grants from the existing table during create or replace. We are thinking through the implementation details here, and talking through whether the behind-the-scenes mechanism for applying grants should look like calculating diffs, or revoking + re-granting all each time. I definitely welcome your thoughts there: #5263 (comment). But the relevant considerations there are around performance and reliability only. There's no question about the end result we want to achieve: everything defined in dbt, where it's most visible to model builders.

Comments: These can be defined as description properties in dbt, and then persisted to the database as table/column comments via the persist_docs feature. I think it makes sense to pull in existing comments on source tables, which are not owned by dbt, but not for models which are. We believe that documentation for transformed datasets should live alongside the code powering that data transformation, in the same codebase and with the same PR/review process. To that end, we should strongly encourage centralization and standardization here. (See dbt-labs/dbt-docs#263 (review) for a related conversation, about which descriptions/comments to surface in dbt's auto-generated documentation site.)

Happy to talk more and hear disagreement. I'm going to close for now, since it's unlikely we'll pursue this.