External Data Audit and Sync

[jonbry]

Our current script for pulling data from GreenIT checks for new data or changes to existing data, but it doesn't make sure our data matches GreenIT completely. There have been cases where systems have been deleted from GreenIT but still exist in our database since we don't check to makes sure our data matches GreenIT.

This has only happened 29 times, but we want to make sure we're designing our systems with this in mind.

@monica-water @ryanebra Should we be deleting this data or should we be relying on a status table for these records?

[monica-water]

I believe we should delete this data. It’s possible that the old SMP IDs get reused, and it sounds like this is unlikely to happen post-construction anyway.

I can write a new section on the GreenIT script that checks to make sure we won’t orphan any fieldwork or water level data before deleting from the SMP/System/Project data tables, and then delete the records that no longer appear in GreenIT.

[jonbry]

Based on this issue from the annual report, my suggestion is to mark values as active/deactive for deleting because of this issue where SMPs don't match up from one year to the next:

@ryanebra Do you want to weigh in too? I would like to be able to reconcile these without looking through each backup

[jonbry]

The previous issue resulted in a change of values, which really is another reason for us to keep track of changes and make comparisons: