# Data model

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

> How the NOVI_WELLS views are grouped, what one row means in each, and how they join.

Novi UDM Data uses one data model, Novi's Unified Data Model (UDM). Two keys connect almost everything: `WELL_KEY` for wells and `WELLBORE_KEY` for wellbores.
Reference lists such as operators have their own IDs, for example `OPERATOR_ID`.

## The core rule: wells and wellbores

- `WELL` has one row per well. `WELLBORE` has one row per wellbore. `WELL` 1:N `WELLBORE` on `WELL_KEY`.
- Production, completions, well tests, spacing, forecasts, and frac chemistry are recorded per wellbore and join on `WELLBORE_KEY`.
- If you join wellbore data to a well and add it up per well, check that you do not count the same volume twice.

## Base views by domain

| Domain | View | One row per | Key |
| --- | --- | --- | --- |
| Wells and wellbores | `WELL` | Well | `WELL_KEY` |
| | `WELLBORE` | Wellbore (lateral, sidetrack, or re-entry) | `WELLBORE_KEY` |
| | `SURFACE_LOCATION` | Surface location record of a well | `SURFACE_LOCATION_ID` |
| | `WELLBORE_TRAJECTORY` | Wellbore, as a WKT LineString | `TRAJECTORY_ID` |
| | `DIRECTIONAL_SURVEY_STATION` | Survey station along a wellbore, ordered by measured depth | `SURVEY_STATION_ID` |
| Production and tests | `PRODUCTION` | Wellbore and period, monthly or weekly | `WELLBORE_KEY` + period |
| | `WELL_PRODUCTION_SUMMARY` | Well, carried on its lowest-sequence wellbore: reported roll-ups and forecast EUR | `WELL_PRODUCTION_SUMMARY_ID` |
| | `WELL_TEST` | Wellbore and test date | `WELL_TEST_ID` |
| Completion and stimulation | `WELL_COMPLETION` | Completion event of a wellbore | `WELL_COMPLETION_ID` |
| | `FRAC_FOCUS_SUMMARY` | FracFocus disclosure (wellbore and frac sequence number) | `FRAC_FOCUS_SUMMARY_ID` |
| | `FRAC_FOCUS_INGREDIENT` | Ingredient of a disclosure | `FRAC_FOCUS_INGREDIENT_ID` |
| Forecasts | `PRODUCTION_FORECAST` | Wellbore, forecast period, and scenario | `PRODUCTION_FORECAST_ID` groups one forecast's rows |
| | `PRODUCTION_FORECAST_ARPS_SEGMENT` | Arps segment of one stream of a wellbore | `SEGMENT_UID` |
| | `PRODUCTION_FORECAST_ARPS_SEGMENT_PARAMETER` | Parameter set of a segment | `SEGMENT_PARAMETER_UID` |
| | `SUPPLY_PROJECTION` | County, production-start month, and month on production | `SUPPLY_PROJECTION_ID` |
| Spacing and subsurface | `WELL_SPACING` | Wellbore | `WELL_SPACING_ID` |
| | `WELL_SPACING_CONFIGURATION` | Basin and subbasin | `WELL_SPACING_CONFIG_ID` |
| | `SUBSURFACE_PROPERTY` | Measurement point along a wellbore | `SUBSURFACE_PROPERTY_ID` |
| Permits and well events | `PLANNED_WELL` | Public permit filing | `PLANNED_WELL_ID` |
| | `WELL_PERMIT` | Permit filing of a wellbore | `PERMIT_ID` + `WELLBORE_KEY` |
| | `WELL_EVENT` | Lifecycle event of a well or wellbore | `WELL_EVENT_ID` |
| Acreage | `COMPANY_ACREAGE` | Leasehold parcel | `COMPANY_ACREAGE_ID` |
| Market and activity | `RIG_ACTIVITY_WEEKLY` | State, county, basin, and subbasin in an ISO week | `RIG_ACTIVITY_WEEKLY_ID` |
| | `RIG_FRAC_ACTIVITY` | Rig or frac fleet observation on an activity date | `RIG_FRAC_ACTIVITY_ID` |
| | `PRICE_WEEKLY` | Week, dated the Friday | `PRICE_WEEKLY_ID` |
| Reference lists | `OPERATOR` | Company | `OPERATOR_ID` |
| | `OPERATOR_CONTACT` | Operator registration or presence in a jurisdiction | `OPERATOR_CONTACT_ID` |
| | `BASIN` | Basin and subbasin | `BASIN`, `SUBBASIN` |
| | `SOURCE` | Data lineage record | `SOURCE_ID` |

