Forum Discussion
Helper IIIs a table ordinary, or special (= from a shortcut, a system table or materialized lake view table)?
Hi,
In Python/PySpark/Spark SQL, is there a way to determine what kind of lakehouse table a table is—that is whether the table is a) an ordinary table, b) a materialized lake view table, c) a table from a shortcut, or d) a system table (e.g. like sys_dq_metrics)?
In PySpark, I can use `spark.catalog.listTables()` to get details about the tables in a lakehouse. In Spark SQL, I can use `SHOW TABLES` to get the table list then `DESCRIBE DETAIL some_table_name` on each to get the particular table's details. However, in both cases, these details returned don't seem to give me an easy way to differentiate between ordinary tables and special tables (materialized views, shortcust, system tables).
I'd really like to be able to differentiate between the two....
Any ideas?
Thanks!
Hi Ben-Dev
Based on the current behavior, spark.catalog.listTables() and DESCRIBE DETAIL do not expose a single metadata property that reliably identifies whether a Lakehouse object is a regular table, Materialized Lake View, shortcut, or system table.For Materialized Lake Views, we can use the MLV-specific Spark SQL commands such as SHOW MATERIALIZED LAKE VIEWS to identify them.
For system tables, we can identify the documented system schemas/table naming patterns, such as the _mlv_system schema for MLV-related system tables.
However, shortcuts are different. A shortcut-backed table is intentionally surfaced to Spark as a table, so the standard Spark catalog and Delta DESCRIBE DETAIL metadata do not provide a straightforward isShortcut property. Shortcut identification requires checking the corresponding OneLake/Fabric shortcut metadata rather than relying only on Spark table metadata.
Therefore, if the requirement is to programmatically classify every Lakehouse object as TABLE, MLV, SHORTCUT, or SYSTEM, there is currently no single Spark SQL/PySpark command that provides all four classifications. A combination of Spark metadata and Fabric/OneLake metadata is required.
Useful Links: https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/overview-materialized-lake-view
https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/create-materialized-lake-view
4 Replies
v-aatheequeCommunity Support
Hi Ben-Dev
Based on the current behavior, spark.catalog.listTables() and DESCRIBE DETAIL do not expose a single metadata property that reliably identifies whether a Lakehouse object is a regular table, Materialized Lake View, shortcut, or system table.For Materialized Lake Views, we can use the MLV-specific Spark SQL commands such as SHOW MATERIALIZED LAKE VIEWS to identify them.
For system tables, we can identify the documented system schemas/table naming patterns, such as the _mlv_system schema for MLV-related system tables.
However, shortcuts are different. A shortcut-backed table is intentionally surfaced to Spark as a table, so the standard Spark catalog and Delta DESCRIBE DETAIL metadata do not provide a straightforward isShortcut property. Shortcut identification requires checking the corresponding OneLake/Fabric shortcut metadata rather than relying only on Spark table metadata.
Therefore, if the requirement is to programmatically classify every Lakehouse object as TABLE, MLV, SHORTCUT, or SYSTEM, there is currently no single Spark SQL/PySpark command that provides all four classifications. A combination of Spark metadata and Fabric/OneLake metadata is required.
Useful Links: https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/overview-materialized-lake-view
https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/create-materialized-lake-view
Ben-DevThank you, v-aatheeque. Your response is helpful. Hopefully, an easier--and unified--way to get these details will be added in the future. Thanks, again, for your help!
- v-aatheeque
Hi Ben-Dev
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
- v-aatheeque
Hi Ben-Dev
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.