Forum Discussion
IR with no Collission
AlexisOlson AntrikshSharma GilbertQ parry2k lbendlin
TLDR: how can I ensure that incremental refresh generates a unique index (rowNum) for the data.
I am working with a sql server table and incremental refresh is baked into it. I am using sql row_number() over some datetime column to generate a unique rowNum value on the sever-side.
//pseudoquery
select colA, row_number() over (order by dateTimeCol) as rowNum from table
//datetimeCol has unique value for each ow
However, after the base table is ingested, incremental refresh essentially sends out a smaller range of table (datetimeCol>=RangeStart&&datetimeCol<RangeEnd) to refresh from the server. Due, to this everyday the Incremental refresh calculating rowNum (for that range) and it is creating collision with the previously generated rowNumField. It is duplicating the rowNum field value.
Is there a way to get past it?
Limitation: Fabric is still not authorized, so can't use any Fabric capabilities.
smpa01 I think you can rely on GUID because the chances of it being a duplicate is extremely low
https://betterexplained.com/articles/the-quick-guide-to-guids/
1/(2^128) * 1/(2^128) = 1/(2^256)
which will return 8.63617e-78 so that's 78 zeroes after decimal and then 8636 so 0.0000000000000000000000000000000000000000000000000000000000000000000000000000008636 which is very low probability of 2 GUIDs being the same.
8 Replies
smpa01 If you only want a unique column then in this case you could use SQL's NEWID() function to generate a GUID?
Thanks AntrikshSharma yes, I have thought that but with my IR being set up the way it is now, how can I ensure that it does not introduce any collision in the data, meaning how can I ensure that checksum(NEWID()) called today and checksum(NEWID()) called in perpetuity will never return a same ID as previously called.
If there is absolute cetrainty , I am totally up for it. I want to devise a solution that works for eternity without needing to be bothered about the data so that i can focus on analysis and viz. Data issue is currently holding me back and I am looking for a permanent fix for this issue.
smpa01 I think you can rely on GUID because the chances of it being a duplicate is extremely low
https://betterexplained.com/articles/the-quick-guide-to-guids/
1/(2^128) * 1/(2^128) = 1/(2^256)
which will return 8.63617e-78 so that's 78 zeroes after decimal and then 8636 so 0.0000000000000000000000000000000000000000000000000000000000000000000000000000008636 which is very low probability of 2 GUIDs being the same.
Your index rownum must be a combination of the row number inside the partition and the number of the partition.
Simplified: each partition can have up to 9000 rows, and you have multiple partitions.
<partition number>*1000 + index
Let's assume you have yearly partitions. Then the year would be your multiplier
<year>*1000000 + index
Or if you have monthly partitions
<Year>*1000000+ <Month>*10000 + index
etc
