Skip to main content

​
Overview

The metadb.base_table table stores information about all tables that are extracted from data sources or transformed from those tables. This is a system metadata table that tracks the lineage and source of each table in your Metadb instance.

​
Schema

​
schema_name
varchar(63)
The schema name where the base table resides
​
table_name
varchar(63)
The name of the base table
​
source_name
varchar(63)
The name of the data source this table was extracted from (as defined in create data source)
​
transformed
boolean
true if this table is a transformed table (created via data mapping), false if it’s extracted directly from a source
​
parent_schema_name
varchar(63)
Schema name of the parent table (only populated if transformed is true)
​
parent_table_name
varchar(63)
Table name of the parent table (only populated if transformed is true)

​
Usage

​
Query All Base Tables

select * from metadb.base_table
order by schema_name, table_name;

​
Example Output

 schema_name |     table_name      | source_name | transformed | parent_schema_name | parent_table_name
-------------+---------------------+-------------+-------------+--------------------+-------------------
 library     | patron__            | folio       | false       | null               | null
 library     | patrongroup__       | folio       | false       | null               | null
 library     | inventory_metadata  | folio       | true        | library            | inventory__
 sensor      | air_temp__          | sensor_1    | false       | null               | null
(4 rows)

​
Use Cases

List all tables extracted from a specific data source:
select schema_name, table_name
from metadb.base_table
where source_name = 'folio'
  and transformed = false
order by table_name;
Find all tables that were created through data transformation:
select 
  schema_name || '.' || table_name as transformed_table,
  parent_schema_name || '.' || parent_table_name as parent_table,
  source_name
from metadb.base_table
where transformed = true;
Understand the complete lineage of a table including its source and any parent relationships:
select 
  table_name,
  case 
    when transformed then 'Transformed from ' || parent_table_name
    else 'Extracted from ' || source_name
  end as origin
from metadb.base_table
where schema_name = 'library';
Get a summary of how many tables come from each data source:
select 
  source_name,
  count(*) filter (where not transformed) as extracted_tables,
  count(*) filter (where transformed) as transformed_tables,
  count(*) as total_tables
from metadb.base_table
group by source_name
order by total_tables desc;

​
Understanding Table Types

Main tables end with double underscores (e.g., patron__) and contain historical records. The corresponding current table (e.g., patron) contains only current records where __current = true.
The metadb.base_table system table tracks metadata only. Changes to this table are managed automatically by Metadb — do not insert, update, or delete rows manually.

​
Transformed Tables

When you create data mappings to transform JSON or other data, Metadb creates new tables and records them in metadb.base_table with:
  • transformed = true
  • parent_schema_name and parent_table_name pointing to the source table
  • source_name inherited from the parent table
Example of creating a transformation that will appear in this table: 
create data mapping for json
    from table library.inventory__ column jsondata path '$.metadata'
    to 'metadata';
This creates library.inventory_metadata which will be tracked as a transformed table.

metadb.table_update

Track when tables were last updated

Data Sources

Define data sources that populate these tables

Data Mappings

Create transformed tables from JSON data

metadb.log

View system logs and events

Build docs developers (and LLMs) love

Get started for freeTalk to us