Skip to main content

Dataverse Change Tracking and Delta Synchronisation

Dataverse Change Tracking and Delta Synchronisation are foundational capabilities for building high-performance, scalable integrations between Microsoft Dataverse and external enterprise data stores (such as SQL Server, Azure Cosmos DB, or custom ERP systems). This chapter provides an exhaustive, production-grade guide to enabling, implementing, securing, and deploying change tracking and delta synchronisation architectures.

1. Conceptual Foundation​

Under-the-Hood Architecture​

Dataverse Change Tracking is built directly upon SQL Server's native Change Tracking engine. When Change Tracking is enabled on a Dataverse table, the platform instructs the underlying SQL database to track changes made to the table's rows.

Unlike Change Data Capture (CDC), which records the historical progression of every change by writing to transaction log tables, SQL Server Change Tracking is a synchronous, lightweight mechanism. It records only the fact that a row has changed (inserted, updated, or deleted) and associates that change with a monotonically increasing version number (a database-level transaction counter). It does not store the historical values of the modified columns.

When a client queries for changes, the engine performs a high-performance join between the base table and the internal change-tracking tables using the client's last-known version stamp. This design minimizes DML (Data Manipulation Language) overhead during standard transactional operations, ensuring that enabling change tracking does not degrade the performance of standard model-driven apps or API integrations.

Delta Tokens and Version Stamps​

The synchronization lifecycle is governed by Delta Tokens (also referred to as version stamps). A delta token is an opaque, service-generated string that represents a specific point-in-time state of the database transaction log.

[Initial Sync: Token = Null] ──> Returns All Records ──> Generates Token "V1"
                                                               │
[Data Changes Occur in Dataverse] <────────────────────────────┘
                               │
[Delta Sync: Token = "V1"] ────> Returns Only Changes ──> Generates Token "V2"
  1. Initial Sync (Baseline): The client executes a change retrieval request without providing a delta token (or passing null). Dataverse treats this as a request for the system minimum version. The server executes a standard query and returns the entire active dataset. The response includes a DataToken (e.g., 919042!08/22/2024).
  2. Token Storage: The client processes the baseline dataset and persists the returned DataToken in a secure, transactional metadata store alongside the synchronized data.
  3. Incremental Sync (Delta): For subsequent synchronizations, the client sends the stored DataToken back to Dataverse. The platform uses this token to filter the internal change-tracking tables, returning only the records that have been created, updated, or deleted since that specific version stamp was generated.
  4. New Token Generation: The delta response contains a new DataToken representing the current state. The client updates its metadata store with this new token for the next execution cycle.

Token Expiration and Retention Lifecycles​

Delta tokens are not permanent. Dataverse maintains change-tracking history for a limited duration to prevent database bloat. This retention period is controlled by the expirechangetrackingindays column in the Organization table.

  • Default Retention: 7 days.
  • Configurable Range: 0 to 365 days.
  • Metadata vs. Data Expiration: It is critical to distinguish between data change tracking (expirechangetrackingindays, default 7 days) and metadata change tracking (expiresubscriptionsindays, default 90 days).

If a client attempts to synchronize using a delta token older than the configured retention period, the underlying change-tracking cleanup job will have already purged the historical change records. In this scenario, Dataverse cannot guarantee data integrity and will throw an ExpiredVersionStamp exception (Error Code: 0x80044352).

Bidirectional Synchronisation and Loopback Prevention​

In a bidirectional synchronization architecture, Dataverse and an external system both act as read/write sources. This introduces the risk of infinite-loop conflicts (loopbacks):

[System A (Dataverse)] ──(Update)──> [Integration Engine] ──(Update)──> [System B (External)]
         ▲                                                                     │
         │                                                                     ▼
   (Triggers Sync)                                                       (Triggers Sync)
         │                                                                     │
         └───────────────(Update)◄── [Integration Engine] ◄──(Update)──────────┘

To prevent this loopback, the integration architecture must identify the origin of each write operation. There are four primary patterns to achieve this:

1. Integration User Context​

The integration engine connects to Dataverse using a dedicated Microsoft Entra ID App Registration mapped to an application user (e.g., svc-integration-sync). In Dataverse plug-ins or Power Automate flows, the system inspects the InitiatingUserId or CallerId. If the user matches the integration service account, the execution is bypassed.

2. Bypass Custom Business Logic (BypassBusinessLogicExecution)​

When the integration engine writes data back to Dataverse, it passes the optional parameter BypassBusinessLogicExecution with the value CustomSync or CustomSync,CustomAsync. This instructs Dataverse to bypass all custom plug-ins and Power Automate flows for that specific transaction, preventing the generation of outbound sync events. This requires the calling user to possess the prvBypassCustomBusinessLogic privilege.

3. Custom Tracking Fields​

Each table is extended with an origin tracking column (e.g., new_lastsyncsource) and a version tracking column (e.g., new_externalsyncversion). When the integration engine updates a record in Dataverse, it populates new_lastsyncsource with "ExternalSystem". Plug-ins or flows triggering outbound syncs evaluate this field; if it equals "ExternalSystem", they suppress the outbound message and clear the field (or set it back to "Dataverse").

4. SharedVariables (Intra-Transaction)​

For scenarios where changes are made within the same execution context (e.g., a plug-in calling an external API which immediately writes back to Dataverse), the plug-in utilizes the IPluginExecutionContext.SharedVariables collection. By setting a flag (e.g., context.SharedVariables["IsSyncing"] = true), subsequent plug-in executions in the same pipeline transaction can detect the flag and terminate early.

2. Architecture & Decision Matrix​

When designing a synchronization pipeline, developers must select the appropriate technology stack based on volume, latency, and complexity.

Comparison of Implementation Approaches​

