Update class code cleaning to allow new omitted assessment class codes

[wrridgeway]

The office introduced new class codes this year that start with "OA". Our current syntax for cleaning class codes (REGEXP_REPLACE(par.class, '([^0-9EXR])', '') AS class) only preserves the number after 'OA'. We should also stop wiping out the A and B suffixes for incentive classes.

Some tables have already been updated in a previous PR.

[wrridgeway]

Here are basic new vs old row counts for all the views that have been changed. Results should only appear if there are any differences in row count within a year.

vw_pin_appeal

with old as (
select year, count(*) as "old" from default.vw_pin_appeal
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_default".vw_pin_appeal
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_pin_condo_char

with old as (
select year, count(*) as "old" from default.vw_pin_condo_char
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_default".vw_pin_condo_char
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_pin_exempt

with old as (
select year, count(*) as "old" from default.vw_pin_exempt
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_default".vw_pin_exempt
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_pin_sale

with old as (
select year, count(*) as "old" from default.vw_pin_sale
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_default".vw_pin_sale
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_pin_universe

with old as (
select year, count(*) as "old" from default.vw_pin_universe
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_default".vw_pin_universe
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_pin_shared_input

with old as (
select meta_year, count(*) as "old" from model.vw_pin_shared_input
group by meta_year
),
new as (
select meta_year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_model".vw_pin_shared_input
group by meta_year
)

select new.meta_year, new.new, old.old from old
left join new
on new.meta_year = old.meta_year
where  new.new != old.old

no results

vw_ratio_stats

with old as (
select year, count(*) as "old" from reporting.vw_ratio_stats
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_reporting".vw_ratio_stats
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_top_5

with old as (
select year, count(*) as "old" from reporting.vw_top_5
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_reporting".vw_top_5
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_code_retrieval

with old as (
select year, count(*) as "old" from rpie.vw_code_retrieval
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_rpie".vw_code_retrieval
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_code_retrieval

with old as (
select year, count(*) as "old" from rpie.vw_code_retrieval
group by year
),
new as (
select year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_rpie".vw_code_retrieval
group by year
)

select new.year, new.new, old.old from old
left join new
on new.year = old.year
where  new.new != old.old

no results

vw_pin_mailers

with old as (
select rpie_year, count(*) as "old" from rpie.vw_pin_mailers
group by rpie_year
),
new as (
select rpie_year, count(*) as "new" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_rpie".vw_pin_mailers
group by rpie_year
)

select new.rpie_year, new.new, old.old from old
left join new
on new.rpie_year = old.rpie_year
where  new.new != old.old

no results

[wrridgeway]

There's a lot to review here... open to any suggestions as to how to better test the implications of these changes.

[wrridgeway]

This is a breakout of all the new and old classes and their counts.

with old as (
select class, count(*) as "old count" from default.vw_pin_universe
group by class
),
new as (
select class, count(*) as "new count" from "z_ci_341-update-class-code-cleaning-to-allow-new-omitted-assessment-class-codes_default".vw_pin_universe
group by class
)

select new.class as "new class", old.class as "old class", "new count", "old count" from old
full outer join new
on new.class = old.class
order by "new class"

complete new vs old class breakout

[dfsnow]

@wrridgeway What's the status on this PR?

[wrridgeway]

Added some more dbt tests for columns that can, but shouldn't be null. It should be ready for review now.