# 104 — Silver AE (First-American Assessor) QA battery + 3 contados (2026-06-03)

**Bottom line.** Stood up the second standing DB-input audit (after Gold #100):
a **28-check binary battery on the Silver Real-Estate-Master (REM) snapshot** —
the assessor parcel row the features read as-of T0 — across all **8 local
counties** (Pasco · Hernando · Pinellas · Miami-Dade · Orange · Duval ·
Sarasota · Maricopa-AZ; 4.81M rows). Same structure as the
Gold QA: binary pass/fail (hits the expected value exactly or fails), tabbed
dashboard (Summary · Audits matrix · **Trends** · Dictionary), accordion per
check with plain-words + two copy-paste **Athena/Trino** queries (① count +
② drill-down `LIMIT 10`) + 3 real failing rows per failing county. Verdict:
**18 of 28 audits fail** per county. Chip numbers are computed in local DuckDB
over the committed `silver_rem` snapshot (**no AWS**); the displayed queries are
the Athena equivalents against `"8020rei-silver-data-lake"."real_estate_master_v1"`
(partitions `fips` + `period`, verified live). The build was preceded by 3 warm-up "contados"
(recency/trend scoping) the client asked for first. `PropertyId` is the stable,
unique-per-row join key (~99.8% persist period-over-period); `Property_snapshot_ID`
resets every period (INTERSECT 0) and must not be used to join across periods.

## The 3 contados (last = 2026-04, prev = 2025-10)

| | Pasco | Hernando | Pinellas |
|---|---|---|---|
| C1 rows (last) | 338,237 | 121,474 | 439,835 |
| OwnerOcc = Y | 56.5% | 57.4% | 61.7% |
| mailing ≠ situs (absentee) | 43.7% | 42.1% | 37.4% |
| situs-addr null | **16.4%** | 0.03% | 0.0% |
| C2 rows (prev) | 337,642 | 120,453 | 439,917 |
| C3 persisted / +new / −gone | 335,194 / +3,043 / −2,448 | 119,659 / +1,815 / −794 | 438,959 / +876 / −958 |
| MarketValue jump >50%↑ | 1.46% | 2.42% | 0.53% |
| new sale recorded | 10,477 (3.1%) | 3,647 (3.0%) | 11,557 (2.6%) |
| **YearBuilt changed (defect)** | 203 | 276 | **766** |

Owner-flips and new-sale churn are *expected* (real transactions / move-outs) —
surfaced as trends, **not** failures (trust principle: don't call real-world
dynamics "errors"). The value jumps are mostly sentinel artifacts (see C4/G2).

## Battery — the fails that matter (exact n, Pasco / Hernando / Pinellas)

| Check | Meaning | Pasco | Hernando | Pinellas |
|---|---|---|---|---|
| **G1 YearBuilt changed** | immutable field mutated across periods → **hard defect** | 203 | 276 | 766 |
| G2 value crosses $100 sentinel | per-period sentinel write ($110 → $17,404) | 7 | 9 | 252 |
| B1 situs-addr empty | breaks address joins | 16.4% | 0.03% | 0.0% |
| B5 YearBuilt empty | kills roof-age feature | 21.6% | 23.9% | 4.6% |
| C3 AVM = 0 | "could not value" sentinel as a number | 16.5% | 11.0% | 7.9% |
| C4 MarketValue below type floor | <$1k Land & Others / <$10k structured — sentinel/nominal | 25,209 | 1,724 | 2,257 |
| F1 OO='Y' but mails elsewhere | contradiction w/ OO-at-permit rule | 4,834 | 2,504 | 10,242 |
| **H1 unit# on SFH/Land** | apt/suite on single-family or vacant land → mis-typed condo / bad parse | 191 | 141 | 1,111 |

Clean passes everywhere: A1 PropertyId fan-out (1.000), A3 PropertyId→address,
C5 AVM-in-band, C6 sale-price≥0, D1 sale-not-future, D3/D4 YearBuilt sane,
E1/E2 geo (FL ZIP + state), F2 Absentee = NOT OwnerOccupied (derivation holds).
G1 was set to **hard FAIL** per client directive — YearBuilt is immutable for an
existing house; `1951 → 2025`, `1966 → 2024` on the same `PropertyId` is the
source overwriting it (effective-year bleed / parcel reuse), not a teardown at
this volume.

## 8-county extension (2026-06-03)

Scaled from the 3 FL deep-audit counties to all 8 local counties in **15.7s**
(DuckDB, no AWS), 4.81M rows. Geo checks held up state-aware — **Maricopa (AZ)
E1/E2 = 0%** (no false FL-band fails). Standouts in the 5 added counties:
Maricopa (1.76M rows) C4 below-floor **88,901** + H1 unit#-on-SFH/Land
**16,996** (~1% of rows); Orange H1 **7,374**, C4 **27,089**; Miami-Dade
(937K rows) C4 **11,224**, G1 YearBuilt-changed 585; Sarasota H1 **3,400**.
E1 ZIP-out-of-state now fails (n=1 each on Sarasota + Maricopa — single
out-of-band ZIPs), nudging the verdict 17/28 → **18/28**.

## Assessor value is an annual step function — not a continuous signal (2026-06-03)

`MarketTotalValue` / `AssdTotalValue` are the county assessor's **annual tax
roll**, keyed to `MarketYear` / `AssdYear`. They change only when a new roll year
is published — across two silver snapshots they are byte-identical unless the roll
vintage advanced. Surfaced while debugging "0 MarketValue jumps" on the Trends tab
for Miami-Dade + Maricopa:

| County | MarketYear prev→last | Market+Assd static | AVM static | MV jump>50% |
|---|---|--:|--:|--:|
| Pinellas | 2024 → 2025 | 2.5% | 5.9% | 2,280 |
| Miami-Dade | 2025 → 2025 | **100%** | 3.4% | 1 |
| Maricopa | 2025 → 2025 | **100%** | 6.8% | 0 |

**Not a bug.** Both counties were already on the 2025 roll at the 2025-10 pull and
hadn't published 2026 by 2026-04 → assessor value frozen → 0 jumps **by design**.
Sales + owner fields still updated (Miami-Dade 16.9K / Maricopa 45.7K new sales),
so the snapshot is live; only the annual roll is static. `CurrentAVMValue` (First
American's own estimate) updates every snapshot in all 8 counties (static 3–7%).
Roll vintages observed: Pasco/Orange/Duval 2025→2026 · Hernando/Pinellas/Sarasota
2024→2025 · Miami-Dade/Maricopa 2025→2025 (frozen).

**Implications:** (1) period-over-period value movement on Market/Assd is only
meaningful across a roll-year boundary — use **AVM** for continuous movement; (2)
the Trends tab now carries a **Roll vintage (MarketYear prev→last)** row that marks
unchanged vintages `(frozen)`, so a 0 MV-jump reads as expected, not broken; (3) for
the model, treat Market/Assd value as a year-stamped step, not a monthly series.

## Artifacts

- Engine: `scripts/roofing/silver_qa/qa_silver_assessor.py` (battery, JSON + examples)
- Contados: `scripts/roofing/silver_qa/contados_silver_assessor.py`
- Dashboard builder: `scripts/roofing/silver_qa/build_silver_qa_dashboard.py`
- Live (internal, gated): `/internal/silver-ae-qa-2f9a1c7e4b81/` · Audits hub card flipped LIVE
- Results: `data/sandbox/silver_qa/{qa_silver_results,qa_silver_examples,contados_*}.json`
- Reproduce: `uv run python scripts/roofing/silver_qa/qa_silver_assessor.py && uv run python scripts/roofing/silver_qa/build_silver_qa_dashboard.py`

## Property-type filter — battery now runs per UseType bucket, SFH-default (2026-06-04)

Added a **property-type filter** to the dashboard at the client's request: the
whole 28-check battery (+ Trends + examples) is now precomputed for **13 buckets**
— `all` plus the 12 distinct `UseType` values — and the page defaults to **SFH**,
the type the roofing model actually trains on. The selector re-renders the verdict,
matrix chips, summary, Trends and the two copy-paste Athena queries (each carries the
active `AND lower(usetype) = '<type>'` clause so it still reproduces the chip).

`UseType` partitions the population exactly — probed across 8 counties / last period:
**0 null, 0 whitespace**, 12 buckets summing to the `all` row in every county. Volume
(8-county, last period): SFH 2,879,958 (60%) · Condo 725,063 · Others 397,576 · Land
347,949 · Townhouse 153,164 · Mobile Home 125,958 · 2-9 units 88,737 · Commercial
61,119 · Warehouse 24,750 · Multi-family 5,575 · Mobile Home Park 2,441 · Self-Storage
2,164.

**SFH is materially cleaner than the whole roll on the feature-critical columns** —
the default view is the honest one for the model:

| Check | ALL (Pinellas) | SFH (Pinellas) | where the gap lives |
|---|--:|--:|---|
| B5 YearBuilt empty | 4.63% | **0.0%** (5 rows) | empties are Land/Others, not houses |
| H1 unit# on SFH/Land | 1,111 | 304 | the 807 balance were Land rows |
| C4 below type floor | 2,257 | 2 | nominal-value sentinels are non-SFH |
| G1 YearBuilt changed | 766 | 275 | immutable-field defect, still present on SFH |

H1 is type-aware by construction: filtered to a type ∉ {sfh, land} (e.g. Condo) it is
0 by design — a unit number on a condo is legitimate. The per-type verdict drops from
**18/28 fail (ALL)** to **17/28 (SFH)** / **16/28 (Condo)**.

**Engine change:** `g(period, fips, tp)` now wraps the parquet read in
`(SELECT * FROM read_parquet(...) WHERE <usetype pred>)`, so the bucket filter flows
into every FROM/JOIN/FILTER unchanged; `main()` loops fips × `TYPES`. A2/A3/A4
`propertyids` example blobs are clipped (one Maricopa A2 id-list was 1.2 MB = 98.6% of
the old 2.4 MB examples payload) → examples JSON **2.4 MB → 876 KB**, dashboard HTML
**2.5 MB → 621 KB** despite 13× the data. Builder rewired from server-side Python
string-building to a small client that renders the active (property-type × counties)
view from one embedded JSON payload.

**Verified (2026-06-04):** ALL-bucket = exact regression vs this finding (G1 766, H1
1,111, C3 7.91%, B5 4.63%); 12 independent raw-SQL recomputes across types pass
(incl. type-dependent C4 floor + G1 within-bucket join); headless Playwright render —
default SFH, type-switch updates verdict/chips/queries, ALL injects no clause, county
toggle works, 0 console errors.

## Multi-select property type + yellow-threshold slider (2026-06-04)

The property-type control is now a **multi-select** (checkbox dropdown, per-type
row counts, Select-all / SFH-only); the cell colour is a **three-tier** green (0) /
yellow (≤ threshold) / red (> threshold) with the **threshold on a live slider**
(12 stops 0…100k, default 1k; 0 = binary green/red). All three render client-side
off the same per-type buckets.

**Combining buckets — what's exact vs approximate.** Types partition the rows, so for
a selected *set* every **row-count check is exact** as the additive sum: verified
`A_rows` (SFH+Condo Pinellas 340,129) and B5 (120) against a fresh
`lower(usetype) IN ('sfh','condo')` query — both match. The **three `rel` checks
(A2/A3/A4 address→PropertyId fan-out) are NOT additive**: the true union counts
addresses that fan out *across* the selected types, which the per-type sum misses
(SFH+Condo A2 = 807 true vs 767 summed — 40 cross-type address collisions). So a
multi-type selection shows A2/A3/A4 as a per-type sum marked **`≈`** (tooltip:
pick a single type or All for the exact figure); a single type and the full 12-type
union both route to an exact precomputed bucket (no `≈`). Roll-vintage mode shows
`mixed` if the selected buckets disagree (they don't within a county — the assessor
roll is county-level).

## Open questions / next

- Correlate G1 YearBuilt-change against `EffectiveYearBuilt` to confirm
  reassessment-bleed vs true parcel-reuse defect.
- Pasco's 16.4% situs-addr gap — is it an ETL parse drop (mailing present,
  situs null) or a source gap? Mirror of the Gold C4 Pasco pattern.
- Extend to the other 5 local model counties (Miami-Dade / Orange / Duval /
  Sarasota / Maricopa) + add a cross-county national-sanity column like Gold.
- Wire a `rerun_silver_qa.sh` (mirror `rerun_gold_qa.sh`) for one-command refresh.