Mutable data refresh

[smpa01]

@lbendlin 

I am working with a mutable dataset that is coming from an app where the users are allowed to make changes to a Transaction ID within a certain time frame (let's say 6 months). So for a particular transaction id it may look like this

| Transaction ID 	| value 	| activity_date  	|
|----------------	|-------	|-----------------	|
| 1              	| 100   	| 5/30/2024 23:48 	|
| 1              	| 200   	| 5/31/2024 23:48 	|
| 1              	| 300   	| 6/1/2024 23:48  	|
| 1              	| 400   	| 6/2/2024 23:48  	|
| 2              	| 1000  	| 5/29/2024 23:48 	|
| 2              	| 2000  	| 5/28/2024 23:48 	|
| 2              	| -1000 	| 5/27/2024 23:48 	|

 My ultimate goal is to able to track each Transaction ID - value combination [as oppose to retrieve Transaction ID - value combination by max(activity_date) group by Transaction ID] so that I can track change of values by Transaction ID over activity date in the analysis (historical tracking). To simplify, I want a dataflow that would have each Transaction ID-value -activity_date rows.

If I set up incremental refresh on the dataflow with the following setting, can I achieve what I have in mind?

Thank you in advance

[lbendlin]

no, the incremental refresh date cannot be the same as the change detection date.

Please note that checking the "Detect changes"  box will effectively double the size of your semantic model in the workspace and capacity.

[smpa01]

@lbendlin I am doing this in a dataflow and my end goal is to keep all transaction rows like I mentioned above.

Please note that checking the "Detect changes" box will effectively double the size of your semantic model dataflow -  Do I understand that with "Detect Changes" being ticked, the dataflow returns the following row tomorrow

| Transaction ID 	| value 	| activity_date  	|
| 1              	| 200   	| 5/31/2024 23:48 	| -- brought in by IR from 2024-05-31

and the following rows day after tomorrow (if a change took place by the activity_date, bring that row, which could be a complete new Transcation ID or update of a same Transaction ID)

| Transaction ID 	| value 	| activity_date  	|
| 1              	| 200   	| 5/31/2024 23:48 	| -- brought in by IR from 2024-05-31
| 1              	| 300   	| 6/1/2024 23:48  	| -- brought in by IR from 2024-01-06

 if that is the case this is what I want or there is something else due to this setting that will inflate the model that I am missing out. Please le tme know

Also, if I understood you correctly, checking "Detect Changes" for immutable data has no such effect at all. Cause there is change to detect because all the past lines are immutable/prevented from editing?

[lbendlin]

The canary copy is not accessible to you, it is used internally to compare data in all partitions against the data source. If changes in the indicated field are detected that will initiate a refresh of all affected partitions (not just the latest ones).  Think of it as a hybrid between full refresh and incremental refresh, ie "refresh all partitions with changes"  to avoid the scenario you are listing (transaction ID 1 appearing in more than one partition)