SP Data model / Snowflake / Standard / Sessions

For data modelers & consumers Snowflake
1

Hey Guys,

I’m starting to look into the Standard Native model coming off the back of the Standard Web model. First step is I’m looking at the SQL, screen_views was spot on no issues.

I’m now looking into sessions starting here

I’m particularly looking at 01-sessions-aggs.sql and this part of the snippet:

CREATE OR REPLACE TABLE {{.scratch_schema}}.mobile_sessions_aggregates{{.entropy}}
AS (
  WITH events AS (
    SELECT
      es.session_id,
      es.event_id,
      es.event_name,
      es.derived_tstamp,
      es.build,
      es.version,
      es.event_index_in_session,
      MAX(es.event_index_in_session) OVER (PARTITION BY es.session_id) AS events_in_session

    FROM
      {{.scratch_schema}}.mobile_events_staged{{.entropy}} es
  )

  , session_aggs AS (
    SELECT
      e.session_id,
      --last dimensions
      MAX(CASE WHEN e.event_index_in_session = e.events_in_session THEN e.build END) AS last_build,
      MAX(CASE WHEN e.event_index_in_session = e.events_in_session THEN e.version END) AS last_version,
      MAX(CASE WHEN e.event_index_in_session = e.events_in_session THEN e.event_name END) AS last_event_name,
      MAX(CASE WHEN e.event_index_in_session = e.events_in_session THEN e.event_id END) AS session_last_event_id,

I’m stumped on event_index_in_session what is this? I can’t find any previous mention of it anywhere else nor in my own staging, I do not have it.

Thanks,
Kyle

2

Hi Kyle,

Good question. So event_index_in_session in calculated here, within events_this_run model in the base module.

It is an index of all events within a given session, order by derived_tstamp. Its primary use is to determine the first event within a session. This information is needed within the sessions model to allow us to report dimensions such as device_latitude at the start of the session.

I am not entirely sure why you do not have it in your events staged table however. Are you able to confirm that you have the model var set to it’s default value mobile as seen here? Also it is worth noting that the mobile and web models output to different event staged tables.

Let me know how you get on.

Will

1 Like
3

Hi @Will

Thanks for reply, that all makes sense.

The issue was on my side, long story short a we forwent the SP Snowflake loader, mostly due to Snowflake makes it straightforward to just flatten the JSON ourselves. The unforeseen knock on impact is now we needed to adapt the models, mostly renaming and a lot of edits.

Kyle

4

Ah that sounds like a pain! Well glad you identified the root issue at least. Let me know if you have any more questions.

Will