[Bug] `fivetran_platform__audit_table` unique test failed due to write_to_table happen before midnight

[minhdoan-eh]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Hi team,
Thank you for working on this dbt packages, this is very convenient to retrieve statistics about Fivetran sync.
However, we bumped into this bug from fivetran_platform__audit_table table, which is raised when the sync happened in midnight.

When the event write_to_table_start happened before the cutoff date, and write_to_table_end + sync_end happened after that, that lead to NULL in the write_to_table_start column of the model.

When that happened frequently, we will have duplicate unique keys unique_table_sync_key, since every other information is the same, and write_to_table_start now is NULL.

Relevant error log or model output

Failure in test unique_fivetran_platform__audit_table_unique_table_sync_key (models/fivetran_platform.yml)

Data example:
[
	{
		"write_to_table_start" : null,
		"write_to_table_end" : "2026-01-07T17:00:50.669Z", -> convert to 00:00:50 in GMT+7
		"sync_start" : null,
		"sync_end" : "2026-01-07T17:02:22.036Z",
		"unique_table_sync_key" : "3b6cae944fc459a3f06db5b6d7994fe0",
		"write_to_table_start_day" : null
	},
	{
		"write_to_table_start" : null,
		"write_to_table_end" : "2026-01-04T17:01:37.339Z",
		"sync_start" : null,
		"sync_end" : "2026-01-04T17:02:51.399Z",
		"unique_table_sync_key" : "3b6cae944fc459a3f06db5b6d7994fe0", 
		"write_to_table_start_day" : null
	}
]

The unique_table_sync_key is duplicated since schema+table+connection_id is the same, 
the syncs started before midnight at both day, ended before midnight.

Expected behavior

model test passes

Possible solution

Since we are implementing lookback of 7 days of the last write_to_table_start_day anyway, I would suggest to filter only those who have sync_start and write_to_table_start is not null, to ensure data integrity.
It's confusing to have rows with write_to_table_start and sync_start is null, while the end timestamp is recorded.

dbt Project configurations

models:
  fivetran_log:
    +schema: fivetran_log
    staging:
      +schema: staging

vars:
  fivetran_platform_using_destination_membership: false

Package versions

packages:   
  - package: fivetran/fivetran_log
    version: [">=2.5.0", "<2.6.0"]

What database are you using dbt with?

redshift

How are you running this dbt package?

dbt Core™

dbt Version

1.10.13

Additional Context

No response

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

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

[fivetran-jamie]

Hi there @minhdoan-eh, thanks for raising this. It sounds like the model is making some assumption about the order of sync events.

It would be super helpful if you could share the output of the write_start_timestamps CTE for the duplicates. If you're open to it, you'd want to adjust the last lines of the model (in dbt_packages/fivetran_log/models/fivetran_log__audit_table.sql) from selecting from final to:

select *
from write_start_timestamps
where connection_id = <duplicated connection_id>
and schema_name = <duplicated schema_name>
and created_at > '2026-01-03'
order by created_at asc

You can find the relevant connection_id and schema_name by querying the fivetran_log__audit_table model where the unique_table_sync_key = '3b6cae944fc459a3f06db5b6d7994fe0'

More than happy to provide a branch with this integrated into it for more ease! Also if you'd prefer not to share the sample ouput here, please let me know and we can do that someplace else (email, Fivetran Support ticket, etc).

Thanks!

[minhdoan-eh]

Hi @fivetran-jamie , thank you for picking this up.
I've accumulated an output saved in an Google sheets, and also filtered the rows that matched the timestamp when the duplication happened.
This also confirms my belief that with syncs that happened during midnight, when the model is run incrementally with a 7 days filtered, the sync_start and write_to_table_start is left out.
Would you mind provide me with an email that I can share it to you? Thank you.

Really appreciate your support!

[fivetran-jamie]

Great thank you! jamie.rodriguez@fivetran.com

Also does this issue persist on full refresh runs?

[minhdoan-eh]

Yes I've shared the sheet with you, please noted that the connection_id and schema are the same value for all rows, I've just masking it to <blank>.
On full refresh runs, the issue is resolved. But full refresh run took more than 1 hour, that's why we're reluctant to use it on daily runs.

Please let me know if you need more information, and I can update to the sheet as well. Thank you!

[fivetran-jamie]

@minhdoan-eh Thank you! I am realizing that the best route to troubleshoot this would actually be for you to create a Support ticket and mention that you're already working with me and the Solution Analytics team. This way, we can very seamlessly and securely access your source data without needing you to add to the sheet.

Apologies for not suggesting this from the get-go! Didn't want to make you file another ticket if it wasn't necessary.

If this works for you, please share the support ticket number with me so I can make sure our team gets it right away. Thanks for your patience and collaboration!

[fivetran-jamie]

@minhdoan-eh Hi there! just bumping this ^