Events

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

select
  a.requesthash as id,
  a.datecreated as event_date,
  b.value as session_id,
  c.value as user_id,
  d.value as namespace,
  e.value as environment,
  f.value as client_id,
  g.value as category_id,
  h.value as event
from
  headerdata as a
left join
  objectdata as b
    on a.requesthash = b.requesthash
    and b.key = 'post.sessionIdentifier'
left join
  objectdata as c
    on a.requesthash = c.requesthash
    and c.key = 'post.userId'
left join
  objectdata as d
    on a.requesthash = d.requesthash
    and d.key = 'post.namespace'
left join
  objectdata as e
    on a.requesthash = e.requesthash
    and e.key = 'post.environment'
left join
  objectdata as f
    on a.requesthash = f.requesthash
    and f.key = 'post.clientId'
left join
  objectdata as g
    on a.requesthash = g.requesthash
    and g.key = 'post.categoryId'
left join
  objectdata as h
    on a.requesthash = h.requesthash
    and h.key = 'post.event'
where
  a.datecreated between getdate() - 6 and getdate() - 5

# models/schema.yml
version: 2

models:
  - name: events
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: session_id
      - name: user_id
      - name: namespace
      - name: environment
      - name: client_id
      - name: category_id
      - name: event

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
  a.requesthash as id,
  a.datecreated as event_date,
  b.value as session_id,
  c.value as user_id,
  d.value as namespace,
  e.value as environment,
  f.value as client_id,
  g.value as category_id,
  h.value as event
from
  headerdata as a
left join
  objectdata as b
    on a.requesthash = b.requesthash
    and b.key = 'post.sessionIdentifier'
left join
  objectdata as c
    on a.requesthash = c.requesthash
    and c.key = 'post.userId'
left join
  objectdata as d
    on a.requesthash = d.requesthash
    and d.key = 'post.namespace'
left join
  objectdata as e
    on a.requesthash = e.requesthash
    and e.key = 'post.environment'
left join
  objectdata as f
    on a.requesthash = f.requesthash
    and f.key = 'post.clientId'
left join
  objectdata as g
    on a.requesthash = g.requesthash
    and g.key = 'post.categoryId'
left join
  objectdata as h
    on a.requesthash = h.requesthash
    and h.key = 'post.event'
where
  a.datecreated between getdate() - 6 and getdate() - 5

view: events {
  derived_table: {
    sql: select
  a.requesthash as id,
  a.datecreated as event_date,
  b.value as session_id,
  c.value as user_id,
  d.value as namespace,
  e.value as environment,
  f.value as client_id,
  g.value as category_id,
  h.value as event
from
  headerdata as a
left join
  objectdata as b
    on a.requesthash = b.requesthash
    and b.key = 'post.sessionIdentifier'
left join
  objectdata as c
    on a.requesthash = c.requesthash
    and c.key = 'post.userId'
left join
  objectdata as d
    on a.requesthash = d.requesthash
    and d.key = 'post.namespace'
left join
  objectdata as e
    on a.requesthash = e.requesthash
    and e.key = 'post.environment'
left join
  objectdata as f
    on a.requesthash = f.requesthash
    and f.key = 'post.clientId'
left join
  objectdata as g
    on a.requesthash = g.requesthash
    and g.key = 'post.categoryId'
left join
  objectdata as h
    on a.requesthash = h.requesthash
    and h.key = 'post.event'
where
  a.datecreated between getdate() - 6 and getdate() - 5 ;;
  }

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

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

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

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

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

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

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

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

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

  measure: count {
    type: count
  }

  measure: session_cnt {
    type: count_distinct
    sql: ${session_id} ;;
  }

  measure: user_cnt {
    type: count_distinct
    sql: ${user_id} ;;
  }
}

- view: events
  derived_table:
    sql: select
  a.requesthash as id,
  a.datecreated as event_date,
  b.value as session_id,
  c.value as user_id,
  d.value as namespace,
  e.value as environment,
  f.value as client_id,
  g.value as category_id,
  h.value as event
from
  headerdata as a
left join
  objectdata as b
    on a.requesthash = b.requesthash
    and b.key = 'post.sessionIdentifier'
left join
  objectdata as c
    on a.requesthash = c.requesthash
    and c.key = 'post.userId'
left join
  objectdata as d
    on a.requesthash = d.requesthash
    and d.key = 'post.namespace'
left join
  objectdata as e
    on a.requesthash = e.requesthash
    and e.key = 'post.environment'
left join
  objectdata as f
    on a.requesthash = f.requesthash
    and f.key = 'post.clientId'
left join
  objectdata as g
    on a.requesthash = g.requesthash
    and g.key = 'post.categoryId'
left join
  objectdata as h
    on a.requesthash = h.requesthash
    and h.key = 'post.event'
where
  a.datecreated between getdate() - 6 and getdate() - 5
  fields:
  - dimension: id
    primary_key: true
    hidden: true
    sql: ${TABLE}.id
  - dimension: session_id
    sql: ${TABLE}.session_id
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: namespace
    sql: ${TABLE}.namespace
  - dimension: environment
    sql: ${TABLE}.environment
  - dimension: client_id
    sql: ${TABLE}.client_id
  - dimension: category_id
    sql: ${TABLE}.category_id
  - dimension: event
    sql: ${TABLE}.event
  - dimension_group: event
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.event_date
  - measure: count
    type: count
  - measure: session_cnt
    type: count_distinct
    sql: ${session_id}
  - measure: user_cnt
    type: count_distinct
    sql: ${user_id}