Every base view has `SOURCE_ID`, which joins to `SOURCE` and tells you which dataset a row came from.

## Relationships

<DataModel />

| From | Join on | To | Notes |
| --- | --- | --- | --- |
| `WELLBORE` | `WELL_KEY` | `WELL` | One well, many wellbores |
| `WELL` | `OPERATOR_ID` | `OPERATOR` | Current operator of record |
| `SURFACE_LOCATION` | `WELL_KEY` | `WELL` | At most one row per well |
| `WELLBORE_TRAJECTORY`, `DIRECTIONAL_SURVEY_STATION`, `WELL_SPACING`, `WELL_TEST`, `WELL_COMPLETION`, `FRAC_FOCUS_SUMMARY`, `PRODUCTION`, `WELL_PRODUCTION_SUMMARY`, `PRODUCTION_FORECAST`, `SUBSURFACE_PROPERTY` | `WELLBORE_KEY` | `WELLBORE` | Wellbore-grain data |
| `PRODUCTION` | `OPERATOR_ID` | `OPERATOR` | Operator of record for that period, not today's operator |
| `SUBSURFACE_PROPERTY` | `SURVEY_STATION_ID` | `DIRECTIONAL_SURVEY_STATION` | Only rows with survey coverage |
| `FRAC_FOCUS_INGREDIENT` | `FRAC_FOCUS_SUMMARY_ID` | `FRAC_FOCUS_SUMMARY` | Ingredients of one disclosure |
| `PRODUCTION_FORECAST_ARPS_SEGMENT` | `PRODUCTION_FORECAST_ID` | `PRODUCTION_FORECAST` | Returns every month of that forecast |
| `PRODUCTION_FORECAST_ARPS_SEGMENT_PARAMETER` | `FORECAST_SEGMENT_ID` = `SEGMENT_UID` | `PRODUCTION_FORECAST_ARPS_SEGMENT` | Decline parameters of a segment |
| `WELL_PERMIT` | `WELLBORE_KEY`, `OPERATOR_ID` | `WELLBORE`, `OPERATOR` | Key is `PERMIT_ID` + `WELLBORE_KEY` |
| `WELL_EVENT` | `WELL_KEY`, `WELLBORE_KEY` | `WELL`, `WELLBORE` | Well-level events have a NULL `WELLBORE_KEY` |
| `WELL_EVENT` | `PERMIT_ID` + `WELLBORE_KEY` | `WELL_PERMIT` | Join on both columns |
| `PLANNED_WELL` | `MATERIALIZED_WELL_KEY` = `WELL_KEY` | `WELL` | Set once the permitted well has been drilled |
| `PLANNED_WELL`, `COMPANY_ACREAGE`, `OPERATOR_CONTACT`, `RIG_FRAC_ACTIVITY` | `OPERATOR_ID` | `OPERATOR` | |
| Every base view | `SOURCE_ID` | `SOURCE` | Lineage |

### Views that join on shared columns

These views share no key with the others. Combine them on the columns listed.

| View | Join on | How to use it |
| --- | --- | --- |
| `BASIN` | `BASIN`, `SUBBASIN` | Every view keyed by a well or wellbore carries `BASIN` and `SUBBASIN`, so filter on them directly |
| `PRICE_WEEKLY` | `DATE` | Prices are dated the Friday of each week. Match a date to the Friday of its week |
| `RIG_ACTIVITY_WEEKLY` | `COUNTY_CODE`, `YEAR`, `WEEK` | Join to `WELL` on `COUNTY_CODE`, the five-digit FIPS code. A county that spans basins has a row per basin, so sum by county and week first |
| `SUPPLY_PROJECTION` | `COUNTY_CODE`, `MONTH_OF_PRODUCTION_START`, `MONTHS_ON_PRODUCTION` | Join to `WELL` on `COUNTY_CODE`, then pick the start month and month on production you need |
| `WELL_SPACING_CONFIGURATION` | `BASIN`, `SUBBASIN` | The search radii and cutoffs behind a wellbore's spacing values |

## Presentation views

The presentation views join base views for you. Where a value appears in both, the base view is authoritative. Their columns can change between releases as they are extended.

