Add data tests for HIE fields

[Damonamajor]

This produces three tests regarding the tot_30 field.

asmt_all_tot30_less_than_7500

• Since the exemption is capped at 7,500 there should be no greater values. This results in 1213 failures, the vast majority of which are exempt buildings (a way of reducing their value to 0) and are prior to 2023.

iasworld_asmt_all_tot30_matches_oby_ovrrcnld_divided_by_10

• My assumption is that tot30 is a derivative of oby.ovrrcnld (it's tough to know for sure). Because it is the value of the exemption, it is divided by 10 to produce the assessed value. It results in 19 failures. Values > 7500 are removed since they should be recoded to an exact number rather than a percentage of the raw number.

iasword_asmt_all_32_equals_29_minus_30_minus_31

The equation written out is

ASSD TOT -(HIE-DVHE) = ASSD TOT EXC AG - HIE - DVHE

• This results in 17 errors when unique conditions are not applied, and 0 when those conditions are applied. Writing the test as accurately written (negative within the parentheses) results in 81 errors. I also remove final values which are 0, since the exemption will subtract to a negative number.

[wrridgeway]

@dfsnow Have we talked to Mirella about these assumptions? I'm a little wary of incorporating iasworld dbt tests based on a hunch. I'm happy to be the one who reaches out.

[dfsnow]

@dfsnow Have we talked to Mirella about these assumptions? I'm a little wary of incorporating iasworld dbt tests based on a hunch. I'm happy to be the one who reaches out.

Not yet, it's on my list of stuff to talk to her about.

[Damonamajor]

@dfsnow Here are the errors when compiling the test for tot30 being 10% of oby.ovrrcnld 733a0ed3-8349-4aeb-ad12-df18d6da0d2f.csv

[Damonamajor]

@dfsnow Here's a sample of 10 pins pulled from the following query

SELECT parid, tot30
FROM (
    SELECT parid, tot30,
           ROW_NUMBER() OVER (PARTITION BY parid ORDER BY tot30 DESC) as rn
    FROM iasworld.asmt_all
    WHERE tot30 > 7500
) subquery
WHERE rn = 1
LIMIT 10;

d000cbc2-fc55-4c37-9f05-4a834f477270.csv

[dfsnow]

@Damonamajor Please wrap use markdown code blocks when commenting with code.

Also, you seem to have accidentally committed the DHI index script to this branch.

I can close out this branch myself after confirming some stuff with Mirella.

[wrridgeway]

I reached out to Mirella, and tot30 is indeed HIE AV amount

[dfsnow]

@wrridgeway @jeancochrane this should be set to go. It has no failures for the new tests (at least in TY 2024).