Skip to content
Developers
Get an API token

Novi Wells recipes

Ready-to-run Snowflake SQL recipes for well, production, forecast and activity questions on Novi UDM Data (v4).

View MarkdownOpen in Claude(opens in a new tab)

Each recipe answers one engineering question. Copy it into a Snowflake worksheet, swap in your own basin, subbasin and direction filters, and run it.

Run this once per session first:

Set your session
USE DATABASE NOVI_DATA_ACCESS;
USE SCHEMA NOVI_WELLS;

Recipes for one wellbore read a session variable, set here to an example Permian horizontal. Use your own WELLBORE_KEY if you have one.

Pick an example wellbore
SET wellbore_key = (
SELECT MIN(WELLBORE_KEY)
FROM NOVI_WELLS.WELLBORE
WHERE BASIN = 'Permian'
AND DIRECTION = 'HORIZONTAL'
AND MIDPOINT_LATITUDE IS NOT NULL
AND HEELPOINT_LATITUDE IS NOT NULL
AND BOTTOM_HOLE_LATITUDE IS NOT NULL
);
SET well_key = (SELECT WELL_KEY FROM NOVI_WELLS.WELLBORE WHERE WELLBORE_KEY = $wellbore_key);

Job: turn a pasted list of API numbers in any format into Novi wells. WELL.NATIVE_ID holds the API10, so the recipe keeps each value’s first 10 digits. Replace the sample list with your own.

Look up wells by API number
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;

Job: shortlist horizontal wellbores by early productivity and completion design. WELL_DETAIL already joins header, completion, spacing, and production roll-ups, one row per wellbore.

Screen with WELL_DETAIL
SELECT wd.WELLBORE_KEY, wd.WELL_NAME, wd.CURRENT_OPERATOR, wd.SUBBASIN, wd.FORMATION,
wd.FIRST_PRODUCTION_DATE, wd.LATERAL_LENGTH, wd.PROPPANT_LOADING, wd.STAGE_COUNT,
wd.CUM_OIL_12_REPORTED, wd.CUM_BOE_12_REPORTED
FROM NOVI_WELLS.WELL_DETAIL wd
WHERE wd.BASIN = 'Permian'
AND wd.DIRECTION = 'HORIZONTAL'
AND wd.LAST_REPORTED_MONTHS_ON_PRODUCTION IS NOT NULL
ORDER BY wd.CUM_BOE_12_REPORTED DESC NULLS LAST, wd.WELLBORE_KEY
LIMIT 100;

Job: select wells inside an area of interest drawn on a map. Coordinates are WGS 84. Keep the cheap bounding-box filter first so ST_WITHIN only checks the candidates.

Wells in a polygon
SELECT sl.WELL_KEY, sl.LATITUDE, sl.LONGITUDE
FROM NOVI_WELLS.SURFACE_LOCATION sl
WHERE sl.LATITUDE BETWEEN 31.0 AND 32.5
AND sl.LONGITUDE BETWEEN -103.0 AND -101.5
AND ST_WITHIN(
ST_MAKEPOINT(sl.LONGITUDE, sl.LATITUDE),
TO_GEOGRAPHY('POLYGON((-103 31, -101.5 31, -101.5 32.5, -103 32.5, -103 31))'))
ORDER BY sl.WELL_KEY
LIMIT 200;

Job: build an average-well curve for a cohort, aligned on months since first production, with oil per 1,000 ft of lateral. Normalize each well-month, then average by month on production.

