Skip to content

Hidden tables pattern

Every catalog in tdfs4ds follows the same dual-object pattern:

FS_<CATALOG>_HIDDEN          ←  mutable table; never queried directly
FS_V_<CATALOG>               →  CURRENT VALIDTIME view; the public read path

The _HIDDEN suffix is conventional — operationally, the hidden table is just a plain physical table that nothing outside tdfs4ds is expected to read.

Why two objects

  • Single-line time travel. The public view applies CURRENT VALIDTIME automatically; switching tdfs4ds.FEATURE_STORE_TIME is enough to query the past.
  • Optimizer hygiene. Direct reads against the hidden temporal table can push the optimizer down inefficient paths. Routing everything through the view keeps the access plan predictable.
  • Hard separation between writes and reads. Anything that mutates state knows it must write to _HIDDEN; anything that reads must use the public view. No "did I forget the VALIDTIME?" mistakes.

Where the pattern lives

Public view Hidden table
FS_V_FEATURE_CATALOG FS_FEATURE_CATALOG
FS_V_PROCESS_CATALOG FS_PROCESS_CATALOG
FS_V_PROCESS_CATALOG_FEATURE_SPLIT (derived from process catalog)
FS_V_FS_DATASET_CATALOG FS_DATASET
FS_V_PROCESS_DOCUMENTATION_BUSINESS_LOGIC FS_PROCESS_DOCUMENTATION_BUSINESS_LOGIC
FS_V_PROCESS_DOCUMENTATION_FEATURES FS_PROCESS_DOCUMENTATION_FEATURES
FS_V_PROCESS_DOCUMENTATION_EXPLAIN FS_PROCESS_DOCUMENTATION_EXPLAIN
FS_V_<ENTITY>_<TYPE> FS_T_<ENTITY>_<TYPE>

The business dictionary uses the same pattern with its own suffix convention (<TABLE> mutable + automatic temporal access through MERGE statements).

Splitting views into a separate database (database_view)

By default the two objects live side by side in one database (tdfs4ds.SCHEMA). Some environments require all views in one database and all tables in another (governance / access-control policy). Pass database_view to setup() / connect() (or set TDFS4DS_SCHEMA_VIEW) and the split follows the same table/view boundary described above:

  • the hidden/base tables (left column) stay in tdfs4ds.SCHEMA;
  • the public views (right column) — and every process view, dataset view, and per-feature value view — are created and read in tdfs4ds.SCHEMA_VIEW.

Internally, table sites use tdfs4ds.SCHEMA and view sites resolve through the tdfs4ds.view_schema() accessor (= SCHEMA_VIEW or SCHEMA). Because the tables never move, a view still reads its own hidden table across the two databases; this needs the view database to hold SELECT ... WITH GRANT OPTION on the table database. Leaving database_view unset keeps both objects co-located (the default, unchanged). See Connect and configure.

When you might bypass it

You should never write to a public view, and you should never read directly from a hidden table — but tdfs4ds itself does both, internally, in tightly scoped functions:

  • register_features, register_entity, store_feature write to the hidden tables via MERGE / UPDATE_INSERT.
  • feature_catalog, process_catalog, dataset_catalog read the public views and apply additional DATA_DOMAIN / pandas filters.

If you ever need to inspect a row that the public view filters out (e.g. a soft-deleted feature), query the hidden table directly with SELECT ... AS OF <ts> — but treat it as a diagnostic, not as part of an application.