Metadata Tables

Metadata tables for a Delta Lake table provide structured views into the underlying system information and operations associated with the Delta Lake.

The `$history` table

The $history table logs metadata changes made to its corresponding Delta Lake table. The syntax to query the $history table is as follows:

SELECT * FROM <catalog_name>.<schema_name>."<table_name>$history"

Set the following parameters based on your actual conditions:

<catalog_name>: the name of the external catalog.
<schema_name>: the name of the external schema that contains the Delta Lake table.
<table_name>: the name of the Delta Lake table.

For example, run the following query to check the metadata changes of the test_table table:

SELECT * FROM catalog_time_travel.time_travel."test_table$history";

The output is as follows:

 version |       timestamp        | user_id | user_name | operation |       operation_parameters       | cluster_id | read_version | isolation_level | is_blind_append
---------+------------------------+---------+-----------+-----------+----------------------------------+------------+--------------+-----------------+-----------------
       0 | 2024-09-06 12:09:57+08 |         |           | WRITE     | {mode=Append, partitionBy=[]}    |            |            0 |                 | t
       1 | 2024-09-12 19:24:39+08 |         |           | WRITE     | {mode=Append, partitionBy=[]}    |            |            0 |                 | t
       2 | 2024-09-12 19:26:28+08 |         |           | WRITE     | {mode=Overwrite, partitionBy=[]} |            |            1 |                 | f
       3 | 2024-09-12 19:28:40+08 |         |           | WRITE     | {mode=Append, partitionBy=[]}    |            |            2 |                 | t
(4 rows)

The returned result contains the following fields:

Field	Description
`version`	The commit version.
`timestamp`	The timestamp when the commit was submitted.
`user_id`	The ID of the user.
`user_name`	The name of the user.
`operation`	The name of the operation performed.
`operation_parameters`	The parameters specified for the operation.
`cluster_id`	The ID of the DPS cluster.
`read_version`	The Delta version.
`isolation_level`	The isolation level.
`is_blind_append`	Whether data was appended in the operation.

The `$properties` table

The $properties table provides access to the configuration, features, and properties of a Delta Lake table, with each row represented as a key/value pair. The syntax to query the $properties table is as follows:

SELECT * FROM <catalog_name>.<schema_name>."<table_name>$properties"

Set the following parameters based on your actual conditions:

<catalog_name>: the name of the external catalog.
<schema_name>: the name of the external schema that contains the Delta Lake table.
<table_name>: the name of the Delta Lake table.

For example, run the following query to check the configuration, features, and properties of the test_table table:

SELECT * FROM catalog_time_travel.time_travel."test_table$properties"

The output is as follows:

 key                        | value           |
----------------------------+-----------------+
delta.minReaderVersion      | 1               |
delta.minWriterVersion      | 4               |
delta.columnMapping.mode    | name            |
delta.feature.columnMapping | supported       |

Metadata Tables

The $history table​

The $properties table​

The `$history` table

The `$properties` table