Feature / MetricPower AutomateAzure Functions (C# SDK)Dataverse Plug-insAzure Data Factory / Synapse Link
ComplexityLow (No-code/Low-code)Medium (Pro-code C#)High (Pro-code C# / Sandbox)Low to Medium
ScalabilityLow to Medium (Throttled by API limits)Extremely High (Serverless scaling)Medium (Bound by 2-minute execution limit)High (Optimized for bulk data)
Execution ContextAsynchronous (Event-driven)Asynchronous (Polling or Webhook)Synchronous or AsynchronousScheduled Batch / Near Real-Time
Offline SupportNoYes (Can cache tokens locally)NoNo
LicensingPower Automate Per-User/Per-FlowAzure Consumption (Pay-as-you-go)Included in Power Apps/DataverseAzure Subscription
PL-400 Exam RelevanceHigh (Triggers, Actions)High (Integration patterns)Critical (Pipeline, Sandbox, Bypass)Medium (Data export concepts)

Architectural Recommendation​

  • Use Azure Synapse Link / Azure Data Factory for one-way, high-volume analytical replication (Dataverse to Data Lake/Warehouse).
  • Use Azure Functions with the .NET Dataverse SDK (ServiceClient) for complex, bidirectional, near-real-time transactional synchronization with external APIs.
  • Use Power Automate only for low-volume, simple, one-way notifications or lightweight integrations where latency is not critical.
  • Use Dataverse Plug-ins strictly for real-time validation, intra-transaction calculations, or immediate loopback prevention using SharedVariables.

3. Step-by-Step Implementation Guide​

This guide details how to configure and execute a complete delta synchronization pipeline between Dataverse and an external system.

Step 1: Enable Change Tracking on the Table​

Change tracking must be enabled at the table metadata level. This can be done via the Power Apps Maker Portal, solution XML customization, or programmatically.

Via Power Apps Maker Portal​

  1. Navigate to the Power Apps Maker Portal.
  2. Select your Environment in the top-right corner.
  3. Expand Dataverse and select Tables.
  4. Select the target table (e.g., Account).
  5. On the command bar, select Properties (or click the gear icon next to the table name).
  6. Expand Advanced options.
  7. Scroll down to the For this table section and check the Track changes checkbox.
  8. Click Save.

Programmatically via C# SDK​

To enable change tracking programmatically (useful for dynamic deployment scripts or ISV solutions), execute an UpdateEntityRequest:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;

public static void EnableChangeTracking(IOrganizationService service, string tableLogicalName)
{
    // Retrieve current metadata
    RetrieveEntityRequest retrieveRequest = new()
    {
        LogicalName = tableLogicalName,
        EntityFilters = EntityFilters.Entity
    };
    var retrieveResponse = (RetrieveEntityResponse)service.Execute(retrieveRequest);
    EntityMetadata metadata = retrieveResponse.EntityMetadata;

    // Set ChangeTrackingEnabled to true
    metadata.ChangeTrackingEnabled = true;

    UpdateEntityRequest updateRequest = new()
    {
        Entity = metadata
    };
    service.Execute(updateRequest);
}

Step 2: Configure the External Metadata Store​

The external system must maintain a metadata table to track the synchronization state. Create a table in your external database (e.g., SQL Server) to store the last successful delta token:

CREATE TABLE DataverseSyncMetadata (
    TableLogicalName VARCHAR(100) PRIMARY KEY,
    LastDeltaToken VARCHAR(250) NULL,
    LastSyncDateTime DATETIME2 DEFAULT GETUTCDATE(),
    SyncStatus VARCHAR(50) NOT NULL DEFAULT 'PENDING'
);

-- Initialize the row for the account table
INSERT INTO DataverseSyncMetadata (TableLogicalName, LastDeltaToken, SyncStatus)
VALUES ('account', NULL, 'INITIAL_REQUIRED');

Step 3: Execute the Initial Synchronization (Baseline)​

When LastDeltaToken is NULL, the integration engine must perform a full synchronization.

  1. Instantiate a RetrieveEntityChangesRequest.
  2. Set EntityName to "account".
  3. Define the Columns to retrieve.
  4. Set DataVersion to null (or omit it).
  5. Execute the request and process the returned records as NewOrUpdatedItem types.
  6. Extract the DataToken from the response and save it to DataverseSyncMetadata.

Step 4: Execute the Incremental Synchronization (Delta)​

For subsequent runs, retrieve the stored token from the database and pass it to the request.

  1. Query DataverseSyncMetadata to retrieve the LastDeltaToken.
  2. Instantiate RetrieveEntityChangesRequest.
  3. Set DataVersion to the retrieved token.
  4. Execute the request.
  5. Loop through the Changes collection:
    • If the item is of type NewOrUpdatedItem, upsert the record in the external database.
    • If the item is of type RemovedOrDeletedItem, delete the record from the external database using the Id from the RemovedItem reference.
  6. Save the new DataToken returned in the response back to DataverseSyncMetadata.

Step 5: Handle Paging​

If the number of changes exceeds the page size (default and maximum is 5,000 records for standard tables), the response will have MoreRecords set to true. You must page through the results using the PagingCookie.

  1. Check response.EntityChanges.MoreRecords.
  2. If true, extract response.EntityChanges.PagingCookie.
  3. Assign the PagingCookie to the PageInfo.PagingCookie property of the next request.
  4. Increment PageInfo.PageNumber.
  5. Execute the request and repeat until MoreRecords is false.

4. Complete Code Reference​

The following production-grade C# class implements the complete delta synchronization lifecycle, including paging, token expiration handling, and loopback prevention.

// <summary>
// Production-grade service to handle Dataverse Change Tracking and Delta Synchronization.
// Implements full sync, incremental sync, paging, and robust error handling for token expiry.
// </summary>

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;

namespace DataverseSyncEngine
{
    public class DataverseDeltaSyncService
    {
        private readonly IOrganizationService _service;
        private readonly string _connectionString; // External DB Connection String
        private const int PageSize = 5000;

        public DataverseDeltaSyncService(IOrganizationService service, string dbConnectionString)
        {
            _service = service ?? throw new ArgumentNullException(nameof(service));
            _connectionString = dbConnectionString ?? throw new ArgumentNullException(nameof(dbConnectionString));
        }

        /// <summary>
        /// Orchestrates the synchronization process for a specific table.
        /// </summary>
        public void SynchronizeTable(string entityName, ColumnSet columns)
        {
            string lastToken = GetLastDeltaToken(entityName);
            bool isFullSync = string.IsNullOrEmpty(lastToken);

            try
            {
                ExecuteSync(entityName, columns, lastToken);
            }
            catch (FaultException<OrganizationServiceFault> ex) when (ex.Detail.ErrorCode == -2147204270) // 0x80044352 - ExpiredVersionStamp
            {
                // Log warning: Token expired. Initiating full resynchronization.
                Console.WriteLine($"[WARNING] Delta token expired for {entityName}. Initiating full resync.");
                ResetSyncMetadata(entityName);
                ExecuteSync(entityName, columns, null);
            }
            catch (Exception ex)
            {
                // Log critical error
                Console.WriteLine($"[ERROR] Synchronization failed for {entityName}: {ex.Message}");
                throw;
            }
        }

        private void ExecuteSync(string entityName, ColumnSet columns, string deltaToken)
        {
            RetrieveEntityChangesRequest request = new RetrieveEntityChangesRequest
            {
                EntityName = entityName,
                Columns = columns,
                DataVersion = deltaToken,
                PageInfo = new PagingInfo
                {
                    Count = PageSize,
                    PageNumber = 1,
                    ReturnTotalRecordCount = false,
                    PagingCookie = null
                }
            };

            string nextToken = null;
            bool hasMoreRecords = true;

            while (hasMoreRecords)
            {
                RetrieveEntityChangesResponse response;
                try
                {
                    response = (RetrieveEntityChangesResponse)_service.Execute(request);
                }
                catch (FaultException<OrganizationServiceFault> ex)
                {
                    throw; // Caught by the orchestrator to handle ExpiredVersionStamp
                }

                if (response.EntityChanges == null)
                {
                    break;
                }

                ProcessChanges(response.EntityChanges.Changes);

                nextToken = response.EntityChanges.DataToken;
                hasMoreRecords = response.EntityChanges.MoreRecords;

                if (hasMoreRecords)
                {
                    request.PageInfo.PagingCookie = response.EntityChanges.PagingCookie;
                    request.PageInfo.PageNumber++;
                }
            }

            if (!string.IsNullOrEmpty(nextToken))
            {
                UpdateSyncMetadata(entityName, nextToken, "SUCCESS");
            }
        }

        private void ProcessChanges(BusinessEntityChangesCollection changes)
        {
            var upsertList = new List<Entity>();
            var deleteList = new List<Guid>();

            foreach (IChangedItem change in changes)
            {
                switch (change.Type)
                {
                    case ChangeType.NewOrUpdated:
                        if (change is NewOrUpdatedItem newItem)
                        {
                            upsertList.Add(newItem.NewOrUpdatedEntity);
                        }
                        break;

                    case ChangeType.RemoveOrDeleted:
                        if (change is RemovedOrDeletedItem deletedItem)
                        {
                            deleteList.Add(deletedItem.RemovedItem.Id);
                        }
                        break;
                }
            }

            // Execute bulk operations in the external database
            BulkUpsertToExternalDatabase(upsertList);
            BulkDeleteFromExternalDatabase(deleteList);
        }

        /// <summary>
        /// Updates a record in Dataverse while bypassing custom business logic to prevent loopbacks.
        /// </summary>
        public void UpdateRecordWithBypass(Entity targetEntity)
        {
            UpdateRequest request = new UpdateRequest
            {
                Target = targetEntity
            };

            // Bypass custom plug-ins and workflows
            request.Parameters.Add("BypassBusinessLogicExecution", "CustomSync,CustomAsync");
            
            // Suppress Power Automate flows
            request.Parameters.Add("SuppressCallbackRegistrationExpanderJob", true);

            _service.Execute(request);
        }

        #region Database Metadata Helpers

        private string GetLastDeltaToken(string entityName)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string query = "SELECT LastDeltaToken FROM DataverseSyncMetadata WHERE TableLogicalName = @TableName";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@TableName", entityName);
                    object result = cmd.ExecuteScalar();
                    return result == DBNull.Value ? null : (string)result;
                }
            }
        }

        private void UpdateSyncMetadata(string entityName, string token, string status)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string query = @"
                    MERGE INTO DataverseSyncMetadata AS Target
                    USING (SELECT @TableName AS TableLogicalName) AS Source
                    ON Target.TableLogicalName = Source.TableLogicalName
                    WHEN MATCHED THEN
                        UPDATE SET LastDeltaToken = @Token, LastSyncDateTime = GETUTCDATE(), SyncStatus = @Status
                    WHEN NOT MATCHED THEN
                        INSERT (TableLogicalName, LastDeltaToken, LastSyncDateTime, SyncStatus)
                        VALUES (@TableName, @Token, GETUTCDATE(), @Status);";

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@TableName", entityName);
                    cmd.Parameters.AddWithValue("@Token", (object)token ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private void ResetSyncMetadata(string entityName)
        {
            UpdateSyncMetadata(entityName, null, "FULL_SYNC_REQUIRED");
        }

        private void BulkUpsertToExternalDatabase(List<Entity> entities)
        {
            if (!entities.Any()) return;

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                // Example implementation of a SQL Merge statement for bulk upsert
                foreach (var entity in entities)
                {
                    string query = @"
                        MERGE INTO ExternalAccounts AS Target
                        USING (SELECT @Id AS AccountId) AS Source
                        ON Target.AccountId = Source.AccountId
                        WHEN MATCHED THEN
                            UPDATE SET Name = @Name, ModifiedOn = @ModifiedOn
                        WHEN NOT MATCHED THEN
                            INSERT (AccountId, Name, ModifiedOn)
                            VALUES (@Id, @Name, @ModifiedOn);";

                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@Id", entity.Id);
                        cmd.Parameters.AddWithValue("@Name", entity.Contains("name") ? entity["name"] : DBNull.Value);
                        cmd.Parameters.AddWithValue("@ModifiedOn", entity.Contains("modifiedon") ? entity["modifiedon"] : DateTime.UtcNow);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        private void BulkDeleteFromExternalDatabase(List<Guid> ids)
        {
            if (!ids.Any()) return;

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string query = "DELETE FROM ExternalAccounts WHERE AccountId = @Id";
                foreach (var id in ids)
                {
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@Id", id);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        #endregion
    }
}

Web API Equivalent Requests​

For non-.NET clients, the same operations can be performed using the Dataverse Web API.

Initial Request (Baseline)​

To initiate change tracking, append the Prefer: odata.track-changes header to your query.

GET [Organization URI]/api/data/v9.2/accounts?$select=name,accountnumber,telephone1 HTTP/1.1
Prefer: odata.track-changes
OData-Version: 4.0
Content-Type: application/json

Response: The response contains the records and an @odata.deltaLink at the end of the payload.

{
  "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,accountnumber,telephone1)",
  "value": [
    {
      "@odata.etag": "W/\"5010321\"",
      "name": "Contoso Pharmaceuticals",
      "accountnumber": "CNT-001",
      "telephone1": "555-0199",
      "accountid": "a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6"
    }
  ],
  "@odata.deltaLink": "[Organization URI]/api/data/v9.2/accounts?$select=name,accountnumber,telephone1&$deltatoken=919042%2108%2f22%2f2024%2014%3a22%3a10"
}

Subsequent Request (Delta Sync)​

Extract the $deltatoken value from the @odata.deltaLink and pass it in the next request.

GET [Organization URI]/api/data/v9.2/accounts?$select=name,accountnumber,telephone1&$deltatoken=919042%2108%2f22%2f2024%2014%3a22%3a10 HTTP/1.1
OData-Version: 4.0
Content-Type: application/json

Response (with changes): The response returns new/updated items, deleted items (marked with @odata.removed), and a new delta link.

{
  "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,accountnumber,telephone1)/$delta",
  "value": [
    {
      "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts/$entity",
      "@odata.etag": "W/\"5020444\"",
      "name": "Contoso Pharmaceuticals Ltd",
      "accountnumber": "CNT-001",
      "telephone1": "555-0199",
      "accountid": "a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6"
    },
    {
      "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts/$deletedEntity",
      "id": "f9e8d7c6-b5a4-f3e2-d1c0-b9a8f7e6d5c4",
      "reason": "deleted"
    }
  ],
  "@odata.deltaLink": "[Organization URI]/api/data/v9.2/accounts?$select=name,accountnumber,telephone1&$deltatoken=920115%2108%2f23%2f2024%2009%3a15%3a30"
}

Web API Bypass Custom Business Logic​

When writing back to Dataverse, pass the MSCRM.BypassBusinessLogicExecution and MSCRM.SuppressCallbackRegistrationExpanderJob headers.

PATCH [Organization URI]/api/data/v9.2/accounts(a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6) HTTP/1.1
Content-Type: application/json
MSCRM.BypassBusinessLogicExecution: CustomSync,CustomAsync
MSCRM.SuppressCallbackRegistrationExpanderJob: true

{
  "name": "Updated Name from External System"
}

5. Configuration & Environment Setup​

To deploy the synchronization engine securely, configuration parameters must be externalized using Dataverse Environment Variables and Azure Key Vault.

Environment Variable Schema Definitions​

Create a solution containing the following environment variables to manage the synchronization endpoints and database connection strings.

new_SyncDatabaseConnectionString (Secret Type)​

  • Logical Name: new_SyncDatabaseConnectionString
  • Display Name: Sync Database Connection String
  • Type: Secret
  • Secret Store: Azure Key Vault
  • Value Format: {"keyVaultReference":{"keyVaultName":"kv-prod-sync","secretName":"SqlConnString"}}

new_SyncBatchSize (Decimal/Integer Type)​

  • Logical Name: new_SyncBatchSize
  • Display Name: Sync Batch Size
  • Type: Number (Integer)
  • Default Value: 5000

Azure Resource Configuration (Bicep)​

The following Bicep template deploys an Azure Function App configured with a System-Assigned Managed Identity, an Azure Key Vault, and the necessary App Settings to securely connect to Dataverse and the SQL Database.

param location string = resourceGroup().location
param prefix string = 'sync'
param keyVaultName string = '`${prefix}`-kv-`${uniqueString(resourceGroup().id)}`'
param functionAppName string = '`${prefix}`-func-`${uniqueString(resourceGroup().id)}`'
param storageAccountName string = '`${prefix}`store`${uniqueString(resourceGroup().id)}`'

// Storage Account for Azure Function
resource storageAccount 'Microsoft.Storage/storageAccounts@2022-09-01' = {
  name: storageAccountName
  location: location
  sku: {
    name: 'Standard_LRS'
  }
  kind: 'StorageV2'
}

// Key Vault for Secrets
resource keyVault 'Microsoft.KeyVault/vaults@2022-07-01' = {
  name: keyVaultName
  location: location
  properties: {
    sku: {
      family: 'A'
      name: 'standard'
    }
    tenantId: subscription().tenantId
    enableRbacAuthorization: true
  }
}

// Azure Function App Service Plan
resource hostingPlan 'Microsoft.Web/serverfarms@2022-09-01' = {
  name: '`${prefix}`-plan'
  location: location
  sku: {
    name: 'Y1'
    tier: 'Dynamic'
  }
}

// Azure Function App with Managed Identity
resource functionApp 'Microsoft.Web/sites@2022-09-01' = {
  name: functionAppName
  location: location
  kind: 'functionapp'
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    serverFarmId: hostingPlan.id
    siteConfig: {
      appSettings: [
        {
          name: 'AzureWebJobsStorage'
          value: 'DefaultEndpointsProtocol=https;AccountName=`${storageAccount.name}`;EndpointSuffix=`${environment().suffixes.storage}`;AccountKey=`${storageAccount.listKeys().keys[0].value}`'
        }
        {
          name: 'WEBSITE_CONTENTAZUREFILECONNECTIONSTRING'
          value: 'DefaultEndpointsProtocol=https;AccountName=`${storageAccount.name}`;EndpointSuffix=`${environment().suffixes.storage}`;AccountKey=`${storageAccount.listKeys().keys[0].value}`'
        }
        {
          name: 'FUNCTIONS_EXTENSION_VERSION'
          value: '~4'
        }
        {
          name: 'FUNCTIONS_WORKER_RUNTIME'
          value: 'dotnet'
        }
        {
          name: 'DataverseUrl'
          value: 'https://org-prod.crm.dynamics.com'
        }
        {
          name: 'SqlConnectionString'
          value: '@Microsoft.KeyVault(VaultName=`${keyVaultName}`;SecretName=SqlConnString)'
        }
      ]
    }
  }
}

// Grant Key Vault Secrets User role to Function App Managed Identity
resource kvRoleAssignment 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  name: guid(keyVault.id, functionApp.id, '46330157-13e1-40c8-87d0-b1e39790773d') // Key Vault Secrets User Role ID
  scope: keyVault
  properties: {
    roleDefinitionId: subscriptionResourceId('Microsoft.Authorization/roleDefinitions', '46330157-13e1-40c8-87d0-b1e39790773d')
    principalId: functionApp.identity.principalId
    principalType: 'ServicePrincipal'
  }
}

6. Security & Permission Matrix​

To execute change tracking and bypass business logic, the integration service principal must be granted specific privileges within Dataverse and Azure.

Security Role Configuration​

Create a custom Dataverse Security Role named Integration Sync Engine with the following minimum privileges:

Privilege NameTable / ComponentScopeReason
prvReadAccountAccountOrganizationRequired to execute RetrieveEntityChangesRequest on the Account table.
prvWriteAccountAccountOrganizationRequired to write updates back to Dataverse.
prvBypassCustomBusinessLogicSystemGlobalRequired to use the BypassBusinessLogicExecution parameter.
prvBypassCustomPluginsSystemGlobalRequired to use the legacy BypassCustomPluginExecution parameter.
prvReadOrganizationOrganizationOrganizationRequired to read change tracking expiration settings.

Azure & OAuth Scope Matrix​

PrincipalTarget ResourcePermission / ScopeReason
Function App Managed IdentityDataverse APIuser_impersonationAllows the Function App to call the Dataverse Web API/SDK.
Function App Managed IdentityAzure Key VaultKey Vault Secrets UserAllows retrieval of the SQL connection string.
Function App Managed IdentityAzure SQL Databasedb_datareader, db_datawriterAllows reading/writing sync metadata and business data.

7. Pipeline Execution Internals​

Understanding the internal execution pipeline of Dataverse is critical when designing synchronization logic, especially when bypassing custom logic.

[Client Request]
       │
       ▼
┌────────────────────────────────────────────────────────┐
│ 1. Pre-Validation Stage (Stage 10)                     │
│    - Security checks, basic validation                 │
│    - NOT bypassed by BypassBusinessLogicExecution      │
└──────────────────────┬─────────────────────────────────┘
                       │
                       ▼
┌────────────────────────────────────────────────────────┐
│ 2. Pre-Operation Stage (Stage 20)                      │
│    - Database transaction starts                       │
│    - Custom plug-ins run here                          │
│    - BYPASSED if BypassBusinessLogicExecution is set   │
└──────────────────────┬─────────────────────────────────┘
                       │
                       ▼
┌────────────────────────────────────────────────────────┐
│ 3. Main Operation Stage (Stage 30)                     │
│    - Core SQL operation (Insert/Update/Delete)         │
│    - System plug-ins execute (cannot be bypassed)      │
└──────────────────────┬─────────────────────────────────┘
                       │
                       ▼
┌────────────────────────────────────────────────────────┐
│ 4. Post-Operation Stage (Stage 40)                     │
│    - Custom plug-ins run here                          │
│    - BYPASSED if BypassBusinessLogicExecution is set   │
└──────────────────────┬─────────────────────────────────┘
                       │
                       ▼
┌────────────────────────────────────────────────────────┐
│ 5. Async Event Queue (Stage 50)                        │
│    - Power Automate flows, async plug-ins queued       │
│    - BYPASSED if SuppressCallbackRegistration... is set│
└────────────────────────────────────────────────────────┘

Execution Pipeline Stages and Bypass Behavior​

When a request is sent to Dataverse with BypassBusinessLogicExecution set to CustomSync,CustomAsync, the platform alters the standard execution pipeline:

  1. Pre-Validation (Stage 10): Custom plug-ins registered in this stage are bypassed. System validation plug-ins still execute.
  2. Pre-Operation (Stage 20): All custom plug-ins registered in this stage are bypassed. The database transaction starts.
  3. Main Operation (Stage 30): The core SQL operation executes. System plug-ins (such as those maintaining internal platform integrity) execute and cannot be bypassed.
  4. Post-Operation (Stage 40): All custom plug-ins registered in this stage are bypassed.
  5. Asynchronous Queueing (Stage 50): Custom asynchronous plug-ins and workflows are bypassed. Power Automate flows are not bypassed by this parameter; they must be bypassed using the SuppressCallbackRegistrationExpanderJob parameter.

Transaction Boundaries and Sandbox Restrictions​

  • Sandbox Isolation: All custom plug-ins run within the Dataverse Sandbox isolation mode. Sandbox plug-ins cannot access local system resources and are restricted to outbound network calls via HTTP/HTTPS (ports 80 and 443).
  • Execution Timeout: Plug-ins have a strict 2-minute (120-second) execution limit. If a plug-in exceeds this limit, the platform terminates the thread and rolls back the entire database transaction.
  • Depth/Loop-Guard Limits: Dataverse maintains a depth counter in the execution context. If a chain of plug-ins triggers updates that cause the pipeline to execute recursively, the platform throws an exception when the depth exceeds 16. Using BypassBusinessLogicExecution is the primary mechanism to reset or avoid depth accumulation during synchronization.

8. Error Handling & Retry Patterns​

Robust synchronization engines must handle transient network failures, API throttling, and token expiration gracefully.

Common Failure Modes and Error Codes​

Error Code (Hex)Error Code (Decimal)Error NameRoot CauseRemediation
0x80044352-2147204270ExpiredVersionStampThe delta token is older than the configured retention period (default 7 days).Catch the exception, clear the stored token, and execute a full resynchronization.
0x8004E023-2147164125AggregateQueryRecordLimitExceededThe query attempted to aggregate or retrieve more than 50,000 records without paging.Implement proper paging using PagingCookie and MoreRecords.
0x80040224-2147220956PrivilegeDeniedThe calling user lacks organization-level read access to the table.Assign a security role with global Read privileges for the target table.
0x80044415-2147204075ExportAttributeMapExceptionSchema mismatch between environments during solution import.Ensure target environment schema matches source before running sync.
429429TooManyRequestsService Protection Limits exceeded (concurrent connections, API requests, or execution time).Implement exponential back-off retry logic using the Retry-After header.

C# Exponential Back-off Retry Implementation​

The following code demonstrates how to implement a resilient execution wrapper using exponential back-off, specifically handling Dataverse Service Protection limits.

using System;
using System.ServiceModel;
using System.Threading;
using Microsoft.Xrm.Sdk;

namespace DataverseSyncEngine
{
    public static class DataverseRetryPolicy
    {
        private const int MaxRetries = 5;
        private const int BaseDelaySeconds = 2;

        public static TResult ExecuteWithRetry<TResult>(Func<TResult> operation)
        {
            int retryCount = 0;

            while (true)
            {
                try
                {
                    return operation();
                }
                catch (FaultException<OrganizationServiceFault> ex) when (IsTransientError(ex.Detail.ErrorCode))
                {
                    retryCount++;
                    if (retryCount > MaxRetries)
                    {
                        throw new Exception("Maximum retry attempts exceeded for transient Dataverse error.", ex);
                    }

                    int delay = CalculateDelay(retryCount, ex.Detail);
                    Console.WriteLine($"[RETRY] Transient error detected ({ex.Detail.ErrorCode}). Retrying in {delay} seconds (Attempt {retryCount}/{MaxRetries})...");
                    Thread.Sleep(delay * 1000);
                }
                catch (Exception ex)
                {
                    // Non-transient exception, rethrow immediately
                    throw;
                }
            }
        }

        private static bool IsTransientError(int errorCode)
        {
            // Service Protection Limit Error Codes:
            // -2147015902: Number of requests exceeded limit
            // -2147015903: Execution time exceeded limit
            // -2147015901: Concurrent connections exceeded limit
            return errorCode == -2147015902 || errorCode == -2147015903 || errorCode == -2147015901;
        }

        private static int CalculateDelay(int retryCount, OrganizationServiceFault fault)
        {
            // If the platform provides a specific retry-after duration in the fault details, use it
            if (fault.ErrorDetails.ContainsKey("Retry-After"))
            {
                if (fault.ErrorDetails["Retry-After"] is TimeSpan retryAfter)
                {
                    return (int)Math.Ceiling(retryAfter.TotalSeconds);
                }
            }

            // Otherwise, calculate exponential back-off: BaseDelay * 2^(retryCount - 1)
            return (int)Math.Pow(2, retryCount - 1) * BaseDelaySeconds;
        }
    }
}

9. Performance Optimisation & Limits​

To maintain high throughput and avoid hitting platform boundaries, synchronization architectures must respect Dataverse API limits.

Platform Limits and Throttling Caps​

  • Service Protection Limits: Evaluated in a sliding 5-minute window per user account per web server instance:
    • Requests Count: Max 6,000 requests per user.
    • Execution Time: Max 20 minutes (1,200 seconds) of combined execution time.
    • Concurrent Requests: Max 52 concurrent requests.
  • API Entitlement Limits: Evaluated in a 24-hour window based on user licenses (e.g., 40,000 to 100,000 requests per day).
  • Payload Size Limit: The maximum request size for any single API call is 104,857,600 bytes (100 MB).

Optimization Strategies​

1. Parallel Request Execution​

To maximize throughput, execute requests in parallel. The optimal Degree of Parallelism (DOP) depends on the number of web servers allocated to your environment. A safe starting point is DOP = 16 to 32.

using System.Threading.Tasks;

public void ParallelUpsert(List<Entity> entities, IOrganizationService service)
{
    Parallel.ForEach(entities, new ParallelOptions { MaxDegreeOfParallelism = 16 }, entity =>
    {
        DataverseRetryPolicy.ExecuteWithRetry(() =>
        {
            service.Update(entity);
            return true;
        });
    });
}

2. Batching with ExecuteMultipleRequest​

When parallel execution is not viable (e.g., in low-concurrency environments), batch requests using ExecuteMultipleRequest.

  • Optimal Batch Size: 200 to 500 records per batch.
  • Platform Limit: Dataverse enforces a maximum of 1,000 requests per ExecuteMultipleRequest and a maximum of 2 concurrent ExecuteMultipleRequest executions per organization to prevent database lock contention.

3. Column Filtering (ColumnSet)​

Never retrieve all columns. Only include the specific columns required by the external system in your ColumnSet. This reduces database read times, network payload sizes, and serialization overhead.

10. ALM & Deployment Checklist​

Deploying change-tracking configurations across environments (Development, Test, Production) must be automated using Power Platform Build Tools and Azure DevOps or GitHub Actions.

Solution Packaging Rules​

  1. Table Customizations: Ensure the table metadata (with ChangeTrackingEnabled = true) is included in your managed solution.
  2. Environment Variables: Include the Environment Variable definitions in the solution, but do not include the Environment Variable Values (Current Value) in the solution package. This prevents development values from overwriting production values.
  3. Connection References: Package Connection References within the solution. Map them to the target environment's connections during deployment.

GitHub Actions Deployment Pipeline YAML​

The following YAML snippet demonstrates how to automate the export, unpack, and deployment of a solution containing change-tracking configurations using GitHub Actions.

name: Deploy Sync Solution to Production

on:
  push:
    branches:
      - main

jobs:
  deploy:
    runs-on: ubuntu-latest
    env:
      DATAVERSE_URL: 'https://org-prod.crm.dynamics.com'
      CLIENT_ID: '00000000-0000-0000-0000-000000000000'
      TENANT_ID: '00000000-0000-0000-0000-000000000000'

    steps:
    - name: Checkout Code
      uses: actions/checkout@v3

    - name: Install Power Platform CLI
      uses: microsoft/powerplatform-actions/actions-install@v1

    - name: Authenticate to Production Environment
      uses: microsoft/powerplatform-actions/authenticate@v1
      with:
        environment-url: `${{ env.DATAVERSE_URL }`}
        app-id: `${{ env.CLIENT_ID }`}
        client-secret: `${{ secrets.DATAVERSE_CLIENT_SECRET }`}
        tenant-id: `${{ env.TENANT_ID }`}

    - name: Pack Solution
      uses: microsoft/powerplatform-actions/pack-solution@v1
      with:
        solution-folder: 'solutions/SyncEngineSolution'
        solution-file: 'out/SyncEngineSolution.zip'
        solution-type: 'Managed'

    - name: Import Solution to Production
      uses: microsoft/powerplatform-actions/import-solution@v1
      with:
        environment-url: `${{ env.DATAVERSE_URL }`}
        solution-file: 'out/SyncEngineSolution.zip'
        force-overwrite: true
        publish-changes: true
        run-asynchronously: true
        # Inject environment variable values and connection references
        deployment-settings-file: 'config/prod-deployment-settings.json'

