Duplicate Records within scripted Source

Tina Douglass

Quick question.  If we have two records going into a script with a source that has 5 columns as the key, but we are only setting up our LK with 4 of the fields.  If, within one scripted source run, there is two records as follows, would it insert both as it doesn't know the LK exists in the previous record yet to do an update?  Should we handle it in the script to skip the 2nd record?

 

For example

Key, Field 1,2,3,4,5 in MDP

Key fields in our lk Fields 1,2,4,5

Values

Rec 1:  Field 1 = A, Field 2 =XX, field 3 =666, field 4 = XYZ, field 5 = POS, field 6 = 1000.00

Rec 2: Field 1 = A, Field 2 =XX, field 3 = 667, field 4 = XYZ, field 5 = POS, field 6 = 800.00

 

Depending on the source order, would it know to 'update' the first record to 800?  I know if Rec 1 came in one batch and Rec 2 came in another batch, it would update, but not sure if in the same batch.  

Should we handle this in the scripted source?

Diane McElhiney

If you know that the source data can contain conflicting data integrity or 'duplicates,' it is best to transform data on output to the 'survivor' row (using variation number or last modified date). The Birst Engine uses Bulk transactions to hierarchy and measure. For hierarchy processing, a checksum is used as a criteria to determine what existing rows need updating from incoming rows. For measure (if field 6 were a measure), there would be 2 rows inserted into the measure table. Every transaction to a measure table is acted upon the prior loaded rows. Therefore, if 'duplicate' rows exist from a source during a load, then those rows will be loaded as they are contained within the current load id.

You didn't state whether the data source is the Data Lake. When using Compass and selecting from the DL object, the current, active variation of the transaction row is retrieved. Therefore, saving process cycle time in Birst from determining what active row. If you need to determine 'survivor' row, then it is advised to use a Scripted Source with SELECT unique key, MAX(last modified date) ←[an example column], this staged output can be used in the main Scripted Source in a join to the staged extract data to pull all other columns for the 'survivor' row.