HG Data Query Discipline

When to use

• Building account lists from ICP criteria via hg_data_query.
• Any workflow step that writes ClickHouse SQL against HG tables.
• Translating natural-language filters into structured queries.

First: is SQL the right tool?

1. Prefer search_companies when it can express the question: product, vendor or category installs (ANY_PRESENT, ALL_PRESENT, NONE_PRESENT), firmographics, intent, spend ranges, company counts. No SQL needed.
2. market_data_agent is the plain-English alternative: it resolves entities, writes and runs the query. It is async: start it, check status once or twice (not in a loop), then fetch the result set with phoenix_get_artifact. Metered separately, billed through the run.
3. hg_data_query only for multi-table joins, time series or aggregations the other tools can't express.

hg_data_query may be unavailable even when hg_catalog is listed. If it's absent, use search_companies or market_data_agent and tell the user.

Pre-flight: always resolve names to ids first

1. hg_catalog: call once with no arguments for the table index, then with table_names for the tables you'll use. Read columns, join_keys, relationships, mandatory_predicate and the sample queries.
2. get_vendor_information: vendor and product ids.
3. get_product_category: category ids and exact names.

All three are free. Filter SQL by product_id / vendor_id, never by name strings you typed. A typo or near-miss name returns zero rows silently.

Key tables

Table	Use
install_global	Current product installs: technographic queries
company_locations	Firmographic data (revenue, employees, industry, country)
install_intensity_momentum_global	Rate of change (intensity_momentum)
install_intensity_trending_global	Trending direction (intensity_trending)
install_time_series_global	Monthly snapshots (weighted_score × date_verified)
spend_absolute	IT spend data
products	Product catalog

Standard join: install_global ig JOIN company_locations cl ON ig.url_id = cl.url_id with cl.is_ghq = true (one global-HQ row per company; installs recorded only at subsidiary level drop out). Read every other join key from hg_catalog; don't assume all tables join on url_id. Use the sql_qualifier the catalog gives for tables outside the default database.

Mandatory rules for every query

1. Apply the catalog's mandatory filters. Include every mandatory_predicate and required filter hg_catalog documents for each table you touch, including the recency predicate for "currently using" and the date bound on the time-series table (date_verified).
2. DISTINCT on install rows. Install rows can repeat; use SELECT DISTINCT or GROUP BY, and count(DISTINCT ...) for counts.
3. List columns explicitly. SELECT * is rejected. Only SELECT or WITH statements run.
4. LIMIT and max_rows, both explicit. max_rows defaults to 1000 (max 10,000) and truncates silently. Use LIMIT 100 with max_rows: 100 for list builds unless the user needs more.
5. Stay inside 30 seconds. Queries time out at 30 seconds. Tight WHERE clauses on ids, aggregates rather than raw rows.

Safe ClickHouse patterns

CTE + NOT IN (has X but not Y)

WITH has_y AS (
  SELECT DISTINCT url_id FROM install_global
  WHERE product_id IN (<y_product_ids>)
)
SELECT DISTINCT cl.name, cl.country_name, cl.revenue_min
FROM install_global ig
JOIN company_locations cl ON ig.url_id = cl.url_id
WHERE ig.product_id IN (<x_product_ids>)
  AND cl.is_ghq = true
  AND ig.url_id NOT IN (SELECT url_id FROM has_y)
  -- plus every mandatory predicate hg_catalog lists for these tables
LIMIT 100

Category sprawl detection

Derive the category from a resolved anchor product rather than a typed name:

SELECT cl.name, cl.url_id,
       count(DISTINCT ig.vendor_id) AS vendor_count,
       groupArray(DISTINCT ig.vendor_name) AS vendors
FROM install_global ig
JOIN company_locations cl ON ig.url_id = cl.url_id
WHERE ig.category_leaf_name IN (
        SELECT DISTINCT category_leaf_name FROM install_global
        WHERE product_id IN (<anchor_product_ids>))
  AND cl.is_ghq = true
  AND cl.revenue_min >= 1000000000
  -- plus every mandatory predicate hg_catalog lists for these tables
GROUP BY cl.name, cl.url_id
HAVING vendor_count >= 3
ORDER BY vendor_count DESC
LIMIT 100

Multi-value aggregation

Use groupArray(DISTINCT column) + has() for multi-value checks within a GROUP BY.

CASE for segment classification

SELECT cl.name,
  CASE
    WHEN vendor_count >= 5 THEN 'Severe sprawl'
    WHEN vendor_count >= 3 THEN 'Moderate sprawl'
    ELSE 'Dual-stack'
  END AS severity
FROM ...

Count before you pull

Run count(DISTINCT cl.url_id) with the same WHERE first. If the count is large, tighten filters before pulling rows.

Dangerous patterns: never use

Pattern	Problem	Fix
NOT EXISTS subquery	Returns HTTP 500	CTE + NOT IN
LEFT JOIN for exclusion	Returns 0 rows unreliably	CTE + NOT IN
SELECT *	Rejected	List columns
No LIMIT / no max_rows	Silent truncation at 1000, blown token budget	Both explicit
Name-string filters	0 rows silently	Resolve ids, filter by id
Missing mandatory predicate	Full scan, high cost or rejection	Read hg_catalog first
Install rows without DISTINCT	Duplicates inflate counts	DISTINCT / GROUP BY

Ordering

• Use ORDER BY intensity DESC (or max(intensity) in a GROUP BY) so the most relevant accounts surface first within the limit. Intensity is unbounded and only meaningful as a ranking, not as a band.
• For momentum queries, ORDER BY intensity_momentum ASC (most declining first). See hg-momentum-signals.

Credit budget

Tool	Credits
hg_data_query	Depends on data scanned, reported in credits_consumed; can be non-zero with 0 rows
market_data_agent	Metered separately, billed through the run
hg_catalog, get_vendor_information, get_product_category	Free
search_companies	1 per 100 companies returned

Because cost follows the data scanned, not the rows returned, prefer COUNT and aggregates, filter on ids, and avoid exploratory full-table queries. Set a query budget with the user before a multi-query list build, and report credits_consumed per query.

Common pitfalls

1. Writing SQL when search_companies could answer. More cost, more risk.
2. Guessing names. Resolve with the free lookups and filter by id.
3. Skipping the catalog's mandatory filters.
4. Trusting the default max_rows. It truncates at 1000 without warning.
5. Assuming the tool exists. Fall back to search_companies or market_data_agent.

Reference

• V2 tool docs: https://phoenix.hginsights.com/docs/mcp-tools/v2/hg-data-query
• V2 tool docs: https://phoenix.hginsights.com/docs/mcp-tools/v2/hg-catalog
• Full docs for LLMs: https://phoenix.hginsights.com/docs/llms-full.txt
• Related skills: hg-mcp-calling-rules, hg-search-companies, hg-momentum-signals, hg-mcp-version-and-availability, hg-credit-awareness