Azure / Azure/Azure-DataFactory
Copy data activity loses factorial seconds
- 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
Assessment
This issue has not been assessed yet.