Company Inventory

-- models/company_inventory.sql
{{ config(materialized='table') }}

SELECT
CAST(@rownum := @rownum + 1 AS UNSIGNED) AS prim_key,
t.*
FROM (
SELECT di.variant_id
, di.sku, di.sku_short
, di.item_name
, sum(coalesce(on_hand, 0)) as on_hand
, min(coalesce(on_sales_order, 0)) as on_sales_order, sum(coalesce(on_hand,0)) - min(coalesce(on_sales_order,0)) as available_to_sell
from inventory di
group by di.variant_id, di.sku, di.sku_short, di.item_name) t,
(SELECT @rownum := 0) r

# models/schema.yml
version: 2

models:
  - name: company_inventory
    columns:
      - name: prim_key
        tests:
          - unique
          - not_null
      - name: sku
      - name: item_name
      - name: variant_id

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
CAST(@rownum := @rownum + 1 AS UNSIGNED) AS prim_key,
t.*
FROM (
SELECT di.variant_id
, di.sku, di.sku_short
, di.item_name
, sum(coalesce(on_hand, 0)) as on_hand
, min(coalesce(on_sales_order, 0)) as on_sales_order, sum(coalesce(on_hand,0)) - min(coalesce(on_sales_order,0)) as available_to_sell
from inventory di
group by di.variant_id, di.sku, di.sku_short, di.item_name) t,
(SELECT @rownum := 0) r

view: company_inventory {
  derived_table: {
    sql: SELECT
CAST(@rownum := @rownum + 1 AS UNSIGNED) AS prim_key,
t.*
FROM (
SELECT di.variant_id
, di.sku, di.sku_short
, di.item_name
, sum(coalesce(on_hand, 0)) as on_hand
, min(coalesce(on_sales_order, 0)) as on_sales_order, sum(coalesce(on_hand,0)) - min(coalesce(on_sales_order,0)) as available_to_sell
from inventory di
group by di.variant_id, di.sku, di.sku_short, di.item_name) t,
(SELECT @rownum := 0) r ;;
    persist_for: "12 hours"
  }

  dimension: prim_key {
    type: int
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.prim_key ;;
  }

  dimension: sku {
    label: "SKU"
    sql: ${TABLE}.sku ;;
  }

  dimension: item_name {
    sql: ${TABLE}.item_name ;;
  }

  dimension: variant_id {
    type: int
    hidden: yes
    sql: ${TABLE}.variant_id ;;
  }

  measure: on_hand {
    type: int
    value_format: "#,##0"
    sql: ${TABLE}.on_hand ;;
  }

  measure: on_sales_order {
    type: int
    value_format: "#,##0"
    sql: ${TABLE}.on_sales_order ;;
  }

  measure: available_to_sell {
    type: int
    value_format: "#,##0"
    sql: ${TABLE}.available_to_sell ;;
  }

  measure: on_hand_materials_cost {
    type: number
    sql: ${on_hand} * ${variant_cost.materials_cost} ;;
  }
}

- view: company_inventory
  derived_table:
    sql: SELECT
CAST(@rownum := @rownum + 1 AS UNSIGNED) AS prim_key,
t.*
FROM (
SELECT di.variant_id
, di.sku, di.sku_short
, di.item_name
, sum(coalesce(on_hand, 0)) as on_hand
, min(coalesce(on_sales_order, 0)) as on_sales_order, sum(coalesce(on_hand,0)) - min(coalesce(on_sales_order,0)) as available_to_sell
from inventory di
group by di.variant_id, di.sku, di.sku_short, di.item_name) t,
(SELECT @rownum := 0) r
    persist_for: 12 hours
  fields:
  - dimension: prim_key
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.prim_key
  - dimension: sku
    label: SKU
    sql: ${TABLE}.sku
  - dimension: item_name
    sql: ${TABLE}.item_name
  - dimension: variant_id
    type: int
    hidden: true
    sql: ${TABLE}.variant_id
  - measure: on_hand
    type: int
    value_format: '#,##0'
    sql: ${TABLE}.on_hand
  - measure: on_sales_order
    type: int
    value_format: '#,##0'
    sql: ${TABLE}.on_sales_order
  - measure: available_to_sell
    type: int
    value_format: '#,##0'
    sql: ${TABLE}.available_to_sell
  - measure: on_hand_materials_cost
    type: number
    sql: ${on_hand} * ${variant_cost.materials_cost}