Hi thomas_berry,
How are you doing today?, As per my understanding, You're spot on with your understanding, and you're not alone in running into this limitation. Unity Catalog doesn’t currently support creating tables using a JDBC query like in your example, especially with complex joins, because UC doesn't allow JDBC-based file schemes for table creation—it expects you to use foreign catalogs through CREATE CONNECTION and CREATE FOREIGN CATALOG. And you're right—those only expose existing tables or views in the source, and joins in custom queries aren’t supported unless pre-defined as views on the source side. Unfortunately, if you’re not allowed to create views in the source system, that limits your options. For now, the best workaround is to pull the tables individually through the foreign catalog into Databricks and then write the join logic within Databricks (even though it won’t be pushed down). It’s not ideal for performance, but it's the safest option when view creation is off the table. Hopefully in the future, Unity Catalog might support more flexible query definitions for federated sources—but for now, we're a bit limited there. Let me know if you want help building a workaround using Databricks-side logic!
Regards,
Brahma