Query curated data

​

Start with shipments

• job_number — the CargoWise job number people recognize in operations
• shipment_key — join key for history tables
• transport_mode, origin_code, destination_code
• total_weight, total_volume, goods_description
• first_seen_at, last_seen_at, updated_at

SELECT
  job_number,
  transport_mode,
  origin_code,
  destination_code,
  total_weight,
  total_volume
FROM reporting.shipments
WHERE transport_mode = 'AIR'
ORDER BY job_number;

​

Related curated views

SELECT
  shipments.job_number,
  count(changes.id) AS accepted_version_changes,
  max(changes.changed_at) AS latest_change_at
FROM reporting.shipments AS shipments
LEFT JOIN reporting.shipment_changes AS changes
  ON changes.shipment_key = shipments.shipment_key
GROUP BY shipments.shipment_key, shipments.job_number
ORDER BY latest_change_at DESC NULLS LAST;

​

Browse the contract in Lambda

​

When curated data is not enough

1. Search Discover fields.
2. If the field is present in observations and becomes a permanent report need, ask Jaantonio to evaluate promoting it into a curated table.
3. Some business concepts need a different upstream feed before they can appear as curated columns. Operations Rep, Post Date, and Revenue are examples of that today.

​

Practical guidance