[CT-1842] [Feature] Support on_schema_change='sync_all_columns' for Delta tables

[jeremyyeo]

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-spark functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Delta Lake 2.0 supports dropping of columns on delta tables if the table has certain tblproperties (https://delta.io/blog/2022-08-29-delta-lake-drop-column/) so we may want to support the on_schema_change = 'sync_all_columns' config when columns are removed from the source (compared to the target).

Describe alternatives you've considered

A user could probably achieve this today by rewriting some of the relevant built-in macros.

Who will this benefit?

Previously a schema change (specifically a column removed from source vs target) will result in an exception:

https://github.com/dbt-labs/dbt-spark/blob/5ca20be56ec2d557b4fff5e42c320949040650d3/dbt/include/spark/macros/adapters.sql#L280

Primarily this will bring it up to par with other adapters's behaviour for on_schema_change without having users to implement their own overrides for the relevant helper macros (e.g. alter_relation_add_remove_columns() and family).

Are you interested in contributing this feature?

Sure

Anything else?

We did not support this behaviour back in dbt-labs/dbt-spark#229 because delta could not drop columns - this is now supported (albeit with the necessary tblproperties applied).

Probably the way to do this is to retrieve the tblproperties of the target - and then decide whether to raise or not (or perhaps warn with a suggestion "in order to drop columns, please alter the tblproperties, ...".

We probably want this in dbt-databricks too. The behaviour in the adapter is the same as it is here.

[jtcohen6]

Delta Lake 2.0 supports dropping of columns on delta tables if the table has certain tblproperties (https://delta.io/blog/2022-08-29-delta-lake-drop-column/)

Good find!

I was just converting these tests (dbt-labs/dbt-spark#593), and remembering the hard way that Delta doesn't (by default) support removing columns.

I'll queue up for discussion with the relevant team. Depending on other commitments, we may want to mark this one as help_wanted.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.

[data-blade]

still relevant for us

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[data-blade]

still relevant for us