Align sql "corrections" functionality

Many data products have "corrections" (or "research"/"changes") - programmatic changes to data products generally based on some sort of key (say, bbl) and updating a specific field from a specific old value to a new value. However, they behave slightly differently based on product and are also implemented differently (i.e. stored procedures vs more manual queries).

This still needs scoping, primarily identifying

• what products have some sort of implementation of corrections
• what use cases do they have?

From my rough understanding, it seems we need a couple things to have an abstracted correction

• target table to apply correction to
  • specified per product? field?
  • seems that it might make sense to limit corrections to final output table. though some products have final outputs, maybe stemming from same source table?
  • for each product, potentially have "set" of valid tables that corrections can be applied to? Or maybe per product and per field (i.e. "lotarea" in pluto can only be updated in the final table and not earlier, or only in a specific intermediate table)
• field
  • currently specified by corrections "row", though often then the call is made to some sort of correcting functionality to apply all corrections of a specific field at once
  • potentially restrict explicitly what fields may be corrected per product
  • logic around what type of corrections are allowed (see below)
• type of correction
  • there are a couple types of corrections - specific rows in corrections files that are applied differently
    • by key - (i.e. update lotarea for only row with bbl = 12345) - one-to-one
    • by value - (i.e. update agencyname to "NY Police Department" for all rows with agencyname = "NYPD") one-to-many
    • by key and value (i.e. update lotarea from 1 to 2 for row with bbl = 12345) - one-to-one, restricted
    • Maybe above are all variants on same thing? Single key vs multiple key becomes sort of the main issue, and whether updating multiple rows is allowed or should return error

Assuming we use above assumptions, roughly what at least the inputs to a "corrections" process could look like would be something like

## corrections.yml
key:
- bbl
fields:
- lotarea:
  - table:
    - pluto
  - allowed_update_types:
    - "key"
    - "key_and_value"
- agencyname:
  - table:
    - valid_dtm
  - allowed_update_types:
    - "key_and_value"
    - "all_values"

And then a corrections.csv file like we currently have

key,field,old_value,new_value,reason,date_added
1000070027,lotarea,,2,Incorrect lot area,20230927
,agencyname,NYPD,NY Police Department,Fix name formatting,20230927

For application, we'll need a bit more standardized functionality around applying. Roughly

• iterate through yml, apply corrections per field?
  • log corrections applied (each instance for one-to-many updates)
  • log corrections which are not applied
  • throw useful errors for any invalid corrections
• other option is to simply iterate through all corrections and apply, using yml as validation when applying

Need to think about this. Just wanted a place to jot down some thoughts for now

The issue names no files, tests, or entry points. Start by inventorying the products that already implement corrections and documenting their use cases and differing behaviors. Define the correction schema, validation rules, application behavior, and logging requirements; done means the scope and design are agreed before implementation.