User-Defined Fields in Veson Data Lake: Configuration, Querying, and Limitations

If you query IMOS data in Snowflake, your User-Defined Fields (UDFs) appear in Data Lake as columns you can report on. This article explains where to find them, which objects to query, why row counts differ between those objects, and what happens in Snowflake when you add, rename, or deactivate a UDF or clear its value in IMOS.

What a User-Defined Field is

A User-Defined Field, or UDF, is an extra field your administrators create in the IMOS Data Center against a particular kind of record: an extra reference on a Cargo, a counterparty code on an Address Book entry, a settlement note on a Time Charter contract. Each one is called a prompt. You can add as many as you need, at any time, without involving Veson.

IMOS stores prompts and their values separately from the records they hang off. Data Lake reassembles them. For every kind of record that supports UDFs, Data Lake publishes a companion object whose columns are your prompts and whose rows are your records. If your administrator adds a prompt called TRN No to the Address Book, a column called TRN No appears in AddressBookUserDefinedFields.

That is the whole idea: your configuration becomes the schema. It also explains most of the behavior described on this page, because a schema that follows your configuration has to cope with prompts being added, renamed, cleared and deactivated while queries are running against it.

Which records support UDFs

Ten record types are published today. Each object is built from the IMOS table that holds the record and is keyed on that record's business key rather than an internal row number, so the keys match what you see in IMOS.

Not yet available

UDFs on Financial Invoices are configurable in IMOS and visible in Report Designer, but they are not published to Data Lake. This is a recognized gap with an open enhancement request. If you depend on invoice UDFs for month-end reporting, raise it with your Veson contact so it can be weighted against other demand.

Where UDFs live in Snowflake

Three objects can carry the same UDF name. They are built to different rules on purpose, and only one of them is the surface to build on.

Action

If any of your queries, views or BI models still read TRANSACTIONAL."<X>UserDefinedFields", migrate them to REPORTING. The transactional views are scheduled for removal and may still be present in your environment in the meantime.

How prompts become columns

The column name is the prompt name

Column names are taken verbatim from the prompt text in IMOS, including spaces and case. Quote them in SQL: SELECT "TRN No" FROM .... Environments configured for Quoted Identifiers Ignore Casing behave as you would expect for case.

Duplicate prompt names are disambiguated by field ID

IMOS permits two prompts on the same record type to share a name, most often when an old prompt has been deactivated and a new one created with the same label. A single column cannot serve both, so where names collide the column is prefixed with the prompt's internal field ID, giving column names of the form 23:NOMINATION and 89:NOMINATION.

If you see a prefixed column where you expected a plain one, it means a second prompt with that name exists on that record type. Checking the Data Center for a deactivated duplicate is usually the quickest way to work out which is which.

Deactivated prompts

Deactivating a prompt in IMOS does not erase the values already recorded against it. The history object keeps deactivated prompts as columns so past values stay readable, while the live REPORTING object reflects your current configuration.

Adding, renaming and removing prompts

You do not raise a request with Veson to add a UDF. Prompts are yours to create in IMOS, and Data Lake picks them up on its own.

UDF objects refresh on the reporting cadence, which is a 30-minute cycle. On environments where transactional data is delivered on a faster chain, the UDF objects still follow the 30-minute reporting cycle rather than the faster one.

Practical consequence

A prompt added in IMOS a few minutes ago will not be in Snowflake yet. Allow a full cycle before treating an absent column as a fault.

Why row counts differ

The most common question about UDFs is why the history object returns more rows than the live one. This is expected, and it follows directly from the two objects answering different questions.

  IMOS source
  ───────────────────────────────────────────────────
   cabv              userdata            userfield
   (records)         (values)            (prompt names)
      │                  │                    │
      └──────────────────┴────────────────────┘
                         │
          ┌──────────────┴───────────────┐
          │                              │
     INNER JOIN                     LEFT JOIN
  "must have a value"           "keep every record"
          ▼                              ▼
  ┌───────────────────────┐   ┌────────────────────────────┐
  │ REPORTING.            │   │ REPORTING_HIST.            │
  │ AddressBookUser...    │   │ hist_AddressBookUser...    │
  │                       │   │                            │
  │        9,879 rows     │   │        10,256 rows         │
  └───────────────────────┘   └────────────────────────────┘
   records that have             every record, values or not
   a value                        = 9,879 with values
                                  +   377 with none yet

Example figures from a production Address Book. The difference is not missing or duplicated data: it is exactly the number of address book entries that currently carry no UDF value at all.

