Azure / Azure/Azure-DataFactory

Copy data activity loses factorial seconds

Open
#72 7 comments 0 reactions 0 assignees View on GitHub
Dominant language
PowerShell
Stars
529
Forks
623
PR merge metrics
No merged PRs in 30d

Description

We have simple pipeline for incremental copying of data from one db to another one. We use the approach you have described in documentation: https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-multiple-tables-portal

Basically we have watermark table, we pull old watermarks and new ones to find the delta. Then we call Copy Data which makes the call of stored procedure in Azure SQL Server with watermarks as parameters. Then we update watermark table by using StoredProcedure activity.

Our watermarks have DateTimeOffset type. Copy Data activity loses factorial seconds meaning:
Value like `2018-12-17 14:02:47.1696724 +00:00`
becomes like this `2018-12-17 14:02:47.0000000 +00:00`

This causes the issue when we miss some data if it was added same second but a bit later.
At the same time StoredProcedure activity works with DateTimeOffset properly without any loss.

It doesn't sound like a huge issue, but it was tricky to find this misbehavior. Actually, when our users reported this issue it sounded like a fiction :)

We have two workarounds to avoid this issue:
1. increment the value of watermark - basically round up the value (ceiling)
2. instead of taking last watermark value from target database table, we use current datetimeoffset value as a watermark

Bu I believe this either should be fixed in CopyData activity or at least in mentioned in documentation.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.