Need feedback from Product on how to handle real world csv errors

[jirhiker]

I need guidance on how to handle all of the issues in the well-inventory-template.

questions

1. should we assume 0 if mp_height is blank?
2. should we auto calculate elevation if elevation_ft is blank?
3. should we add "Monitoring Complete" to "monitoring_frequency" lexicon category?
4. should rows with blank well_name_point_Id trigger an autogenerated name? what prefix should be used by default NM-?
5. how should we handle blank depth_to_water_ft? Should it be nullable or 0, etc?
6. should rows without issues be imported or does the entire CSV need to be correct before any wells are imported?

@marissafichera @Rachel-Hobbs @savenoch

https://docs.google.com/spreadsheets/d/13_g0dSK4iHBAEGYYT5Af3mQbW0S0Sj_WAh0r1PxkMqk/edit?usp=sharing

oco well-inventory-csv tests/features/data/well-inventory-real-user-entered-data.csv
[WELL INVENTORY IMPORT] COMPLETED WITH ISSUES
========================================================================
SUMMARY
  ---------------------------
  processed        |      129
  imported         |        0
  rows_with_issues |      129

VALIDATION
Validation errors: 607
Most common validation errors:
   # | field                        | count | error
  ------------------------------------------------------------------------------------------------------------------------------------------------
   1 | mp_height                    |   112 | Input should be a valid number, unable to parse string as a number
   2 | elevation_method             |   103 | Input should be 'Altimeter', 'Differentially corrected GPS', 'Survey-grade GPS', 'Global...
   3 | elevation_ft                 |    99 | Input should be a valid number, unable to parse string as a number
   4 | monitoring_frequency         |    82 | Input should be 'Monthly', 'Bimonthly', 'Bimonthly reported', 'Quarterly', 'Biannual', 'Annual',...
   5 | depth_to_water_ft            |    68 | Input should be a valid number, unable to parse string as a number

  Row 1 (8 issues)
    1. utm_easting: Input should be a valid number, unable to parse string as a number
       input:
    2. utm_northing: Input should be a valid number, unable to parse string as a number
       input:
    3. elevation_ft: Input should be a valid number, unable to parse string as a number
       input:
    4. elevation_method: Input should be 'Altimeter', 'Differentially corrected GPS', 'Survey-grade GPS', 'Global positioning system (GPS)', 'LiDAR DEM', 'Level or other survey method', 'Interpolated
                         from topographic map', 'Interpolated from digital elevation model (DEM)', 'Reported', 'Survey-grade Global Navigation Satellite Sys, Lvl1', 'USGS National Elevation Dataset
                         (NED)' or 'Unknown'
       input:
    5. measuring_point_height_ft: Input should be a valid number, unable to parse string as a number
       input:
    6. monitoring_frequency: Input should be 'Monthly', 'Bimonthly', 'Bimonthly reported', 'Quarterly', 'Biannual', 'Annual', 'Decadal' or 'Event-based'
       input: Monitoring complete
    7. mp_height: Input should be a valid number, unable to parse string as a number
       input:
    8. depth_to_water_ft: Input should be a valid number, unable to parse string as a number
       input:

  --------------------------------------------------------
  Row 2 (4 issues)
    1. elevation_ft: Input should be a valid number, unable to parse string as a number
       input:
    2. elevation_method: Input should be 'Altimeter', 'Differentially corrected GPS', 'Survey-grade GPS', 'Global positioning system (GPS)', 'LiDAR DEM', 'Level or other survey method', 'Interpolated
                         from topographic map', 'Interpolated from digital elevation model (DEM)', 'Reported', 'Survey-grade Global Navigation Satellite Sys, Lvl1', 'USGS National Elevation Dataset
                         (NED)' or 'Unknown'
       input:

... and 597 more validation errors
========================================================================

[github-actions]

Linked Jira ticket created: BDMS-560

[ksmuczynski]

@jirhiker I’ve had similar questions while running import tests on the AMP well inventory data. My thoughts are below.

1. Should we assume 0 if mp_height is blank?

I would defer to AMMP, but in my opinion, yes.

2. Should we auto calculate elevation if elevation_ft is blank?

Again, I'd defer to AMMP, but in my opinion, yes.

3. Should we add "Monitoring Complete" to "monitoring_frequency" lexicon category?

No. In my opinion,Monitoring Complete is more indicative of a Monitoring Status than a Monitoring Frequency and should be mapped as follows: StatusHistory.status_type = Monitoring Status and StatusHistory.status_value = Not currently monitored. Alternatively, we could add Monitoring Complete to the status_value lexicon, but I would first ask AMMP to clarify the meaning of Monitoring Complete and see how/if it differs from Not currently monitored. For additional context, the definition of Not currently monitored is a well not currently being monitored by AMMP.

4. Should rows with blank well_name_point_Id trigger an autogenerated name? what prefix should be used by default NM-?

I'm not sure how I feel about auto-generated names, especially if we decide that complete records can be imported despite errors still existing in the csv import file. In terms of naming conventions, I was recently thinking that county abbreviations might be a good choice moving forward.

5. How should we handle blank depth_to_water_ft? Should it be nullable or 0, etc?

It should be nullable with a flag/qualifier. Currently, the Observation model uses groundwater_level_reason to provide context for a NULL value. Side note, I would like to generalize this at a later time to field_qualifier and add another lab_qualifier field.

6. Should rows without issues be imported or does the entire CSV need to be correct before any wells are imported?

Yes, I think rows without issues should be imported. The entire CSV does not need to be correct before data is imported. It would also be useful to have a log/message at the end noting the number of records imported out of total number of records (e.g. 12 records imported out of 20 records). A separate, additional error log would also help point users in the right direction to fix any issues.

[jirhiker]

@savenoch ☝️