bomrevision

-- ============================================================
-- Table  : bomrevision — A dated version of a bill of materials: one row per revision, with the effective start and end dates that decide which component list is in force on a given day.
-- Purpose: Column-selected read of bomrevision — auto-generated from field metadata
-- Grain  : One row per id
-- Caution: no lastmodifieddate on this record — full-refresh the revisions. effectivestartdate and effectiveenddate are a validity WINDOW, so an as-of read is a BETWEEN against a chosen date, never a max() on the revision id.
-- 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.billofmaterials AS "BOM this revision belongs to (internal id)",
  t.name AS "Revision name",
  t.effectivestartdate AS "First day this revision is in force",
  t.effectiveenddate AS "Last day this revision is in force",
  t.memo AS "Memo",
  t.isinactive AS "Inactive",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.createddate AS "Date created",
  t.externalid AS "External ID"
FROM <catalog>.<schema>.bomrevision t
WHERE
  1 = 1  -- no partition column on this record; the filters below are optional
  -- AND t.id = <id>
  -- AND t.effectivestartdate >= DATE '<DATE_FROM>'
  -- AND t.effectivestartdate <= DATE '<DATE_TO>'
  -- no lastmodifieddate on this record — there is no watermark column, so incremental extraction must full-refresh it (see quirks #deletes)
ORDER BY t.id;