data.ml
SQL Saas cloud infrastructure

Dcos Command Funnel

Dcos Command Funnel model for saas cloud infrastructure.

source-preserved

Blocks

dcos_command_funnel.sql · 77 lines
inferred 
-- models/dcos_command_funnel.sql
{{ config(materialized='view') }}

SELECT
  sessions.session_id as session_id
  , dcos.dcos_cli.anonymous_id
  , dcos.dcos_cli.user_id
  , SUBSTRING(dcos.dcos_cli.cmd, POSITION('/placeholder_command ' IN dcos.dcos_cli.cmd), 99999) AS "cmd_core"
  , dcos.dcos_cli.cmd
  , dcos.dcos_cli.received_at
  , dcos.dcos_cli.event_id
  , MIN(
      CASE WHEN
        {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event3_time
  , (
      CASE WHEN
      {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event1_anonymous_id
  , (
      CASE WHEN
      {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event2_anonymous_id
  , (
      CASE WHEN
      {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event3_anonymous_id
FROM dcos.dcos_cli
LEFT JOIN {{ ref('aliases_mapping') }} as aliases_mapping
  ON aliases_mapping.alias = coalesce(dcos.dcos_cli.user_id, dcos.dcos_cli.anonymous_id)
LEFT JOIN {{ ref('track_facts') }} as tracks_sessions_map
  ON dcos.dcos_cli.event_id = tracks_sessions_map.event_id
LEFT JOIN {{ ref('sessions_trk') }} as sessions
  ON tracks_sessions_map.session_id = sessions.session_id
GROUP BY 1,2,3,4,5,6,7

# models/schema.yml
version: 2

models:
  - name: dcos_command_funnel
    columns:
      - name: anonymous_id
      - name: event_id
      - name: event1_anonymous_id
      - name: event2_anonymous_id
      - name: event3_anonymous_id
      - name: user_id
      - name: session_id
      - name: cmd
      - name: cmd_core
      - name: event1
      - name: event2
      - name: event3
      - name: received
      - name: event1_before_event2
      - name: event2_before_event3

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.
dcos_command_funnel.sql · 52 lines
source-preserved validation: not checked 
SELECT
  sessions.session_id as session_id
  , dcos.dcos_cli.anonymous_id
  , dcos.dcos_cli.user_id
  , SUBSTRING(dcos.dcos_cli.cmd, POSITION('/placeholder_command ' IN dcos.dcos_cli.cmd), 99999) AS "cmd_core"
  , dcos.dcos_cli.cmd
  , dcos.dcos_cli.received_at
  , dcos.dcos_cli.event_id
  , MIN(
      CASE WHEN
        {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event3_time
  , (
      CASE WHEN
      {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event1_anonymous_id
  , (
      CASE WHEN
      {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event2_anonymous_id
  , (
      CASE WHEN
      {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event3_anonymous_id
FROM dcos.dcos_cli
LEFT JOIN ${aliases_mapping.SQL_TABLE_NAME} as aliases_mapping
  ON aliases_mapping.alias = coalesce(dcos.dcos_cli.user_id, dcos.dcos_cli.anonymous_id)
LEFT JOIN ${track_facts.SQL_TABLE_NAME} as tracks_sessions_map
  ON dcos.dcos_cli.event_id = tracks_sessions_map.event_id
LEFT JOIN ${sessions_trk.SQL_TABLE_NAME} as sessions
  ON tracks_sessions_map.session_id = sessions.session_id
GROUP BY 1,2,3,4,5,6,7
dcos_command_funnel.view.lkml · 141 lines
inferred 
view: dcos_command_funnel {
  derived_table: {
    sql: SELECT
  sessions.session_id as session_id
  , dcos.dcos_cli.anonymous_id
  , dcos.dcos_cli.user_id
  , SUBSTRING(dcos.dcos_cli.cmd, POSITION('/placeholder_command ' IN dcos.dcos_cli.cmd), 99999) AS "cmd_core"
  , dcos.dcos_cli.cmd
  , dcos.dcos_cli.received_at
  , dcos.dcos_cli.event_id
  , MIN(
      CASE WHEN
        {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event3_time
  , (
      CASE WHEN
      {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event1_anonymous_id
  , (
      CASE WHEN
      {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event2_anonymous_id
  , (
      CASE WHEN
      {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event3_anonymous_id
FROM dcos.dcos_cli
LEFT JOIN ${aliases_mapping.SQL_TABLE_NAME} as aliases_mapping
  ON aliases_mapping.alias = coalesce(dcos.dcos_cli.user_id, dcos.dcos_cli.anonymous_id)
LEFT JOIN ${track_facts.SQL_TABLE_NAME} as tracks_sessions_map
  ON dcos.dcos_cli.event_id = tracks_sessions_map.event_id
LEFT JOIN ${sessions_trk.SQL_TABLE_NAME} as sessions
  ON tracks_sessions_map.session_id = sessions.session_id
GROUP BY 1,2,3,4,5,6,7 ;;
  }

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

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

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

  dimension: event2_anonymous_id {
    sql: ${TABLE}.event1_anonymous_id ;;
  }

  dimension: event3_anonymous_id {
    sql: ${TABLE}.event1_anonymous_id ;;
  }

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

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

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

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

  dimension: event1 {
    type: time
    sql: ${TABLE}.event1_time ;;
  }

  dimension: event2 {
    type: time
    sql: ${TABLE}.event2_time ;;
  }

  dimension: event3 {
    type: time
    sql: ${TABLE}.event3_time ;;
  }

  dimension: received {
    type: time
    sql: ${TABLE}.received_at ;;
  }

  dimension: event1_before_event2 {
    type: yesno
    sql: ${event1_time} < ${event2_time} ;;
  }

  dimension: event2_before_event3 {
    type: yesno
    sql: ${event2_time} < ${event3_time} ;;
  }

  measure: count_sessions {
    type: count
  }

  measure: count_sessions_event1 {
    type: count
    filters: [event1_time: "NOT NULL"]
  }

  measure: count_sessions_event2 {
    type: count
    filters: [event2_time: "NOT NULL"]
  }

  measure: count_sessions_event3 {
    type: count
    filters: [event3_time: "NOT NULL"]
  }
}
dcos_command_funnel.view.lookml · 105 lines
source-preserved 
- view: dcos_command_funnel
  derived_table:
    sql: SELECT
  sessions.session_id as session_id
  , dcos.dcos_cli.anonymous_id
  , dcos.dcos_cli.user_id
  , SUBSTRING(dcos.dcos_cli.cmd, POSITION('/placeholder_command ' IN dcos.dcos_cli.cmd), 99999) AS "cmd_core"
  , dcos.dcos_cli.cmd
  , dcos.dcos_cli.received_at
  , dcos.dcos_cli.event_id
  , MIN(
      CASE WHEN
        {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos.dcos_cli.received_at
        ELSE NULL END
      ) as event3_time
  , (
      CASE WHEN
      {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event1_anonymous_id
  , (
      CASE WHEN
      {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event2_anonymous_id
  , (
      CASE WHEN
      {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
      THEN dcos.dcos_cli.anonymous_id
      ELSE NULL END
    ) as event3_anonymous_id
FROM dcos.dcos_cli
LEFT JOIN ${aliases_mapping.SQL_TABLE_NAME} as aliases_mapping
  ON aliases_mapping.alias = coalesce(dcos.dcos_cli.user_id, dcos.dcos_cli.anonymous_id)
LEFT JOIN ${track_facts.SQL_TABLE_NAME} as tracks_sessions_map
  ON dcos.dcos_cli.event_id = tracks_sessions_map.event_id
LEFT JOIN ${sessions_trk.SQL_TABLE_NAME} as sessions
  ON tracks_sessions_map.session_id = sessions.session_id
GROUP BY 1,2,3,4,5,6,7
  fields:
  - dimension: anonymous_id
    sql: ${TABLE}.anonymous_id
  - dimension: event_id
    sql: ${TABLE}.event_id
  - dimension: event1_anonymous_id
    sql: ${TABLE}.event1_anonymous_id
  - dimension: event2_anonymous_id
    sql: ${TABLE}.event1_anonymous_id
  - dimension: event3_anonymous_id
    sql: ${TABLE}.event1_anonymous_id
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: session_id
    sql: ${TABLE}.session_id
  - dimension: cmd
    sql: ${TABLE}.cmd
  - dimension: cmd_core
    sql: ${TABLE}.cmd_core
  - dimension: event1
    type: time
    sql: ${TABLE}.event1_time
  - dimension: event2
    type: time
    sql: ${TABLE}.event2_time
  - dimension: event3
    type: time
    sql: ${TABLE}.event3_time
  - dimension: received
    type: time
    sql: ${TABLE}.received_at
  - dimension: event1_before_event2
    type: yesno
    sql: ${event1_time} < ${event2_time}
  - dimension: event2_before_event3
    type: yesno
    sql: ${event2_time} < ${event3_time}
  - measure: count_sessions
    type: count
  - measure: count_sessions_event1
    type: count
    filters:
    - event1_time: NOT NULL
  - measure: count_sessions_event2
    type: count
    filters:
    - event2_time: NOT NULL
  - measure: count_sessions_event3
    type: count
    filters:
    - event3_time: NOT NULL

Contents

  • dcos_command_funnel.sql
  • dcos_command_funnel.sql
  • dcos_command_funnel.view.lkml
  • dcos_command_funnel.view.lookml