Technique for Ordering Records in Talend Open Studio

With a load timestamp or auto-incremented counter, you can often infer a record ordering.  However, for maintenance purposes, you may want to break out the ordering data.

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

This job loads Access tables from Excel spreadsheets.  The Access tables have auto-incremented surrogate keys that establish record identity.  Each record added to the tables also records a LOAD_DT which is set using TalendDate.getCurrentDate().

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.