Daily Incremental Refresh

[kahn_ohara]

I am working with large snowflake data and hence would like to use incremental refresh. The data I'm working with has several columns with the key ones being create_timestamp, update_timestamp and unique_identifier (this should be the Primary key of the table after the data is refreshed). Overall, I'd like to provide 3 years worth of data from the refresh date based on create_Ts. Old data (based on create_ts) gets updated but only rarely. Otherwise only newer data gets updated. I'd like to refresh the data daily.

In an ideal world, I'd want to to perform these 4 steps

1. query the results with "where date(update_ts)=refresh date" to minimize database load/runtime. Load this data to Power BI.

2. have power bi append the results in step 1 with the existing historical data/table in power bi. Call this TABLE_A.

3. In power bi, for each unique_identifier in TABLE_A., keep only the record with the latest update_Ts and drop all others.

4. In power bi, apply filter based on create_Ts to last 3 years to TABLE_A. 

I reviewed Power BI's incremental refresh documentation and considered using an incremental refresh policy with "Set incremental period to 1 day prior to refresh" and archival period of 3 years based on update_ts. Would this accomplish steps 1 and 2 described above? Based on microsoft documentation it seems like this policy isn't recommended, though, and that we're supposed to use create_ts for the incremental refresh/archival period and not update_Ts. Can anyone expalin why? 

Everything created in the last 3 years is a subset of everything updated in the last 3 years so there's no issue there. When a record is first created create_Ts=update_Ts.

[lbendlin · Accepted solution]

considered using an incremental refresh policy with "Set incremental period to 1 day prior to refresh" and archival period of 3 years based on update_ts. Would this accomplish steps 1 and 2 described above?

yes.  

If you use update_ts instead of create_ts  you will end up with duplicates of your rows across multiple partitions, and you will have to include occasional partition refreshes in your process (or consider using the "track changes" feature - at an enormous storage cost - to handle changes for you).

[kahn_ohara]

@lbendlin can you explain how the track changes feature works on the backend? Couldn't find any documentation.

[lbendlin · Accepted solution]

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

[lbendlin · Accepted solution]

considered using an incremental refresh policy with "Set incremental period to 1 day prior to refresh" and archival period of 3 years based on update_ts. Would this accomplish steps 1 and 2 described above?

yes.  

If you use update_ts instead of create_ts  you will end up with duplicates of your rows across multiple partitions, and you will have to include occasional partition refreshes in your process (or consider using the "track changes" feature - at an enormous storage cost - to handle changes for you).