Work with EMS to address spike in Code 3 incidents

[johnclary]

While working on #27020 and related changes to our injury severity handling in EMS incident data, we uncovered a dramatic spike in Code 3 (lights and sirens) transports reported by EMS in late 2024 and 2025. Lynn @ EMS uncovered an underlying problem in their data warehouse related to Code 3 transports being overreported.

The scope of this work is to:

• Wait for Lynn to fix the pcr_transport_priority field in the source data
• Receive an updated backfill of EMS injury data that can be processed by our import ETL
• Process the backfill, which is blocked by Update the EMS incident import to allow record updates #29284

[johnclary]

Edit: I forgot we don't process upserts 🤦 The new data is available and we can import it once merge cityofaustin/vision-zero#2083.

---

Lynn reports today that this issue should have been fixed. I am using this query to monitor the EMS change log for updates that have come through. There hasn't been anything yet. I think we'll actually need to request a backfill from him 🤔

WITH diffs AS (
    SELECT
        id AS change_id,
        record_id,
        created_at,
        created_by,
        column_name,
        record_json -> 'old' -> column_name AS old_value,
        record_json -> 'new' -> column_name AS new_value
    FROM
        change_log_ems__incidents,
        LATERAL jsonb_object_keys(record_json -> 'new') AS column_name
    WHERE (record_json -> 'new' -> column_name) IS DISTINCT FROM (record_json -> 'old' -> column_name)
    AND operation_type = 'UPDATE'
ORDER BY
    id DESC
)
SELECT
    *
FROM
    diffs
WHERE
    column_name NOT IN('updated_at', 'created_at', 'updated_by', 'person_match_status', 'crash_pk', 'crash_match_status','person_id', 'person_match_attributes', 'matched_crash_pks', 'person_match_score', 'matched_person_ids', 'non_cr3_match_status', 'atd_apd_blueform_case_id', 'matched_non_cr3_case_ids')
LIMIT 100;

[johnclary]

Lynn sent us data going back to 2010 (as 16 separate files), and I updated our ETL to process local files rather than fussing with email attachments. I'm checking with Xavier and Joel to make sure it's ok if we bring in data back to 2010 (we currently have 2018+). Assuming that's fine, I'll probably run the ETL locally against staging, then prod.