Average type well, months 1 to 60
WITH scope AS (
SELECT wb.WELLBORE_KEY, wb.LATERAL_LENGTH
FROM NOVI_WELLS.WELLBORE wb
WHERE wb.BASIN = 'Permian'
AND wb.SUBBASIN = 'Midland'
AND wb.DIRECTION = 'HORIZONTAL'
),
monthly AS (
SELECT p.WELLBORE_KEY, p.YEAR, p.MONTH,
MAX(p.MONTHS_ON_PRODUCTION) AS mop,
SUM(p.OIL) AS oil,
SUM(p.GAS) AS gas
FROM NOVI_WELLS.PRODUCTION p
JOIN scope s ON p.WELLBORE_KEY = s.WELLBORE_KEY
WHERE p.PERIOD_GRANULARITY = 'monthly'
GROUP BY p.WELLBORE_KEY, p.YEAR, p.MONTH
)
SELECT m.mop AS months_on_production,
COUNT(*) AS well_months,
ROUND(AVG(m.oil), 1) AS avg_oil_bbl,
ROUND(AVG(m.gas), 1) AS avg_gas_mcf,
ROUND(AVG(COALESCE(m.oil, 0) + COALESCE(m.gas, 0) / 6), 1) AS avg_boe,
ROUND(AVG(m.oil / NULLIF(s.LATERAL_LENGTH / 1000, 0)), 2) AS avg_oil_bbl_per_1000ft
FROM monthly m
JOIN scope s ON m.WELLBORE_KEY = s.WELLBORE_KEY
WHERE m.mop BETWEEN 1 AND 60
GROUP BY m.mop
ORDER BY m.mop;

MONTHS_ON_PRODUCTION starts at 1 and counts every calendar month after first production, shut-in months included. PRODUCTION can hold more than one row for a wellbore and month, so the recipe collapses to one row per wellbore-month first.

5. 12-month cumulative oil and gas per wellbore

Section titled “5. 12-month cumulative oil and gas per wellbore”

Job: compute first-12-month cumulatives yourself from monthly production, and keep only wellbores whose history supports it. The HAVING checks matter: a unique, gap-free month index in step with the calendar, months 1 to 12 all present, and production through month 12.

Cumulative oil and gas, months 1 to 12
WITH scope AS (
SELECT wb.WELLBORE_KEY
FROM NOVI_WELLS.WELLBORE wb
WHERE wb.BASIN = 'Permian'
AND wb.SUBBASIN = 'Midland'
AND wb.DIRECTION = 'HORIZONTAL'
),
monthly AS (
SELECT p.WELLBORE_KEY, p.YEAR, p.MONTH,
MAX(p.MONTHS_ON_PRODUCTION) AS mop,
SUM(p.OIL) AS oil, SUM(p.GAS) AS gas, SUM(p.WATER) AS water,
SUM(p.CONDENSATE) AS condensate, SUM(p.GAS_FLARED) AS gas_flared
FROM NOVI_WELLS.PRODUCTION p
JOIN scope s ON p.WELLBORE_KEY = s.WELLBORE_KEY
WHERE p.PERIOD_GRANULARITY = 'monthly'
GROUP BY p.WELLBORE_KEY, p.YEAR, p.MONTH
)
SELECT WELLBORE_KEY,
SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN COALESCE(oil, 0) END) AS cum12_oil_bbl,
SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN COALESCE(gas, 0) END) AS cum12_gas_mcf
FROM monthly
GROUP BY WELLBORE_KEY
HAVING COUNT(DISTINCT CASE WHEN mop BETWEEN 1 AND 12 THEN mop END) = 12
AND MAX(mop) - MIN(mop) + 1 = COUNT(*)
AND COUNT(DISTINCT mop) = COUNT(*)
AND MIN(mop) >= 0
AND MIN(mop - (YEAR * 12 + MONTH)) = MAX(mop - (YEAR * 12 + MONTH))
AND MAX(CASE WHEN COALESCE(oil, 0) > 0 OR COALESCE(gas, 0) > 0 OR COALESCE(water, 0) > 0
OR COALESCE(condensate, 0) > 0 OR COALESCE(gas_flared, 0) > 0
THEN mop END) >= 12
AND SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN oil END) > 0
ORDER BY cum12_oil_bbl DESC, WELLBORE_KEY
LIMIT 100;

