fivetran/dbt_apple_store

Fivetran's Apple App Store source dbt package

dbt_apple_store's Issues

[Bug]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Building apple_store__subscription_report model fails with following error on BigQuery.

21:44:25  Completed with 1 error and 0 warnings:
21:44:25  
21:44:25  Database Error in model apple_store__subscription_report (models/apple_store__subscription_report.sql)
21:44:25    No matching signature for operator = for argument types: DATE, STRING. Supported signature: ANY = ANY at [243:12]
21:44:25    compiled Code at target/run/apple_store/models/apple_store__subscription_report.sql
21:44:25  
21:44:25  Done. PASS=27 WARN=0 ERROR=1 SKIP=1 TOTAL=29

The code referred here seems to be:

dbt_apple_store/models/apple_store__subscription_report.sql

Line 88 in 0e7ea50

on reporting_grain.date_day = subscription_events.date_day

Relevant error log or model output

Error log itself is provided above.

Expected behavior

It should run without any issue.

dbt Project configurations

I have set the using_subscriptions flag to true.

vars:
  # set source database and schema name (https://github.com/fivetran/dbt_app_reporting#step-3-configure-database-and-schema-variables)
  apple_store_database: project-XXX
  apple_store_schema: itunes_connect
  google_play_database: project-XXX
  google_play_schema: google_play

  # enable subscriptions and earnings models (https://github.com/fivetran/dbt_app_reporting#step-4-disable-and-enable-source-tables)
  apple_store__using_subscriptions: true # by default this is assumed to be false
  google_play__using_subscriptions: true # by default this is assumed to be false
  google_play__using_earnings: true # by default this is assumed to be false

Package versions

packages:

• package: fivetran/app_reporting
  version: 0.2.0

What database are you using dbt with?

bigquery

dbt Version

Core:

• installed: 1.4.1
• latest: 1.4.1 - Up to date!

Plugins:

• bigquery: 1.4.0 - Up to date!

Additional Context

As a temporary measure, I changed the line to

reporting_grain.date_day = cast(subscription_events.date_day as date)

and it worked.

Maybe it would be better if it is fixed in other location like upstream model.

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[Bug] cannot download latest version (0.2.0) via dbt Hub

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I tried installing the latest version of package (0.2.0) by following the guide in the README.md.

But it failed with following error,

08:34:10  Running with dbt=1.3.1
08:34:12  Encountered an error:
Could not find a matching compatible version for package fivetran/apple_store
  Requested range: >=0.2.0, <0.3.0
  Compatible versions: ['0.1.0']

It was weired at first since I just followed the instruction. It turns out that the package info on the dbt Hub hasn't been updated yet.

