Perm Name Facts

-- models/perm_name_facts.sql
{{ config(materialized='view') }}

SELECT
        perm_name,
        start_date AS max_start_date,
        allocation AS current_allocation
      FROM (
        SELECT
          perm_name,
          DATE(start_date) AS start_date,
          allocation,
          ROW_NUMBER() OVER (PARTITION BY perm_name ORDER BY DATE(start_date) DESC) as rn
        FROM public.ts_inventory
      ) as subquery
      WHERE rn = 1

# models/schema.yml
version: 2

models:
  - name: perm_name_facts
    columns:
      - name: perm_name
      - name: max_start_date
      - name: current_allocation

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
        perm_name,
        start_date AS max_start_date,
        allocation AS current_allocation
      FROM (
        SELECT
          perm_name,
          DATE(start_date) AS start_date,
          allocation,
          ROW_NUMBER() OVER (PARTITION BY perm_name ORDER BY DATE(start_date) DESC) as rn
        FROM public.ts_inventory
      ) as subquery
      WHERE rn = 1

view: perm_name_facts {
  derived_table: {
    sql: SELECT
        perm_name,
        start_date AS max_start_date,
        allocation AS current_allocation
      FROM (
        SELECT
          perm_name,
          DATE(start_date) AS start_date,
          allocation,
          ROW_NUMBER() OVER (PARTITION BY perm_name ORDER BY DATE(start_date) DESC) as rn
        FROM public.ts_inventory
      ) as subquery
      WHERE rn = 1 ;;
  }

  dimension: perm_name {
    type: string
    sql: ${TABLE}.perm_name ;;
  }

  dimension: max_start_date {
    type: date
    sql: ${TABLE}.max_start_date ;;
  }

  dimension: current_allocation {
    type: number
    sql: ${TABLE}.current_allocation ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
  set: detail {
    fields: [perm_name, max_start_date, current_allocation]
  }
}

- view: perm_name_facts
  derived_table:
    sql: SELECT
        perm_name,
        start_date AS max_start_date,
        allocation AS current_allocation
      FROM (
        SELECT
          perm_name,
          DATE(start_date) AS start_date,
          allocation,
          ROW_NUMBER() OVER (PARTITION BY perm_name ORDER BY DATE(start_date) DESC) as rn
        FROM public.ts_inventory
      ) as subquery
      WHERE rn = 1
  fields:
  - dimension: perm_name
    type: string
    sql: ${TABLE}.perm_name
  - dimension: max_start_date
    type: date
    sql: ${TABLE}.max_start_date
  - dimension: current_allocation
    type: number
    sql: ${TABLE}.current_allocation
  - measure: count
    type: count
    drill_fields:
    - detail*