Crosswalks

Crosswalks enable matching and merging data from multiple sources. A crosswalk takes data from two or more sources, optionally matches rows based on rules you define, then creates a table that links IDs from the input systems with a unique, persistent Relevant-generated ID. Crosswalks can be used to identify matching records for use in reporting and populations, or can be incorporated in Data Elements to expand the definition of your main patient (or other data element) population.

How to use crosswalks

The crosswalks module in Relevant produces a table in the crosswalk schema, for example, crosswalk.patients for a patients crosswalk. Some Health Centers prefer to refer to the crosswalk table in reports, populations, and manually run SQL. Other Health Centers need the data from multiple sources to flow through the related Data Elements, so that all reports, measures, and other parts of Relevant reflect the aggregated data.

For manual use

If you prefer to query the crosswalk table directly in reports, populations, or custom SQL, simply setting up a crosswalk is all you need to do. You can then immediately start writing queries in reports, or build a population, based on the crosswalk data.

For use across Relevant

If you need multiple data sources to be aggregated and used throughout Relevant, we recommend discussing options with your Relevant data analyst. For new Relevant implementations, this integration typically involves writing joins between the crosswalk table and the source table whenever sources are combined. For existing Relevant clients, an alternate solution may be available.

How crosswalks work

First, you configure a crosswalk, specifying the source tables to pull data from and how to match them. Then, the crosswalk module does the following steps:

  • Run the custom matcher SQL, to make a list of all possible matches
  • Deduplicate the matches, selecting the match with the match_rank closest to 0 and discarding others
  • Combine the results of all the matchers
  • Copy over IDs from pre-existing crosswalk rows
  • Insert any new non-matching rows from the sources

For example, imagine a Health Center with 2 data sources: their EHR, and a CSV of patients from a payer. Suppose there are 3 patients:

  • One appears only in the EHR: Gazunda Bowlegaz, ID 100
  • One appears only in the payer data: Scarbinger Poduuley, ID ABC
  • One appears in both systems: Normandy Snuzzlekup, ID 101 in the EHR and ID DEF in the payer data

To match these two tables, you could write matcher SQL like this:

SELECT
    ehr.patients.id AS ehr_id,
    payer_data.patients.id AS payer_id,
    1 AS match_rank
FROM ehr.patients
INNER JOIN payer_data.patients
ON ehr.name = payer_data.name

(Realistically, you probably want to match on other fields, like date of birth.)

Behind the scenes, the matcher SQL will run first and produce the following table:

ehr_id, payer_id, match_rank
101,    DEF,      1

Relevant then performs several additional processing steps to produce the final crosswalk table:

id, ehr_id, payer_id, ehr_payer_match_rank
1,  101,    DEF,      1
2,  100,    null,     null
3,  null,   ABC,      null

Every source record appears exactly once in the crosswalk table. Records that matched share the same generated crosswalk ID. Unmatched records have NULL for the IDs from other sources.