Typically we ingest AVs from ias into our lake only after there is a procname assigned, and it gets assigned to an appropriate stage (such as 1st pass or 2nd pass) based on the value of its procname. These data live in the default.vw_pin_value view and the default.vw_pin_value_long view, which various reporting views build upon. However, when towns are approved to mail but not yet mailed, their values are present in the table with a null procname and the value cur = 'Y'. This opens up the possibility that we can start drafting up reporting before a town mails. Let's investigate and build out the ability to ingest data while procname is null, i.e., a pre-mailed stage.
The larger vision is to ingest this data into the lake to empower internal draft analysis, diagnostics, and draft reporting in advance of a township being mailed, such that AVs could be compared between a prior certified stage and the pre-mailed stage.
From a technical perspective, implementation should look like adjusting the default.vw_pin_value view to add a new stage named something like pre_mailed. We would need to adjust the stage_values CTE to select pre_mailed_* columns with a condition like WHEN procname IS NULL AND cur = 'Y'. We'll also want to update the stage_name and stage_num calculations in the clean_values and change_reasons CTEs to include a stage indicator for PRE-MAILED with the stage number 0.
Finally, we want to make sure we exclude the pre-mailed stage from any open data assets or published reporting, since the values are provisional and should not be public until they mail. Nicole has mentioned vw_assessment_roll and vw_assessment_roll_muni as particular concerns.
Typically we ingest AVs from ias into our lake only after there is a procname assigned, and it gets assigned to an appropriate stage (such as 1st pass or 2nd pass) based on the value of its procname. These data live in the
default.vw_pin_valueview and thedefault.vw_pin_value_longview, which various reporting views build upon. However, when towns are approved to mail but not yet mailed, their values are present in the table with a null procname and the valuecur = 'Y'. This opens up the possibility that we can start drafting up reporting before a town mails. Let's investigate and build out the ability to ingest data while procname is null, i.e., a pre-mailed stage.The larger vision is to ingest this data into the lake to empower internal draft analysis, diagnostics, and draft reporting in advance of a township being mailed, such that AVs could be compared between a prior certified stage and the pre-mailed stage.
From a technical perspective, implementation should look like adjusting the
default.vw_pin_valueview to add a new stage named something likepre_mailed. We would need to adjust thestage_valuesCTE to selectpre_mailed_*columns with a condition likeWHEN procname IS NULL AND cur = 'Y'. We'll also want to update thestage_nameandstage_numcalculations in theclean_valuesandchange_reasonsCTEs to include a stage indicator forPRE-MAILEDwith the stage number 0.Finally, we want to make sure we exclude the pre-mailed stage from any open data assets or published reporting, since the values are provisional and should not be public until they mail. Nicole has mentioned
vw_assessment_rollandvw_assessment_roll_munias particular concerns.