item

-- ============================================================
-- Table  : item — The unified item master: one row for every item type — inventory, non-inventory, service, kit, assembly — discriminated by itemtype and subtype, carrying costing, pricing, planning parameters and global quantity rollups.
-- Purpose: Column-selected read of item — auto-generated from field metadata
-- Grain  : One row per id
-- Caution: `location` on the item record is a default assignment, not a balance grain — per-location on hand, cost and planning parameters live on inventoryitemlocations, and no location anchor is emitted here. The quantity columns on this record are global rollups across every location.
-- 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.id AS "Internal ID",
  t.itemid AS "Item name/number",
  t.fullname AS "Full hierarchical name",
  t.displayname AS "Display name/code",
  t.itemtype AS "Item type discriminator (internal id)",
  t.subtype AS "Item subtype (internal id)",
  t.isinactive AS "Inactive",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.createddate AS "Date created",
  t.lastmodifieddate AS "Last modified — the incremental watermark",
  t.parent AS "Parent item (internal id)",
  t.subsidiary AS "Subsidiaries — MULTISELECT; the join spine is itemsubsidiarymap",
  t.location AS "Default location (internal id) — not the per-location balance grain",
  t.vendor AS "Preferred vendor (internal id)",
  t.unitstype AS "Units-of-measure family (internal id)",
  t.stockunit AS "Stocking unit (internal id)",
  t.costingmethod AS "Costing method (internal id) — Average, FIFO, LIFO or Standard",
  t.description AS "Sales description",
  t.externalid AS "External ID"
FROM <catalog>.<schema>.item t
WHERE
  1 = 1  -- no partition column on this record; the filters below are optional
  -- AND t.id = <id>
  -- AND t.lastmodifieddate >= TIMESTAMP '<watermark>'  -- inserts and updates only — deletes never appear here; diff deletedrecord, which is itself blind to line-level deletes (see quirks #deletes)
ORDER BY t.id;