Road data script troubleshooting and fixes (FY25 Q2)

[MichaelAGRC]

Benefit

As road data comes in from the counties, occasionally they change their schema or add values to the data that is outside the expected domain. This causes our scripts to fail. Troubleshooting these error messages and fixing the issues will speed up the monthly road update process.

Acceptance Criteria

Getting the scripts to run after they throw an error message. This will require either altering the scripts a bit or the data.

Notes

Notes on troubleshooting script errors
The majority of the time, when a script throws an error it's because the county changed the schema. This could be a change in expected domain values or a field name change. Our county ETL scripts are hard-coded to expect certain values and when they change, the script throws an error.

The troubleshooting part is determining what changed and how to proceed. Here are some steps to take when troubleshooting and then fixing the issue:

Check the error message. It will often inform you of the unexpected value (domain change) or a missing or renamed field name.
Based on the error message, determine if it's easier to fix/update the script or the county data. Oftentimes, it's quicker to alter the county's data (fixing the wonky field values or the missing field). Other times, it's easier to alter the script to accommodate their new schema. It's a case-by-case situation.
When updating scripts, if the issue occurs in Step 1 or Step 3 you can update the script directly. If the issue occurs in Step 2, feel free to work with Greg the first couple times as these scripts are a slightly more complex, but not out of scope. Essentially, if it occurs in Step 2, you'll need to look for the county's name in UtransETL_FieldMappingFunctions.py and make the change there.

Risks

No response

Issue Reference

refs #

[MichaelAGRC]

The November 2024 data push was the first run without Greg, and it presented some learning opportunities.

It took several days to determine that after pushing the data, during the re-establishment of editor tracking I mistakenly chose to enable tracking at the dataset level. This should have been performed at the feature class level for the roads feature class. Setting editor tracking at the dataset level resulted in the 4 tracking fields being added to all tables within the dataset. This then broke the append that runs at the end of the second data preparation script.

To resolve, I ended up removing the 4 fields added to the DFC_RESULT table, which allowed the append to complete. (Schema mismatch.) Re-enabled editor tracking on the Roads feature class.