snowplow / dbt-snowplow-media-player Goto Github PK

Describe the feature

We should add automated tests to the repository such that on every PR the tests run to ensure that we are not making breaking changes.

Who will this benefit?

This will benefit all developers of future versions of the media player package, as well as all reviewers as there will be some checks in place to ensure that changes don't deviate from expected behaviour

Describe the feature

Currently the model does not expect media duration to be equaling to 0, which may happen due to incorrect tracking which causes the model to fail due to division by 0 error in case none of the events coming from the same media_id have the expected duration equaling a number other than 0. This should be handled in the model together with a dbt test to to alert in case it happens.

Describe the feature

Add adapter support for Databricks.

Describe the bug

If you don't have whatwg media and video enabled and are using Snowflake or BigQuery then the snowplow_media_player_base_events_this_run model will fail. In this scenario the playback_quality_field macro casts null as the video_width dtype, which in this case is integer, (see this line in the macro) which conflicts with the other fields in the coalesce which are correctly a string.

This is the failed Snowflake compiled SQL snippet:

coalesce(
    
a.contexts_com_snowplowanalytics_snowplow_media_player_2[0]:quality::varchar
,
    
          cast(null as varchar),
    
          cast(null as integer)
    ,
    'N/A'
   )
 as playback_quality

The snowplow_media_player_custom.yml refers to the snowplow_media_player_session_stats as snowplow_media_player_plays_by_session, which should be updated.

Describe the bug

mp_context as (

select
root_id,
root_tstamp,
duration,
playback_rate,
current_time,
percent_progress,
muted,
is_live,
loop,
volume,
row_number() over (partition by root_id order by root_tstamp) dedupe_index

from {{ var('snowplow__media_player_context') }}

where root_tstamp between {{ lower_limit }} and {{ upper_limit }}

)

in this query, the current_time variable is the default redshift function so that column will be generated like as timetz

so the dbt process will be failed

Completed with 1 error and 0 warnings:
06:52:02
06:52:02 Database Error in model snowplow_media_player_interactions_this_run (models/web/scratch/interactions_this_run/redshift_postgres/snowplow_media_player_interactions_this_run.sql)
06:52:02 column mp.current_time does not exist
06:52:02 compiled Code at target/run/snowplow_media_player/models/web/scratch/interactions_this_run/redshift_postgres/snowplow_media_player_interactions_this_run.sql
06:52:02

Steps to reproduce

I can fix this issue used to create a custom variable like "current_time"
select
root_id,
root_tstamp,
duration,
playback_rate,
"current_time",
percent_progress,
muted,
is_live,
loop,
volume,
row_number() over (partition by root_id order by root_tstamp) dedupe_index

from {{ var('snowplow__media_player_context') }}

where root_tstamp between {{ lower_limit }} and {{ upper_limit }}

Expected results

Actual results

Screenshots and log output

System information

The contents of your packages.yml file:

# contents goes here

Which database are you using dbt with?

• redshift

The output of dbt --version:

<output goes here>

The operating system you're using:

The output of python --version:

Additional context

Are you interested in contributing towards the fix?

Describe the feature

The macros being used in the package should be migrated to snowplow_utils as they would be valuable to be used across different packages.

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

A video played in picture-in-picture (PIP) mode may produce events events with multiple different page view IDs. Currently we assume that a playback belongs to only a single page view ID.

Describe the solution you'd like

Allow for the play to cover multiple page view IDs and add a column in the media base table that lists all of the page view IDs.

Describe the feature

dbt-core v1.3.x is a decent shift away from previous versions of dbt in terms of functionality and macros, so we need to prepare our media-player package to be able to support this version and drop support for lower versions.

Dependency

We need to wait for dbt-utils v1.0 to be released, then update snowplow-utils to support that version of dbt-utils, and then build on top of that later snowplow-utils release

Describe the bug

Currently the time window to wait for a page_view to be processed (snowplow__max_media_pv_window) for the media_stats table is capped at 10 hours by default to allow for any late arriving data and is calculated from current date and not from the event's derived_tstamp, which means that the filter will not work as indented, with sessions being potentially processed while they are ongoing. Although the current logic could still work if the value would be changed to a negative value, aka -10 from the default 10, it would be confusing for users, therefore it is best to turn the logic around and add the 10 hours to the event's timestamp.

Describe the bug

In case of databricks/spark, the get_string_agg macro currently uses collect_set, which removes potential duplicates when percent progresses are concatenated within the base model. For other warehouses the macro aggregates all percent progresses, and the same number would be added twice if the player watched the same part of the video twice during the same page_view. It should be using collect_list instead for the intended use and play_time_sec calculation.

