snowplow_tests_view_in_session_values scans all the data every time the test is run

[juhani-sc]

Is your feature request related to a problem? Please describe.

The test snowplow_tests_view_in_session_values doesn't include any filters. This means that as the data the test is referencing grows in size the test takes longer and longer to execute and requires more compute resources.

Describe the solution you'd like

One simple solution would be to limit the test to only look at past n-days worth of data or some other fixed time window:

with prep as (
  select
    session_identifier,
    count(distinct views_in_session) as dist_pvis_values,
    count(*) - count(distinct view_in_session_index)  as all_minus_dist_pvisi,
    count(*) - count(distinct view_id) as all_minus_dist_pvids

  from {{ ref('snowplow_unified_views') }}
  -- as a simple example
  where date(dvce_created_tstamp) >= current_date() - 2
  group by 1
)

select
  session_identifier

from prep

where dist_pvis_values != 1
or all_minus_dist_pvisi != 0
or all_minus_dist_pvids != 0

Describe alternatives you've considered

Haven't thought of other solutions.

Additional context

Not relevant.

Are you interested in contributing towards this feature?

Sure. If we go with the solution I suggested, it's a relatively minor fix.

[rlh1994]

Thanks @juhani-sc, we've agreed to swap this to run on the page views this run table instead which should greatly reduce the scan volume while still identifying any issues that may make it into the table.