Tickets Combined

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

SELECT
  i.id * 1000000 AS id
  , lower(i_type.name) AS ticket_type
  , 'external_system' AS ticket_source
  , lower(i_priority.name) AS priority
  , i.created AS created_at
  , i.resolution_date AS closed_at
  , lower(i_status_category.name) AS status
  , i.summary AS subject
  , i.self AS url
  , i_user.email_address AS assignee_email
FROM external_system._issue AS i
LEFT JOIN external_system._issue_type AS i_type ON i_type.id = i.issuetype
LEFT JOIN external_system._status AS i_status ON i_status.id = i.status = i_status.id
LEFT JOIN external_system._status_category AS i_status_category ON i_status.status_category = i_status_category.id
LEFT JOIN external_system._priority AS i_priority ON i_priority.id = i.priority
LEFT JOIN external_system._user AS i_user ON i_user.key = i.assignee

UNION ALL

SELECT
  t.id AS id
  , lower(t.type) AS ticket_type
  , 'external_system' AS ticket_source
  , lower(t.priority) AS priority
  , t.created_at AS created_at
  , CASE WHEN t.status = 'closed' THEN t.updated_at ELSE NULL END AS closed_at
  , lower(t.status) AS status
  , t.subject AS subject
  , t.url AS url
  , u.email AS assignee_email
FROM external_system._tickets AS t
LEFT JOIN external_system._users AS u ON u.id = t.assignee_id

# models/schema.yml
version: 2

models:
  - name: tickets_combined
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: ticket_type
      - name: ticket_source
      - name: priority
      - name: combined_priority
      - name: closed_at
      - name: hours_created_to_closed
      - name: status
      - name: combined_status
      - name: subject
      - name: url
      - name: assignee_email

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
  i.id * 1000000 AS id
  , lower(i_type.name) AS ticket_type
  , 'external_system' AS ticket_source
  , lower(i_priority.name) AS priority
  , i.created AS created_at
  , i.resolution_date AS closed_at
  , lower(i_status_category.name) AS status
  , i.summary AS subject
  , i.self AS url
  , i_user.email_address AS assignee_email
FROM external_system._issue AS i
LEFT JOIN external_system._issue_type AS i_type ON i_type.id = i.issuetype
LEFT JOIN external_system._status AS i_status ON i_status.id = i.status = i_status.id
LEFT JOIN external_system._status_category AS i_status_category ON i_status.status_category = i_status_category.id
LEFT JOIN external_system._priority AS i_priority ON i_priority.id = i.priority
LEFT JOIN external_system._user AS i_user ON i_user.key = i.assignee

UNION ALL

SELECT
  t.id AS id
  , lower(t.type) AS ticket_type
  , 'external_system' AS ticket_source
  , lower(t.priority) AS priority
  , t.created_at AS created_at
  , CASE WHEN t.status = 'closed' THEN t.updated_at ELSE NULL END AS closed_at
  , lower(t.status) AS status
  , t.subject AS subject
  , t.url AS url
  , u.email AS assignee_email
FROM external_system._tickets AS t
LEFT JOIN external_system._users AS u ON u.id = t.assignee_id

