A generated sequence value or a timestamp usually shows the order in which records are loaded. This can provide an ordering for display purposes. But as data is loaded repeatedly, it may become difficult to preserve ordering while holding fast to the purpose of the sequence or timestamp.
An auto-incremented field or a value generated from an Oracle sequence that is a surrogate key should have no meaningful value attached to it. In practice, you might find that these types of fields are used for display purposes to establish an ordering.
As time goes on, you may be called on to re-load data or apply a data patch based on a new requirement. An example requirement is to change the ordering of records. If there is a special column -- say a DISPLAY_SEQ_NB -- devoted solely to ordering, the new requirements can be handled without having to subvert the normal generated value or timestamp mechanisms.
Take the following Talend Open Studio job as an example.
|TOS Job with Variable Preserving Ordering|
The job also records a DISPLAY_SEQ_NB which maps to a loop counter variable. The variable is set with a tSetGlobal which puts an integer in the globalMap. The integer is retrieved in the tMap ("Map Revenues") and unpacked, incremented, and re-packed in the Incr. Counter step.
Running the job results in 3 columns -- ID, LOAD_DT, DISPLAY_SEQ_NB -- that show a similar increasing order. However, if a future requirement or new data re-load operation comes up, the display sequence can be adjusted easily.