ccao-data / data-architecture

Codebase for CCAO data infrastructure construction and management
https://ccao-data.github.io/data-architecture/
6 stars 4 forks source link

Investigate and document `external_propct` fields #392

Open dfsnow opened 5 months ago

dfsnow commented 5 months ago

Tax year 2024 introduced new proration fields for both res and commercial properties. We haven't had to fully use or handle these yet, but they're starting to come up. We should chat with MV about the structure and constraints the fields, and get an inventory of which fields actually changed.

Additional context from dbt testing PR:

I tried a few different ways of joining dweldat to pardat and filtering for only PINs with tiebacks but none of them seemed to produce a result set that made sense to me. I'm wondering if maybe my interpretation of the suggestion is incorrect; it seems a bit weird to be comparing a proration rate set on the dwelling level against a tieback set at the parcel level, which is making me think that perhaps I'm getting two different types of proration mixed up. Let me know if I'm misunderstanding the suggestion, and if not (meaning that the join is indeed just very complex) I can split this out into a separate issue and kick it over to you.

wrridgeway commented 4 months ago

@jeancochrane One of the issues that makes this mind-numbingly frustrating is that our tieback pins are not dependable. If you look inside pardat you'll find sometimes multi-pins reference each other as tiebacks, rather than solely referencing the tieback pin as a tieback. Here's an example:

select
    parid,
    tieback
from iasworld.pardat
where parid IN ('17332080040000', '17332080030000')
and taxyr = '2024'
and cur = 'Y'
and deactivat IS NULL
parid tieback
17332080030000 17332080040000
17332080040000 17332080030000

This makes it impossible to group or join things on tieback pin and come out with clean data.