1.1 Dataverse Table Types, Properties & Ownership

Dataverse Table Types, Properties & Ownership

Microsoft Dataverse serves as the intelligent, secure, and scalable relational data backbone of the Microsoft Power Platform. Built on enterprise-grade Azure infrastructure, Dataverse abstracts underlying relational storage (Azure SQL Database, Cosmos DB, and Azure Blob/Data Lake storage) behind a unified, metadata-driven business layer. As a Power Platform Functional Consultant preparing for the PL-200 exam, understanding how tables are classified, how records are owned, and how foundational table properties govern enterprise data behavior is essential for designing resilient data architectures.

---

1. Dataverse Table Classifications

Dataverse organizes structured business data into distinct table categories. Choosing the correct table type directly impacts system capabilities, performance, and application user experience.

+-----------------------------------------------------------------------------+
|                        DATAVERSE TABLE CLASSIFICATIONS                      |
|                                                                             |
|   +-------------------+  +-------------------+  +-------------------+      |
|   |  STANDARD TABLES  |  |   CUSTOM TABLES   |  |  ACTIVITY TABLES  |      |
|   | - Common Data Mod |  | - Custom schemas  |  | - activityparty   |      |
|   | - Account, Contact|  | - Publisher prefix|  | - Timeline rollup |      |
|   | - Extensible      |  | - User/Org owned  |  | - Immutable type  |      |
|   +-------------------+  +-------------------+  +-------------------+      |
|                                    |                                        |
|                                    v                                        |
|                          +-------------------+                              |
|                          |  VIRTUAL TABLES   |                              |
|                          | - External OData  |                              |
|                          | - No native store |                              |
|                          | - No audit/rollup |                              |
|                          +-------------------+                              |
+-----------------------------------------------------------------------------+

1. Standard Tables

Standard tables are out-of-the-box business entities included with the Dataverse environment, aligned with the open-source Common Data Model (CDM). Examples include Account, Contact, Lead, Opportunity, and Case (Incident).

• Extensibility: Standard tables can be extended with custom columns, forms, views, relationships, and business logic.
• Restrictions: Core system columns and baseline system tables cannot be deleted. Display names can be customized to match organizational terminology (e.g., renaming Account to Company), but the underlying schema name remains constant.

2. Custom Tables

Custom tables are created by functional consultants and solution architects to fulfill bespoke business requirements not satisfied by the standard CDM schema.

• Naming Conventions: Custom tables automatically receive a solution publisher customization prefix (e.g., cr123_Equipment, contoso_InspectionLog) separated by an underscore.
• Schema vs. Display Name: The Display Name (singular/plural) can be modified at any time, but the Schema Name and Logical Name are permanently fixed upon table creation.

3. Activity Tables

Activity tables represent time-based, actionable interactions or tasks performed by users or automated processes. Built-in activity tables include Email, Phone Call, Task, Appointment, and Letter.

• The ActivityParty Model: Activity tables utilize a specialized multi-party relationship model (activityparty). This allows a single activity record to link to multiple sender, recipient, CC, BCC, and organizer records across different tables (e.g., Accounts, Contacts, Leads, System Users) simultaneously.
• The Regarding (regardingobjectid) Polymorphic Lookup: Every activity table includes a standard polymorphic lookup column called Regarding. When an activity is linked to a parent record (such as an Account), the activity automatically appears in that record's timeline control.
• Timeline Aggregation & Rollup: Activities roll up hierarchically. If an activity is linked regarding an Opportunity, it surfaces on the Opportunity timeline, the parent Account timeline, and the primary Contact timeline.
• Immutability: Once a table is created as an Activity table, it CANNOT be converted into a standard table. Conversely, a standard table cannot be converted into an Activity table.

4. Virtual Tables (Virtual Entities)

Virtual tables enable Dataverse to surface, query, and manipulate data stored in external data sources (such as Azure SQL, SharePoint lists, SAP, or RESTful endpoints via OData v4) as if the records resided natively inside Dataverse.

• Architecture: Data remains residing in the external system. When a user opens a view or form in Power Apps, Dataverse makes a real-time call through an OData v4 Data Provider, SQL Provider, or Custom Plug-in Data Provider.
• Full CRUD Support: Virtual tables support Create, Read, Update, and Delete operations if the underlying data provider and external API support them.
• Platform Limitations: Because virtual data is not physically stored or indexed within the Dataverse relational database engine, virtual tables have specific functional limitations:
  • No native Dataverse Auditing.
  • No Change Tracking support for Azure Synapse Link / Microsoft Fabric.
  • No Rollup column calculations aggregating virtual child records.
  • No offline synchronization in Power Apps mobile apps.
  • Cannot be enabled for Business Process Flows without custom background synchronizations.

---

2. Table Ownership Architecture

When creating a custom table in Dataverse, the consultant must choose between two distinct ownership models: User or Team ownership and Organization ownership. This setting establishes how the Dataverse security engine evaluates record-level access privileges.

+-----------------------------------------------------------------------------+
|                        TABLE OWNERSHIP ARCHITECTURES                        |
|                                                                             |
|   [USER OR TEAM OWNED]                    [ORGANIZATION OWNED]              |
|   - Granular 4-level security depth       - Binary security access          |
|   - User / BU / Parent-Child / Org        - Organization-wide or None       |
|   - Has 'Owner' & 'OwningBusinessUnit'    - No 'Owner' column               |
|   - Reassignable, shareable               - Shared global reference data    |
|   - Example: Projects, Invoices, Leads    - Example: Tax Codes, Zip Codes   |
+-----------------------------------------------------------------------------+

