Skip to main content

Data Freshness Queries

Use these queries to poll for Data Warehouse load completion instead of synchronizing clocks with the nightly schedule.

data_mart_metadata.data_freshness records when the full Data Warehouse load finished successfully and the data is ready to query. data_mart_metadata.table_data_freshness records when an individual delivered table finished loading.

Latest Full Data Warehouse Load Complete Time​

-- Recommended overall ready-to-query signal for the Data Warehouse

SELECT MAX(data_freshness_dt) AS latest_data_freshness_dt
FROM data_mart_metadata.data_freshness;

Full Data Warehouse Load History​

-- Append-only history of successful full Data Warehouse loads

SELECT
    data_freshness_id
    , data_freshness_dt
FROM data_mart_metadata.data_freshness
ORDER BY data_freshness_dt DESC;

Per-Table Freshness​

-- Check whether a specific delivered table has finished loading

SELECT
    table_name
    , institution_id
    , table_freshness_dt
FROM data_mart_metadata.table_data_freshness
WHERE table_name = 'data_mart.dim_person';

All Table Freshness Timestamps​

-- Current freshness snapshot for every delivered Data Warehouse table

SELECT
    table_name
    , institution_id
    , table_freshness_dt
FROM data_mart_metadata.table_data_freshness
ORDER BY table_name;