New field dbt_change_type in snapshots.

[gekas93]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Hi, I would like dbt to add one more column in their snapshots. That field would be something like change_type and it would be able to have 3 options: updated, created and deleted.

• Updated: each new row which is an update of an old one. If we have in the previous one any state (updated, created or deleted), we will have an updated one.
• Created: first ocurrence of that primary key in the snapshot.
• Deleted: Primary key that not appears in the table currently. Only, If we have invalidated_hard_deletes = true.

Describe alternatives you've considered

Creating a new model that has to calculate with windows functions this field which is poor performance.

Who will this benefit?

Taking advantage of the calculations that snapshot is doing and adding this field without performance cost.
Filter per deleted, updated or created values can be very useful in some scenarios.

Are you interested in contributing this feature?

Yes. I was taking a look into the snapshot macros and I saw that in default__snapshot_staging_table macro dbt_change_type exists but it's not added to the final table. Maybe, it's only to add the field dbt_change_type in default__build_snapshot_table macro for the base case and add it in the final table in the last step of the both process.