Apache Paimon uses system tables to expose metadata and data consumption information for each Paimon table. Query these tables with SQL to inspect snapshots, schema history, partitions, files, and tags, or to consume change data more efficiently.
System tables fall into two categories:
Metadata tables — snapshots, schemas, options, partitions, files, tags
Data consumption tables — read-optimized table, audit log table
Metadata tables
Snapshots table
The snapshots table ($snapshots) records information about each snapshot, including its ID, creation time, and record counts.
SELECT * FROM mycatalog.mydb.`mytbl$snapshots`;| Column | Data type | Description |
|---|---|---|
snapshot_id | Long | ID of the snapshot. |
schema_id | Long | ID of the schema used by this snapshot. Cross-reference with the schemas table to get field definitions. |
commit_time | Timestamp | Time the snapshot was created. |
total_record_count | Long | Total records in the data files this snapshot points to. |
delta_record_count | Long | Records added compared with the previous snapshot. |
changelog_record_count | Long | Changelog records generated compared with the previous snapshot. |
total_record_count does not reflect the logical record count in the Paimon table. Data files are compacted in memory before being captured in a snapshot.Schemas table
The schemas table ($schemas) records every schema version of a Paimon table. Each time you modify the schema using ALTER TABLE, CREATE TABLE AS, or CREATE DATABASE AS, a new record is written to this table.
SELECT * FROM mycatalog.mydb.`mytbl$schemas`;| Column | Data type | Description |
|---|---|---|
schema_id | Long | ID of the schema version. |
fields | String | Name and data type of each column. |
partition_keys | String | Name of the partition key. |
primary_keys | String | Name of the primary key. |
options | String | Current table option values. |
comment | String | Comment on the table. |
update_time | Timestamp | Time the schema was last modified. |
Options table
The options table ($options) shows the current configuration of all explicitly set table options.
SELECT * FROM mycatalog.mydb.`mytbl$options`;| Column | Data type | Description |
|---|---|---|
key | String | Name of the table option. |
value | String | Value of the table option. |
Options not listed in this table are using their default values.
Partitions table
The partitions table ($partitions) shows the partitions in a Paimon table along with their record counts and total file sizes.
SELECT * FROM mycatalog.mydb.`mytbl$partitions`;| Column | Data type | Description |
|---|---|---|
partition | String | Partition in [partition value 1, partition value 2, ...] format. |
record_count | Long | Total records in the partition. |
file_size_in_bytes | Long | Total size of files in the partition, in bytes. Only data files in the current snapshot are counted. |
record_count does not reflect the logical record count. Data files are compacted in memory before being captured in a snapshot.Files table
The files table ($files) lists all data files the current snapshot points to, including their format, record count, and size.
Query files for the most recent snapshot:
SELECT * FROM mycatalog.mydb.`mytbl$files`;Query files for a specific snapshot (for example, snapshot ID 5):
SELECT * FROM mycatalog.mydb.`mytbl$files` /*+ OPTIONS('scan.snapshot-id'='5') */;| Column | Data type | Description |
|---|---|---|
partition | String | Partition containing the file, in [partition value 1, partition value 2, ...] format. |
bucket | Integer | Bucket containing the file. Applies only to primary key tables using fixed bucket mode. |
file_path | String | Path of the file. |
file_format | String | Format of the file. |
schema_id | Long | ID of the schema used by this file. Cross-reference with the schemas table to get field definitions. |
level | Integer | Log-structured merge-tree (LSM) level of the file. Applies only to primary key tables. A value of 0 indicates a small file — monitor this column to track compaction progress in a bucket. |
record_count | Long | Number of records in the file. |
file_size_in_bytes | Long | Size of the file, in bytes. |
The files table only covers data files in the queried snapshot. Historical data files not referenced by that snapshot are not included.
Tags table
The tags table ($tags) records information about tags, including the snapshot each tag is associated with.
SELECT * FROM mycatalog.mydb.`mytbl$tags`;| Column | Data type | Description |
|---|---|---|
tag_name | String | Name of the tag. |
snapshot_id | Long | ID of the snapshot the tag is associated with. |
schema_id | Long | ID of the schema used by the tag. Cross-reference with the schemas table to get field definitions. |
commit_time | Timestamp | Time the associated snapshot was created. |
record_count | Long | Number of records in the data files. |
Data consumption tables
Read-optimized table
If you use a primary key table and want to improve the efficiency of batch reading or ad hoc OLAP queries, you can consume data from the read-optimized table ($ro). Unlike a direct table query, which compacts data files in memory before returning results, $ro skips that step by scanning only files that are already compacted. This eliminates compaction overhead and speeds up reads, but the results may not include the latest uncompacted small files.
To compact small files at a regular interval and balance write throughput, read performance, and data latency, set the following parameter:
| Parameter | Data type | Default | Description |
|---|---|---|---|
compaction.optimization-interval | Duration | None | Interval at which small files are compacted. Set this to greater than 30 min to avoid impacting write throughput. |
SELECT * FROM mycatalog.mydb.`mytbl$ro`;Audit log table
Use the audit log table ($audit_log) when you need to trace the exact operation type behind each record — for example, to debug unexpected data changes or build downstream change-data pipelines.
Compared to the original table, the audit log table inserts a rowkind column at the start of each record to indicate the operation type:
rowkind value | Operation |
|---|---|
+I | Insert |
-U | Update (before) |
+U | Update (after) |
-D | Delete |
All records returned by the audit log table have a rowkind of +I (insert), because the table itself only appends records.
SELECT * FROM mycatalog.mydb.`mytbl$audit_log`;References
For the complete structure of each system table, see the Apache Paimon documentation.