Aug 20 (edited) • 🤓 Tech Chat
Watermark table
I have the following issue;
- In my operational db the date time is saved as datetimeoutoffset(7) in sql server (example of column name AddedAt and the format of the date 2025-06-10 14:07:21.9054118 +00:00)
- Based on this column, I gradually take new data in Fabric. I persist the last AddedAt value in the watermark table and on the next pipeline run I take all the rows that are > than the old AddedtAt value.
- In the watermark table the column is called LastProccessedValue. Is of TimestampType. What I observed is that when the AddedAt value is saved in thie column is not fully saved. For instance, in the date from above the last 8 digit is deleted also the +00:00. I know this happens because Timestamp type cannot keep the miliseconds and timezone offset.
- And now the issue that I have. In the pipeline I have a testing step. I want to make sure that the count of data from Fabric and operation untile the LastProcessedDate is equal. To check that the pipeline didn’t skipped any rows. To do this I go in the operational db with a query that filteres based on the AddedAt column and with the LastProccessedValue from the watermark. Now the issue appears. So the query will look like this: take all my data from the table x where AddedAt <= LastProccessedValue. But the LastProccesedValue is in this format 2025-06-10 14:07:21.905411. With this value from the watermark the row that has the AddedAt value from the first bullet, is skipped. And in the end my testing will fail.
- How do you handle this issue? My first idea was to store in the watermark both the timestamp value and the full version of date as string.
- Can you give me some tips and tricks for this flow? Also any tips related to how you handle the datetime struggle in Fabric will help.
- Thanks a lot in advance for reading my long post
2
2 comments
Watermark table
skool.com/microsoft-fabric
Helping passionate analysts, data engineers, data scientists (& more) to advance their careers on the Microsoft Fabric platform.
Leaderboard (30-day)
1
+27
2
+21
3
+19
4
+13
5
+12
Powered by