Skip to content
Developers
Get an API token

M&A data in Snowflake

The nine views in NOVI_MA_INTEL, how they join, the columns most analyses need, and SQL that counts each deal once.

View MarkdownOpen in Claude(opens in a new tab)

Query M&A deals in Snowflake database NOVI_DATA_ACCESS, schema NOVI_MA_INTEL. The current release is 1.6.72, the first release.

Connect the same ways as Novi UDM Data: a Novi-hosted reader account, a share into your own Snowflake account, or SQL over HTTPS. Setup: Delivery options.

  • Its own subscription. M&A Intelligence is licensed separately. On your own Snowflake account it arrives in the same Novi share and database as any other Novi data you subscribe to.
  • Every deal. There is no basin filter on this schema.
  • Its own guide. The welcome email includes the M&A Intelligence guide (PDF) and an example cookbook with 10 recipes.

Set the database and schema at the start of a session, and the SQL on this page runs as printed.

Set your session
USE DATABASE NOVI_DATA_ACCESS;
USE SCHEMA NOVI_MA_INTEL;

From a script, send the same SQL over HTTPS with a Snowflake programmatic access token (Authentication).

Count deals over HTTPS
curl -X POST "$NOVI_SNOWFLAKE_URL/api/v2/statements" \
-H "Authorization: Bearer $NOVI_SNOWFLAKE_TOKEN" \
-H "Content-Type: application/json" \
-d '{"statement": "SELECT COUNT(*) FROM NOVI_MA_INTEL.MA_DEAL", "warehouse": "NOVI_WH", "database": "NOVI_DATA_ACCESS"}'

Start with MA_DEAL, one row per deal. Every other deal view joins to it on MA_DEAL_KEY.

View One row per A row is
MA_DEAL MA_DEAL_KEY A deal
MA_DEAL_SCOPE MA_DEAL_KEY, BASIN, SUBBASIN A basin and subbasin that a deal touches
MA_DEAL_VALUATION MA_DEAL_KEY Novi’s valuation of a deal
MA_DEAL_COMMENT MA_DEAL_KEY, WORKSHEET_COLUMN The analyst’s comment on one field of a deal
MA_DEAL_PARTY MA_DEAL_KEY, PARTY_KEY, ROLE, DEAL_SIDE A party in one role on one side of a deal
PARTY PARTY_KEY A company
PARTY_ALIAS PARTY_KEY, ALIAS A name a company was recorded under
MA_DEAL_DOCUMENT MA_DEAL_KEY, MA_DOCUMENT_KEY A document that covers a deal
MA_DOCUMENT MA_DOCUMENT_KEY A document written by Novi
From To On
MA_DEAL_SCOPE, MA_DEAL_VALUATION, MA_DEAL_COMMENT, MA_DEAL_PARTY, MA_DEAL_DOCUMENT MA_DEAL MA_DEAL_KEY
MA_DEAL_PARTY PARTY PARTY_KEY
PARTY_ALIAS PARTY PARTY_KEY
MA_DEAL_DOCUMENT MA_DOCUMENT MA_DOCUMENT_KEY

MA_DEAL_PARTY and MA_DEAL_DOCUMENT are link views: go through them to get from a deal to its parties or documents. MA_DEAL_KEY is a 64-bit integer, NUMBER(19,0).

The columns most analyses start from. The guide describes every column.