WELLBORE_PRODUCTION_SUMMARY also publishes 12-month cumulatives, as reported by the source rather than computed from the monthly rows. The two are not guaranteed to agree.

Job: rank operators in a cohort by average 12-month cumulative oil per well. Compare per-well averages, not totals. The window checks are the same as recipe 5.

Operator ranking, cohorts of 25 wells or more
WITH cohort AS (
SELECT wb.WELLBORE_KEY, o.NAME AS operator
FROM NOVI_WELLS.WELLBORE wb
JOIN NOVI_WELLS.WELL w ON wb.WELL_KEY = w.WELL_KEY
JOIN NOVI_WELLS.OPERATOR o ON w.OPERATOR_ID = o.OPERATOR_ID
WHERE wb.BASIN = 'Permian'
AND wb.SUBBASIN = 'Midland'
AND wb.DIRECTION = 'HORIZONTAL'
),
monthly AS (
SELECT c.operator, p.WELLBORE_KEY, p.YEAR, p.MONTH,
MAX(p.MONTHS_ON_PRODUCTION) AS mop,
SUM(p.OIL) AS oil, SUM(p.GAS) AS gas, SUM(p.WATER) AS water,
SUM(p.CONDENSATE) AS condensate, SUM(p.GAS_FLARED) AS gas_flared
FROM NOVI_WELLS.PRODUCTION p
JOIN cohort c ON p.WELLBORE_KEY = c.WELLBORE_KEY
WHERE p.PERIOD_GRANULARITY = 'monthly'
GROUP BY c.operator, p.WELLBORE_KEY, p.YEAR, p.MONTH
),
well_cum AS (
SELECT operator, WELLBORE_KEY,
SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN COALESCE(oil, 0) END) AS cum12_oil_bbl
FROM monthly
GROUP BY operator, WELLBORE_KEY
HAVING COUNT(DISTINCT CASE WHEN mop BETWEEN 1 AND 12 THEN mop END) = 12
AND MAX(mop) - MIN(mop) + 1 = COUNT(*)
AND COUNT(DISTINCT mop) = COUNT(*)
AND MIN(mop) >= 0
AND MIN(mop - (YEAR * 12 + MONTH)) = MAX(mop - (YEAR * 12 + MONTH))
AND MAX(CASE WHEN COALESCE(oil, 0) > 0 OR COALESCE(gas, 0) > 0 OR COALESCE(water, 0) > 0
OR COALESCE(condensate, 0) > 0 OR COALESCE(gas_flared, 0) > 0
THEN mop END) >= 12
AND SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN oil END) > 0
)
SELECT operator,
COUNT(*) AS wells,
ROUND(AVG(cum12_oil_bbl)) AS avg_cum12_oil_bbl,
ROUND(MEDIAN(cum12_oil_bbl)) AS median_cum12_oil_bbl
FROM well_cum
GROUP BY operator
HAVING COUNT(*) >= 25
ORDER BY avg_cum12_oil_bbl DESC, operator
LIMIT 25;

7. Forecast and decline parameters for one wellbore

Section titled “7. Forecast and decline parameters for one wellbore”

Job: read the monthly P50 forecast and the Arps segments behind it. Order forecast rows by FORECAST_DAY. Align them with actual production on YEAR and MONTH, not on FORECAST_DAY.

