systemnote2

-- ============================================================
-- Table  : systemnote2 — The second-generation change log: the same field-level history as systemnote, but with the old and new values split into typed columns — string, numeric, date, boolean and reference — beside a UTC change timestamp.
-- Purpose: Column-selected read of systemnote2 — auto-generated from field metadata
-- Grain  : One row per record — see the Fields section for the full key
-- Caution: the value columns are MUTUALLY EXCLUSIVE per row: valuedatatype names which typed pair is populated and the others are null, so a change history reads as a COALESCE over the typed columns, never as one column.
-- Notes  : Auto-generated skeleton for NetSuite data landed in your lakehouse from a SuiteAnalytics Connect (or SuiteQL) extract — it never addresses live NetSuite. Identifiers are lowercase as NetSuite2.com renders them. Select-type columns hold numeric internal ids: BUILTIN.DF() display resolution exists only at extraction time, so decode ids by joining the landed list records; see quirks #display-values. Check-box columns arrive as 'T'/'F' strings; see quirks #tf-booleans.
-- ============================================================
SELECT
  t.objectid AS "Internal id of the object that changed — repeats once per logged change",
  t.instancename AS "Object name as displayed",
  t.objectelementname AS "Element that changed, by name",
  t.objectactiontype AS "Action performed on the object (internal id)",
  t.valuedatatype AS "Which typed value pair below is populated (internal id) — the others are null",
  t.valueoldstring AS "Old value, string form",
  t.valuenewstring AS "New value, string form",
  t.valueolddisplayname AS "Old value as the UI displayed it",
  t.valuenewdisplayname AS "New value as the UI displayed it",
  t.valueoldnumber AS "Old value, numeric form — no casting needed, unlike systemnote",
  t.valuenewnumber AS "New value, numeric form",
  t.valueolddate AS "Old value, date form",
  t.valuenewdate AS "New value, date form",
  t.valueoldboolean AS "Old value, boolean form",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.valuenewboolean AS "New value, boolean form",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.actor AS "Who made the change (internal id)",
  t.context AS "Context the change came from (internal id) — UI, CSV, script",
  t.timestamputc AS "When the change was made, in UTC"
FROM <catalog>.<schema>.systemnote2 t
WHERE
  1 = 1  -- no partition column on this record; the filters below are optional
  -- AND t.timestamputc >= TIMESTAMP '<DATE_FROM>'
  -- AND t.timestamputc <= TIMESTAMP '<DATE_TO>'
  -- no lastmodifieddate on this record — there is no watermark column, so incremental extraction must full-refresh it (see quirks #deletes)
;