ID Mapping Files

BenefitFlow IDs can change between data releases. When records are merged or re-keyed, the older ID is retired and a new one replaces it. The ID mapping files tell you which older IDs now point to which current IDs, so the records you’ve stored in your CRM or warehouse stay matched to the latest data.

Make this a standard step in every load. Each time you load a Broker, Broker Office, or Employer file, remap your stored IDs with the latest mapping file from the same folder. If you skip this, stored IDs that have changed won’t match the current data, and joins will silently drop those records.

Where the files are

Each of these three folders has a mapping file next to its weekly data files:

Folder	Data file	Mapping file	What it maps
`broker/`	`BROKER-YYYYMMDD.csv`	`BROKER_ID_MAPPING-YYYYMMDD.csv`	Older Broker ID → current Broker ID
`brokerOffices/`	`BROKER_OFFICE-YYYYMMDD.csv`	`OFFICE_ID_MAPPING-YYYYMMDD.csv`	Older Broker Office ID → current Broker Office ID
`employer/`	`EMPLOYER-YYYYMMDD.csv`	`EMPLOYER_ID_MAPPING-YYYYMMDD.csv`	Older Employer ID → current Employer ID

Each release includes a new, complete mapping file. Use only the latest mapping file in each folder, and don’t combine it with older versions.

What’s in a mapping file

Each mapping file has two columns:

Mapping file	Older ID column	Current ID column
`BROKER_ID_MAPPING`	`Source ID`	`Company ID` (the current Broker ID)
`OFFICE_ID_MAPPING`	`Source ID`	`Office ID` (the current Broker Office ID)
`EMPLOYER_ID_MAPPING`	`Source ID`	`Company ID` (the current Employer ID)

How to read it:

Source ID is an older ID. Join your stored IDs against this column.
Many-to-one. Several older IDs can point to the same current ID, for example when two records are merged into one. In rare cases, one older ID is listed with more than one current ID. See step 4.
IDs not listed don’t need remapping. If a stored ID isn’t in Source ID, keep using it.

How to keep your stored IDs current

Pull the latest data file and mapping file togetherDownload the latest data file and the latest mapping file from the same folder.

Load distinct rows from the mapping fileThe file can contain duplicate rows. Load it with SELECT DISTINCT.

Join your stored IDs to the mapping file on Source IDUse a left join so that every stored record is kept, whether or not its ID has changed.

Replace any ID that matchedIf a stored ID appears in Source ID, replace it with the current ID. If it doesn’t appear, keep it as is.If a Source ID is listed with more than one current ID, keep your stored ID as is and contact your customer success manager rather than choosing one.

Repeat until nothing changesSome IDs change more than once. Run the join again on your updated IDs until no stored IDs change. Usually one pass is enough.

Join the result to the current data fileJoin your updated IDs to the current data file.

Example (SQL)

WITH employer_map AS (
  SELECT DISTINCT "Source ID", "Company ID"
  FROM employer_id_mapping
)
SELECT
  s.*,
  COALESCE(m."Company ID", s."Employer ID") AS "Current Employer ID"
FROM stored_employers s
LEFT JOIN employer_map m
  ON s."Employer ID" = m."Source ID";

If a Source ID has more than one current ID, this query returns one row per match. Find those IDs and handle them as described in step 4. Write Current Employer ID back to your stored records and run the query again until no IDs change. Then join Current Employer ID to EMPLOYER."Employer ID" in the latest data file.

The mapping files start with a hidden byte-order mark (BOM). Some tools read the first column name as \ufeffSource ID instead of Source ID. If the query above can’t find Source ID, strip the BOM when you load the file.

Which columns to remap

Remap each of these columns in your stored data with the mapping file shown:

Mapping file	Columns to remap
`EMPLOYER_ID_MAPPING`	`Employer ID` (Employer, Employer Contact, Policy)
`BROKER_ID_MAPPING`	`Broker ID` (Policy, Broker, Broker Office, Broker Contact), `Primary Broker ID` (Employer), `Benefits: Primary Broker ID` (Employer Contact), `P&C: Broker ID` (Employer, Employer Contact)
`OFFICE_ID_MAPPING`	`Broker Office ID` (Broker Office), `Closest Office ID` (Broker Contact), `Primary Broker Closest Office ID` (Employer), `Benefits: Primary Broker Closest Office ID` (Employer Contact)

For how the datasets join to each other, see Entity Relationships in the Bulk Data Schema.

Reconciling week over week

If you compare this week’s data file to last week’s, remap first and compare second:

Remap last week’s stored IDs to current IDs using the latest mapping file.
Compare the remapped IDs to the IDs in this week’s data file.

After remapping, each ID falls into one of three groups:

Result	What it means
In your stored data and in this week’s file	Same record. If the ID was remapped, the record was re-keyed or merged, not added or removed.
In this week’s file only	New record.
In your stored data only	Not in this week’s file.

If you skip the remap, a re-keyed record looks like one deletion plus one new record. Remapping first stops that false churn from showing up in your counts.

FAQs

Two of my stored records now map to the same current ID. Is that a mistake?

No. That happens when BenefitFlow merges records. Both older IDs point to the single current record. Merge or dedupe them on your side.

Why is the mapping file so large?

It covers older IDs from across past releases, not just the latest one, and it’s republished in full each time.

Do mapping files exist for contacts or policies?

No. Policy, Broker Contact, and Employer Contact records use Employer, Broker, and Broker Office IDs. Remap those columns with the three mapping files.

Bulk Data Schema

Bulk Data Access Setup

Questions? Contact your customer success manager or email support@benefitflow.com.

Salesforce Integration

HubSpot Integration

Dynamics Integration

Bulk Data (SFTP)

Where the files are

What’s in a mapping file

How to keep your stored IDs current

Example (SQL)

Which columns to remap

Reconciling week over week

FAQs

Bulk Data Schema

Bulk Data Access Setup

​Where the files are

​What’s in a mapping file

​How to keep your stored IDs current

​Example (SQL)

​Which columns to remap

​Reconciling week over week

​FAQs

​Related

Bulk Data Schema

Bulk Data Access Setup

Where the files are

What’s in a mapping file

How to keep your stored IDs current

Example (SQL)

Which columns to remap

Reconciling week over week

FAQs

Related