Describe the bug

This problem occurs in case of either:

1. The same media label being used for two different media contents
2. Or some events being tracked with a different media_type or media_player_type than other media events for the same content (the properties being set later in the tracking)

This causes the media_stats table to break because it has a unique key on the media_id while also grouping by the media_label, media_type and media_player_type (see here).

Steps to reproduce

Generate events which have different media_type tracked for the same media_label.

Expected results

We don't want to hide this problem as it signals an issue in the tracking. But we also don't want the model to break. Instead, it would be better if the media_stats table contained multiple rows for each of the tracked property combinations.

Actual results

dbt jobs fail in this case.

Potential solutions

A couple of solutions are possible:

1. Add media_type and media_player_type to the surrogate key when generaing media_id (here) – this would be a breaking change.
2. Change the unique key for the media_stats table to instead be a combined version of the media_id, media_label, media_type, and media_player_type (or a surrogate key for them).

Standardise the GitHub workflow with issue creation and PRs to be in line with the web and mobile packages

Describe the feature

We've released Databricks performance improvements in the Snowplow web package but need to bump the web package version to take advantage of that for the Media player.

The following line dosen't yield any results when the table is empty in databricks.

Describe the bug

Impressions in the media base table are counted as the number of distinct page views. However, this is imprecise as there may be multiple videos per page, which then leads to the play rate being higher than 1 (the number of plays is more than the number of page views).

Expected results

A better solution would be to use the number of distinct play_id as the number of impressions. This would take into account all the loaded videos on the page.

Describe the feature

We need to optimize performance in Databricks. We can leverage the optimizeWrite and optimizeCompact table properties in Databricks to achieve this, and we should focus on all incremental (Snowplow or otherwise) tables that are being generated from the Snowplow models.

Describe the bug

There is a mismatch between following fields in models/custom/snowplow_media_player_custom.yml and models/custom/snowplow_media_player_session.sql

Steps to reproduce

Expected results

Actual results

Screenshots and log output

System information

The contents of your packages.yml file:

# contents goes here

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

<output goes here>

The operating system you're using:

The output of python --version:

Additional context

Are you interested in contributing towards the fix?

Describe the feature

Currently the package only supports Redshift & Postgres. Add adapter support for Snowflake and BigQuery.

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

Prepare to add support for mobile events.

Describe the solution you'd like

Take out the snowplow_web package as the base and use a custom one taken from the ecommerce package. The motivation for this is to be able to process mobile events as well.

Additional context

We won't yet support mobile events in this issue. We can only generate mobile events from the new media schemas which are not yet supported by the package, so we will add support for mobile later, when adding support for the new media schemas. This issue just prepares for this in advance.

Add support for the new media schemas tracked using the Snowplow media plugin on Web or using the media APIs on the iOS and Android trackers.

Describe the bug

Currently the model will only work as expected if both HTML5 and YouTube video tracking is enabled.

Steps to reproduce

Running the snowplow_media_player_interactions_this_run table when any of the context tables do not exist in the database:

    from {{ ref("snowplow_web_base_events_this_run") }} as e

    inner join {{ source('atomic', 'com_snowplowanalytics_snowplow_media_player_event_1') }} as mpe
    on mpe.root_id = e.event_id and mpe.root_tstamp = e.collector_tstamp

    left join {{ source('atomic', 'com_snowplowanalytics_snowplow_media_player_1') }} as mp
    on mp.root_id = e.event_id and mp.root_tstamp = e.collector_tstamp

    left join {{ source('atomic', 'com_youtube_youtube_1') }} as y
    on  y.root_id = e.event_id and y.root_tstamp = e.collector_tstamp

    left join {{ source('atomic', 'org_whatwg_media_element_1') }} as me
    on me.root_id = e.event_id and me.root_tstamp = e.collector_tstamp

    left join {{ source('atomic', 'org_whatwg_video_element_1') }} as ve
    on ve.root_id = e.event_id and ve.root_tstamp = e.collector_tstamp

Describe the bug

Error creating incremental update to snowplow_media_player_media_stats

Steps to reproduce

Run:

dbt build --select snowplow_media_player_media_stats

Expected results

The command is run successfully.

Actual results

The command fails with:

Database Error in model snowplow_media_player_media_stats (models/web/snowplow_media_player_media_stats.sql)
  division by zero: 0 / 0
  compiled Code at target/run/snowplow_media_player/models/web/snowplow_media_player_media_stats.sql

packages:
  - package: snowplow/snowplow_web
    version: [">=0.12.0", "<0.13.0"]

1.5.0