Dataverse: Alternate Keys, Change Tracking, and Auditing

Dataverse: Alternate Keys, Change Tracking, and Auditing

1. Alternate Keys

What are Alternate Keys?

Every Dataverse row has a system-generated primary key (GUID). An alternate key is a user-defined unique identifier built from one or more columns (for example, Account Number or Account Code + Region). It lets Dataverse uniquely identify records using business-friendly values instead of GUIDs.

When should you use Alternate Keys?

  • Data integration – External systems don’t store Dataverse GUIDs but have their own IDs.
  • Upsert operations – To update-or-insert (UPSERT) records using a natural key like AccountNumber.
  • Uniqueness enforcement – Prevent duplicates at database level.
  • Lookups without GUID – Power Automate / API can reference records by alternate key instead of GUID.

How to create an Alternate Key (Maker Portal)

  1. Open make.powerapps.com and select the correct environment.
  2. Go to Tables and open the required table (for example, Account).
  3. In the left pane, select Keys (or from the table designer choose the Keys area).

  4. Click + New alternate key.
  5. Give a Display name and (optionally) confirm the Schema name.
  6. Select one or more columns that together must be unique (for example: accountnumber)

  7. Click Save. Dataverse creates a backing index in the database.
  8. Monitor key status:
    • Pending / In Progress – Index creation is still running.
    • Active – Key is usable and uniqueness is enforced.
    • Failed – Often due to existing duplicate data or size constraints.

Supported column types for Alternate Keys

Only specific column types can be used in an alternate key:

  • Decimal Number
  • Whole Number
  • Single line of text
  • Date and Time
  • Lookup
  • Choice / Option Set

Important rules and limitations

  • Max 10 alternate keys per table in Dataverse.
  • Columns used in the key must not have field-level security enabled.
  • Total key size must respect SQL index limits (for example, ~900 bytes and up to 16 columns in a key).
  • Virtual tables are not supported for alternate keys.
  • If key column values contain characters like / < > * % & : \ ? +, they can’t be used for some API operations (GET/PATCH using that key).
  • Null values are allowed but not indexed; they don’t participate in uniqueness.

How Alternate Keys are used (examples)

  • Power Automate – Use "Row ID = alternate key value" in some actions that support keys instead of GUID.
  • Web API Upsert – Call PATCH /accounts(accountnumber='ACC-1001') to create or update based on key.
  • Data migration – Load data multiple times and let alternate keys handle de-duplication and updates.

2. Change Tracking

What is Change Tracking?

Change Tracking is a Dataverse feature that marks which rows were added, updated, or deleted so that external systems can synchronize data incrementally instead of reading the full table every time. It is designed for data synchronization, not for detailed history.

Typical use cases

  • Power BI incremental refresh – Load only changed records.
  • Azure Data Factory / Synapse – Delta copy from Dataverse to SQL / Lake.
  • Custom integrations – Use Web API delta queries to sync changes.

How to enable Change Tracking on a table

  1. Open make.powerapps.com and select your environment.
  2. Go to Tables and open the required table.
  3. In the table designer, go to the Settings or Advanced options area.
  4. Find the option Track changes or Enable change tracking.

  5. Turn it ON and click Save / Publish.

How consumers read changes

  • Dataverse Web API (delta queries)
    1. First request with track-changes preference returns all data + a delta token (deltaLink).
    2. Next time, call the deltaLink to get only new/changed/deleted rows since last sync.
  • Power BI / ADF / Synapse
    These tools use change tracking internally to pull only changes from Dataverse.

What Change Tracking does not provide

  • No field-level history (it doesn’t show which specific column changed).
  • No “old value vs new value”.
  • No direct UI to view changes in model-driven apps.
  • Not a replacement for Auditing; it’s only for incremental sync.

Key points & considerations

  • Change Tracking is configured per table, not per column.
  • It helps reduce load on Dataverse and external systems by avoiding full loads.
  • If you enable it on many large tables, there is still some overhead – choose tables that are actually needed for syncing.
  • Power Automate Dataverse triggers (When a row is added, modified, or deleted) do not require change tracking.

3. Auditing

What is Auditing?

Auditing in Dataverse records a detailed history of: who changed data, what they changed, and when they changed it. It is used for compliance, security, and troubleshooting.

What Auditing can log

  • Create, update, and delete operations on records.
  • Field-level changes (old value → new value for audited columns).
  • User access and operations (opening records, logging in through apps or SDK).
  • Changes to security roles, sharing, and some configuration operations.

