[Bug] new_vs_repeat field in shopify__orders can be inconsistent on incremental runs

[fivetran-joemarkiewicz]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I believe this logic in the shopify__orders model can produce inaccurate results based on the incremental logic within the model. I believe the window function to calculate the new_vs_repeat logic is only performed on the data within the window and not the entire table. As such, the logic could be incorrect if there a customer had a new order outside the window, but since it's not included in the incremental logic we won't be able to recognize that and the order which should actually be a repeat order is categorized as new.

Relevant error log or model output

Incorrect new_vs_repeat results

Expected behavior

The new_vs_repeat field is able to properly produce accurate results

Possible solution

We could remove the incremental strategy to address this. However, ideally we can resolve this without needing to remove the incremental strategy for Snowflake, Redshift, and Postgres. As of this moment, I don't have a solution in mind.

dbt Project configurations

Default

Package versions

Latest

What database are you using dbt with?

postgres, redshift, snowflake

How are you running this dbt package?

Fivetran Quickstart Data Model

dbt Version

Latest

Additional Context

No response

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

• Yes.
• Yes, but I will need assistance.
• No.