REPORTING uses an inner join. A record with no UDF values has nothing to show, so it does not appear. REPORTING_HIST uses a left join, so every record gets a row whether it has values or not.

That asymmetry is deliberate. History has to be able to record a field going from populated to empty. If empty records were dropped, clearing a value would look identical to deleting the record, and the change you are watching for would be invisible.

  Time ──────────────────────────────────────────────►
  Record "ACME SHIPPING, GB"
    rev 1   TRN No = "GB123456"      ← recorded
    rev 2   TRN No = (cleared)       ← still recorded, because
                                       empty rows are kept

Rule of thumb

Do not expect REPORTING and REPORTING_HIST row counts to match. By design they will not. Compare each against the source record count instead, as shown below.

A comparison that does mean something

Read both sides in a single statement. Environments ingest every 30 minutes, so counts taken as separate queries can be read moments apart and drift by a row or two on their own.

-- Substitute your own database name for <DB>
SELECT
    base_rows,
    base_without_udf,
    reporting_rows,
    reporting_rows - (base_rows - base_without_udf) AS residual
FROM (
  SELECT
    (SELECT COUNT(*) FROM <DB>.TRANSACTIONAL."cabv") AS base_rows,
    (SELECT COUNT(*) FROM <DB>.TRANSACTIONAL."cabv" b
      WHERE NOT EXISTS (SELECT 1 FROM <DB>.TRANSACTIONAL."userdata" u
                         WHERE u."objID"   = b."_sqlid"
                           AND u."objType" = 'cabv')) AS base_without_udf,
    (SELECT COUNT(*) FROM <DB>.REPORTING."AddressBookUserDefinedFields") AS reporting_rows
);

Expect residual = 0. That is the check that matters.

A residual of zero confirms the live object holds every record that has at least one value. The base_without_udf figure is the legitimate gap between the live object and history, and it is the 377 in the diagram above.

Querying UDFs

Current values

Join the UDF object back to the main reporting object on the business key.

SELECT
    ab."ShortName",
    ab."FullName",
    udf."TRN No",
    udf."Credit Terms"
FROM REPORTING."AddressBook" ab
JOIN REPORTING."AddressBookUserDefinedFields" udf
  ON udf."ShortName" = ab."ShortName"
 AND udf."Type"      = ab."Type";

The latest state, including records with no values

Take the highest revision per record from the history object. This is also how you pick up records that have never had a UDF value.

SELECT *
FROM REPORTING_HIST."hist_AddressBookUserDefinedFields"
QUALIFY ROW_NUMBER() OVER (PARTITION BY "_ID"
                            ORDER BY "_REVISION" DESC) = 1;

What changed, and when

Every revision is a full row, not a delta: when one prompt value changes, the whole row is recorded as it stood at that moment. Comparing consecutive revisions therefore tells you exactly which prompt moved.

-- Revisions for one address book entry, newest first
SELECT "_REVISION", "TRN No", "Credit Terms"
FROM REPORTING_HIST."hist_AddressBookUserDefinedFields"
WHERE "_ID":"ShortName"::VARCHAR = 'ACME SHIPPING'
  AND "_ID":"Type"::VARCHAR      = 'V'
ORDER BY "_REVISION" DESC;

A cleared value appears as an empty revision, not as a missing row.

History columns

Alongside your prompt columns, every history object carries a small set of control columns.

Current limitations

Three behaviors are worth knowing before you build change detection on the history objects. All three affect history only. The live REPORTING objects are unaffected.

Deleted records are not yet marked inactive

When a record is deleted in IMOS, its last revision stays in the history object still flagged "_ACTIVE" = 1. In practice a WHERE "_ACTIVE" = 1 filter currently excludes nothing, and history counts drift gradually upward as records are deleted over time. Volumes are small: a recent production measurement found single figures to low tens of retained rows per object. A fix is in progress and the existing rows will be corrected when it lands.

Renaming a business key creates a new identity

Because records are identified by their business key, renaming that key in IMOS, for example changing an Address Book short name, produces a new "_ID" starting again at revision 1. The revision trail under the old key is left in place rather than carried across. Treat a business key rename as a new record for change-detection purposes.

Configured default values are not exposed

Where a prompt is configured with a default value in IMOS, the default itself is not published to Data Lake. Values actually recorded against a record are published as normal. Exposing configured defaults is a recognized request and is not yet scheduled.

Requesting a change