view: tickets_combined {
  derived_table: {
    sql: SELECT
  i.id * 1000000 AS id
  , lower(i_type.name) AS ticket_type
  , 'external_system' AS ticket_source
  , lower(i_priority.name) AS priority
  , i.created AS created_at
  , i.resolution_date AS closed_at
  , lower(i_status_category.name) AS status
  , i.summary AS subject
  , i.self AS url
  , i_user.email_address AS assignee_email
FROM external_system._issue AS i
LEFT JOIN external_system._issue_type AS i_type ON i_type.id = i.issuetype
LEFT JOIN external_system._status AS i_status ON i_status.id = i.status = i_status.id
LEFT JOIN external_system._status_category AS i_status_category ON i_status.status_category = i_status_category.id
LEFT JOIN external_system._priority AS i_priority ON i_priority.id = i.priority
LEFT JOIN external_system._user AS i_user ON i_user.key = i.assignee

UNION ALL

SELECT
  t.id AS id
  , lower(t.type) AS ticket_type
  , 'external_system' AS ticket_source
  , lower(t.priority) AS priority
  , t.created_at AS created_at
  , CASE WHEN t.status = 'closed' THEN t.updated_at ELSE NULL END AS closed_at
  , lower(t.status) AS status
  , t.subject AS subject
  , t.url AS url
  , u.email AS assignee_email
FROM external_system._tickets AS t
LEFT JOIN external_system._users AS u ON u.id = t.assignee_id ;;
  }

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

  dimension: ticket_type {
    sql: lower(${TABLE}.ticket_type) ;;
  }

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

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

  dimension: combined_priority {
  }

  dimension: closed_at {
    type: time
    sql: ${TABLE}.closed_at ;;
  }

  dimension: hours_created_to_closed {
    type: number
    sql: DATEDIFF(HOURS,${TABLE}.created_at,NULLIF(${TABLE}.closed_at,0)) ;;
  }

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

  dimension: combined_status {
  }

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

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

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

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month, hour, hour_of_day, day_of_week]
    sql: ${TABLE}.created_at ;;
  }

  measure: count {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${id} ;;
  }

  measure: average_creation_to_close_time {
    type: average
    sql: ${hours_created_to_closed} ;;
  }
}

- view: tickets_combined
  derived_table:
    sql: SELECT
  i.id * 1000000 AS id
  , lower(i_type.name) AS ticket_type
  , 'external_system' AS ticket_source
  , lower(i_priority.name) AS priority
  , i.created AS created_at
  , i.resolution_date AS closed_at
  , lower(i_status_category.name) AS status
  , i.summary AS subject
  , i.self AS url
  , i_user.email_address AS assignee_email
FROM external_system._issue AS i
LEFT JOIN external_system._issue_type AS i_type ON i_type.id = i.issuetype
LEFT JOIN external_system._status AS i_status ON i_status.id = i.status = i_status.id
LEFT JOIN external_system._status_category AS i_status_category ON i_status.status_category = i_status_category.id
LEFT JOIN external_system._priority AS i_priority ON i_priority.id = i.priority
LEFT JOIN external_system._user AS i_user ON i_user.key = i.assignee

UNION ALL

SELECT
  t.id AS id
  , lower(t.type) AS ticket_type
  , 'external_system' AS ticket_source
  , lower(t.priority) AS priority
  , t.created_at AS created_at
  , CASE WHEN t.status = 'closed' THEN t.updated_at ELSE NULL END AS closed_at
  , lower(t.status) AS status
  , t.subject AS subject
  , t.url AS url
  , u.email AS assignee_email
FROM external_system._tickets AS t
LEFT JOIN external_system._users AS u ON u.id = t.assignee_id
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: ticket_type
    sql: lower(${TABLE}.ticket_type)
  - dimension: ticket_source
    sql: ${TABLE}.ticket_source
  - dimension: priority
    sql: ${TABLE}.priority
  - dimension: combined_priority
  - dimension: closed_at
    type: time
    sql: ${TABLE}.closed_at
  - dimension: hours_created_to_closed
    type: number
    sql: DATEDIFF(HOURS,${TABLE}.created_at,NULLIF(${TABLE}.closed_at,0))
  - dimension: status
    sql: ${TABLE}.status
  - dimension: combined_status
  - dimension: subject
    sql: ${TABLE}.subject
  - dimension: url
    sql: ${TABLE}.url
  - dimension: assignee_email
    sql: ${TABLE}.assignee_email
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - hour
    - hour_of_day
    - day_of_week
    sql: ${TABLE}.created_at
  - measure: count
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${id}
  - measure: average_creation_to_close_time
    type: average
    sql: ${hours_created_to_closed}