# Well identifiers

Source: https://developers.novilabs.com/data/identifiers/

> How Novi UDM Data identifies wells and wellbores, and how to match your API numbers to them.

Every well has a `WELL_KEY` and every wellbore has a `WELLBORE_KEY`. Both are stable strings built from the regulatory identifier.
Join on the keys. Use `NATIVE_ID` to match identifiers that come from outside Novi.

## Key formats

| Key | Format | US example |
| --- | --- | --- |
| `WELL_KEY` | `{COUNTRY}:{NATIVE_ID_TYPE}:{NATIVE_ID}` | `US:API10:4212340567` |
| `WELLBORE_KEY` | `{COUNTRY}:{NATIVE_ID_TYPE}:{NATIVE_ID}:{SEQUENCE_NUMBER}` | `US:API10:4212340567:0` |

<WellKey />

The parts are columns on the same row:

| Column | On `WELL` | On `WELLBORE` |
| --- | --- | --- |
| `COUNTRY` | ISO 3166-1 alpha-2 code, for example `US` | Same as its well |
| `NATIVE_ID_TYPE` | `API10` in the US, `UWI-DLS` in Canada | `API14` when a borehole and event suffix is available, otherwise `API10`. `UWI-DLS` in Canada. `PLANNED` for a planned wellbore with no regulatory ID yet |
| `NATIVE_ID` | The regulatory ID in canonical form: 10 digits for API10, no hyphens | The wellbore's regulatory ID. API14 is 14 contiguous digits. For `PLANNED` it is a Novi ID |
| `SEQUENCE_NUMBER` | Not used | Ordinal of wellbores that share a `NATIVE_ID`, starting at 0 |

`SEQUENCE_NUMBER` is 0 on every row in the current release: no `NATIVE_ID` yet resolves to more than one wellbore.

## Match your API numbers

`WELL.NATIVE_ID` holds the API10. Normalize the values you have before you match: strip everything that is not a digit, then take the first 10 digits.
Dashed forms, API10, API12, and API14 then all match.

```sql
SELECT w.WELL_KEY, w.WELL_NAME, w.NATIVE_ID, w.STATE, w.COUNTY, w.STATUS
FROM NOVI_WELLS.WELL w
WHERE w.NATIVE_ID IN (
    SELECT LEFT(REGEXP_REPLACE(value, '[^0-9]', ''), 10)
    FROM TABLE(SPLIT_TO_TABLE('42-000-00001, 4200000002, 42000000030000', ','))
)
ORDER BY w.WELL_KEY;
```

Replace the sample list with your own comma-separated API numbers.

## Rules of thumb

- Join well-level attributes on `WELL_KEY` and wellbore-level data on `WELLBORE_KEY`.
- Count wells with `COUNT(DISTINCT WELL_KEY)`. Count wellbores with `COUNT(DISTINCT WELLBORE_KEY)`.
- `WELL_NAME` is for display. It is not a key.
- `OPERATOR_ID` identifies an operator. `OPERATOR.NAME`, `OPERATOR_REPORTED`, and `OPERATOR_ORIGINAL` are names, not keys. `OPERATOR.NAME` is stored as the source spelled it and may carry padding, so match it with `LOWER(TRIM(NAME))`.
- `COUNTY` is a county or parish name. `COUNTY_CODE` is the five-digit FIPS code.
- `STATE` is the two-letter USPS code.
- `LEASE_NUMBER` on `WELL` is a state lease or production-unit identifier, not a key. Texas files the Railroad Commission district and lease number joined by a semicolon, for example `08;39152`. Oklahoma files the 14-digit production unit number. It is NULL in every other state today.

## Planned

- **API14 wellbore identifiers for US wells.** The model supports `API14` for wellbores. Distinct US wellbores keyed on API14 are planned.
- **Canadian wells.** Canadian wells will use the `CA` country code and Canadian UWI schemes such as `UWI-DLS`, for example `CA:UWI-DLS:00/02-13-069-09W5/0:0` for a wellbore. See [Releases](https://developers.novilabs.com/data/releases/#coverage).

## Glossary

| Term | Meaning |
| --- | --- |
| API10 | The 10-digit API well number, the US regulatory identifier for a well |
| API14 | The 14-digit API number, which identifies a single wellbore or completion |
| UWI | Unique well identifier: the API number in the US, or the Canadian equivalent (UWI-DLS) |
| `NATIVE_ID` / `NATIVE_ID_TYPE` | The regulatory identifier of a well or wellbore and the kind of identifier it is |
