pricing

-- ============================================================
-- Table  : pricing — The item price matrix: one row per item, price level, currency, quantity break and sale unit, carrying the unit price. This is where a price actually lives.
-- Purpose: Column-selected read of pricing — auto-generated from field metadata
-- Grain  : One row per internalid + item + pricelevel + currency + quantity + saleunit
-- Caution: the grain is item + pricelevel + currency + quantity + saleunit — joining item to this record without pinning a price level (and, in a OneWorld account, a currency) multiplies every item row by its entire price matrix.
-- 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.internalid AS "Internal ID",
  t.item AS "Item (internal id)",
  t.pricelevel AS "Price level (internal id) — pin this or the matrix multiplies",
  t.currency AS "Currency (internal id)",
  t.quantity AS "Quantity break this price applies from (internal id)",
  t.saleunit AS "Sale unit the price is stated in (internal id)",
  t.unitprice AS "Unit price",
  t.customer AS "Customer (internal id) — populated only on customer-specific price rows",
  t.assignedpricelevel AS "Price level was assigned rather than derived"  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
FROM <catalog>.<schema>.pricing t
WHERE
  1 = 1  -- no partition column on this record; the filters below are optional
  -- AND t.item = <item>
  -- no lastmodifieddate on this record — there is no watermark column, so incremental extraction must full-refresh it (see quirks #deletes)
ORDER BY t.internalid;