Monthly P50 forecast
SELECT FORECAST_DATE, FORECAST_MONTH, OIL_PER_DAY, GAS_PER_DAY, CUMULATIVE_OIL, CUMULATIVE_GAS
FROM NOVI_WELLS.PRODUCTION_FORECAST
WHERE WELLBORE_KEY = $wellbore_key
AND SCENARIO = 'P50'
AND PERIOD_GRANULARITY = 'monthly'
ORDER BY FORECAST_DAY;
Arps segments and parameters
SELECT seg.PRODUCTION_STREAM, seg.SEGMENT_NUMBER, seg.KIND, seg.SEGMENT_CURVE_TYPE,
seg.SEGMENT_START_DATE, seg.SEGMENT_END_DATE,
prm.B_FACTOR, prm.NOMINAL_DECLINE_RATE, prm.SEGMENT_START_RATE
FROM NOVI_WELLS.PRODUCTION_FORECAST_ARPS_SEGMENT seg
JOIN NOVI_WELLS.PRODUCTION_FORECAST_ARPS_SEGMENT_PARAMETER prm
ON prm.FORECAST_SEGMENT_ID = seg.SEGMENT_UID
WHERE seg.WELLBORE_KEY = $wellbore_key
ORDER BY seg.PRODUCTION_STREAM, seg.SEGMENT_NUMBER;

Forecast cumulatives start at the first forecast period and exclude earlier production. Decline rates are per-day fractions unless noted. b = 0 is exponential, b = 1 is harmonic, and values between are hyperbolic.

Job: compare 30-year forecast EUR per well across subbasins. EUR columns are additive across wells at one horizon, never across horizons. Wells with no forecast are NULL and drop out.

Average 30-year EUR per well
SELECT wb.SUBBASIN,
COUNT(wps.EUR_BOE_30YR) AS wells_with_forecast,
ROUND(AVG(wps.EUR_OIL_30YR)) AS avg_eur_oil_30yr_bbl,
ROUND(AVG(wps.EUR_GAS_30YR)) AS avg_eur_gas_30yr_mcf,
ROUND(AVG(wps.EUR_BOE_30YR)) AS avg_eur_boe_30yr
FROM NOVI_WELLS.WELL_PRODUCTION_SUMMARY wps
JOIN NOVI_WELLS.WELLBORE wb ON wb.WELLBORE_KEY = wps.WELLBORE_KEY
WHERE wb.BASIN = 'Permian'
AND wb.DIRECTION = 'HORIZONTAL'
GROUP BY wb.SUBBASIN
ORDER BY avg_eur_boe_30yr DESC NULLS LAST;

Job: list permit, spud, total depth, completion, and first production events in order. Keep this wellbore’s events plus well-level events, which have no WELLBORE_KEY. Per-event details live in the PAYLOAD map.

Well events in date order
SELECT EVENT_TYPE, EVENT_DT, EFFECTIVE_DT, WELLBORE_KEY,
PAYLOAD['operator_reported']::string AS operator_reported
FROM NOVI_WELLS.WELL_EVENT
WHERE WELL_KEY = $well_key
AND (WELLBORE_KEY = $wellbore_key OR WELLBORE_KEY IS NULL)
ORDER BY EVENT_DT;

EVENT_DT is when something happened. EFFECTIVE_DT is when it counts for status.

Job: find neighbors within about 1,000 m of a target lateral’s midpoint, with lateral spacing and vertical offset, for a cross-section plot. lateral_spacing_ft is the distance from each neighbor’s midpoint to the target’s heel-to-bottom-hole line, on either side.

