RAP (Rapid Analytics Platform) Integration

Ready-to-run SQL queries for ICE's RAP environment. These snippets demonstrate how to join CLIMA PFAS risk data with McDash loan and security tables for common analytics workflows.

1. Basic Loan Enrichment

Join CLIMA PFAS scores onto the McDash loan table to identify loans backed by properties with elevated contamination risk. Filters to scores >= 50 (moderate-to-high risk).

sql

SELECT
    l.loan_id, l.property_id, l.current_upb,
    p.pfas_score, p.pfas_contam_prob, p.pfas_value_decline,
    p.pfas_cercla_tier
FROM mcdash.loans l
JOIN clima.pfas_property_risk p ON l.property_id = p.property_id
WHERE p.pfas_score >= 50
ORDER BY p.pfas_score DESC;

2. Security-Level Aggregation

Aggregate PFAS risk from the property level up to the CUSIP level for MBS analysis. Computes the total dollar expected-loss delta across all loans in each security, enabling portfolio-level risk screening.

sql

SELECT
    s.cusip,
    COUNT(*) AS total_loans,
    COUNT(CASE WHEN p.pfas_score >= 50 THEN 1 END) AS high_risk_loans,
    AVG(p.pfas_score) AS avg_pfas_score,
    SUM(l.current_upb * p.pfas_el_adjustment_bps / 10000) AS total_el_delta_usd
FROM mcdash.securities s
JOIN mcdash.loan_to_security ls ON s.cusip = ls.cusip
JOIN mcdash.loans l ON ls.loan_id = l.loan_id
JOIN clima.pfas_property_risk p ON l.property_id = p.property_id
GROUP BY s.cusip
HAVING AVG(p.pfas_score) > 20
ORDER BY total_el_delta_usd DESC;

3. 30-Year Regulatory Scenario

Project PFAS risk forward under a regulatory tightening scenario (stricter MCLs, expanded CERCLA scope, increased enforcement). Surfaces loans where the projected expected-loss delta exceeds 50 bps over a 30-year mortgage horizon.

sql

SELECT
    l.loan_id,
    t.horizon_years,
    t.scenario,
    t.pfas_risk_score AS projected_score,
    t.expected_loss_delta_bps AS projected_el_bps
FROM mcdash.loans l
JOIN clima.pfas_risk_temporal t ON l.property_id = t.property_id
WHERE t.scenario = 'regulatory_tightening'
    AND t.horizon_years = 30
    AND t.expected_loss_delta_bps > 50
ORDER BY t.expected_loss_delta_bps DESC;

CLIMA Schema Reference

All CLIMA tables are available in the clima schema within RAP. The two primary tables:

Table	Primary Key	Description
clima.pfas_property_risk	property_id	Current-state PFAS risk scores per property. One row per property. Updated daily.
clima.pfas_risk_temporal	property_id, horizon_years, scenario	Forward-looking risk projections under three scenarios: current_trajectory, regulatory_tightening, remediation. Updated monthly.