DataConv script writes duplicate components with unique facility IDs

[monica-water]

Some queries report duplicate values in fields that are probably not supposed to be duplicated in the DB.

select count(*) from external.tbl_gswiconveyance group by component_id returns duplicates, but select count(*) from external.tbl_gswiconveyance group by facility_id does not. Overlapping lifecycle statuses are all the more puzzling.

Such duplicates may suggest improper handling by the script. End users have not reported duplicated component IDs, though, so I am surprised to find this 1:many relationship in the DB.

More investigation to come.

[monica-water]

Queries directly on the underlying DataConv tables confirm the existence of duplicate component IDs, with different facility IDs, and overlapping lifecycle statuses. Some metadata fields (such as pipe length or material) differ between them. I will make contact with GSO for guidance on detecting the canonical record, and make a plan to implement a fix based on what they say.