[Bug] Possible Duplicates in Audit Table Model

[fivetran-joemarkiewicz]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

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.

dbt_fivetran_log/models/fivetran_platform__audit_table.sql

Lines 131 to 132 in 5fef364

	and records_modified_log.created_at > limit_to_table_starts.sync_start
	and records_modified_log.created_at < coalesce(limit_to_table_starts.sync_end, limit_to_table_starts.next_sync_start)

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.

dbt Project configurations

vars:
  fivetran_platform_schema: fivetran_log_3
  fivetran_platform_using_destination_membership: false

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?

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