Deployment Settings JSON (prod-deployment-settings.json)​

This file maps the environment variables and connection references to production-specific values during the import process.

{
  "EnvironmentVariables": [
    {
      "SchemaName": "new_SyncDatabaseConnectionString",
      "Value": "{\"keyVaultReference\":{\"keyVaultName\":\"kv-prod-sync\",\"secretName\":\"SqlConnString\"}}"
    },
    {
      "SchemaName": "new_SyncBatchSize",
      "Value": "5000"
    }
  ],
  "ConnectionReferences": [
    {
      "LogicalName": "new_sharedsql_connectionref",
      "ConnectionId": "prod-sql-connection-guid"
    }
  ]
}

11. Common Pitfalls & Troubleshooting Guide​

1. The "Ghost Deleted Items" Phenomenon​

  • Symptom: The synchronization engine receives delete notifications (RemovedOrDeletedItem) for records that the client application has never seen or synchronized.
  • Root Cause: If a record is created and subsequently deleted within the interval between two synchronization runs, Dataverse records both events. When the client queries for changes using the previous delta token, the engine returns the deleted item.
  • Diagnostic Steps: Inspect the Changes collection. Note that the RemovedItem ID does not exist in the external database.
  • Fix: Ensure the external database deletion logic uses a safe DELETE WHERE AccountId = @Id statement that executes successfully regardless of whether the record exists (idempotent deletion).

