snowplow / dbt-snowplow-mobile Goto Github PK

Describe the feature

Add support for Databricks via Spark

Describe the feature

Some users are confused by the wording in the docs regarding the context tables for page views. Can we clarify the wording to ensure that it is clear that these tables are going to be processed/present in Redshift/Postgres only?

Describe the bug

Described in customer ticket, but somehow in the tracking the previous_session_id is sometimes not unique at a session and user table level (where we run the tests). We think this is where someone has ported files to a new phone but then continued to use both devices.

Steps to reproduce

N/A

Expected results

Tests pass

Actual results

Tests fail

Are you interested in contributing towards the fix?

Yes, will reduce the test severity to warn so that the models can still pass but alert people if it fails.

Describe the feature

The 0.4 web package introduced a user mapping module. We should replicate this on the mobile side, mapping user_id to device_user_id

To match what was done in web package

Describe the bug

The type for the context/unstruct columns does not work for redshift, the length need specifying

Steps to reproduce

Run int tests on redshift

Expected results

They work

Actual results

The seed fails

Screenshots and log output

System information

Which database are you using dbt with?

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

Additional context

They just need changing to varchar(65535) instead

Are you interested in contributing towards the fix?

Will do with next useful release

For the next release we should bump to Copyright notices to state 2022 rather than 2021.

Describe the feature

Take advantage of the type_string functionality in the snowplow_utils package to clean up the incremental manifest table logic:

{# Redshift produces varchar(1) column. Fixing char limit #}
{% set type_string = dbt_utils.type_string() %}
{% set type_string = 'varchar(4096)' if type_string == 'varchar' else type_string %}

with prep as (
  select
    cast(null as {{ type_string }}) model,

to

with prep as (
  select
    cast(null as {{ snowplow_utils.type_string(4096) }}) model,

Describe the feature

In order to tag Snowplow models as being run by Snowplow, we need to add a query tag in Snowflake. Can we have some functionality that allows this to be generated when running the dbt models in Snowflake but not in the other databases?

Describe the feature

Add support for the dbt mobile model on Snowflake

Describe the bug

The stitched_user_id column is being created as a varchar column of the same length as device_user_id. If the user_id is longer than the device_user_id then this leads to errors since we later update the value of stitched_user_id to that of user_id.

Steps to reproduce

Create dummy data where the domain_userid is a VARCHAR(6) and the user_id is VARCHAR(12).

Describe the bug

This package uses an older version of snowplow_utils which will cause dependancy conflicts if using other snowplow packages which depend on different versions of snowplow_utils in your dbt project.

Steps to reproduce

1. Use both the latest mobile and web packages in your dbt project packages.yml file:

packages:
  - package: snowplow/snowplow_web
    version: 0.9.2
  - package: snowplow/snowplow_mobile
    version: 0.5.3

2. Run dbt deps.

Expected results

No error, all packages install successfully.

Actual results

Version error for package snowplow/snowplow_utils: Could not find a satisfactory version from options: ['>=0.12.0', '<0.13.0', '>=0.11.0', '<0.12.0']

Additional context

Issue first raised in discourse.

Describe the bug

Currently the unstruct_event_com_snowplowanalytics_mobile_screen_view_1 is treated as an array just like similar context fields such as the contexts_com_snowplowanalytics_mobile_screen_1. This, however, produces a Field name should be String Literal, but it's 0 error when the snowplow_mobile_base_events_this_run is executed. To resolve this, [0] needs to be removed during field extraction. Example: a.unstruct_event_com_snowplowanalytics_mobile_screen_view_1[0].id::STRING AS screen_view_id

Describe the bug

In the geo context columns in the events_this_run.sql file in Databricks there are 3 different erroneous AS AS trying to alias column names which fail to run successfully

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
• [ x] databricks
• other (specify: ____________)

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 mobile 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 feature

Update docs ahead of the 0.3.0 release

Describe the bug

Typo in file models/base/scratch/base_scratch.yml, line 46: screescreen_top_view_controllern_type

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
• databricks
• 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

In order to drop deprecation warnings and to not worry about backwards compatibility, we need to remove support for older versions of dbt and start supporting dbt v1.0.0 onwards.

Describe the feature

Add support for the dbt mobile model for Databricks.

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 mobile package, as well as all reviewers as there will be some checks in place to ensure that changes don't deviate from expected behaviour

For the release of snowplow-mobile v0.2.0 we will need to:

• Update the READMEs
• Update the docsite
• Regenerate the docsite

Describe the feature

We use the unique_key parameter in the config of many derived tables (see here), but this seems to have no actual effect outside of dbt's snapshot functionality (see here). We should use indexes to actually add a unique key index to Postgres to improve performance (see here).

So in short, we should remove the unique_key parameter from the tables, and introduce indexes with the unique key index in our derived tables.

Describe the feature

Add support for the dbt mobile model on BigQuery.

Describe the bug

If you have user_stitching disabled, you still have to keep the user_mapping table enabled as the sessions table depends directly on this by the ref call in the arguments.

Steps to reproduce

1. Create project with mobile package as dependency.
2. Disable user sitching
3. Disable the user_mapping model
4. Run dbt compile/docs/whatever

Expected results

Will compile/run and not see dependency on user_mapping to sessions

Actual results

Will not compile, see dependency

Screenshots and log output

System information

Which database are you using dbt with?

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

The output of dbt --version:

The operating system you're using:

The output of python --version:

Additional context

Are you interested in contributing towards the fix?

Sure, why not

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

Since dbt Cloud uses the dbt-spark adapter for Databricks which does not support the Unity Catalog, but dbt-databricks 1.1.1+ does support the unity catalog, we need to release a fix for the web package to enable both version to be able to work in tandem.

Datediff based filters which calc the number of days between two timestamps and filter accordingly, produce counterintuitive results. An example of such a filter:

When inputting a timestamp into the datediff function while using date based datepart (year, month, week, day), the time component of the timestamp is effectively ignored. An example of the problem:

with prep as (
select cast('2021-01-03 13:00:00' as timestamp) as dvce_created_tstamp, cast('2021-01-06 13:00:10' as timestamp) as dvce_sent_tstamp 
)

select 
    dvce_created_tstamp,
    dvce_sent_tstamp,
    datediff(day, dvce_created_tstamp, dvce_sent_tstamp) as date_diff_calc,
    case when datediff(day, dvce_created_tstamp, dvce_sent_tstamp) > 3 then true else false end late_arriving_day_comparison,
    case when dvce_sent_tstamp > dateadd(day, 3, dvce_created_tstamp) then true else false end late_arriving_tstamp_comparison
    
from prep

The data point should be classified as late arriving since the dvce_sent_tstamp is more than 3 days after then dvce_created_tstamp. datediff misclassifies this whereas when comparing two timestamps (by using dateadd) we get the intended result.

Models including such filters:

• snowplow_mobile_base_events_this_run

Describe the feature

Add support for the dbt mobile model on Redshift.

Additional context

This is only to support Redshift for the dbt-snowplow-mobile model

Who will this benefit?

Beta testers of the Redshift dbt-snowplow-mobile model