Skip to content
Developers
Get an API token

Well identifiers

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

View MarkdownOpen in Claude(opens in a new tab)

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 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
Anatomy of a Novi UDM Data wellbore key: an API10 exampleThe wellbore key US:API10:4212340567:0 has four parts: COUNTRY, US, an ISO 3166-1 alpha-2 code; NATIVE_ID_TYPE, API10 in the US; NATIVE_ID, 4212340567, the API10 in 10 digits with no hyphens; and SEQUENCE_NUMBER, 0, an ordinal starting at 0. In this API10 example the first three parts, US:API10:4212340567, match the WELL_KEY; all four are the WELLBORE_KEY. Join wellbores to wells on WELLBORE.WELL_KEY, not by trimming the key. API numbers come as API10, the well; API12, the API10 plus two more digits; and API14, a single wellbore or completion. The first 10 digits of every form are the API10, so to match, strip everything that is not a digit and keep the first 10.WELLBORE_KEYWELL_KEY in this exampleUS:API10:4212340567:0SEQUENCE_NUMBEROrdinal, starting at 0NATIVE_IDThe API10: 10 digits, no hyphensNATIVE_ID_TYPEAPI10 in the USCOUNTRYISO 3166-1 alpha-2 codeAPI numbersAPI104212340567The wellAPI124212340567API10 plus two more digitsAPI144212340567A single wellbore or completionFirst 10 digits: the API10To match, strip everything that is not a digit and keep the first 10.Join wellbores to wells on WELLBORE.WELL_KEY, not by trimming the key.Anatomy of a Novi UDM Data wellbore key: an API10 exampleThe wellbore key US:API10:4212340567:0 has four parts: COUNTRY, US, an ISO 3166-1 alpha-2 code; NATIVE_ID_TYPE, API10 in the US; NATIVE_ID, 4212340567, the API10 in 10 digits with no hyphens; and SEQUENCE_NUMBER, 0, an ordinal starting at 0. In this API10 example the first three parts, US:API10:4212340567, match the WELL_KEY; all four are the WELLBORE_KEY. Join wellbores to wells on WELLBORE.WELL_KEY, not by trimming the key. API numbers come as API10, the well; API12, the API10 plus two more digits; and API14, a single wellbore or completion. The first 10 digits of every form are the API10, so to match, strip everything that is not a digit and keep the first 10.WELLBORE_KEYWELL_KEY in this exampleUS:API10:4212340567:012341COUNTRYISO 3166-1 alpha-2 code2NATIVE_ID_TYPEAPI10 in the US3NATIVE_IDThe API10: 10 digits, no hyphens4SEQUENCE_NUMBEROrdinal, starting at 0API numbersAPI104212340567API124212340567API144212340567First 10 digits: the API10API10: The wellAPI12: API10 plus two more digitsAPI14: A single wellbore or completionTo match, strip everything that is not a digit andkeep the first 10.Join wellbores to wells on WELLBORE.WELL_KEY,not by trimming the key.

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.

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.

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.

  • 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.
  • 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.
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