2. Infinite Loopback in Bidirectional Sync​

  • Symptom: CPU usage spikes on both Dataverse and the external database. The same record is updated repeatedly, incrementing version numbers indefinitely.
  • Root Cause: System A updates System B, which triggers System B to update System A, which triggers System A to update System B.
  • Diagnostic Steps: Check the modifiedby field in Dataverse. If it alternates between the integration user and standard users rapidly, a loopback is active.
  • Fix: Implement the BypassBusinessLogicExecution parameter on all write operations initiated by the integration engine.

3. Missing Deletes for N:N Relationship Tables​

  • Symptom: Changes to standard tables are tracked, but associations/disassociations in N:N relationships are missed.
  • Root Cause: Change tracking cannot be enabled on native N:N intersect tables.
  • Diagnostic Steps: Attempt to enable change tracking on an intersect table via code; the platform will throw an error indicating the table is not eligible.
  • Fix: Convert the native N:N relationship into a custom intersect table (a manual entity with two 1:N relationships). Enable change tracking on this custom table.

4. Expired Token Crash on Day 8​

  • Symptom: The integration runs successfully for a week, then crashes on the eighth day with error code 0x80044352.
  • Root Cause: The integration was offline or failed to run over the weekend, exceeding the default 7-day retention period of the change-tracking engine.
  • Diagnostic Steps: Check the last run timestamp in DataverseSyncMetadata. If it is > 7 days ago, the token is expired.
  • Fix: Implement a try-catch block specifically for ExpiredVersionStamp (error code -2147204270). In the catch block, clear the token and trigger a full resynchronization.

