> For the complete documentation index, see [llms.txt](https://docs.opendatadiscovery.org/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.opendatadiscovery.org/features/master-data-management/lookup-tables.md). # Lookup Tables **Lookup Tables** are operator-curated reference tables that live inside the ODD Platform itself rather than in an external source system. The platform manages their lifecycle end-to-end: schema definition, row-level data entry, versioning of structure changes, RBAC, and read access via the platform's API. Each lookup table is exposed in the catalog as a standard Data Entity (entity type `LOOKUP_TABLE`, ID `24`), so existing search, tagging, descriptions, and term-linking flows apply uniformly. In the platform UI, lookup tables live under the top-level **Master Data** tab → **Lookup Tables**. The same page name appears throughout the API and code as "Lookup Table"; "Master Data" is the toolbar tab label, "Lookup Table" is the per-row noun. ![Master Data → Lookup Tables list page — every lookup table the user can read, with name, description, and namespace columns plus search and the + Add new entry-point gated by the LOOKUP\_TABLE\_CREATE permission.](/files/UdauHXmx9hK012HuKVdA) ## Creating a Lookup Table The creation of Lookup Tables involves adding a new table through the **`+Add new`** button in the right upper corner of the Master Data section. On a physical level, this implies that an actual table will be generated by ODD using the specified [configuration](/configuration-and-deployment/odd-platform.md) connection. These tables are entirely managed by the ODD Platform.

Adding a new table

When a Lookup Table is created a Data Entity of type "lookup table" is generated.

Lookup Table is created

The Table name entered during creation is assigned to it as a Business Name. Simultaneously, an Original Name is provided, prefixed with the ID of Namespace. This prefix is added to distinguish tables with potentially identical names and it ensures that the tables adhere to a format acceptable for PostgreSQL.

The Business and the Original names of Lookup Table

The Original Name of Lookup Table functions as a table identifier within PostgreSQL, allowing it to be identified by the users. This information is useful when tasks such as uploading the table elsewhere or establishing a direct connection to the table data are required. **Renaming the table after creation issues `ALTER TABLE … RENAME TO` against the underlying PostgreSQL table** — downstream BI / ETL pipelines that hardcoded the old name break immediately. See the [rename-break caveat](#known-operator-caveats) below before renaming a table that any downstream consumer depends on. ## Lookup Table structure The creation of the Lookup Table structure starts with column creation on a Structure Tab. Column creation starts with **`+Add column`** button and requires the specification of Column Name.

Column creation

In contrast to the Table Name, which can assume any format, the right selection of name at this stage is crucial and adhering to a specific format is considered essential.

Specific format for Column Name is required

Further, the creation of Descriptions occurs at this point. Essentially, the columns that will be generated within the current table are the focus of these descriptions. The final step in creating a column involves selecting a Data Type. Currently, a limited list of PostgreSQL data types is supported for columns (see [Supported field types](#supported-field-types) below). Once the Column Name, Description and Data Type are provided, the column is created by clicking on the **`Add column`** button.

Lookup Table Structure

It is possible to further edit, i.e. to rename column or edit the description for that column, or delete columns:

Deleting a Column

Once a column is created and its corresponding data type is already selected, the alteration of that data type becomes impossible.

Editing a Column

If there is a need to change the data type for a column, a secure approach involves the: 1. creation of a new column with the desired data type, 2. transfer of the old column data to the new column, and 3. deletion of the old column. This method ensures a ***secure approach to any data migration***. Each time the table structure is modified, a new revision of structure is generated, allowing table version differences to be tracked.

Revisions

### Supported field types A column's data type is selected once at column creation and cannot be changed afterwards (use the migration pattern above). The platform exposes the following nine field-type options via the `LookupTableFieldType` enum. They map to **eight** distinct PostgreSQL types — `SERIAL` is not a separate type but an auto-increment `INTEGER`, so it shares the underlying `integer` type with the plain `INTEGER` option: | Field type | PostgreSQL mapping | Typical use | | ---------- | ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------- | | `VARCHAR` | `varchar` | Variable-length text. | | `INTEGER` | `integer` | 4-byte signed integer. | | `SERIAL` | `serial` | Auto-incrementing integer (typical for synthetic keys); the primary-key `id` column is generated automatically and cannot be redefined. | | `DECIMAL` | `numeric` | Arbitrary-precision decimal. | | `BOOLEAN` | `boolean` | True/false. | | `DATE` | `date` | Calendar date (no time component). | | `TIME` | `timestamp` | A full date+time instant, **not** a time-of-day. Supply values as `yyyy-mm-dd hh:mm:ss`; a bare time-of-day value is rejected with a `400`. | | `JSON` | `json` | JSON document. | | `UUID` | `uuid` | 128-bit identifier. | Per-column validation is also configurable: each field carries `is_nullable` (default `true`), `is_unique` (default `false`), and an optional `default_value` string. ## Adding and managing data Within ODD Platform an exclusive Data tab is made available for Lookup Tables. It displays a table with headers that mirror the structure of Lookup Table, which is created in the Structure tab. When a column is added in the Structure tab, its appearance in the Data tab is immediate. Data values can be inputted into the table columns by clicking on **`[+]`** button within the Data tab.

Lookup Table Headers

This table has a flexible structure, allowing addition of multiple diverse columns.

Filling in the columns with data

Even after filling in the columns with data, the table structure remains customizable, i.e. it is still possible to edit, add or delete its columns. The filled in data is also subject to be edited or deleted.

Lookup Table Data is subject to be edited or deleted

The information entered into the table resides in a separate schema within the database (`lookup_tables_schema` — see [Direct database access](#direct-database-access) below). ## Lookup Table vs regular Data Entity The Lookup Table is essentially the same as regular Data Entity in ODD. The key difference lies in the fact that for a standard data entity there is no direct ability to modify its structure while for a lookup table the interaction with the structure becomes possible. All the actions possible with the structure in any other data entity, such as adding Descriptions, Tags, Terms and Business Names can be performed both with Lookup Table itself and its columns also.

Adding Descriptions, Tags, Terms and Business Names to Lookup Table itself and to its columns

Also, the ID column, marked as the Primary Key, is automatically generated and cannot be modified. While the Primary key is currently unchangeable, there are plans for future developments that will enhance user experience by allowing interaction with it. ## Accessing Lookup Table data Access to the data in the Lookup table is available by the distinct schema within PostgreSQL, and it can also be accessed via an API. * **Access via an API** is currently considered not the most user friendly or recommended method, because with this access for example only column IDs are accessible and not their corresponding names. Nevertheless, assistance from our team is available to users in configuring access, ensuring that additional data can also be made available through the API in such cases. The full surface is documented at [API Reference → Reference Data](/developer-guides/api-reference/reference-data.md). * **Direct database access** is the recommended path for downstream consumers (BI tools, ETL jobs, ad-hoc queries) that need the human-readable column names. ### Direct database access For all of its features ODD Platform uses PostgreSQL database and PostgreSQL database only. Database connection defining block would look like this: {% code title="YAML" overflow="wrap" %} ```yaml spring.custom-datasource.url #unset by default (@Value("${spring.custom-datasource.url:}")); falls back to #spring.datasource.url, which itself defaults to jdbc:postgresql://127.0.0.1:5432/odd-platform spring.custom-datasource.username #unset by default; falls back to spring.datasource.username (default: odd-platform) spring.custom-datasource.password #unset by default; falls back to spring.datasource.password (default: odd-platform-password) ``` {% endcode %} Within PostgreSQL, ODD Platform database comprises two primary schemas: * **public** — this schema contains the essential resources of the ODD itself; * **lookup\_tables\_schema** — this schema contains all the lookup tables created by the user.\ Users can interact with these tables just like any other regular tables within the database.

ODD Platform database two primary schemas

Placing them in a separate schema is a matter of convenience, aimed to simplify the management of these tables particularly during migration like activities. This approach ensures a clear separation between user tables and service tables, allowing for independent handling and manipulation. ## RBAC permissions Lookup Table actions are gated by 9 permissions on three surfaces — table, definition (the column schema), and data (the rows). Assign these via the platform's [Policies](/configuration-and-deployment/enable-security/authorization/policies.md) and [Roles](/configuration-and-deployment/enable-security/authorization/roles.md) flow. The full permission list is regenerated from the OpenAPI spec under [Permissions](/configuration-and-deployment/enable-security/authorization/permissions.md); the rows specific to Lookup Tables: | Permission | Surface | What it gates | | -------------------------------- | -------------------- | ----------------------------------------------------------------------------------------------------------------- | | `LOOKUP_TABLE_CREATE` | Table | Creating a new lookup table (the `+Add new` flow). | | `LOOKUP_TABLE_UPDATE` | Table | Renaming a lookup table or editing its description. | | `LOOKUP_TABLE_DELETE` | Table | Deleting a lookup table. | | `LOOKUP_TABLE_DEFINITION_CREATE` | Definition (columns) | Adding a column to an existing table's structure. | | `LOOKUP_TABLE_DEFINITION_UPDATE` | Definition (columns) | Editing column metadata (rename, description, validation flags). The data type itself cannot be changed once set. | | `LOOKUP_TABLE_DEFINITION_DELETE` | Definition (columns) | Deleting a column from the structure. | | `LOOKUP_TABLE_DATA_CREATE` | Data (rows) | Inserting a new row. | | `LOOKUP_TABLE_DATA_UPDATE` | Data (rows) | Editing an existing row's cell values. | | `LOOKUP_TABLE_DATA_DELETE` | Data (rows) | Deleting a row. | **These nine permissions gate writes only, and they are global.** There is no read permission for Lookup Tables: every read and search endpoint (`GET` table / columns / rows, and search) requires only that the caller is authenticated, so any authenticated user can read every lookup table's structure and data regardless of who created it. The write permissions above are also global rather than per-table or per-owner — a holder of `LOOKUP_TABLE_DEFINITION_UPDATE` (or any other `LOOKUP_TABLE_*` permission) can act on **any** lookup table, not only ones they created. Multi-team deployments that need per-team isolation of lookup-table reads or writes must enforce it outside the platform's RBAC (network perimeter / ingress rules). The split lets operators grant edit-the-data without grant-edit-the-schema (a typical pattern for steward-curated reference lists), or grant create-the-table without grant-fill-the-data. ## Known operator caveats Six behaviours of the Lookup Tables surface are non-obvious from the UI alone. Each item below states what an operator might assume, what the platform actually does, and what to do today. {% hint style="danger" %} **`LOOKUP_TABLE_*` permissions are global — there is no per-table RBAC today.** All nine permissions in the table above are gated against the **table-class** as a whole, not against a specific lookup-table id. The platform's authorization rules apply a no-context resolver to every `/api/referencedata/table/*` endpoint — the table id in the URL is not passed to a per-table permission-scope extractor. Contrast with `DATA_ENTITY_*` and `TERM_*` permissions, which **do** use per-resource resolvers (a `DATA_ENTITY_TAGS_UPDATE` Policy can target one entity; a `TERM_UPDATE` Policy can target one term). **Operator-visible consequence.** A Policy granting `LOOKUP_TABLE_UPDATE` (or any other `LOOKUP_TABLE_*`) to a role permits modifying / deleting **every** lookup table in the platform — regardless of how the Policy's Conditions are scoped. A user with `LOOKUP_TABLE_UPDATE` granted "for the Finance team's tables" can edit the Marketing team's tables and the Engineering team's tables identically. The Policy Conditions UI accepts per-table scoping; the runtime ignores it for this permission class. **Mitigation today:** do not grant any `LOOKUP_TABLE_*` permission to roles that should be restricted to a subset of tables. Treat the nine permissions as a single "global lookup-table steward" cluster and assign them to a small set of trusted operators. Per-table RBAC is on the upstream roadmap; until it ships, the Policy-Condition UI's per-table affordance does not narrow these permissions. {% endhint %} {% hint style="info" %} **Deleting a Lookup Table removes both halves — use the Master Data UI.** A Lookup Table is two linked objects: the platform-side registry row (in `public.lookup_tables`) and the backing PostgreSQL table (in `lookup_tables_schema`). Deleting it through the Master Data UI — or `DELETE /api/referencedata/table/{lookup_table_id}` — cleans up both: it drops the backing schema-side table and its sequences, then removes the registry row. There is no whole-entity catalog delete for a Lookup Table's parent (the `/api/dataentities/{data_entity_id}` path is read-only, and the entity detail page exposes no Delete action), so the catalog side cannot leave the backing table orphaned. {% endhint %} {% hint style="danger" %} **Renaming a Lookup Table silently breaks every downstream BI / ETL pipeline that hardcoded the old name.** The page above documents the Original Name as the PostgreSQL table identifier — the "SQL-joinable schema-direct-access" contract on which BI / ETL integrations rely. Renaming the table through the platform UI (or the `PUT /api/referencedata/table/{id}` endpoint) issues `ALTER TABLE … RENAME TO` against the underlying PostgreSQL table; from the very next millisecond, queries against the old name return `relation "" does not exist`. The platform does **not** create a PostgreSQL VIEW alias under the old name, does **not** issue a "rename-blocker" warning at the UI, and does **not** surface a list of downstream consumers before applying the change. **Operator workflow.** Treat Lookup Table renames as a coordinated migration window: enumerate the downstream SQL references (BI dashboards, ETL DAGs, ad-hoc queries cached in tools like Hex / Metabase / dbt), rewrite each to the new name in lock-step with the rename, and avoid renaming Lookup Tables that are referenced by integrations you do not own. The same class of silent-default risk that previously affected attachment storage and the soft-delete TTL applies here — a default-shaped action with cascading-failure consequences. {% endhint %} {% hint style="warning" %} **Lookup Table values, column names, and the table name itself are stored and rendered without escaping — treat the surface as trusted-input.** The platform's lookup-table value-validator returns input values verbatim and stores them unchanged. The same path is used for the table's display name, the column names entered on the Structure tab, and every per-row cell value entered on the Data tab. Rendering surfaces in the catalog UI display the stored text as HTML; pasting content from untrusted sources (an external spreadsheet, a webhook payload, a user-submitted form) can store an active payload that fires when another operator opens the table. **Mitigation today.** Restrict Lookup Table editing to operators reviewing the source of every value they paste in — the same posture you would apply to any "free-text into the database" surface. Avoid bulk-importing values from external systems without a sanitisation pre-pass. If your deployment ingests Lookup Table content from external pipelines, gate that ingestion behind a sanitiser at the integration layer. {% endhint %} {% hint style="warning" %} **The Lookup Tables list silently truncates at 30 rows — deployments with more than 30 tables see only the first page.** The list page on Master Data → Lookup Tables uses an infinite-scroll renderer wired with a mismatched DOM target id (the renderer attempts to attach its scroll listener to a container id that exists on the catalog Directory page, not on this page). The renderer falls back to a window-scroll listener; the container's `overflow: auto` scroll never reaches the window. Net effect: the **31st row is never requested**. The list appears complete (no skeleton at the bottom, no "Load more" button, no pagination indicator), but stops at 30 rows for any tenant with more than 30 lookup tables. **How to spot it.** The page's H1 counter displays the **real total** — "250 lookup tables overall" — while the list shows 30. The discrepancy between the H1 counter and the visible-list length is the only operator-visible signal that the truncation is in effect. **Workaround until the upstream fix lands.** To find a specific table that doesn't appear in the visible list, use the URL-driven search affordance on this page (the search-id query parameter). The API surface at `/api/referencedata/table` returns the full set with proper pagination — direct API consumers are not affected by the UI bug. The upstream platform fix is a one-line DOM-id correction. {% endhint %} {% hint style="warning" %} **Renaming a Lookup Table emits no Activity Feed event — post-mortems cannot answer "who renamed which table when."** Combined with the rename-break caveat above, the audit silence amplifies the blast radius: an operator investigating a "BI dashboard broke yesterday" cannot trace the root cause to a specific rename through platform logs. The Activity Feed has no `LOOKUP_TABLE_RENAMED` event type, the `lookup_tables` row does not stamp `last_modified_by`, and the platform's audit instrumentation does not annotate the underlying service method. **Operator workflow.** If compliance / audit requirements include "who renamed which table when," instrument it externally — PostgreSQL `pgaudit` on `ALTER TABLE` statements against `lookup_tables_schema.*` plus a manual cross-reference to the operator's authenticated session is the most reliable substitute today. Until the platform-side activity-event ships, the in-app audit trail is silent on this action. {% endhint %} {% hint style="warning" %} **The Description you enter when creating or editing a Lookup Table does not appear on the table's overview "About" section.** The Create / Edit Lookup Table form has a **Description** field, but the platform stores that text only on the lookup-table record — it is **never copied to the underlying catalog Data Entity**, and the entity overview's **About** block reads the entity's *own* internal description. So a description typed during lookup-table creation renders nowhere on the entity page, and the overview looks empty even though you filled the field in. **Workaround until the fix lands.** To give a Lookup Table a description that shows in the catalog, add it on the **entity overview** itself: open the table in the catalog and use **About → Add info / Edit info** (the same description flow as any other Data Entity). That writes the entity's internal description directly. Re-entering it there is the workaround until the Create / Edit form writes the description through to the entity. The upstream fix is to propagate the form's description onto the entity's internal description. {% endhint %} ## API reference The full HTTP API for Lookup Tables is documented at [API Reference → Reference Data](/developer-guides/api-reference/reference-data.md) — 16 endpoints across four groups (Table CRUD, Column CRUD, Row CRUD, Search) under `/api/referencedata/`, plus the `LookupTableFieldType` mapping notes and the per-endpoint `LOOKUP_TABLE_*` RBAC gating that ties back to the [permissions table above](#rbac-permissions). ## Where to next * [Master Data Management](/features/master-data-management.md) — the parent pillar landing; positions Lookup Tables within the broader MDM / Reference-Data Management surface and the Data Governance map. * [Data Modelling](/features/data-modelling.md) — the sibling pillar for query examples and entity relationships. * [Permissions](/configuration-and-deployment/enable-security/authorization/permissions.md) — the canonical, regenerated-from-spec permission list (includes the 9 Lookup Tables rows above). * [Configure ODD Platform](/configuration-and-deployment/odd-platform.md) — the Spring datasource keys (`spring.custom-datasource.*`) the platform uses for both its own metadata and the `lookup_tables_schema`. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.opendatadiscovery.org/features/master-data-management/lookup-tables.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.