Add new filters for ingesting sales

[wrridgeway]

default.vw_pin_sale will soon be unfiltered by default and we need to use certain conditions to make sure we don't ingest unwanted sales:

AND NOT sale.sale_filter_is_outlier
AND NOT sale.sale_filter_deed_type
AND NOT sale.sale_filter_less_than_10k
AND NOT sale.sale_filter_same_sale_within_365

[stevevance]

Hey, I'm curious about how you determine "arms length...

sale.sale_filter_is_outlier
What constitutes an outlier?

sale.sale_filter_deed_type
What deed types are you excluding?

sale.sale_filter_less_than_10k
Why did you pick $10,000? I've been using $101.

sale.sale_filter_same_sale_within_365
I get that two sales within 365 days is a clue but why is it a "hard" filtering rule?

[dfsnow]

Hi @stevevance!

"Arms-length" in this case is our best conservative estimate based on a variety of heuristics. Almost all of the work we've done on this front can be found in the ccao-data/model-sales-val repo. Note that this is a WIP effort and is by no means finalized.

sale.sale_filter_is_outlier
What constitutes an outlier?

This field is created by ccao-data/model-sales-val. It combines heuristics and statistical measures (N standard deviations from some group mean). The heuristics are things you'd probably expect (matching last name + extreme value, institutional buyer/seller + extreme value, certain PTAX-203 form answers, etc.).

sale.sale_filter_deed_type
What deed types are you excluding?

In this case, quit claims, executor deeds, and beneficiary deeds. You can see the exact SQL view definition if you're curious: https://github.com/ccao-data/data-architecture/blob/master/aws-athena/views/default-vw_pin_sale.sql#L120C16-L120C16

sale.sale_filter_less_than_10k
Why did you pick $10,000? I've been using $101.

These are the filters applied for residential modeling (so sales of improvement + land). We find that sales below $10K are rarely arms-length, but I'm happy to discuss further. Note that most of these are booted anyways by sale.sale_filter_is_outlier.

sale.sale_filter_same_sale_within_365
I get that two sales within 365 days is a clue but why is it a "hard" filtering rule?

This is somewhat confusingly named; it's actually detecting sales with the same price and PIN that have occurred within the prior year. Usually these are just data errors, hence the filtering. Note that we keep the earlier sale when we detect multiple with the same price.

[dfsnow]

@wrridgeway This is closed now that #61 is merged right?

[wrridgeway]

yes.