Skip to main content
Metadb generates several types of tables to support both current state queries and historical analysis. Understanding these table types is essential for effective querying and data analysis.

​
Metadata Columns

All Metadb-generated tables include standard metadata columns with names beginning with double underscores:
ColumnDescription
__idSurrogate key that uniquely identifies a row (defined by Metadb)
__startTimestamp when the row was generated
__endTimestamp when the row became no longer current, or 9999-12-31 00:00:00+00 if still current
__currentBoolean indicating whether the row is current
__originOptional identifier for grouping related data from one or more data sources
The __id column is distinct from any id column that may come from your data source. The __id is always unique and managed by Metadb, while source columns may change with schema changes.

​
Main Tables

Main tables have names ending with two underscores (e.g., patrongroup__) and contain both the current state and complete history of all previous states.

​
Example: Library Patron Groups

select __start, __end, __current, id, groupname, description 
    from library.patrongroup__;
__start__end__currentidgroupnamedescription
2022-04-17 21:42:25-002022-04-18 19:27:18-00false15undergradStudent
2022-04-18 19:27:18-009999-12-31 00:00:00+00true15undergradUndergraduate Student
2022-04-17 21:42:25-009999-12-31 00:00:00+00true10graduateGraduate Student
In this example, the “undergrad” group with id = 15 has two rows because it was modified, changing the description from “Student” to “Undergraduate Student”.
Main tables are perfect for historical analysis, tracking changes over time, and answering questions like “What did this record look like on a specific date?”

​
Current Tables

For queries that only need current records, current tables provide a convenient filtered view. Current tables have the same name as their main table but without the trailing underscores.

​
Querying Current Data

These two queries are equivalent: 
-- Using main table with filter
select id, groupname, description 
    from library.patrongroup__ 
    where __current;

-- Using current table (simpler)
select id, groupname, description 
    from library.patrongroup;
In some database client applications, current tables may not appear separately in the table list because they have a parent-child relationship with main tables.

​
Current Table Benefits

  • Simpler queries: No need to filter on __current
  • Better performance: Optimized for current state queries
  • Familiar naming: Matches the original table names in the data source
Current tables are sometimes called base tables because they match the original table names from the data source.

​
Transformed Tables

Metadb can apply transformations to create additional tables with processed data. The most common transformation is extracting JSON fields into columns.

​
JSON Transformation

Suppose your data contains JSON in a jsondata column: 
select __id, __start, id, jsondata 
    from library.patrongroup;
__id__startidjsondata
82022-04-18 19:27:18-0015{"groupname": "undergrad", "description": "Undergraduate Student"}
Without transformation, you’d need to extract fields manually: 
select __id,
       __start,
       id,
       jsonb_extract_path_text(jsondata, 'groupname') as groupname,
       jsonb_extract_path_text(jsondata, 'description') as description
    from library.patrongroup;

​
Creating Transformations

Define a transformation to automatically extract JSON fields: 
create data mapping for json
    from table library.patrongroup__ column jsondata path '$'
    to 't';
This creates transformed tables:
  • Main transformed table: patrongroup__t__
  • Current transformed table: patrongroup__t
Now you can query with simpler SQL: 
select __id, __start, id, groupname, description 
    from library.patrongroup__t;
JSON fields are inferred dynamically from the data. A new column is added automatically when a record contains a new JSON field.

​
Nested JSON Transformations

For nested JSON objects and arrays, create multiple mappings: 
-- Transform the root object
create data mapping for json
    from table library.inventory__ column jsondata path '$'
    to 't';

-- Transform nested metadata object
create data mapping for json
    from table library.inventory__ column jsondata path '$.metadata'
    to 'metadata';

-- Transform tags object
create data mapping for json
    from table library.inventory__ column jsondata path '$.tags'
    to 'tags';

-- Transform tagList array within tags
create data mapping for json
    from table library.inventory__ column jsondata path '$.tags.tagList'
    to 'taglist';
An object or array will only be transformed if all its parent objects are also transformed. For example, transforming $.tags.tagList requires transforming both $.tags and $.

​
Table Type Comparison

Table NameTable TypeDescription
patrongroup__Main tableCurrent and historical records
patrongroupCurrent table (base table)Current records only
patrongroup__t__Main transformed tableTransformed versions of records in patrongroup__
patrongroup__tCurrent transformed tableTransformed versions of records in patrongroup
Where t is an identifier configured during transformation setup. Multiple transformed tables can exist with different identifiers.

​
Partition Tables

Some partition tables have names beginning with zzz___. These are internal tables used by Metadb for performance optimization.
It is recommended not to query partition tables directly. Access to these tables is generally disabled.

​
Modifying Tables

You can modify table definitions using the alter table command:

​
Adding Columns

alter table library.patrongroup__ add column description text;

​
Changing Column Types

alter table library.patron__ alter column patrongroup_id type uuid;
Currently supported types for manual alterations are text and uuid. Metadb also performs automatic type conversions in many cases.

​
Best Practices

Choose the Right Table Type

Use current tables for most queries. Only query main tables when you need historical data.

Transform Complex JSON

Create transformations for frequently queried JSON fields to improve query simplicity and performance.

Avoid Querying Partition Tables

Stick to main, current, and transformed tables. Partition tables are internal implementation details.

Monitor Transformed Columns

Remember that JSON transformations add columns dynamically as new fields appear in the data.

Build docs developers (and LLMs) love

Get started for freeTalk to us