Schema evolution for databricks delta is not working using pre_hook

[cvsekhar]

Describe the feature
We are using DBT+Spark on Delta for incremental load. As we are getting data from various sources, one of the key features is to be able to support schema evolution. Delta lake does support it with the merge command as documented here
Can this be supported by DBT?

Describe alternatives you've considered
the current alternative is refresh all, this has too issues:

very time consuming
As part of ingesting the data, we keep the histories and some of our source data don't maintain history. the refresh all will lose this historic records
Who will this benefit?
I saw another issue #162 that the issue has been fixed, when I have tried to use the same solution it doesn't work

[jtcohen6]

@cvsekhar Sorry for the delay getting back to you!

There are a few problems with set statements is that:

• Databricks SQL endpoints don't seem to support them (Databricks SQL Analytics endpoint does not support set statements #133)
• Even with other Spark cluster types, ODBC connections don't work with set statements — something to do with the way connections are persisted between queries? (Rationalize incremental materialization #141 (comment))

So if you want to set spark.databricks.delta.schema.autoMerge.enabled = true, I believe you can in one of two ways:

• You can edit the spark config of your cluster directly (e.g. docs on doing this in Databricks)
• New in v0.21, you can pass in additional server_side_parameters with ODBC connections (Add support for ODBC Server Side Parameters #201), defined in profiles.yml

We need to do a better job of documenting both. And in both cases, you'd have to persist the setting for an entire dbt invocation—it wouldn't be possible to mix and match different settings for different models in the same run.

An alternative: In v0.21, dbt-core introduced a new on_schema_change config, with options for failing, ignoring, or adding columns to incremental models when the query returns a different column schema. You can follow our progress on implementing that over in #226 + #229.

I'm going to close this issue in the meantime.

[cvsekhar]

Thank you very much fill those instructions.