Steps to enable Auditing

  1. Enable auditing at environment level
    • Go to Power Platform Admin Center.
    • Select your environment > Settings.
    • Under Audit and logs / Auditing, turn on Dataverse auditing.

  2. Enable auditing on specific tables
    • Open make.powerapps.com, select environment.
    • Go to Tables and open the table (e.g., Account).
    • On the table’s Settings / Advanced section, enable Auditing.


  3. Enable auditing on important columns
    • Open the table, go to Columns.
    • Open a column (for example, creditlimit) and turn on Auditing for that column.

How to view audit logs

  • Per record – In a model-driven app form, use the command like Audit History (or related tab) to see the list of changes for that record.

  • System-wide – Use the auditing views (Audit Summary View or equivalent) in the advanced settings / app area to filter by user, table, date, or action.
  • Analytics – Export audit logs to Azure (for example with Azure Synapse Link) and report in Power BI if you need large-scale audit reporting.

Difference between Auditing and Change Tracking

  • Auditing – Detailed history (who, what, when, old value, new value). Used for compliance and investigations.
  • Change Tracking – Only that a row changed (add/update/delete) for sync. Does not show old/new value or user.
  • They are independent features and can be enabled separately.

Best practices & limitations for Auditing

  • Enable auditing only for important tables and fields to control storage usage.
  • Regularly monitor and manage audit log storage; old logs can be exported and cleaned up if needed.
  • Be careful with sensitive data (PII, financial, health) and your organization’s compliance policies.
  • Auditing may add some overhead; avoid auditing every field on very high-volume tables unless required.

Comments

Post a Comment

Popular posts from this blog

Part 1: Creating Code Apps in Power Apps - A step-by-step guide (with real errors I faced & how I fixed them)

Image
Intro In this post I’ll walk you through how I set up a code-based app for Power Apps (React/Vite front-end + Power Platform CLI for packaging/deployment), the exact errors I ran into, and how I solved them. If you want a hands-on, copy-paste able guide that covers Node versions, pac auth, environment issues, and packaging tips, this is for you. Prerequisites Microsoft 365 account with access to a Power Platform environment Node.js ( 20.19+  recommended LTS) VS Code Install power platform tools extension in VS Code. Steps: Step 1 : Open Visual Studio Code and launch the integrated terminal. Step 2: In the terminal, run the following commands one by one.   mkdir D:\CodeApps - Force cd D:\CodeApps npm create vite @latest myfirstapp -- -- template react - ts cd D:\CodeApps\myfirstapp npm install Initially, I ran the command: npm create vite@latest MyFirstApp -- --template react-ts This gave the error: Invalid package.json name The issue was caused because package...
Read more

Calling Microsoft Graph API from Power Automate Using Azure App Services – Step-by-Step Guide

Image
Step 1: You should have an Azure subscription, or if you have access to the Microsoft Entra Admin Center, you can create an app from there. This app can then be used in Power Automate to connect to Microsoft Graph API. Step 2: Click on "New registration" , provide a name for your app, assign the required API permissions (such as Microsoft Graph permissions), and then click "Register" to complete the app registration process. Step 3: After registration, you'll be redirected to the app's overview page. From there, navigate to the API permissions tab and click "Add a permission" to assign the necessary Microsoft Graph API permissions. Step 4: Select Microsoft Graph as the API, and based on your requirements, choose the appropriate permissions. In my case, I selected Application permissions , which do not require user sign-in and are suitable for background services or automation. Step 5: Select the required Microsoft Graph API permissions. Yo...
Read more

Step-by-Step Guide: Power Automate Custom Connector Using Graph API from Azure App Service

Image
Step 1: Open Power Automate, navigate to the Custom Connectors tab, and select Create from blank as shown in the image below. custom connector(click image for better resolution) Step 2: In the General tab, upload an icon (less than 1MB), add a description, select the correct scheme (in my case, HTTPS ), and provide the host name. For example, if your API URL is https://graph.microsoft.com/v1.0/users/{email} , the host name will be the highlighted part — graph.microsoft.com . Step 3: Go to the Security tab. Here, you need to select the authentication method. Choose OAuth 2.0 as the authentication type, as shown in the image below. Step 4: Select the Identity Provider as Azure Active Directory . Check the box Enable Service Principal Support . This option allows you to create a connection using a Client ID and Client Secret instead of using the user's own authentication. Next, provide the Client ID , Client Secret , Tenant ID , and other required details as shown in t...
Read more