Unity Catalog external table: Delta Lake table comment

[JonLaRose]

Hi there,

When creating an external table in Unity Catalog using an existing Delta Lake table with a comment on the table itself, the comment isn't imported to the `Comment` key's value in the Unity Catalog table.

Could you explain why?

[Kaniz]

Hi @JonLaRose, When you create an external table in Unity Catalog by referencing an existing Delta Lake table, the behavior you’ve observed is indeed expected.

Let’s delve into the reasons behind this:

1. Unity Catalog and Delta Lake:

   • Unity Catalog is a powerful feature in Databricks that allows you to manage metadata for tables, views, and other data artifacts.
   • Delta Lake, on the other hand, is a storage layer that provides ACID transactions, schema enforcement, and time travel capabilities on top of data lakes.

2. External Tables:

   • An external table in Unity Catalog is essentially a pointer to data residing outside Databricks. It doesn’t physically store the data but provides metadata about the external data source.
   • When you create an external table, you define its schema, location, and other properties. However, the actual data remains in the external system (such as a Delta Lake table).

3. Comments and Metadata:

   • Comments on tables (whether Delta Lake or other types) are valuable for documentation and understanding the purpose of the data.
   • In the case of Delta Lake tables, you can add comments directly to the table using the COMMENT clause during table creation or modification.
   • However, when you create an external table in Unity Catalog based on an existing Delta Lake table, the comment associated with the original Delta Lake table is not automatically imported into the Unity Catalog table’s Comment key.

4. Why Is the Comment Not Imported?:

   • The reason lies in the fundamental difference between the two:
     • Delta Lake tables are managed within Databricks and store both data and metadata (including comments) within the platform.
     • Unity Catalog external tables, being pointers to external data, don’t have direct access to the internal metadata of the original Delta Lake table.
   • Unity Catalog focuses on managing metadata related to the external table itself (e.g., schema, location, format), not the underlying data’s metadata.

5. Workaround:

   • If you want to preserve the comments from the original Delta Lake table, consider adding a separate metadata field (e.g., a custom column) to your Unity Catalog external table.
   • You can manually populate this field with the relevant comments or other metadata during the external table creation process.

In summary, while Unity Catalog provides powerful metadata management capabilities, it doesn’t automatically inherit comments from the underlying Delta Lake table. To maintain consistency, consider documenting relevant information separately within your Unity Catalog external tables. 📝

For further discussions and insights, feel free to explore the Databricks Community Discussions on this topic¹.