Event Registry Derived

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

SELECT
  id as id,
  platform as platform,
  os as os,
  category as category,
  action as action,
  label as label,
  sub_label as sub_label,
  sub_sub_label as sub_sub_label,
  value as value,
  event_id as event_id,
  user_id as user_id,
  created_at as created_at,
  updated_at as updated_at,
  fallback_analytics_token as fallback_analytics_token
FROM analytics
WHERE action = "screen" and (label = "RegistryHome" OR label = "Registry" OR label = "registries" OR label = "ViewRegistryWeb-Guest" OR label = "ViewRegistryWeb-Admin") OR (action = "click" and (label = "go_to_registry" and sub_label = "guest"))

# models/schema.yml
version: 2

models:
  - name: event_registry_derived
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: action
      - name: category
      - name: event_id
      - name: label
      - name: os
      - name: platform
      - name: sub_label
      - name: sub_sub_label
      - name: fallback_analytics_token
      - name: user_id
      - name: registry_home_viewed
      - name: registry_clicked

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
  id as id,
  platform as platform,
  os as os,
  category as category,
  action as action,
  label as label,
  sub_label as sub_label,
  sub_sub_label as sub_sub_label,
  value as value,
  event_id as event_id,
  user_id as user_id,
  created_at as created_at,
  updated_at as updated_at,
  fallback_analytics_token as fallback_analytics_token
FROM analytics
WHERE action = "screen" and (label = "RegistryHome" OR label = "Registry" OR label = "registries" OR label = "ViewRegistryWeb-Guest" OR label = "ViewRegistryWeb-Admin") OR (action = "click" and (label = "go_to_registry" and sub_label = "guest"))

view: event_registry_derived {
  derived_table: {
    sql: SELECT
  id as id,
  platform as platform,
  os as os,
  category as category,
  action as action,
  label as label,
  sub_label as sub_label,
  sub_sub_label as sub_sub_label,
  value as value,
  event_id as event_id,
  user_id as user_id,
  created_at as created_at,
  updated_at as updated_at,
  fallback_analytics_token as fallback_analytics_token
FROM analytics
WHERE action = "screen" and (label = "RegistryHome" OR label = "Registry" OR label = "registries" OR label = "ViewRegistryWeb-Guest" OR label = "ViewRegistryWeb-Admin") OR (action = "click" and (label = "go_to_registry" and sub_label = "guest")) ;;
    persist_for: "6 hours"
  }

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

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

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

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

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

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

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

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

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

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

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

  dimension: registry_home_viewed {
    type: int
    sql: ${TABLE}.label IN ("RegistryHome", "Registry", "registries") ;;
  }

  dimension: registry_clicked {
    type: int
    sql: ${TABLE}.label IN ("ViewRegistryWeb-Guest", "go_to_registry") ;;
  }

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

  dimension_group: updated {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.updated_at ;;
  }

  measure: count {
    type: count
  }

  measure: registry_home_viewed_count {
    type: sum
    sql: ${registry_home_viewed} ;;
  }

  measure: registry_clicked_count {
    type: sum
    sql: ${registry_clicked} ;;
  }
}

- view: event_registry_derived
  derived_table:
    sql: SELECT
  id as id,
  platform as platform,
  os as os,
  category as category,
  action as action,
  label as label,
  sub_label as sub_label,
  sub_sub_label as sub_sub_label,
  value as value,
  event_id as event_id,
  user_id as user_id,
  created_at as created_at,
  updated_at as updated_at,
  fallback_analytics_token as fallback_analytics_token
FROM analytics
WHERE action = "screen" and (label = "RegistryHome" OR label = "Registry" OR label = "registries" OR label = "ViewRegistryWeb-Guest" OR label = "ViewRegistryWeb-Admin") OR (action = "click" and (label = "go_to_registry" and sub_label = "guest"))
    persist_for: 6 hours
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: action
    sql: ${TABLE}.action
  - dimension: category
    sql: ${TABLE}.category
  - dimension: event_id
    type: int
    hidden: true
    sql: ${TABLE}.event_id
  - dimension: label
    sql: ${TABLE}.label
  - dimension: os
    sql: ${TABLE}.os
  - dimension: platform
    sql: ${TABLE}.platform
  - dimension: sub_label
    sql: ${TABLE}.sub_label
  - dimension: sub_sub_label
    sql: ${TABLE}.sub_sub_label
  - dimension: fallback_analytics_token
    sql: ${TABLE}.fallback_analytics_token
  - dimension: user_id
    type: int
    hidden: true
    sql: ${TABLE}.user_id
  - dimension: registry_home_viewed
    type: int
    sql: ${TABLE}.label IN ("RegistryHome", "Registry", "registries")
  - dimension: registry_clicked
    type: int
    sql: ${TABLE}.label IN ("ViewRegistryWeb-Guest", "go_to_registry")
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - hour
    sql: ${TABLE}.created_at
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.updated_at
  - measure: count
    type: count
  - measure: registry_home_viewed_count
    type: sum
    sql: ${registry_home_viewed}
  - measure: registry_clicked_count
    type: sum
    sql: ${registry_clicked}