5. Schema Drift Silent Failures​

  • Symptom: The synchronization runs without errors, but new columns added to Dataverse are not appearing in the external database.
  • Root Cause: Change tracking only tracks the fact of a change. If the client's ColumnSet or the external database schema is not updated to include the new columns, the data is ignored.
  • Diagnostic Steps: Compare the ColumnSet defined in the integration code with the active metadata in Dataverse.
  • Fix: Implement a schema validation step in the integration startup that queries the Dataverse metadata and alerts administrators if schema drift is detected.

6. Service Protection Throttling (HTTP 429)​

  • Symptom: The integration fails during high-volume periods with Too Many Requests or OrganizationServiceFault indicating limits exceeded.
  • Root Cause: The integration is executing too many concurrent requests or consuming too much database CPU.
  • Diagnostic Steps: Inspect the exception details for the presence of the Retry-After key.
  • Fix: Implement exponential back-off retry logic that respects the Retry-After header returned by Dataverse.

7. SharedVariables Loss Across Pipeline Stages​

  • Symptom: Loopback prevention using SharedVariables fails when the update moves from a synchronous plug-in to an asynchronous plug-in.
  • Root Cause: SharedVariables are only maintained within the same execution context. Asynchronous steps run in a separate system job context, losing the variables.
  • Diagnostic Steps: Log the contents of context.SharedVariables in both the synchronous and asynchronous plug-ins.
  • Fix: For asynchronous steps, persist the bypass state in a physical column on the record (e.g., new_bypasssync) instead of relying on SharedVariables.
  • Symptom: Paging fails on large datasets with XML parsing errors.
  • Root Cause: The paging cookie returned by Dataverse contains special characters that must be properly encoded before being passed back in subsequent XML/SDK requests.
  • Diagnostic Steps: Inspect the raw string value of the PagingCookie returned in the response.
  • Fix: Ensure the SDK handles the cookie directly without manual string manipulation, or use System.Net.WebUtility.HtmlEncode if constructing raw XML.