Column Meaning
ANNOUNCE_DT, CLOSE_DT Announcement and closing dates. CLOSE_DT is empty until the deal closes
DEAL_STATUS ANNOUNCED, CLOSED or TERMINATED. Ownership has not moved until CLOSED
DEAL_TYPE, ASSET_OR_CORPORATE Merger or acquisition, and whether assets or the corporate entity changed hands
DEAL_DESCRIPTION A one-line summary of the deal
COMMODITY Primary commodity character of the deal’s assets
TOTAL_CONSIDERATION_USD_MM Headline consideration, USD millions. On all but a few deals it equals CASH_USD_MM + ISSUED_EQUITY_USD_MM + DEBT_ASSUMED_USD_MM + OTHER_CONSIDERATION_USD_MM, empty parts as 0
TOTAL_PRODUCTION_MBOE_D Production at deal time, thousand boe per day
NET_MINERAL_ACRES, WORKING_INTEREST Net acres conveyed, and working interest as a fraction from 0 to 1
LIQUIDS Liquids share of production as the analyst reports it, a fraction from 0 to 1
CONSIDERATION_PER_FLOWING_BOE_D_USD, CONSIDERATION_PER_NET_ACRE_USD Price per flowing boe/d and per net acre, in US dollars
STATES, CONSIDERATION_TYPE, DEAL_ASSET_TAGS Lists, stored as VARIANT arrays
DEAL_GROUP_KEY Shared by deals recorded as parts of one package. Empty for most deals
Column Meaning
BASIN From Novi’s controlled list; Novi Wells basin names where Novi Wells has coverage
SUBBASIN Sub-basin or play as reported. Part of the grain, so a deal in the Delaware and the Midland is two rows
REGION US_LOWER_48, GULF_OF_MEXICO, CANADA, ALASKA, INTERNATIONAL or UNDISCLOSED
STATE State or province for this basin, when the source gives it

Novi’s deal-level comparables valuation, not per-well economics. Money is USD millions unless the column name says otherwise.

Column Meaning
PDP_PV10_USD_MM Present value of proved developed producing reserves at 10%
ALLOCATED_VALUE_PDP_USD_MM, ALLOCATED_VALUE_PUD_USD_MM, ALLOCATED_VALUE_OTHER_USD_MM How the price divides between producing wells, undeveloped locations and other assets
IMPLIED_VALUE_PER_LOCATION_USD_MM Implied value per undeveloped location
RQ_TIER1_LOCATIONS to RQ_TIER4_LOCATIONS Locations by Novi rock-quality tier
PDP_PV10_PER_FLOWING_BOE_D_USD PDP PV10 per flowing boe/d, in US dollars
BENCHMARK_WTI_PROMPT, BENCHMARK_HH_PROMPT, STRIP_DATE Prompt-month WTI (USD/bbl) and Henry Hub (USD/MMBtu) at deal time, and the as-of date of the forward strip the valuation used
Column Meaning
MA_DEAL_PARTY.ROLE PRINCIPAL for a buyer or seller. SPONSOR, ADVISOR_FINANCIAL, ADVISOR_LEGAL, RECEIVER or TRUSTEE for a party attached to one side
MA_DEAL_PARTY.DEAL_SIDE BUYER or SELLER. For a sponsor or advisor, the side it backs or advises
MA_DEAL_PARTY.PARTY_NAME_REPORTED The name as this deal recorded it
MA_DEAL_PARTY.TICKER, IS_PUBLIC, MARKET_CAP_USD_MM, ENTERPRISE_VALUE_USD_MM A principal’s ticker, listing, market cap and enterprise value at the time of the deal
PARTY.PARTY_NAME One display name per company, for grouping
PARTY_ALIAS.ALIAS, ALIAS_TYPE Every name a company was recorded under, such as a former name before a rebrand

MA_DEAL_COMMENT, MA_DEAL_DOCUMENT and MA_DOCUMENT

Section titled “MA_DEAL_COMMENT, MA_DEAL_DOCUMENT and MA_DOCUMENT”
Column Meaning
MA_DEAL_COMMENT.WORKSHEET_COLUMN_LABEL, COMMENT_TEXT The field the analyst commented on, and the comment
MA_DEAL_DOCUMENT.PAGE_NUMBER Page where the deal appears in a document that covers several deals
MA_DOCUMENT.TITLE, PUBLISHED_DATE, PAGE_COUNT Novi’s written analysis of a deal
MA_DOCUMENT.DOWNLOAD_URL A download link, good for 24 hours after your query runs. Empty while a new file is not ready

