Forum Discussion
Helper IIBest Practice: Order Data via API that contains an Order Date and a Last Modified Date
I have order data that contains an Order Date, and a Last Modified Date. Order Data periodically updates as orders progress through the system as shipment status changes etc. Typically for a month or so after order date. Data is accessed via API and can be filtered by Order Date or Last Modified Date.
In terms of pure efficiency, it seems to me that after the initial table load, once should only be refreshing the table by filtering for order data from the api where the Last Modified date is after whatever the latest last modified date is in the table.
Is this possible, and can someone provide a high level overview of how I'd go about it?
Many thanks
but how do I setup an incremental refresh on a value that is changing?What you are looking for is differential refresh, not incremental refresh. Incremental refresh assumes that your data is largely immutable, and it can only work on partition level, not row level.
How you handle that is up to you. Think of it in terms of cold-warm-hot data. You refresh the hot partitions frequently to capture all changes to recently created rows. You refresh the warm partitions occasionally to capture the changes to older rows. You refresh the cold partitions if you know for sure that a row there has changed.
Consider using SCD instead.
3 Replies
- lbendlin
Super User
This is possible but only makes sense if the API source is actually optimized for queries filtering on Last Modified Date. Let's say the source is a SQL server table, and there is no (maintained) index on that column - then it will be a slow as no filter at all.
You will also want to keep the option open to periodically flush and fill in case you missed some updates, or the query bombed halfway through etc.
ChaucerThanks lbendlin ,
It's indexed, so no issue there.
Big picture, how do I do that? I get how to setup an incremental refresh based on the order data, because that is going to be fixed, but how do I setup an incremental refresh on a value that is changing? Or am I not actually doing an incremental refresh? Do I not then run the risk of introducing duplicate orders everytime the order updates?
Easiest way to setup a flush and fill? Just via desktop and manually updating that Last Modified value?- lbendlin
but how do I setup an incremental refresh on a value that is changing?What you are looking for is differential refresh, not incremental refresh. Incremental refresh assumes that your data is largely immutable, and it can only work on partition level, not row level.
How you handle that is up to you. Think of it in terms of cold-warm-hot data. You refresh the hot partitions frequently to capture all changes to recently created rows. You refresh the warm partitions occasionally to capture the changes to older rows. You refresh the cold partitions if you know for sure that a row there has changed.
Consider using SCD instead.