Event Session Map

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

select a.anonymous_id
            , a.sent_at
            , a.id
            , a.event
            , s.session_id
            , a.user_id
            , row_number() over(partition by s.session_id order by a.sent_at) as event_sequence_number
          from ember.tracks as a
          inner join {{ ref('application_sessions') }} as s
          on a.user_id = s.user_id
            and a.sent_at >= s.session_start_at
            and (a.sent_at < s.next_session_start_at or s.next_session_start_at is null)

# models/schema.yml
version: 2

models:
  - name: event_session_map
    columns:
      - name: anonymous_id
      - name: id
      - name: event
      - name: session_id
      - name: user_id
      - name: event_sequence_number

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 a.anonymous_id
            , a.sent_at
            , a.id
            , a.event
            , s.session_id
            , a.user_id
            , row_number() over(partition by s.session_id order by a.sent_at) as event_sequence_number
          from ember.tracks as a
          inner join ${application_sessions.SQL_TABLE_NAME} as s
          on a.user_id = s.user_id
            and a.sent_at >= s.session_start_at
            and (a.sent_at < s.next_session_start_at or s.next_session_start_at is null)

view: event_session_map {
  derived_table: {
    sql: select a.anonymous_id
            , a.sent_at
            , a.id
            , a.event
            , s.session_id
            , a.user_id
            , row_number() over(partition by s.session_id order by a.sent_at) as event_sequence_number
          from ember.tracks as a
          inner join ${application_sessions.SQL_TABLE_NAME} as s
          on a.user_id = s.user_id
            and a.sent_at >= s.session_start_at
            and (a.sent_at < s.next_session_start_at or s.next_session_start_at is null) ;;
  }

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

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

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

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

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

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}

- view: event_session_map
  derived_table:
    sql: select a.anonymous_id
            , a.sent_at
            , a.id
            , a.event
            , s.session_id
            , a.user_id
            , row_number() over(partition by s.session_id order by a.sent_at) as event_sequence_number
          from ember.tracks as a
          inner join ${application_sessions.SQL_TABLE_NAME} as s
          on a.user_id = s.user_id
            and a.sent_at >= s.session_start_at
            and (a.sent_at < s.next_session_start_at or s.next_session_start_at is null)
  fields:
  - dimension: anonymous_id
    sql: ${TABLE}.anonymous_id
  - dimension: id
    type: number
    sql: ${TABLE}.id
  - dimension: event
    sql: ${TABLE}.event
  - dimension: session_id
    sql: ${TABLE}.session_id
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: event_sequence_number
    type: number
    sql: ${TABLE}.event_sequence_number
  - dimension_group: sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.sent_at
  - measure: count
    type: count
    drill_fields:
    - detail*