search

fivetran / dbt_shopify_source

Fivetran's Shopify source dbt package
https://fivetran.github.io/dbt_shopify_source/
Apache License 2.0
29 stars 23 forks source link

[Bug] stg_shopify__order_discount_code limits discount codes to 1 Per Order #80

Closed justinwagg closed 4 months ago

justinwagg commented 5 months ago

Is there an existing issue for this?

Describe the issue

Summary

The model stg_shopify__order_discount_code results in a table that incorrectly limits the number of included coupon codes per order to 1.

Impact

  1. stg_shopify__order_discount_code is incorrect. And therefore consumers of this model are too.
    • The dbt_shopify package depends on this model in the shopify__orders transformation.
      • Any of the columns shopify__orders.count_discount_codes_applied, shopify__orders.shipping_discount_amount, shopify__orders.percentage_calc_discount_amount, shopify__orders.fixed_amount_discount_amount may be incorrect.

For example here

Issue

The issue is caused by this line:

https://github.com/fivetran/dbt_shopify_source/blob/738491aef12c2b8faf0ce031ba5f93c3cb1f5381/models/stg_shopify__order_discount_code.sql#L37

This is not a good sanity check. It assumes there will only ever be 1 discount code applied to an order. There is no reason why there can't be multiple discounts per order. See the order resource documentation: https://shopify.dev/docs/api/admin-rest/2024-04/resources/order#resource-object, specifically discount_codes which is described as

A list of discounts applied to the order. Each discount object includes the following properties:

Steps to reproduce

  1. I place a Shopify order that includes a shipping discount and a product discount
  2. I sync data from Shopify to Data Warehouse of choice via Fivetran
  3. I run all Shopify models in this repo
  4. Moving from source data up
    1. I look at shopify.order_discount_code and see 2 rows for the order, 1 per applied coupon. This is correct.
    2. I look at stg_shopify__order_discount_code_tmp and see 2 rows for the order, 1 per applied coupon. This is correct.
    3. I look at stg_shopify__order_discount_code and see 1 row for the order (only the first coupon applied, where index = 1) This is incorrect.
  5. I run dbt_shopify models
  6. I query shopify__orders for my test order
  7. I see that shopify__orders.count_discount_codes_applied is 1. This is incorrect.
    1. Any of the columns shipping_discount_amount, percentage_calc_discount_amount, fixed_amount_discount_amount may also be incorrect depending on the order in which the coupon was added to the order.

Relevant error log or model output

No response

Expected behavior

The model stg_shopify__order_discount_code does not result in a table that limits the number of included coupon codes per order to 1.

dbt Project configurations

n/a

Package versions

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: fivetran/shopify
    version: [">=0.11.0", "<0.12.0"]

What database are you using dbt with?

bigquery

dbt Version

Core:
  - installed: 1.5.2
  - latest:    1.7.11 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 1.5.3 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Additional Context

No response

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

fivetran-avinash commented 5 months ago

Thanks for the thorough scoping @justinwagg ! At first glance these changes to address your issue make sense to me. Let me sync with my team and confirm that there aren't any intricacies I'm missing. If so we will prioritize this for a coming fix!

justinwagg commented 5 months ago

Thanks @fivetran-avinash !

fivetran-avinash commented 4 months ago

Hi @justinwagg , we did our initial assessment and agree with your proposed changes! Our plan is to bring this task into the coming sprint next week and we will prioritize bringing in all the discount codes per order so there are no aggregate issues downstream.

Let us know if you have any questions! We'll be in touch when our solution is live for you to try out.

fivetran-avinash commented 4 months ago

Hi @justinwagg , we've deployed your suggested changes live in our latest release of dbt_shopify_source. Upgrade to the newest version of our package in your packages.yml and you should be good to see all your discount codes! https://hub.getdbt.com/fivetran/shopify_source/latest/

Let me know if you have any additional questions and feel free to submit any other features or bugs that come to mind!