[Bug] Divide by zero error - stripe__line_item_enhanced

[jjspratt]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

When trying to enable this model I am hit with a divide by zero error. Looking at the table shows it's not uncommon for us to have an amount of 0 or a quantity of 0 on a line item.

I think this is due to tiered pricing but I haven't been able to validate this yet.

Relevant error log or model output

20:16:31 Redshift adapter: Redshift error: Divide by zero
20:16:31 On model.stripe.stripe__line_item_enhanced: ROLLBACK
20:16:31 On model.stripe.stripe__line_item_enhanced: Close
20:16:31 Database Error in model stripe__line_item_enhanced (models/standardized_models/stripe__line_item_enhanced.sql)
  Divide by zero
  compiled Code at target/run/stripe/models/standardized_models/stripe__line_item_enhanced.sql
20:16:31 1 of 2 ERROR creating sql table model dbt_jspratt_stripe.stripe__line_item_enhanced  [ERROR in 20.59s]
20:16:31 Finished running node model.stripe.stripe__line_item_enhanced
20:16:32   Database Error in model stripe__line_item_enhanced (models/standardized_models/stripe__line_item_enhanced.sql)
  Divide by zero
  compiled Code at target/run/stripe/models/standardized_models/stripe__line_item_enhanced.sql

Expected behavior

The expected behavior is that the model either pass these rows with $0.00 for unit amount.

dbt Project configurations

vars:
stripe_schema: "fivetran_stripe"
stripe_transfer_identifier: fivetran_stripe.alt_stripe_transfers
stripe_account_identifier: account_filtered
stripe__using_credit_notes: True
stripe__standardized_billing_model_enabled: True

Package versions

0.14.0

What database are you using dbt with?

redshift

dbt Version

versionless

Additional Context

Thanks for all you do!

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.

[fivetran-joemarkiewicz]

Thanks for raising this issue @jjspratt!

This is definitely something we can fix in the coming sprint (starting tomorrow). We have addressed this in the past by applying a nullif(<field>,0) update to the troublesome field. However, I noticed in this case the error is occurring here

dbt_stripe/models/standardized_models/stripe__line_item_enhanced.sql

Line 111 in a912d17

cast((invoice_line_item.amount/invoice_line_item.quantity) as {{ dbt.type_numeric() }}) as unit_amount,

In this scenario it must be that the quantity is null. However, I'm worried about applying the nullif function if the amount isn't null as that will just nullify the result. Would you be able to check your data and let us know if cases where your quantity is null, do you also see the amount as null and would you expect the results of this to be nulled or should we consider a different approach?

Thanks!

[jjspratt]

@fivetran-joemarkiewicz there are not nulls in the table. There are rows where quantity is 0 due to various billing behaviors.

For instance, it is not uncommon to see tiered pricing where the quantity is 0. There is also an outlier where both the amount and quantity were 0. This last one feels unintentional but it's also on a paid invoice and necessary for our reporting.

The desired outcome for us would be that resulting unit amount were 0.

[fivetran-joemarkiewicz]

Thanks for the clarification @jjspratt! So in the case you mentioned the expected unit_amount logic would likely be the following:

cast(case when invoice_line_item.quantity = 0
    then 0
    else (invoice_line_item.amount/invoice_line_item.quantity) 
end as {{ dbt.type_numeric() }}) as unit_amount, 

[fivetran-joemarkiewicz]

Hi @jjspratt, just wanted to let you know that we'll be moving forward with the above code change in the upcoming Stripe dbt package release. Our team will plan to integrate this change along with a few other outstanding dbt_stripe and dbt_stripe_source updates.

You can expect to see us fold this in within the next two weeks. Thanks!

[jjspratt]

My connector is running without issue now. Thank you so much.

[fivetran-jamie]

Excellent, I'll go ahead and close this out 😄