Get Help

Test Dataverse

Dataverse — the database behind Dynamics 365 and Power Apps — exposes a read-only SQL endpoint. CAT queries it like SQL Server with the SqlServer@2 provider, so CRM data can be tested, and compared with the warehouse it feeds, without an export.

Prerequisites

  • The environment’s TDS endpoint is enabled — Power Platform admin center → Environments → the environment → Settings → Product → Features → Enable TDS endpoint. It is on by default; an administrator may have turned it off. With user-level control on, the account also needs the Allow user to access TDS endpoint privilege.
  • Microsoft Entra ID authentication only — your own account (MFA prompts included) interactively, a service principal with access to the environment for unattended runs. SQL and Windows authentication are rejected.
  • Outbound TCP 1433 (or 5558) from the machine that runs CAT to <organization>.crm.dynamics.com (the region suffix varies — crm4, crm11, …).

Add the data source

The server is the environment’s URL; there is no database to name. CAT Studio: technology Dataverse. YAML:

Data sources:
- Name: crm
  Provider: SqlServer@2
  Connection string: >
    Server=contoso.crm4.dynamics.com;
    Authentication=Active Directory Service Principal;
    User Id=%DATAVERSE_CLIENT_ID%;
    Password=%DATAVERSE_CLIENT_SECRET%;

Interactive instead: Authentication=Active Directory Interactive and no user/password. If only port 5558 is open, Server=contoso.crm4.dynamics.com,5558.

Write the tests

Tables are the Dataverse logical names — account, contact, opportunity; a lookup column comes as <lookup>id and <lookup>name, a choice column as value and label:

Tests:
- Name: Every active account has an owner
  Suite: CRM data quality
  Data source: crm
  Query: SELECT TOP 50 accountid, name FROM account WHERE statecode = 0 AND ownerid IS NULL
  Expectation: set is empty

- Name: Warehouse customers equal CRM accounts
  Suite: CRM vs warehouse
  First data source: crm
  First query: SELECT accountid, name FROM account WHERE statecode = 0 ORDER BY accountid
  Second data source: DWH
  Second query: SELECT SourceId, CustomerName FROM dim.Customer WHERE SourceSystem = 'CRM' AND IsActive = 1 ORDER BY SourceId
  Expectation: sets match
  Key: 1

Good to know

  • The endpoint is read-only and speaks a subset of T-SQL; SELECT with joins, GROUP BY, TOP, aggregates — yes; no temp tables, no INSERT. Details: How Dataverse SQL differs from Transact-SQL.
  • Queries time out after five minutes (two for SELECT *, nested FROMs and heavy joins) — select the columns you need, TOP what you can, and let Key + ORDER BY do the comparison, not the query.
  • Dates come back in UTC. Choice labels are expensive — filter and compare on the value column.
  • Dataverse is also a classic source for generated tests: one template over the list of entities or of option-set values.
← Test Microsoft Fabric
Test CSV and Excel files →