Hi,

This is a challenging area to get right, especially with so many tables and schemas. Unity Catalog is definitely a good bet for storing this metadata. There are a few approaches that you could take to the data definitions part, and it depends on what you need from them.

At the moment, I would recommend using the comments on fields and table definitions to store possible values and definitions. This could be done with a fixed JSON structure to make it readable, but this depends on whether you need queryable metadata or not. If it's just to be read by AI and humans, you probably don't need it to be so structured. 

Comments Docs - https://docs.databricks.com/aws/en/comments/

You can also use tags, but it could become cumbersome for all the different columns to have tags for the definitions etc which is why I’d probably stick to the comments. I may use tags for specific things like PII, etc. You can also mark Primary keys for tables where needed. 

Tag Docs - https://docs.databricks.com/aws/en/database-objects/tags

In terms of propagation, comments don’t generally propagate; usually, I’d expect some sort of transformation between layers, so comments propagating doesn’t really work. I’d focus your efforts on the layers that will be consumed by humans and AI, which are usually your gold layers. These would then represent your final business layer. As others have said, AI can help with this, but be careful, good metadata should be potentially things the AI will not necessarily be able to infer, and therefore, using AI to infer it may leave gaps.

The final things to keep an eye on are the Genie Ontology and the UC Glossary; these have just been announced. Genie ontology learns context from your notebooks and dashboard, and could, over time, make some of your metadata less necessary. 

https://www.databricks.com/product/unity-catalog/business-semantics

My general recommendation would be to pick one schema and map through that first, and this will help to inform your approach and think about how you want to tackle it going forward.

I hope this helps.

Thanks,

Emma