Dv Query

Skill: Query — Read and Analyze Dataverse Records

This skill uses Python and the Dataverse CLI. Do not use Node.js, JavaScript, or any other language for Dataverse scripting. See the overview skill's Hard Rules.

Reads: prefer a managed surface, choose by shape

Fast path for simple reads: If dataverse auth who shows an active profile, skip workspace setup and query directly with the CLI examples below. No .env, auth.py, pip install, or PAC needed for data reads.

Pick MCP, the Dataverse CLI, or the SDK by the shape of the read — all three handle auth and retry (see the routing table below and the overview's Tool Capabilities / Hard Rule 2). MCP fits small, interactive reads; the CLI fits headless one-liners (OData, SQL, count); the SDK fits bulk iteration and analytics. For $apply aggregation and N:N $expand, prefer client.query.fetchxml() (aggregates + link-entity) or the managed dataverse api escape hatch; reach for hand-rolled urllib/get_token() only to stay in-process inside a tight Python loop (e.g. paging thousands of rows with client-side processing — see web-api-advanced.md).

Dataverse CLI gotchas (custom tables + Windows)

When you drive the dataverse CLI directly (headless reads/CRUD; note the CLI needs .NET + a keyring, so it is blocked on ChatGPT web / Codex cloud — use the SDK there), two empirical traps:

• Custom-table SQL pluralization. dataverse data query in SQL mode auto-pluralizes the table name, and irregular plurals resolve wrong: FROM im_category looks up entity set im_categorys and returns a 404 that reads like "table missing." It is not — switch to OData mode with the explicit entity set: dataverse data query --table im_categories --select im_name. Discover the real EntitySetName from EntityDefinitions when unsure; never conclude the table doesn't exist from this 404.
• Windows shell quoting. Wrap the whole --path value in double quotes so cmd.exe/PowerShell don't treat & as a command separator. Keep & literal — it separates OData query options; encoding it to %26 merges them and breaks the query. Encode only $->%24 (in PowerShell a bare $select is read as a variable). If an unquoted & splits the command, the wrapper can exit nonzero even when the API returned valid JSON — quoting prevents it. (This is why the dataverse api request examples in other skills quote the path, use %24, and leave & literal.)

Dataverse CLI query examples (copy-paste ready)

All dataverse commands take --context for skill attribution (global flag).

# OData filtered read (--table takes the EntitySet name, e.g. accounts not account)
dataverse data query --table accounts --select "name,accountid" --filter "name eq 'john'" --top 10 --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

# Count records
dataverse data count --table accounts --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

# SQL mode (uses the logical name, e.g. account not accounts)
dataverse data query --sql "SELECT name, accountid FROM account WHERE name LIKE '%john%'" --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

# Get single record by ID
dataverse data get --table accounts --id <guid> --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

# Raw API escape hatch
dataverse api request --target dataverse --path "/api/data/v9.2/accounts?%24select=name&%24top=5" --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

ERP target is a separate path. ERP (Finance and Operations), when linked to a Dataverse env, does not go through the Python SDK. See references/erp-reads.md.

How to Answer Data Questions

When the user asks a question about their data, pick the approach by what they're asking, not by which API you know:

Key principle: Let the server do the work. For single-table aggregation, use $apply (raw) or client.query.sql() GROUP BY — both run server-side and return only grouped results. For cross-table questions, prefer a server-side sql() JOIN (INNER/LEFT) or fetchxml() link-entity; when SQL can't express it, pull each table via client.query.builder(t).select(...).execute().to_dataframe() and pd.merge() — the merge is sub-second; the bottleneck is network transfer, which select minimizes.

Always query the live Dataverse environment. Do not query local copies, cached files, or source databases when the user expects results from Dataverse. The data in Dataverse is the source of truth.

SQL Queries — client.query.sql()

client.query.sql() uses the Dataverse Web API ?sql= parameter — a T-SQL subset. It supports SELECT / SELECT DISTINCT / SELECT TOP N (0-5000), INNER JOIN / LEFT JOIN, WHERE, GROUP BY, ORDER BY, OFFSET/FETCH, and COUNT/SUM/AVG/MIN/MAX. It does NOT support SELECT *, subqueries, CTEs, HAVING, UNION, RIGHT/FULL/CROSS JOIN, CASE, or string/date/math functions. Results are capped at ~5,000 rows.

When to use: Fast filtered reads on tables with <5K rows. For these, it's significantly faster (~2-6s) than page iteration or DataFrames because it's a single HTTP call.

# Fast filtered read on small tables (<5K rows)
results = client.query.sql(
    "SELECT TOP 100 name, estimatedvalue "
    "FROM opportunity "
    "WHERE statecode = 0 "
    "ORDER BY estimatedvalue DESC"
)
for r in results:
    print(f"{r['name']}: ${r.get('estimatedvalue', 0):,.0f}")

Do NOT use for: Tables >5K rows (results silently truncated), SELECT *, subqueries/CTEs, HAVING, UNION, RIGHT/FULL/CROSS JOIN, or functions. INNER/LEFT JOIN and GROUP BY are supported — use them for server-side joins/aggregation on <5K-row results; for larger or unsupported shapes use fetchxml() or $apply.

FetchXML — server-side joins and aggregates

For SQL-JOIN scenarios or aggregates the OData builder cannot express, use FetchXML. client.query.fetchxml(xml) returns an inert query object — no HTTP is made until you call .execute() (eager, all pages) or .execute_pages() (lazy, one page at a time). Both return QueryResult pages with .to_dataframe().

query = client.query.fetchxml("""
  <fetch top="50">
    <entity name="account">
      <attribute name="name" />
      <link-entity name="contact" from="parentcustomerid" to="accountid" alias="c" link-type="inner">
        <attribute name="fullname" />
      </link-entity>
    </entity>
  </fetch>
""")

result = query.execute()          # collect all pages
df = result.to_dataframe()

# Or stream one page at a time for large results:
for page in query.execute_pages():
    print(page.to_dataframe().shape)

Discover queryable columns — client.query.sql_columns()

Before writing a SQL or $select read, list the columns the SQL endpoint can actually query — virtual and computed lookup-display columns are excluded. Each entry has name, type, is_pk, is_name, and label.

for c in client.query.sql_columns("account"):
    print(f"{c['name']:30s} {c['type']:20s} PK={c['is_pk']}")

For deeper schema inspection — full column metadata and table relationships — use dv-metadata (client.tables.list_columns(), client.tables.list_relationships(), client.tables.list_table_relationships()).

Skill boundaries

Setup

import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

# get_client sets a plugin attribution context on the User-Agent header.
# Do not modify the context value — it is a closed schema for server-side
# telemetry (app/skill/agent). Never include secrets or PII.
client = get_client("dv-query")

get_client(skill) handles auth, environment URL, and plugin attribution (User-Agent tagging). See scripts/auth.py. For scripts that run to completion, wrap the returned client in a with statement for automatic connection cleanup. For ERP, use ERP MCP or the Dataverse CLI --target erp path — see references/erp-reads.md.