Entity mapping

The Net Purpose dataset is entity-level, so the usual first step of any analysis is mapping your own security-level universe onto our entity_ids. For ISINs, join to the primary_isin column on entities. For other identifier schemes (CUSIP, SEDOL, PermID, FIGI, ticker, LEI), use entity_identifiers.

`entity_identifiers`

One row per identifier per version — filter is_current = TRUE.

Column	Type	Description
`entity_identifier_id`	NUMBER	Identifier row key
`entity_id`	NUMBER	The entity this identifier belongs to
`identifier_type`	VARCHAR	The identifier scheme
`value`	VARCHAR	The identifier value

identifier_type is one of cusip, sedol, figi, perm_id, ticker, or lei. ISIN is not among them — it lives on entities.primary_isin. An entity can have several identifiers of the same type, so expect the mapping to be many-to-one onto entity_id.

Example: resolve a list of ISINs to entities

SELECT p.isin, e.entity_id, e.name
FROM (
    SELECT column1 AS isin
    FROM VALUES ('US0378331005'), ('US5949181045')
) p
JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.primary_isin = p.isin AND e.is_current = TRUE;

Example: resolve a list of tickers to entities

SELECT ei.value AS ticker, ei.entity_id, e.name
FROM NET_PURPOSE.ENTITIES.ENTITY_IDENTIFIERS ei
JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.entity_id = ei.entity_id AND e.is_current = TRUE
WHERE ei.is_current = TRUE
  AND ei.identifier_type = 'ticker'
  AND ei.value IN ('AAPL', 'MSFT');

For a full portfolio-mapping workflow, including coverage checks, see the Query cookbook.