Gunbarrel neighbors
WITH target AS (
SELECT wb.WELLBORE_KEY, wb.TVD_TD,
wb.MIDPOINT_LATITUDE AS lat,
wb.MIDPOINT_LONGITUDE AS lon,
ST_MAKEPOINT(wb.MIDPOINT_LONGITUDE, wb.MIDPOINT_LATITUDE) AS mid,
TO_GEOGRAPHY('LINESTRING('
|| wb.HEELPOINT_LONGITUDE || ' ' || wb.HEELPOINT_LATITUDE || ', '
|| wb.BOTTOM_HOLE_LONGITUDE || ' ' || wb.BOTTOM_HOLE_LATITUDE || ')'
) AS lateral_line
FROM NOVI_WELLS.WELLBORE wb
WHERE wb.WELLBORE_KEY = $wellbore_key
)
SELECT n.WELLBORE_KEY AS neighbor,
n.FORMATION,
ROUND(n.TVD_TD) AS tvd_ft,
ROUND(ST_DISTANCE(ST_MAKEPOINT(n.MIDPOINT_LONGITUDE, n.MIDPOINT_LATITUDE),
t.lateral_line) * 3.28084) AS lateral_spacing_ft,
ROUND(n.TVD_TD - t.TVD_TD) AS tvd_offset_ft
FROM target t
JOIN NOVI_WELLS.WELLBORE n
ON n.WELLBORE_KEY <> t.WELLBORE_KEY
AND n.MIDPOINT_LATITUDE BETWEEN t.lat - 0.02 AND t.lat + 0.02
AND n.MIDPOINT_LONGITUDE BETWEEN t.lon - 0.02 AND t.lon + 0.02
AND ST_DWITHIN(ST_MAKEPOINT(n.MIDPOINT_LONGITUDE, n.MIDPOINT_LATITUDE), t.mid, 1000)
ORDER BY lateral_spacing_ft, neighbor
LIMIT 30;

WELL_SPACING has precomputed distances, neighbor counts, and parent and child flags for each wellbore, but not the neighbor list.

Job: track drilling activity. Counts add up across geography but not across time.

Weekly rig counts
SELECT r.BASIN, r.YEAR, r.WEEK,
SUM(r.OIL_RIGS) AS oil_rigs,
SUM(r.GAS_RIGS) AS gas_rigs,
SUM(r.TOTAL_RIGS) AS total_rigs
FROM NOVI_WELLS.RIG_ACTIVITY_WEEKLY r
GROUP BY r.BASIN, r.YEAR, r.WEEK
ORDER BY r.YEAR DESC, r.WEEK DESC, total_rigs DESC, r.BASIN
LIMIT 100;

Job: count gas-producing wellbores using the declared function and stream. For gas producers, read GAS, OIL_AND_GAS, and CBM, not GAS alone. States that file no well-type code report only PRODUCER or NULL.

Gas producers by state
SELECT w.STATE,
wb.PRIMARY_STREAM,
COUNT(*) AS wellbores
FROM NOVI_WELLS.WELLBORE wb
JOIN NOVI_WELLS.WELL w ON w.WELL_KEY = wb.WELL_KEY
WHERE wb.WELLBORE_TYPE_NORMALIZED = 'PRODUCER'
AND wb.PRIMARY_STREAM IN ('GAS', 'OIL_AND_GAS', 'CBM')
GROUP BY w.STATE, wb.PRIMARY_STREAM
ORDER BY wellbores DESC;

The notebook also covers well status mixes, slant and survey checks, well tests and gas gravity, permit expiry, lease numbers, legal land lookups, completion ratios and soak time. Reader accounts get it as notebooks in a shared workspace or as a link to the online cookbook; the welcome email says which.

The full cookbook holds 18 recipes for this release (26.4.3):

  • Cookbook recipe 1: Well lookup and filters
  • Cookbook recipe 2: Spatial map
  • Cookbook recipe 3: Production timeseries
  • Cookbook recipe 4: Cumulative and IP
  • Cookbook recipe 5: Forecast and decline
  • Cookbook recipe 6: Well card detail
  • Cookbook recipe 7: Benchmarking rankings
  • Cookbook recipe 8: Activity and rigs
  • Cookbook recipe 9: Well status
  • Cookbook recipe 10: Bulk export
  • Cookbook recipe 11: Gunbarrel
  • Cookbook recipe 12: Wellbore slant and surveys
  • Cookbook recipe 13: Well tests permits and land
  • Cookbook recipe 14: Lease numbers permit expiry and primary stream
  • Cookbook recipe 15: Presentation views
  • Cookbook recipe 16: Completion ratios soak and EUR
  • Cookbook recipe 17: Wellbore type and primary stream
  • Cookbook recipe 18: Bulk downloads