Hi @vishnuvardhan, I appreciate the information isn't directly going to answer your question but I wanted to share what I've found. 

Firstly, I've not heard of a consumer databricks workspace prior to your post. I'm not sure if you've looked into the documentation for it? https://docs.databricks.com/aws/en/ai-bi/consumers/. There's a section around Consumer Access Capabilites which might be useful. Perhaps this isn't what you're accessing? If it is, it doesn't even seem to provide Notebooks/SQL Editor; I'd be curious to see how youre getting around that.

@vishnuvardhan are you sure it's two different unity catalogs and not two catalogs within a single unity catalog? We have to remember that the three level namespace within a unity catalog is catalog->schema->table(etc). You can have many catalogs within a single unity catalog. I'm also unsure why they'd have multiple unity catalogs unless they're working across different regions?

I also stumbled across this: https://docs.databricks.com/aws/en/catalogs/binding?utm_source=chatgpt.com&language=CLI . If the group your part of has been setup with specific workspace catalog bindings, it may be why you're experiencing what you are. It could be a good time to reach out to your databricks workspace admin, they could shed some light

There's some better examples of the commands that can be run here: https://docs.databricks.com/aws/en/dev-tools/cli/reference/workspace-bindings-commands . It may be that there's a binding used to keep the catalogs isolated. Catalog isolation is certainly mentioned in the docs but it seems geared towards isolating catalogs to certain workspaces. I wonder if an additional constraint can be imposed which prevents the joining. 

I'm looking forward to seeing what other community members mention for this! 

All the best,
BS