Novi Wells recipes
Ready-to-run Snowflake SQL recipes for well, production, forecast and activity questions on Novi UDM Data (v4).
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:
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.
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);1. Find wells from a list of API numbers
Section titled “1. Find wells from a list of API numbers”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.
SELECT w.WELL_KEY, w.WELL_NAME, w.NATIVE_ID, w.STATE, w.COUNTY, w.STATUSFROM NOVI_WELLS.WELL wWHERE 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;2. Screen wellbores with no joins
Section titled “2. Screen wellbores with no joins”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.
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_REPORTEDFROM NOVI_WELLS.WELL_DETAIL wdWHERE wd.BASIN = 'Permian' AND wd.DIRECTION = 'HORIZONTAL' AND wd.LAST_REPORTED_MONTHS_ON_PRODUCTION IS NOT NULLORDER BY wd.CUM_BOE_12_REPORTED DESC NULLS LAST, wd.WELLBORE_KEYLIMIT 100;3. Wells inside a drawn polygon
Section titled “3. Wells inside a drawn polygon”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.
SELECT sl.WELL_KEY, sl.LATITUDE, sl.LONGITUDEFROM NOVI_WELLS.SURFACE_LOCATION slWHERE 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_KEYLIMIT 200;4. Type-well profile
Section titled “4. Type-well profile”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.
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_1000ftFROM monthly mJOIN scope s ON m.WELLBORE_KEY = s.WELLBORE_KEYWHERE m.mop BETWEEN 1 AND 60GROUP BY m.mopORDER 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.
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_mcfFROM monthlyGROUP BY WELLBORE_KEYHAVING 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) > 0ORDER BY cum12_oil_bbl DESC, WELLBORE_KEYLIMIT 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.
6. Benchmark operators
Section titled “6. Benchmark operators”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.
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_bblFROM well_cumGROUP BY operatorHAVING COUNT(*) >= 25ORDER BY avg_cum12_oil_bbl DESC, operatorLIMIT 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.
SELECT FORECAST_DATE, FORECAST_MONTH, OIL_PER_DAY, GAS_PER_DAY, CUMULATIVE_OIL, CUMULATIVE_GASFROM NOVI_WELLS.PRODUCTION_FORECASTWHERE WELLBORE_KEY = $wellbore_key AND SCENARIO = 'P50' AND PERIOD_GRANULARITY = 'monthly'ORDER BY FORECAST_DAY;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_RATEFROM NOVI_WELLS.PRODUCTION_FORECAST_ARPS_SEGMENT segJOIN NOVI_WELLS.PRODUCTION_FORECAST_ARPS_SEGMENT_PARAMETER prm ON prm.FORECAST_SEGMENT_ID = seg.SEGMENT_UIDWHERE seg.WELLBORE_KEY = $wellbore_keyORDER 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.
8. EUR by subbasin
Section titled “8. EUR by subbasin”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.
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_30yrFROM NOVI_WELLS.WELL_PRODUCTION_SUMMARY wpsJOIN NOVI_WELLS.WELLBORE wb ON wb.WELLBORE_KEY = wps.WELLBORE_KEYWHERE wb.BASIN = 'Permian' AND wb.DIRECTION = 'HORIZONTAL'GROUP BY wb.SUBBASINORDER BY avg_eur_boe_30yr DESC NULLS LAST;9. Lifecycle timeline for one well
Section titled “9. Lifecycle timeline for one well”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.
SELECT EVENT_TYPE, EVENT_DT, EFFECTIVE_DT, WELLBORE_KEY, PAYLOAD['operator_reported']::string AS operator_reportedFROM NOVI_WELLS.WELL_EVENTWHERE 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.
10. Gunbarrel: offsets around a wellbore
Section titled “10. Gunbarrel: offsets around a wellbore”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.
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_ftFROM target tJOIN 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, neighborLIMIT 30;WELL_SPACING has precomputed distances, neighbor counts, and parent and child flags for each wellbore, but not the neighbor list.
11. Rig counts by basin and week
Section titled “11. Rig counts by basin and week”Job: track drilling activity. Counts add up across geography but not across time.
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_rigsFROM NOVI_WELLS.RIG_ACTIVITY_WEEKLY rGROUP BY r.BASIN, r.YEAR, r.WEEKORDER BY r.YEAR DESC, r.WEEK DESC, total_rigs DESC, r.BASINLIMIT 100;12. Gas producers by state
Section titled “12. Gas producers by state”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.
SELECT w.STATE, wb.PRIMARY_STREAM, COUNT(*) AS wellboresFROM NOVI_WELLS.WELLBORE wbJOIN NOVI_WELLS.WELL w ON w.WELL_KEY = wb.WELL_KEYWHERE wb.WELLBORE_TYPE_NORMALIZED = 'PRODUCER' AND wb.PRIMARY_STREAM IN ('GAS', 'OIL_AND_GAS', 'CBM')GROUP BY w.STATE, wb.PRIMARY_STREAMORDER BY wellbores DESC;More recipes
Section titled “More recipes”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