---
source_html: notes/etl_audit/gold_qa_audit_results_table_2026-05-29.html
synthesized: 2026-06-07
what: Results of 19 QA-audit queries against Roofing_Master_Gold_V1 snapshot 20260311 across Pasco/Hernando/Pinellas counties.
---

## Bottom line

Pinellas (12103) is severely broken. Root cause: **F1 STREET4JOIN1 78.41% corrupt** → FA_PROPERTYID unusable (B1 92.5%) and APN join unreliable (B5 71%). Reliable key for Pinellas is **PropertyID(gold)** (B3 ≤0.70%). Pasco/Hernando mostly clean on identity but both have critical date gaps (C2/C3/D4). Hernando APN is effectively absent (C3 99.99%). Geometry/FIPS/partition checks all pass.

Source: `data/permits/gold/Roofing_Master_Gold_V1_20260311`
Row counts: Pasco 1.34M · Hernando 0.75M · Pinellas 5.00M
Reproducible: `scripts/roofing/qa_gold_audit.py` (--live for S3) → `scripts/roofing/build_gold_qa_results_html.py` · Finding 90.

---

## Thresholds

| Color | Range | Status |
|-------|-------|--------|
| Green | < 1% | OK |
| Amber | 1–10% | Suspicious |
| Red | > 10% | Broken |
| n/d | APN ~100% null | Audit meaningless |

---

## QA Results Table

| ID | Audit | What it counts | Expected | Pasco 12101 | Hernando 12053 | Pinellas 12103 |
|----|-------|---------------|----------|-------------|----------------|----------------|
| A1 | Fan-out permit | Rows per BUILDING_PERMIT_ID (must be 1) | ratio = 1.0 | **1.00** ✓ | **1.00** ✓ | **1.00** ✓ |
| A2 | Duplicate permit | Groups (PERMIT_NUMBER+JURISDICTION) in >1 permit | 0 groups | **145,982** ⚠ | **76,174** ⚠ | **737,859** ✗ |
| B1 | FA_PROPERTYID → Address | FA ids linked to ≥2 distinct addresses | 0 ids | **2.23%** (4,601; worst 32) ⚠ | **0.12%** (111; worst 2) ✓ | **92.53%** (279,575; worst 3,998) ✗ |
| B2 | Address → FA_PROPERTYID | Addresses with ≥2 distinct FA ids | 0 addresses | **0.53%** (1,116; worst 18) ✓ | **3.02%** (2,595; worst 40) ⚠ | **73.71%** (307,318; worst 3,587) ✗ |
| B3 | Address → PropertyID(gold) | Addresses with ≥2 internal PropertyID (good key) | 0 addresses | **0.16%** (408; worst 9) ✓ | **0.50%** (500; worst 11) ✓ | **0.70%** (3,042; worst 67) ✓ |
| B4 | PropertyID(gold) → Address | Internal PropertyID with ≥2 addresses | 0 PropertyID | **0.00%** (0) ✓ | **0.00%** (0) ✓ | **16.91%** (61,049; worst 32) ✗ |
| B5 | Address → APN | Addresses with ≥2 PARCEL_NUMBER | 0 addresses | **15.99%** (36,576; worst 1,393) ✗ | **n/d*** | **71.20%** (254,918; worst 3,311) ✗ |
| C1 | FA_PROPERTYID empty | Permits with no linked property ([]) | 0% | **21.18%** (283,559) ✗ | **22.04%** (165,067) ✗ | **13.47%** (673,866) ✗ |
| C2 | ISSUED_DATE null | Permits with no issue date | 0% | **72.11%** (965,664) ✗ | **6.23%** (46,660) ⚠ | **52.52%** (2,628,150) ✗ |
| C3 | APN missing | Permits without PARCEL_NUMBER | 0% | **32.99%** (441,815) ✗ | **99.99%** (748,997) ✗ | **45.04%** (2,253,984) ✗ |
| C4 | No house number | STREET does not start with digit | 0% | **5.87%** (78,595) ⚠ | **2.20%** (16,496) ⚠ | **0.47%** (23,532) ✓ |
| D1 | ISSUED > COMPLETED | Issue date after completion (impossible) | 0 | **0.00%** (0) ✓ | **0.00%** (0) ✓ | **7.10%** (355,300) ⚠ |
| D2 | APPLIED > ISSUED | Application date after issue (impossible) | 0 | **0.00%** (0) ✓ | **0.00%** (0) ✓ | **12.55%** (627,883) ✗ |
| D3 | Future date | Any date > today | 0 | **0.00%** (58) ✓ | **0.00%** (13) ✓ | **0.00%** (115) ✓ |
| D4 | Final without issue | Status final/complete but ISSUED null | 0 | **59.79%** (800,714) ✗ | **0.34%** (2,562) ✓ | **40.14%** (2,008,935) ✗ |
| E1 | FIPS = COUNTY_FIPS | Row FIPS ≠ COUNTY_FIPS | 0 | **0.00%** (0) ✓ | **0.00%** (0) ✓ | **0.00%** (0) ✓ |
| E2 | Partition = FIPS | Partition prefix ≠ FIPS | 0 | **0.00%** (0) ✓ | **0.00%** (0) ✓ | **0.00%** (0) ✓ |
| E3 | ZIP out of FL | ZIP outside 32000–34999 | 0 | **0.00%** (58) ✓ | **0.00%** (13) ✓ | **0.02%** (857) ✓ |
| F1 | STREET4JOIN1 corrupt | Normalized street (join key) ≠ real street. ROOT CAUSE. | 0 | **0.00%** (0) ✓ | **0.00%** (0) ✓ | **78.41%** (3,923,983) ✗ |

*n/d: APN nearly 100% null in Hernando → audit meaningless.

---

## Key findings

- **F1 is the root cause for all Pinellas identity failures.** STREET4JOIN1 (the normalized join key) is corrupt in 78.41% of rows. This propagates to B1 (FA_PROPERTYID→address 92.53% broken) and B5 (address→APN 71.20% broken).
- **PropertyID(gold) is the reliable key** across all counties: B3 ≤0.70%, B4 clean in Pasco/Hernando (Pinellas 16.91% needs investigation).
- **ISSUED_DATE gaps are severe**: Pasco 72.11%, Pinellas 52.52% null. D4 (final status but no issue date) follows: Pasco 59.79%, Pinellas 40.14%.
- **APN is effectively absent** in Hernando (C3 99.99%) — do not use for joins there.
- **A2 duplicate PERMIT_NUMBER+JURISDICTION** is amber/red everywhere — not necessarily errors (could be multi-county overlap or Hudi bulk_insert artifact), but warrants dedup before label generation.
- Geography checks (E1, E2, E3, D3) pass across all counties.