SQL Saas cloud infrastructure

Tracks Flow

Tracks Flow model for saas cloud infrastructure.

source-preserved

Blocks

dbt SQL LKML LookML

tracks_flow.sql · 45 lines

inferred

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

select a.event_id
      , a.session_id
      , a.track_sequence_number
      , a.event
      , a.looker_visitor_id
      , b.event as event_2
      , c.event as event_3
      , d.event as event_4
      , e.event as event_5
from {{ ref('track_facts') }} a
left join {{ ref('track_facts') }} b
on a.track_sequence_number + 1 = b.track_sequence_number
and a.session_id = b.session_id
left join {{ ref('track_facts') }} c
on a.track_sequence_number + 2 = c.track_sequence_number
and a.session_id = c.session_id
left join {{ ref('track_facts') }} d
on a.track_sequence_number + 3 = d.track_sequence_number
and a.session_id = d.session_id
left join {{ ref('track_facts') }} e
on a.track_sequence_number + 4 = e.track_sequence_number
and a.session_id = e.session_id
order by a.session_id, a.track_sequence_number

# models/schema.yml
version: 2

models:
  - name: tracks_flow
    columns:
      - name: event_id
        tests:
          - unique
          - not_null
      - name: session_id
      - name: track_sequence_number
      - name: event
      - name: user_id
      - name: event_2
      - name: event_3
      - name: event_4
      - name: event_5

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.

tracks_flow.sql · 23 lines

source-preserved validation: not checked

select a.event_id
      , a.session_id
      , a.track_sequence_number
      , a.event
      , a.looker_visitor_id
      , b.event as event_2
      , c.event as event_3
      , d.event as event_4
      , e.event as event_5
from ${track_facts.SQL_TABLE_NAME} a
left join ${track_facts.SQL_TABLE_NAME} b
on a.track_sequence_number + 1 = b.track_sequence_number
and a.session_id = b.session_id
left join ${track_facts.SQL_TABLE_NAME} c
on a.track_sequence_number + 2 = c.track_sequence_number
and a.session_id = c.session_id
left join ${track_facts.SQL_TABLE_NAME} d
on a.track_sequence_number + 3 = d.track_sequence_number
and a.session_id = d.session_id
left join ${track_facts.SQL_TABLE_NAME} e
on a.track_sequence_number + 4 = e.track_sequence_number
and a.session_id = e.session_id
order by a.session_id, a.track_sequence_number

tracks_flow.view.lkml · 94 lines

inferred

view: tracks_flow {
  derived_table: {
    sql: select a.event_id
      , a.session_id
      , a.track_sequence_number
      , a.event
      , a.looker_visitor_id
      , b.event as event_2
      , c.event as event_3
      , d.event as event_4
      , e.event as event_5
from ${track_facts.SQL_TABLE_NAME} a
left join ${track_facts.SQL_TABLE_NAME} b
on a.track_sequence_number + 1 = b.track_sequence_number
and a.session_id = b.session_id
left join ${track_facts.SQL_TABLE_NAME} c
on a.track_sequence_number + 2 = c.track_sequence_number
and a.session_id = c.session_id
left join ${track_facts.SQL_TABLE_NAME} d
on a.track_sequence_number + 3 = d.track_sequence_number
and a.session_id = d.session_id
left join ${track_facts.SQL_TABLE_NAME} e
on a.track_sequence_number + 4 = e.track_sequence_number
and a.session_id = e.session_id
order by a.session_id, a.track_sequence_number ;;
  }

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

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

  dimension: track_sequence_number {
    type: number
    hidden: yes
    sql: ${TABLE}.track_sequence_number ;;
  }

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

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

  dimension: event_2 {
    label: "2nd Event"
    sql: ${TABLE}.event_2 ;;
  }

  dimension: event_3 {
    label: "3rd Event"
    sql: ${TABLE}.event_3 ;;
  }

  dimension: event_4 {
    label: "4th Event"
    sql: ${TABLE}.event_4 ;;
  }

  dimension: event_5 {
    label: "5th Event"
    sql: ${TABLE}.event_5 ;;
  }

  measure: event_2_drop_off {
    label: "2nd Event Remaining Count"
    type: count
  }

  measure: event_3_drop_off {
    label: "3rd Event Remaining Count"
    type: count
  }

  measure: event_4_drop_off {
    label: "4th Event Remaining Count"
    type: count
  }

  measure: event_5_drop_off {
    label: "5th Event Remaining Count"
    type: count
  }
}

tracks_flow.view.lookml · 67 lines

source-preserved

- view: tracks_flow
  derived_table:
    sql: select a.event_id
      , a.session_id
      , a.track_sequence_number
      , a.event
      , a.looker_visitor_id
      , b.event as event_2
      , c.event as event_3
      , d.event as event_4
      , e.event as event_5
from ${track_facts.SQL_TABLE_NAME} a
left join ${track_facts.SQL_TABLE_NAME} b
on a.track_sequence_number + 1 = b.track_sequence_number
and a.session_id = b.session_id
left join ${track_facts.SQL_TABLE_NAME} c
on a.track_sequence_number + 2 = c.track_sequence_number
and a.session_id = c.session_id
left join ${track_facts.SQL_TABLE_NAME} d
on a.track_sequence_number + 3 = d.track_sequence_number
and a.session_id = d.session_id
left join ${track_facts.SQL_TABLE_NAME} e
on a.track_sequence_number + 4 = e.track_sequence_number
and a.session_id = e.session_id
order by a.session_id, a.track_sequence_number
  fields:
  - dimension: event_id
    primary_key: true
    hidden: true
    sql: ${TABLE}.event_id
  - dimension: session_id
    hidden: true
    sql: ${TABLE}.session_id
  - dimension: track_sequence_number
    type: number
    hidden: true
    sql: ${TABLE}.track_sequence_number
  - dimension: event
    hidden: true
    sql: ${TABLE}.event
  - dimension: user_id
    hidden: true
    sql: ${TABLE}.user_id
  - dimension: event_2
    label: 2nd Event
    sql: ${TABLE}.event_2
  - dimension: event_3
    label: 3rd Event
    sql: ${TABLE}.event_3
  - dimension: event_4
    label: 4th Event
    sql: ${TABLE}.event_4
  - dimension: event_5
    label: 5th Event
    sql: ${TABLE}.event_5
  - measure: event_2_drop_off
    type: count
    label: 2nd Event Remaining Count
  - measure: event_3_drop_off
    type: count
    label: 3rd Event Remaining Count
  - measure: event_4_drop_off
    type: count
    label: 4th Event Remaining Count
  - measure: event_5_drop_off
    type: count
    label: 5th Event Remaining Count

tracks_flow.sql
tracks_flow.sql
tracks_flow.view.lkml
tracks_flow.view.lookml

Tracks Flow

Blocks

Assumptions

Contents