Discover fields

Find fields that are not curated columns yet, then query their values.

Use this path when a value lives in Analytics but does not appear as a column on reporting.shipments.

Nothing here requires a schema change from Jaantonio first. Newly observed fields show up as rows in the discovery views.

Step 1: list fields

reporting.cargowise_xml_fields is the catalog of field names and paths your organization has already observed.

SELECT
  label,
  semantic_path,
  inferred_type,
  total_observation_count,
  last_seen_at
FROM reporting.cargowise_xml_fields
WHERE label ILIKE '%origin%'
ORDER BY last_seen_at DESC;

Change the search text to whatever word your team uses for the field. semantic_path is the more precise identifier when labels repeat.

Step 2: query values

reporting.cargowise_xml_observations holds the values.

Always add a date window. This table is large by design.

SELECT
  label,
  semantic_path,
  original_text,
  numeric_value,
  boolean_value,
  timestamp_value,
  created_at,
  payload_id
FROM reporting.cargowise_xml_observations
WHERE label ILIKE '%Operations%'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 100;

Or pin an exact path once you know it:

SELECT
  original_text,
  created_at,
  payload_id
FROM reporting.cargowise_xml_observations
WHERE semantic_path = 'the/exact/path/from/xml_fields'
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC;

How to read a row

Column	Meaning
label	Short leaf name
semantic_path	Stable path used for discovery and joins
original_text	Value as text
numeric_value / boolean_value / timestamp_value	Typed forms when Analytics could infer them
payload_id	Link back to payload metadata
created_at	When the observation was stored

Recommended team workflow

1. Someone asks for a missing field

   A report owner names the concept they need that is not on shipments.

2. Search the catalog

   Run the cargowise_xml_fields query, then a bounded observations query.

3. Use or promote

   If the field is useful once, keep using discovery queries or a filtered Power BI detail page. If it is needed every week, ask Jaantonio about promoting it into a curated table after confirming the CargoWise Analytics feed actually sends it.

Power BI tip

Load cargowise_xml_fields for browsing names. Keep observations behind filters for date range and path or label. Do not drop the full observations table onto an overview page.

If the field never appears

Then Analytics has not observed that field for your organization yet. Common reasons:

• the Analytics outbound feed is not sending that element on the current message type
• the feed exists but is not configured for your organization
• the concept needs a different document family (for example posted transactions for Post Date or revenue)

In those cases discovery stays empty until the source data includes the field.