A little more digging revealed that dbt hub seems to auto-update packages by observing changes of their (each package's) GitHub releases.

I think publishing new release will solve the issue.

Relevant error log or model output

08:34:10  Running with dbt=1.3.1
08:34:12  Encountered an error:
Could not find a matching compatible version for package fivetran/apple_store
  Requested range: >=0.2.0, <0.3.0
  Compatible versions: ['0.1.0']

Expected behavior

One should be able to download latest version of apple_store package from dbt Hub.

dbt Project configurations

not changed from auto-generated default

Package versions

packages:

• package: fivetran/apple_store
  version: [">=0.2.0", "<0.3.0"]

What database are you using dbt with?

snowflake

dbt Version

Core:

• installed: 1.3.1
• latest: 1.3.1 - Up to date!

Plugins:

• snowflake: 1.3.0 - Up to date!

Additional Context

No response

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[Bug] Test fail on duplicate found

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Test fail due to records not unique on date_day, app_platform, app_name, and country_short. My data is displaying duplicate records for country code TR:

Relevant error log or model output

Failure in test dbt_utils_unique_combination_of_columns_app_reporting__country_report_date_day__app_platform__app_name__country_short (models/app_reporting.yml)

Expected behavior

No errors

dbt Project configurations

N/A

Package versions

0.3.0

What database are you using dbt with?

snowflake

dbt Version

1.5

Additional Context

See this issue on Google Play for additional background.

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

Tests fail due to apparent duplicate rows in apple_store__subscription_report

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The test case unique_combination_of_columns in the apple_store__subscription_report table fails because the data does seem to have duplicates (where multiple rows have the same date_day, account_id, app_id, subscription_name, territory_long, state).

Looking into it further, the duplicates seem to exist in the transformed data from app_store.sales_subscription_event_summary (though the Fivetran assigned Primary keys are still all distinct).

I raised a ticket with Fivetran support who have said the duplicates are not a mistake, meaning I believe the test in the DBT package should change.

Relevant error log or model output

No response

Expected behavior

Tests should pass regardless of duplicates for the listed fields.

dbt Project configurations

N/A

Package versions

0.3.1

What database are you using dbt with?

bigquery

dbt Version

1.5.0

Additional Context

No response

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[Bug] ERD readme link is a 404

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Clicking the link "ERD" in the home page of this repo fails with a 404 error.

Relevant error log or model output

URL: https://fivetran.com/docs/applications/apple_store/#schemainformation

Expected behavior

No 404

dbt Project configurations

N/A (markdown only)

Package versions

Latest

What database are you using dbt with?

other (mention it in "Additional Context")

dbt Version

N/A

Additional Context

N/A

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[Bug] apple_store__subscription_report joins on null values

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

In apple_store__subscription_report, there is a join on state but that field will be null for most countries. This results in metrics like event_renew, event_cancel, event_subscribe being 0 as the rows are not joined correctly.

    select 
        reporting_grain.date_day,
        reporting_grain.account_id,
        reporting_grain.account_name, 
        reporting_grain.app_id,
        reporting_grain.app_name,
        reporting_grain.subscription_name, 
        case 
            when country_codes.alternative_country_name is null then country_codes.country_name
            else country_codes.alternative_country_name
        end as territory_long,
        reporting_grain.country as territory_short,
        reporting_grain.state,
        country_codes.region, 
        country_codes.sub_region,
        coalesce(subscription_summary.active_free_trial_introductory_offer_subscriptions, 0) as active_free_trial_introductory_offer_subscriptions,
        coalesce(subscription_summary.active_pay_as_you_go_introductory_offer_subscriptions, 0) as active_pay_as_you_go_introductory_offer_subscriptions,
        coalesce(subscription_summary.active_pay_up_front_introductory_offer_subscriptions, 0) as active_pay_up_front_introductory_offer_subscriptions,
        coalesce(subscription_summary.active_standard_price_subscriptions, 0) as active_standard_price_subscriptions
        {% for event_val in var('apple_store__subscription_events') %}
        {% set event_column = 'event_' ~ event_val | replace(' ', '_') | trim | lower %}
        , coalesce({{ 'subscription_events.' ~ event_column }}, 0)
            as {{ event_column }} 
        {% endfor %}
    from reporting_grain
    left join subscription_summary
        on reporting_grain.date_day = subscription_summary.date_day
        and reporting_grain.account_id =  subscription_summary.account_id 
        and reporting_grain.app_name = subscription_summary.app_name
        and reporting_grain.subscription_name = subscription_summary.subscription_name
        and reporting_grain.country = subscription_summary.country
        **and reporting_grain.state = subscription_summary.state**
    left join subscription_events
        on reporting_grain.date_day = subscription_events.date_day
        and reporting_grain.account_id =  subscription_events.account_id 
        and reporting_grain.app_name = subscription_events.app_name
        and reporting_grain.subscription_name = subscription_events.subscription_name
        and reporting_grain.country = subscription_events.country
        **and reporting_grain.state = subscription_events.state**
    left join country_codes
        on reporting_grain.country = country_codes.country_code_alpha_2

Relevant error log or model output

No response

Expected behavior

Correctly get all required rows from all joins

dbt Project configurations

Package versions

• package: fivetran/app_reporting
  version: 0.3.2

What database are you using dbt with?

bigquery

dbt Version

1.7.7

Additional Context

No response

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.