McDash Schema Mapping
Field-level mapping between CLIMA PFAS risk output and ICE McDash database tables. All CLIMA fields are designed to join cleanly with existing McDash loan and property tables.
| CLIMA Field | McDash Table | McDash Column | Join Key | Type | Description |
|---|---|---|---|---|---|
| pfas_risk_score | property_risk | pfas_score | property_id | int (0-100) | Composite PFAS financial risk score. 0 = negligible, 100 = severe. |
| expected_loss_delta_bps | loan_risk | pfas_el_adjustment_bps | loan_id | float | Additional expected loss attributable to PFAS, in basis points. |
| value_decline_pct | property_risk | pfas_value_decline | property_id | float (0.0-0.20) | Estimated property value decline due to PFAS proximity and contamination probability. |
| adjusted_ltv | loan_risk | pfas_adjusted_ltv | loan_id | float | Loan-to-value ratio recalculated using PFAS-adjusted property value. |
| contamination_probability | property_risk | pfas_contam_prob | property_id | float (0.0-0.95) | ML-predicted probability that the serving water system has PFAS above detection limits. |
| cercla_risk.tier | property_risk | pfas_cercla_tier | property_id | int (1-4) | CERCLA regulatory risk tier. 1 = critical, 4 = minimal. |
| water_system.any_pfas_detected | property_risk | pfas_ws_detected | property_id | boolean | Whether the serving public water system has confirmed PFAS detections (UCMR 5). |
Join Key Convention
property_id is the primary join key for property-level fields and matches the ICE property identifier used across McDash tables. Loan-level fields join on loan_id, which maps to the McDash loan sequence number. Both keys are provided in every CLIMA delivery file.
Score Scale
The pfas_score column uses a 0-100 integer scale, matching the convention established by ICE Climate Risk scores (Fire Factor, Flood Factor, etc.). This allows downstream consumers to apply the same thresholding logic and reporting templates already in use for climate risk.
Null Handling
pfas_contam_prob may be NULL for properties outside public water system coverage (private wells). In this case, pfas_score is computed from proximity features only and a water_system_coverage flag is set to 'none_detected'.