Shipment Companyery Exception Summary

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

Select
        distinct
        exception_entries.exceptionable_id as exceptionable_id
       ,exception_types.id as exception_type_id
       ,exception_types.code as exception_type_code
       ,exception_nodes.id as exception_node_id
       ,exception_nodes.code as exception_node_code
  from exception_entries
  left join exception_types
    on exception_entries.exception_type_id = exception_types.id
  left join exception_nodes
    on exception_types.exception_node_id = exception_nodes.id
  where exception_entries.exceptionable_type = "<% parameter placeholder_task_type %>"
Union
Select
       distinct
       order_id as exceptionable_id
      ,exception_type_id
      ,exception_type_code
      ,exception_node_id
      ,exception_node_code
 from {{ ref('grouped_exception_group') }} grouped_exception_group
  where exception_type_id is not null

# models/schema.yml
version: 2

models:
  - name: shipment_companyery_exception_summary
    columns:
      - name: exceptionable_id
        tests:
          - unique
          - not_null
      - name: exception_type_id
      - name: exception_type_code
      - name: exception_node_id
      - name: exception_node_code

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• 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
        distinct
        exception_entries.exceptionable_id as exceptionable_id
       ,exception_types.id as exception_type_id
       ,exception_types.code as exception_type_code
       ,exception_nodes.id as exception_node_id
       ,exception_nodes.code as exception_node_code
  from exception_entries
  left join exception_types
    on exception_entries.exception_type_id = exception_types.id
  left join exception_nodes
    on exception_types.exception_node_id = exception_nodes.id
  where exception_entries.exceptionable_type = "<% parameter placeholder_task_type %>"
Union
Select
       distinct
       order_id as exceptionable_id
      ,exception_type_id
      ,exception_type_code
      ,exception_node_id
      ,exception_node_code
 from ${grouped_exception_group.SQL_TABLE_NAME} grouped_exception_group
  where exception_type_id is not null

view: shipment_companyery_exception_summary {
  derived_table: {
    sql: Select
        distinct
        exception_entries.exceptionable_id as exceptionable_id
       ,exception_types.id as exception_type_id
       ,exception_types.code as exception_type_code
       ,exception_nodes.id as exception_node_id
       ,exception_nodes.code as exception_node_code
  from exception_entries
  left join exception_types
    on exception_entries.exception_type_id = exception_types.id
  left join exception_nodes
    on exception_types.exception_node_id = exception_nodes.id
  where exception_entries.exceptionable_type = "<% parameter placeholder_task_type %>"
Union
Select
       distinct
       order_id as exceptionable_id
      ,exception_type_id
      ,exception_type_code
      ,exception_node_id
      ,exception_node_code
 from ${grouped_exception_group.SQL_TABLE_NAME} grouped_exception_group
  where exception_type_id is not null ;;
  }

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

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

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

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

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

  measure: count {
    type: count
  }

  measure: distinct_order_count {
    type: count_distinct
    sql: ${TABLE}.exceptionable_id ;;
  }
}

- view: shipment_companyery_exception_summary
  derived_table:
    sql: Select
        distinct
        exception_entries.exceptionable_id as exceptionable_id
       ,exception_types.id as exception_type_id
       ,exception_types.code as exception_type_code
       ,exception_nodes.id as exception_node_id
       ,exception_nodes.code as exception_node_code
  from exception_entries
  left join exception_types
    on exception_entries.exception_type_id = exception_types.id
  left join exception_nodes
    on exception_types.exception_node_id = exception_nodes.id
  where exception_entries.exceptionable_type = "<% parameter placeholder_task_type %>"
Union
Select
       distinct
       order_id as exceptionable_id
      ,exception_type_id
      ,exception_type_code
      ,exception_node_id
      ,exception_node_code
 from ${grouped_exception_group.SQL_TABLE_NAME} grouped_exception_group
  where exception_type_id is not null
  fields:
  - dimension: exceptionable_id
    type: int
    primary_key: true
    sql: ${TABLE}.exceptionable_id
  - dimension: exception_type_id
    type: number
    sql: ${TABLE}.exception_type_id
  - dimension: exception_type_code
    type: string
    sql: ${TABLE}.exception_type_code
  - dimension: exception_node_id
    type: number
    sql: ${TABLE}.exception_node_id
  - dimension: exception_node_code
    type: string
    sql: ${TABLE}.exception_node_code
  - measure: count
    type: count
  - measure: distinct_order_count
    type: count_distinct
    sql: ${TABLE}.exceptionable_id