9. Privilege Check Failures for Non-Admin Integration Users​

  • Symptom: The integration runs successfully for System Administrators but fails for custom integration service accounts with a privilege check error.
  • Root Cause: The integration user lacks organization-level Read privileges on the target table, which is a strict requirement for executing RetrieveEntityChangesRequest.
  • Diagnostic Steps: Run the integration under the service account context and capture the FaultException. Look for Principal user is missing prvRead[Table] privilege.
  • Fix: Assign a security role to the service account that grants global (Organization) Read access to the table being synchronized.

10. Change Tracking Disabled Automatically After Solution Import​

  • Symptom: Change tracking was enabled in Development and Production, but after importing an updated solution, change tracking is disabled in Production.
  • Root Cause: The solution imported was an unmanaged solution that did not have the ChangeTrackingEnabled property set to true in the table metadata, overwriting the target environment's settings.
  • Diagnostic Steps: Query the EntityDefinitions endpoint in Production and check if ChangeTrackingEnabled is false.
  • Fix: Always use Managed Solutions for downstream environments. Ensure that the source solution explicitly has change tracking enabled before export.

12. Exam Focus: Key Facts & Edge Cases​

For candidates preparing for the PL-400: Microsoft Power Platform Developer exam, the following technical details and edge cases are highly testable:

  • Underlying Technology: Dataverse Change Tracking is built on SQL Server Change Tracking, not Change Data Capture (CDC). It is synchronous and lightweight.
  • Retention Period: The default retention period for data change tracking is 7 days. This is controlled by the expirechangetrackingindays column in the Organization table. The maximum value is 365 days.
  • Metadata Expiration: Metadata change tracking expiration is controlled by expiresubscriptionsindays and defaults to 90 days.
  • Error Code for Expired Token: The exact error code for an expired delta token is 0x80044352 (ExpiredVersionStamp). The remediation is always a full resynchronization.
  • Initial Sync Trigger: To trigger a full sync (baseline), pass null or an empty string to the DataVersion property of RetrieveEntityChangesRequest.
  • Paging Limits: The maximum page size for retrieving changes is 5,000 records for standard tables (500 for elastic tables). If more records exist, MoreRecords is true, and you must use the PagingCookie.
  • Bypass Privilege: To bypass custom business logic using BypassBusinessLogicExecution, the calling user must possess the prvBypassCustomBusinessLogic privilege (ID: 0ea552b0-a491-4470-9a1b-82068deccf66).
  • Bypass Parameter Values: The valid values for the BypassBusinessLogicExecution parameter are CustomSync, CustomAsync, or CustomSync,CustomAsync.
  • Flow Suppression: To prevent Power Automate flows from triggering during an integration write, you must pass the SuppressCallbackRegistrationExpanderJob parameter set to true. This does not require a special privilege.
  • Unsupported Query Options: When using the Prefer: odata.track-changes header in Web API requests, the system query options $filter, $orderby, $expand, and $top are not supported and will cause the request to fail.