| View | One row per | Other keys |
| --- | --- | --- |
| `WELL_DETAIL` | `WELLBORE_KEY` | `WELL_KEY`, `OPERATOR_ID`, `PERMIT_ID`, `WELL_COMPLETION_ID`, `WELL_SPACING_ID` |
| `WELLBORE_HEADER` | `WELLBORE_KEY` | `WELL_KEY`, `OPERATOR_ID`, `PERMIT_ID` |
| `WELLBORE_COMPLETION_DESIGN` | `WELLBORE_KEY` | `WELL_COMPLETION_ID` |
| `WELLBORE_PRODUCTION_SUMMARY` | `WELLBORE_KEY` | None |
| `WELLBORE_DECLINE_PARAMETERS` | `WELLBORE_KEY`, `PRODUCTION_FORECAST_ID`, `PRODUCTION_STREAM`, `SEGMENT_NUMBER` | `SEGMENT_UID`, `SEGMENT_PARAMETER_UID` |
| `WELLBORE_SPACING_DETAIL` | `WELLBORE_KEY` | `WELL_SPACING_ID` |
| `WELLBORE_SUBSURFACE_PROPERTY` | `WELLBORE_KEY`, `LOCATION_TYPE`, `FORMATION` | `SUBSURFACE_PROPERTY_ID`, `SURVEY_STATION_ID` |
| `WELLBORE_TEST_HISTORY` | `WELLBORE_KEY`, `TEST_DATE` | `WELL_TEST_ID` |
| `PRODUCTION_MONTHLY` | `WELLBORE_KEY`, `YEAR`, `MONTH` | None |
| `PRODUCTION_FORECAST_MONTHLY` | `WELLBORE_KEY`, `SCENARIO`, `YEAR`, `MONTH` | `PRODUCTION_FORECAST_ID` |

`WELL_DETAIL` is the composition of `WELLBORE_HEADER`, `WELLBORE_PRODUCTION_SUMMARY`, `WELLBORE_COMPLETION_DESIGN`, and `WELLBORE_SPACING_DETAIL`.
The narrow views are not cheaper than `WELL_DETAIL`. They are just narrower to read.

> **Caution:**
> `PRODUCTION_MONTHLY` and `PRODUCTION_FORECAST_MONTHLY` overlap. A wellbore can have an actual and a forecast row for the same month, and the overlap grows until the forecast is regenerated.
> Do not add the two for a period. Take actuals where they exist and the forecast only beyond them.

## Rules that keep numbers right

| Rule | Why |
| --- | --- |
| Filter `PRODUCTION` and `PRODUCTION_FORECAST` on `PERIOD_GRANULARITY = 'monthly'` | `PRODUCTION` holds monthly and weekly rows; without the filter, totals count the same volume twice. `PRODUCTION_FORECAST` ships monthly rows only today; keep the filter so the query stays right if other granularities ship |
| Collapse `PRODUCTION` to one row per wellbore and month before windowed math | The view does not guarantee a single row per wellbore, year, and month |
| Align wells on `MONTHS_ON_PRODUCTION`, not on date | It counts calendar months from first production, starting at 1, including shut-in months |
| Check a well has month 1 before a fixed window such as months 1 to 60 | A few wells have no early records and start at 2 or later |
| Never add ratios such as GOR, WOR, or proppant per foot | Compute them per well or well-month, then average |
| Read percentage columns as fractions | 0.18 means 18% |
| Never add `EUR_*` columns across horizons | The 20, 30, and 50-year EUR columns overlap |
| Count wells with `COUNT(DISTINCT WELL_KEY)` | One well can have several wellbores |
| Name only the columns you need and filter on `WELLBORE_KEY`, date, and granularity | `PRODUCTION` has hundreds of millions of rows |

## Units and conventions

- **Volumes:** oil, condensate, NGL, and water in barrels (bbl). Gas in thousand cubic feet (Mcf).
- **BOE:** 6 Mcf of gas = 1 barrel of oil equivalent.
- **Depths and lengths:** feet.
- **Coordinates:** decimal degrees, WGS 84 (EPSG:4326).
- **Production streams** on forecast segments: `oil`, `gas`, `water`, `ngl`, `condensate`.
- **Forecast scenarios:** this release publishes `P50`, the median forecast, only.

See [All views](https://developers.novilabs.com/data/tables/) for every column, its type, and its unit.
