Skip to main content

​
Understanding Table Types

Metadb generates several types of tables to support both current state and historical analytics. Understanding these table types is essential for effective querying.

​
Main Tables

Main tables have two underscores at the end of their name (e.g., patrongroup__) and contain both the current state and the complete history of all previous states from the data source. All Metadb tables include these metadata columns:
ColumnDescription
__idSurrogate key that uniquely identifies a row
__startDate and time when the row was generated
__endDate and time 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 defined by Metadb and is present in all generated tables. It differs from any id column that may exist in your source data.

​
Example: Querying Main Tables

SELECT __start, __end, __current, id, groupname, description 
FROM library.patrongroup__;
This query might return:
__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 (id=15) has two rows because the description was modified on 2022-04-18.

​
Current Tables

Current tables contain only the most recent version of each row. They share the same name as their main table but without the trailing underscores.
1

Filter main table by __current

You can query current records from a main table:
SELECT id, groupname, description 
FROM library.patrongroup__ 
WHERE __current;
2

Use the current table directly

For convenience, use the current table instead:
SELECT id, groupname, description 
FROM library.patrongroup;
Both queries return identical results.
Current table names are sometimes called “base tables” because they match the original table names in the data source.

​
Example: Current Table with Metadata

SELECT __id, __start, __origin, id, groupname, description 
FROM library.patrongroup;
__id__start__originidgroupnamedescription
82022-04-18 19:27:18-00west15undergradUndergraduate Student
42022-04-17 17:42:25-00east10graduateGraduate Student
52022-04-17 17:52:53-00east9facultyFaculty Member

​
Transformed Tables

Metadb can apply transformations to data, particularly for extracting JSON fields into columns. Transformed tables simplify queries by flattening nested data structures.

​
JSON Transformations

Suppose your data includes JSON fields: 
SELECT __id, __start, id, jsondata 
FROM library.patrongroup;
__id__startidjsondata
82022-04-18 19:27:18-0015{"groupname": "undergrad", "description": "Undergraduate Student"}
SELECT __id,
       __start,
       id,
       jsonb_extract_path_text(jsondata, 'groupname') AS groupname,
       jsonb_extract_path_text(jsondata, 'description') AS description
FROM library.patrongroup;
The transformed table (patrongroup__t) provides cleaner, more maintainable queries.
JSON fields are inferred from the data itself. New columns are added dynamically when records containing new JSON fields are streamed.

​
Table Type Summary

Table NameTable TypeDescription
patrongroup__Main tableCurrent and historical records
patrongroupCurrent table (base table)Current records only
patrongroup__{X}__Main transformed tableTransformed versions of records in main table
patrongroup__{X}Current transformed tableTransformed versions of current records
where {X} is an identifier configured by your system administrator (e.g., t).
Tables beginning with zzz___ are partition tables used internally. Do not query these tables directly.

​
Querying System Information

​
Check Metadb Version

SELECT mdbversion();

​
View Data Update Times

SELECT * 
FROM metadb.table_update 
ORDER BY schema_name, table_name;

​
Review System Logs

SELECT * FROM mdblog();

​
Monitor Long-Running Queries

From another session, check the status of query processes: 
SELECT * FROM ps();
Use ps() to monitor the progress of long-running analytical queries without interrupting them.

Build docs developers (and LLMs) love

Get started for freeTalk to us