Iceberg Metadata Tables

Iceberg's Metadata Tables in SQL

Every engine exposes the same information as metadata tables: Spark 129 names them booknest.orders.snapshots, .history, .files, .manifests, .partitions and .refs, and Trino 403,499 quotes them as "orders$snapshots". Reading them through Trino also proves that a second engine understands what Spark wrote:

metadata_tables.sql: snapshots and partitions through TrinoSQL

-- Iceberg metadata tables, read by Trino from the tables Spark wrote.
USE iceberg.booknest;
SELECT snapshot_id, parent_id, operation, element_at(summary, 'added-records') AS added
FROM "orders$snapshots" ORDER BY committed_at;
SELECT partition.order_ts_month AS month, record_count, file_count, data.total.min AS min_total
FROM "orders$partitions" ORDER BY 1 LIMIT 3;

Output

     snapshot_id     |      parent_id      | operation | added
---------------------+---------------------+-----------+-------
 3742401330379284395 |                NULL | append    | 66761
  916038210498707551 | 3742401330379284395 | append    | 33239
  996694203951870685 |  916038210498707551 | delete    | NULL
(3 rows)
 month | record_count | file_count | min_total
-------+--------------+------------+-----------
   660 |         5713 |          1 |     12.74
   661 |         4993 |          1 |     12.74
   662 |         5762 |          1 |     12.74
(3 rows)

$snapshots lists every commit, the rolled-back delete included ($history marks it as off the current branch), and $partitions aggregates manifest statistics per month without reading any data. With $files and $manifests these are the daily tools of operating a lakehouse: counting small files before compaction (Compaction and Expiry), finding a snapshot to time-travel to, and auditing which job committed what.

MENU