When writing validations for an upcoming release I found that the following join condition within the fivetran_platform__audit_table model could result in possible duplicate entries.
This is because there are sometimes syncs which kick off simultaneously and we a records modified event that fits this timeframe. However, this is not entirely accurate and could result in multiple events that overlap in this period. Therefore, we should adjust this logic to ensure we are matching the appropriate records modified event with the proper sync event.
Please note, this is likely an uncommon issue that needs a specific criteria to be met in order for the duplicates to appear. Nevertheless we should ensure we are addressing this issue in a future release.
Relevant error log or model output
Possible duplicate entries in the audit table model
Expected behavior
There are no duplicate entries if there are sync start/end times that overlap with the records modified times for the condition highlighting in the join above.
I was able to get a quick working version of this in a draft PR from a while back which is now out of date. See the relevant code change here. We can likely leverage this code and fully validate it before introducing in an upcoming release.
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
Is there an existing issue for this?
Describe the issue
When writing validations for an upcoming release I found that the following join condition within the
fivetran_platform__audit_tablemodel could result in possible duplicate entries.https://github.com/fivetran/dbt_fivetran_log/blob/5fef3647a47e682236713c274895905ec5a81643/models/fivetran_platform__audit_table.sql#L131-L132
This is because there are sometimes syncs which kick off simultaneously and we a records modified event that fits this timeframe. However, this is not entirely accurate and could result in multiple events that overlap in this period. Therefore, we should adjust this logic to ensure we are matching the appropriate records modified event with the proper sync event.
Please note, this is likely an uncommon issue that needs a specific criteria to be met in order for the duplicates to appear. Nevertheless we should ensure we are addressing this issue in a future release.
Relevant error log or model output
Expected behavior
There are no duplicate entries if there are sync start/end times that overlap with the records modified times for the condition highlighting in the join above.
dbt Project configurations
Package versions
Latest
What database are you using dbt with?
bigquery
dbt Version
Latest
Additional Context
I was able to get a quick working version of this in a draft PR from a while back which is now out of date. See the relevant code change here. We can likely leverage this code and fully validate it before introducing in an upcoming release.
Are you willing to open a PR to help address this issue?