How to handle Odata metadata changes in Azure DataFactory/Database?

Question

Current situation: I use an Odata connection to receive data to fill a database. I'm not the owner of that connection. When the supplier releases a new version, there might be changes to the tables, columns and datatypes coming through this connection. I want to configure my landing zone in such a way that I don't have to spend ages figuring out what has changed with a new release. I am aware of the schema compare function with dacpac files, but I don't have access to the changes before the come through the Odata pipeline,

extra info: I have a (local) table in which I record my tables, columns and datatypes called 'metadata' and have the option to request a metadata xml (GET http: [supplier's url] /$metadata/) coming from Odata. Right now, the way I solve my issue is to manually compare these two and figure out the changes. I then manually create SQL ALTER TABLE statements in visual studio to make sure the new tables, columns and datatypes fit together with the changes in Odata. (I also record the changes in my local metadata table) However, this will definitely become impossible with the inevitable big release. Possibly useful: I use MS Visual Studio and Azure DataFactory

My question: How can I quickly configure my side of the Odata pipeline to handle the new tables, columns and datatypes that I receive? (without a lot of manual actions) I could go down the path of creating my own custom SQL script with a lot of ALTER TABLE statements, but is that the way to go, or is there a simpler way?

Thanks!

if this question has been asked before, I couldn't find it. Could be because I am not sure of what terms to use when googling this issue

Answer 1

OData metadata contains an Entity Data Model (EDM).

An EDM typically contains several lists:

IEnumerable<EdmAssociation> associations;
IEnumerable<EdmEntityType> entityTypes;
IEnumerable<EdmEntityType> complexTypes;

It would be possible to perform a text comparison line-by-line of the XML representations of the old and new EDM to find differences. However, an automatic comparison with a meaningful summary of the semantic changes would require a lot of artificial intelligence.

Simple changes could be the addition, alteration or deletion of a property field. But how could you automatically handle changes in nested complex types and their associations?

To reduce the manual work, you could export the metadata to an XML file. Then run it through an XML beautifier to facilitate human inspection. Tools like WinMerge visually show the difference between subsequent versions.