Column Data type miss-match while databricks catalog federation

[vishal-kilaskar]

We are facing an issue related to the Data type mismatch when performing the federation queries (link).
Basic need is to perform union on these both tables from federated(system tables) and system tables (target/current workspace).

Problem:  Struct and Map data type is getting converted into Varchar(255) while federating from another databricks account.

Temporary solution: Using of inbuild functions from_json() but again problem is the struct field are changing based on new resales which is breaking the queries. 
 
The federation is simple databricks-to-databricks.
Please visit these below links for more details: 
  * https://docs.databricks.com/en/query-federation/databricks.html
  * https://docs.databricks.com/en/query-federation/index.html

Below are the images showing the discrepancy in data-type mismatch.

[dkushari]

System table data is available at the account level. If you are trying LH Federation for querying system tables isn't that already available in the source workspace? 

https://docs.databricks.com/en/admin/system-tables/index.html#what-are-system-tables

https://docs.databricks.com/en/admin/system-tables/index.html#enable-system-table-schemas

Since system tables are governed by Unity Catalog, you need to have at least one Unity Catalog-enabled workspace in your account to enable and access system tables. System tables include data from all workspaces in your account but they can only be accessed from a Unity Catalog-enabled workspace.

System tables are enabled at the schema level. If you enable a system schema, you enable all the tables within that schema. When new schemas are released, an account admin needs to manually enable the schema.

[vishal-kilaskar]

@dkushari  correct, We have already enabled UC and system tables.
Federated Queries we are mainly using it for external data source.
For Ex:
One account is target(current account) say Azure another one which we want to federate is from Another account say AWS.
so its aws account/ system tables are federated into Azure is  Azure (system tables) <---- AWS (system tables)

Hope it makes clear. Please check the Images one is Federated(AWS/GCP) another is Target (Azure).