The documents are Novi’s own analysis, not press releases, investor presentations or other third-party material.

  • Sum deal values on MA_DEAL only. A join to MA_DEAL_PARTY, MA_DEAL_SCOPE, MA_DEAL_COMMENT or MA_DEAL_DOCUMENT repeats a deal’s values on each of its rows. Filter with EXISTS instead.
  • A deal’s value is not split between basins. Count deals by basin; a sum by basin gives each basin the whole deal.
  • Buyers and sellers need ROLE and DEAL_SIDE. A buyer is ROLE = 'PRINCIPAL' and DEAL_SIDE = 'BUYER'. DEAL_SIDE alone also returns sponsors and advisors.
  • Group companies on PARTY_KEY. Search PARTY_ALIAS.ALIAS to find one by a former name.
  • Units are in the column name. _USD_MM is millions of US dollars, _MBOE_D thousand boe per day. Percentages are fractions (0.18 is 18%). Ratios and per-unit prices do not add up across deals.
  • Empty is not zero. It means not reported or not applicable.
  • Lists are arrays. Test one value with ARRAY_CONTAINS('TX'::VARIANT, STATES). Use LATERAL FLATTEN for one row per value, then count distinct MA_DEAL_KEY.
The 50 most recently announced deals, with buyers and sellers
SELECT d.ANNOUNCE_DT,
d.DEAL_DESCRIPTION,
d.DEAL_TYPE,
d.DEAL_STATUS,
d.COMMODITY,
d.TOTAL_CONSIDERATION_USD_MM,
LISTAGG(DISTINCT CASE WHEN dp.DEAL_SIDE = 'BUYER' THEN p.PARTY_NAME END, '; ') AS buyers,
LISTAGG(DISTINCT CASE WHEN dp.DEAL_SIDE = 'SELLER' THEN p.PARTY_NAME END, '; ') AS sellers
FROM NOVI_MA_INTEL.MA_DEAL d
LEFT JOIN NOVI_MA_INTEL.MA_DEAL_PARTY dp
ON dp.MA_DEAL_KEY = d.MA_DEAL_KEY AND dp.ROLE = 'PRINCIPAL'
LEFT JOIN NOVI_MA_INTEL.PARTY p ON p.PARTY_KEY = dp.PARTY_KEY
GROUP BY d.MA_DEAL_KEY, d.ANNOUNCE_DT, d.DEAL_DESCRIPTION, d.DEAL_TYPE, d.DEAL_STATUS,
d.COMMODITY, d.TOTAL_CONSIDERATION_USD_MM
ORDER BY d.ANNOUNCE_DT DESC NULLS LAST
LIMIT 50;
Number of deals that touch each basin
SELECT s.BASIN, COUNT(DISTINCT s.MA_DEAL_KEY) AS deal_count
FROM NOVI_MA_INTEL.MA_DEAL_SCOPE s
GROUP BY s.BASIN
ORDER BY deal_count DESC;
Total consideration of deals that touch the Permian, each deal once
SELECT SUM(d.TOTAL_CONSIDERATION_USD_MM) AS total_consideration_usd_mm
FROM NOVI_MA_INTEL.MA_DEAL d
WHERE EXISTS (
SELECT 1
FROM NOVI_MA_INTEL.MA_DEAL_SCOPE s
WHERE s.MA_DEAL_KEY = d.MA_DEAL_KEY
AND s.BASIN = 'Permian'
);

That total includes the whole value of any deal that also touches another basin.

Download links for Novi's deal analysis
SELECT doc.TITLE, doc.PUBLISHED_DATE, doc.PAGE_COUNT, doc.SIZE_BYTES, doc.DOWNLOAD_URL
FROM NOVI_MA_INTEL.MA_DOCUMENT doc
WHERE doc.DOWNLOAD_URL IS NOT NULL
ORDER BY doc.PUBLISHED_DATE DESC NULLS LAST, doc.TITLE;

More recipes, from the cookbook in your welcome email, are in the M&A Intelligence cookbook: deal value and price per unit, Novi’s valuation, sponsors and advisors, public company deals and premiums, states and tags, and linked deals with analyst comments.

View and column descriptions in Snowflake’s catalog match the guide.

Every view in NOVI_MA_INTEL
SELECT table_name, table_type, row_count, comment
FROM NOVI_DATA_ACCESS.INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'NOVI_MA_INTEL'
ORDER BY table_type, table_name;
Columns of MA_DEAL
SELECT column_name, data_type, is_nullable, comment
FROM NOVI_DATA_ACCESS.INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'NOVI_MA_INTEL'
AND table_name = 'MA_DEAL'
ORDER BY ordinal_position;