Skip to main content

​
Overview

The metadb.table_update table stores information about table updates, including when each table was last successfully updated and how long the update took. This is particularly useful for monitoring external SQL execution and periodic table transformations.
This table tracks updates for tables created by external SQL execution and some transform operations. It does not track every individual record insert/update from streaming sources.

​
Schema

​
schema_name
varchar(63)
The schema name of the table being tracked
​
table_name
varchar(63)
The table name being tracked
​
last_update
timestamptz
The timestamp when the table was last updated successfully
​
elapsed_real_time
real
The wall-clock time in seconds of the last completed update process

​
Usage

​
Query All Table Updates

select * from metadb.table_update
order by last_update desc;

​
Example Output

 schema_name |    table_name     |        last_update         | elapsed_real_time
-------------+-------------------+----------------------------+-------------------
 folio       | user_group        | 2024-03-11 10:30:15.123-05 |             12.45
 folio       | library_patrons   | 2024-03-11 10:25:08.456-05 |             45.78
 reshare     | circulation_stats | 2024-03-11 09:15:22.789-05 |             23.12
(3 rows)

​
Use Cases

Check when tables were last updated to ensure data is current:
select 
  schema_name || '.' || table_name as full_table_name,
  last_update,
  now() - last_update as time_since_update
from metadb.table_update
order by last_update desc;
Identify tables that haven’t been updated recently:
select 
  schema_name,
  table_name,
  last_update,
  age(now(), last_update) as age
from metadb.table_update
where last_update < now() - interval '24 hours'
order by last_update;
Find tables that take a long time to update:
select 
  schema_name,
  table_name,
  elapsed_real_time,
  elapsed_real_time / 60.0 as elapsed_minutes
from metadb.table_update
where elapsed_real_time > 30
order by elapsed_real_time desc;
Create a summary report of all table updates:
select 
  schema_name,
  count(*) as tables,
  max(last_update) as most_recent_update,
  avg(elapsed_real_time) as avg_update_time_seconds,
  max(elapsed_real_time) as max_update_time_seconds
from metadb.table_update
group by schema_name
order by schema_name;
Get update information for a specific table:
select 
  last_update,
  elapsed_real_time,
  now() - last_update as time_since_update,
  case 
    when last_update > now() - interval '1 hour' then 'Fresh'
    when last_update > now() - interval '24 hours' then 'Current'
    else 'Stale'
  end as freshness_status
from metadb.table_update
where schema_name = 'folio'
  and table_name = 'user_group';

​
External SQL Integration

This table is closely integrated with Metadb’s external SQL feature. When external SQL files include the --metadb:table directive, Metadb updates this table after the SQL executes: 
-- In your external SQL file:
--metadb:table library_patrons

drop table if exists library_patrons;

create table library_patrons as
select * from patron_transform;
After this SQL runs, metadb.table_update will contain:
  • schema_name: The schema where the table was created
  • table_name: library_patrons
  • last_update: Timestamp when the SQL completed
  • elapsed_real_time: How many seconds the SQL took to execute
The --metadb:table directive should not include a schema name. Tables are created in the schema designated for external SQL execution.

​
Performance Monitoring

You can track performance over time by querying this table regularly: 
-- Create a history table
create table admin.table_update_history as
select 
  current_timestamp as snapshot_time,
  *
from metadb.table_update;

-- Periodically insert snapshots
insert into admin.table_update_history
select current_timestamp, *
from metadb.table_update;

-- Analyze trends
select 
  table_name,
  snapshot_time::date as date,
  avg(elapsed_real_time) as avg_seconds,
  count(*) as update_count
from admin.table_update_history
where table_name = 'library_patrons'
group by table_name, snapshot_time::date
order by date desc;

​
Set Up Alerts

-- Find tables that haven't updated in expected timeframe
select 
  schema_name || '.' || table_name as table_name,
  last_update,
  age(now(), last_update) as overdue_by
from metadb.table_update
where last_update < now() - interval '2 hours'
order by last_update;
The metadb.table_update table is managed automatically by Metadb. Do not insert, update, or delete rows manually. The system updates this table after successful table update operations.

​
Relationship to Other Tables

Combine with metadb.base_table to get complete table metadata: 
select 
  bt.schema_name,
  bt.table_name,
  bt.source_name,
  bt.transformed,
  tu.last_update,
  tu.elapsed_real_time
from metadb.base_table bt
left join metadb.table_update tu
  on bt.schema_name = tu.schema_name
  and bt.table_name = tu.table_name
where bt.source_name = 'folio'
order by tu.last_update desc nulls last;
This shows you which tables from a data source have update tracking and when they last ran.

metadb.base_table

View table metadata and lineage

metadb.log

Check logs for update errors

External SQL Configuration

Learn about external SQL execution

Monitoring Functions

Monitor system activity

Build docs developers (and LLMs) love

Get started for freeTalk to us