User or Team Owned

• Mechanics: Each individual record contains system-managed foreign key columns: OwnerId (pointing to a System User or Owner Team) and OwningBusinessUnit.
• Security Depth: Supports all four security role privilege depths:
  1. User (Basic): User can access only records they own or share.
  2. Business Unit (Local): User can access records owned by anyone within their business unit.
  3. Parent-Child Business Unit (Deep): User can access records in their business unit and all subordinate child business units.
  4. Organization (Global): User can access all records across the entire tenant environment.
• Collaboration: Records can be assigned, shared, reassigned, and managed across dynamic access teams and Entra ID security groups.
• Best For: Core transactional business entities requiring department segregation, sales territories, case assignments, and individual ownership (e.g., Work Orders, Contracts, Claims).

Organization Owned

• Mechanics: Records belong globally to the entire organization. There is no Owner or OwningBusinessUnit column.
• Security Depth: Security roles provide only binary access: either Organization (Global) access or None.
• Best For: Global reference data, lookup tables, configuration parameters, and company-wide catalogs that apply uniformly to all users regardless of department (e.g., Country Codes, Tax Rates, Currency Lists, Product Categories).

[!CAUTION] Ownership Setting Immutability: The Table Ownership model is defined during initial table provisioning and is permanently immutable. Once a table is saved as User/Team owned or Organization owned, this property cannot be modified. If an incorrect ownership model is selected, the table must be deleted and recreated.

---

3. Primary Name and Primary Key Identifiers

Every Dataverse table is automatically generated with two essential identifier columns:

1. Primary Key (GUID):
   • A unique 128-bit Globally Unique Identifier (e.g., a1b2c3d4-e5f6-7890-abcd-ef1234567890).
   • Schema name follows the convention <tablename>id (e.g., accountid, cr123_inspectionid).
   • System-managed, auto-generated upon record insertion, immutable, and indexed as the primary clustered key in SQL storage.
2. Primary Name Column:
   • A text column representing the human-readable label of the record.
   • Used as the display value in lookup fields, header controls, search results, and navigation links.
   • By default, Dataverse names this column Name (or cr123_name).
   • Can be configured with single-line text formatting (maximum length up to 4,000 characters; default is 100 characters).
   • Requirement level can be set to Business Required, Business Recommended, or Optional.

---

4. Enterprise Table Properties & Feature Enablement

When provisioning tables in the Power Apps Maker Portal or through solution XML, several advanced enterprise capabilities can be enabled. Some properties can be toggled on or off freely, while others are one-way activations that cannot be disabled once turned on.

+-----------------------------------------------------------------------------+
|                   DATAVERSE 3-TIER AUDITING HIERARCHY                       |
|                                                                             |
|   [TIER 1: ENVIRONMENT LEVEL]  ---> Must enable global tenant auditing      |
|               |                                                             |
|               v                                                             |
|   [TIER 2: TABLE LEVEL]        ---> Must enable auditing on specific table  |
|               |                                                             |
|               v                                                             |
|   [TIER 3: COLUMN LEVEL]       ---> Must enable auditing on specific column |
|                                     (Logs attribute changes to audit log)   |
+-----------------------------------------------------------------------------+

1. Auditing (3-Tier Hierarchy)

Dataverse provides enterprise auditing to track record creation, updates, deletions, and security role assignments. Auditing operates on a strict 3-tier hierarchical structure:

1. Environment Level: Auditing must be turned on in Power Platform Admin Center (Settings > Audit and logs > Audit settings).
2. Table Level: Auditing must be checked in the table properties.
3. Column Level: Auditing must be enabled on the individual columns requiring change tracking.

[!IMPORTANT] Auditing Rule for PL-200: If Auditing is enabled at the Environment level and Table level, but disabled on a specific Column, changes to that column will NOT be captured. Conversely, if a column has auditing enabled but the parent table has auditing disabled, no logs are created. All three tiers must be active for attribute logging.

2. Change Tracking

Change Tracking allows external applications and data pipelines to detect and synchronize incremental data modifications (inserts, updates, and deletes) without performing expensive full-table scans.

• Downstream Dependencies: Change Tracking is a mandatory prerequisite for enabling Azure Synapse Link for Dataverse, Microsoft Fabric Link, and custom data integration export services.
• One-Way Setting: Once downstream export services lock Change Tracking, it cannot be disabled without removing the service link.

3. Duplicate Detection

Duplicate Detection evaluates incoming and modified records against predefined Duplicate Detection Rules.

• Prerequisites: Duplicate detection must be enabled at the Environment level and Table level.
• Execution Points: Triggers during manual record creation/updates in model-driven apps, bulk data imports, and when records are synchronized via Server-Side Synchronization.

4. Document Management (SharePoint Integration)

Enabling Document Management allows Dataverse records to automatically provision linked document folders within SharePoint document libraries.

• Creates a seamless document grid on the model-driven form, allowing users to upload, view, and collaborate on Office documents associated with a specific record.

5. Connections

Enabling Connections allows users to establish ad-hoc, semantic relationships between records across any connection-enabled tables (e.g., linking a Contact to another Contact as 'Spouse', 'Attorney', or 'Colleague') without creating formal 1:N or N:N database schema relationships.