Add corner lot indicator to modeling views

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

Add corner lot indicator to modeling views #278

Closed wrridgeway closed 9 months ago

wrridgeway commented 9 months ago

Corner lots live in ccao.corner_lot but have not been added to any of the views used for modelling. Let's add it to default.vw_card_res_char and default.vw_pin_condo_char so that we can use it in the modeling pipelines.

wrridgeway commented 9 months ago

This seems to be working, though there are NULL values for certain PINs:

SELECT meta_year, count(*) AS count
FROM "z_ci_277-add-corner-lots-to-athena-views_model".vw_pin_shared_input
WHERE ccao_is_corner_lot IS NULL
GROUP BY meta_year

meta_year	count
2023	9769
2022	5317
2021	707
2020	3849
2019	8708
2018	13439
2017	17682
2016	20878
2015	24384
2014	28851
2013	33058
2012	37847
2011	43814
2010	49517
2009	51785
2008	53500
2007	55929
2006	57394
2005	63144
2004	66598
2003	68292
2002	70310
2001	70368
2000	73355
1999	74747

wrridgeway commented 9 months ago

I've got a separate issue/PR for refactoring the corner lots workflow, so I'll address buckets, etc. there. I'll work on getting this into pin_universe. Gonna keep it simple for the sake of getting it into the modeling pipeline for now.

wrridgeway commented 9 months ago

I'm going to refrain from referencing vw_pin_universe in vw_pin_shared_input for now and just grab it straight from ccao.corner_lot. We haven't referenced it there historically and there really isn't much need to now.