> ## Documentation Index
> Fetch the complete documentation index at: https://docs.benefitflow.com/llms.txt
> Use this file to discover all available pages before exploring further.

# ID Mapping Files

> How to use the Broker, Broker Office, and Employer ID mapping files to keep your stored SFTP IDs current from one release to the next.

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.

<Warning>
  **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.
</Warning>

## 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 |

<Tip>
  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.
</Tip>

## 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

<Steps>
  <Step>
    **Pull the latest data file and mapping file together**

    Download the latest data file and the latest mapping file from the same folder.
  </Step>

  <Step>
    **Load distinct rows from the mapping file**

    The file can contain duplicate rows. Load it with `SELECT DISTINCT`.
  </Step>

  <Step>
    **Join your stored IDs to the mapping file on `Source ID`**

    Use a left join so that every stored record is kept, whether or not its ID has changed.
  </Step>

  <Step>
    **Replace any ID that matched**

    If 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.
  </Step>

  <Step>
    **Repeat until nothing changes**

    Some IDs change more than once. Run the join again on your updated IDs until no stored IDs change. Usually one pass is enough.
  </Step>

  <Step>
    **Join the result to the current data file**

    Join your updated IDs to the current data file.
  </Step>
</Steps>

### Example (SQL)

```sql theme={null}
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.

<Tip>
  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.
</Tip>

## 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](/knowledge-base/bulk-data-schema#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:

1. Remap last week's stored IDs to current IDs using the latest mapping file.
2. 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. |

<Tip>
  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.
</Tip>

## FAQs

<AccordionGroup>
  <Accordion title="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.
  </Accordion>

  <Accordion title="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.
  </Accordion>

  <Accordion title="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.
  </Accordion>
</AccordionGroup>

## Related

<CardGroup>
  <Card title="Bulk Data Schema" icon="database" href="/knowledge-base/bulk-data-schema" horizontal={true} />

  <Card title="Bulk Data Access Setup" icon="server" href="/knowledge-base/bulk-data-access-setup" horizontal={true} />
</CardGroup>

Questions? Contact your customer success manager or email [support@benefitflow.com](mailto:support@benefitflow.com).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.