Changes to dbt tests following review from other teams

[jeancochrane]

Some changes that we need to make to our dbt tests:

• [x] Update class_mismatch tests so that we're only looking at res (i.e. no entry in comdat)
• [x] iasworld.comdat and excluding any PINs that have a match in comdat, is that right?
• [x] Change matching logic so that we're looking for at least one dweldat class that matches pardat
• [ ] Add a test to ensure there are no minor classes on PINs where there's a major class component
• [ ] Reach out to Brian and discuss tests we can add focused on commercial
• [x] Work with Rodrigo to add tests for land codes and CALP model <-> neighborhood mismatches
  • This needs to check a source spreadsheet against configured neighborhood parameters, but I'm not totally sure if we ingest those parameters into our warehouse yet
  • Spun off here: https://github.com/ccao-data/data-architecture/issues/319
• [x] Update land major class tests to accommodate EE/EX classes
• [x] Work with Tia to expand list of accepted values for a number of characteristics:
  • Exterior walls: 1,2,3,4,6,7,8,9
  • Basement type: 1,2.3,4,5,6,7
  • Basement finish: 1,2,3,4,5,6
  • Attic type: 1,2,3,4,5
  • Garage size: 1-10
• [x] Work with Mirella to add tests to ensure that new occupancy and proration fields are not 0
• [x] Work with Mirella to tweak tests that search for duplicates to screen for role type
  • Fail any record that has role type RR
• [x] Update the description for nbhd must be contained in town_nbhd test to make the purpose of the test clearer
• [x] Add test to ensure that prorated res PINs with cards have at least one of the cards in both PINs associated with the tieback
  • Spun off here: https://github.com/ccao-data/data-architecture/issues/322
• [ ] Work with Mirella to add test to confirm that addn changes match HIEs
  • Test that addn.value matches asmt.tot30 when addn.lline > 0
    • Question: How to join these two tables?
• [x] Update address tests to check against legdat instead of owndat
• [x] Add test to ensure entries in legdat with missing addresses have at least a 0 in address number or municipality
• [x] Add test to ensure legdat street directionals are correct
• [x] Add test to ensure that 234s have partial finished basements

[jeancochrane]

A few questions that came up for me while working through these edits:

• Update class_mismatch tests so that we're only looking at res (i.e. no entry in comdat)

This seems pretty straightforward, just moving the *_matches_pardat_class tests to a QC view joined against iasworld.comdat and excluding any PINs that have a match in comdat, is that right?

• Change matching logic so that we're looking for at least one dweldat class that matches pardat

I think we already do this due to the fact that row_values_match_after_join looks for any matches:

https://github.com/ccao-data/data-architecture/blob/bdbf827ad6a3d63248aeb2bd559570f35e625ad0/dbt/models/iasworld/schema/iasworld.dweldat.yml#L1127-L1128 https://github.com/ccao-data/data-architecture/blob/bdbf827ad6a3d63248aeb2bd559570f35e625ad0/dbt/tests/generic/test_row_values_match_after_join.sql#L38-L39

• Add a test to ensure there are no minor classes on PINs where there's a major class component

I don't really get what this means, perhaps because I don't understand the distinction between a major class and a minor class (or maybe I took bad notes). Isn't the major class just the first number (e.g. 2-) and the minor class is the subsequent numbers (e.g. -11)?

• Update land major class tests to accommodate EE/EX classes

It seems like we are maybe already doing this, no? The code in question:

https://github.com/ccao-data/data-architecture/blob/ad14e3b7cfcf47411a7a28ee269d268c1db49a1c/dbt/models/iasworld/schema/iasworld.land.yml#L316-L320

• Add test to ensure that prorated res PINs with cards have at least one of the cards in both PINs associated with the tieback

I think this has something to do with checking pardat.tieback when pardat.tiebldgpct IS NOT NULL AND pardat.ofcard IS NOT NULL, but I'm not sure how to interpret "have at least one of the cards in both PINs associated with the tieback" (probably my fault for not taking better notes).

• Update address tests to check against legdat instead of owndat

I'm not quite sure what to do here since our well-formed address tests check owndat.addr1, which seems to be a concatenation of address fields and doesn't have a direct match to any field in legdat.

• Add test to ensure entries in legdat with missing addresses have at least a 0 in address number or municipality

I assume this is referring to legdat.adrno and legdat.cityname. My guess as to the specific query we want is something like "check that adrno = 0 when adrstr IS NULL, but I'm not totally sure how cityname (or just municipality, if cityname isn't the right field) fits in here.

• Add test to ensure legdat street directionals are correct

I think we may be testing this already:

https://github.com/ccao-data/data-architecture/blob/b58f85a74e1b4aa02a05e11c4703dedbb21ed1f7/dbt/models/iasworld/schema/iasworld.legdat.yml#L27-L30

Notes from call

Some notes from our discussion on these issues:

• Display whole class instead of major class for tests that truncate
• Follow up with Ray on "no minor classes on PINs where there's a major class component"
  • E.g. A shed for a 6-unit apartment building
  • We're not really sure what this means yet though
• EE/EX classes: If pardat is exempt, we need at least one exempt land line
  • Also, no EE class
• Prorated PINs need to have cards that span all of the PINs
  • Make this it's own issue and backburner
  • We maybe need to follow up with Mirella on this, since they may have started to move to a card-level proration rate with a different tieback system
• Concat address fields for legdat well-formed address test
• Follow up with Mirella on 0 indicator for addresses
• Aim to deliver updated sheet two weeks before Rogers mail date