Skip to main content

Alternate Keys and Upsert Operations in Dataverse

1. Conceptual Foundation​

In enterprise data integration architectures, synchronizing data between Microsoft Dataverse and external systems (such as ERPs, legacy databases, or third-party SaaS platforms) is a common requirement. A fundamental challenge in these scenarios is record identification. External systems rarely store the Dataverse globally unique identifier (GUID) primary key. Historically, developers resolved this by performing a "lookup query" (e.g., retrieving a record by an external account number), checking if it existed, and then executing either a Create or Update operation. This "query-then-write" pattern introduces significant network latency, increases API consumption, and creates race conditions in multi-threaded environments.

Alternate keys and upsert operations in Dataverse solve these challenges by allowing developers to define business-centric unique identifiers and perform atomic "create or update" operations in a single database round-trip.

Alternate Keys: Under the Hood​

An Alternate Key (represented in metadata as EntityKeyMetadata) allows you to uniquely identify a row in a Dataverse table using one or more business columns instead of the GUID primary key.

When you define an alternate key on a Dataverse table, the platform automatically triggers an asynchronous process to create a unique index in the underlying SQL Server database. This index enforces uniqueness at the database level and optimizes query performance.

The Index Creation Lifecycle​

Because creating database indexes on tables with millions of rows can be resource-intensive, Dataverse handles index creation asynchronously using system jobs. The status of an alternate key is tracked via the EntityKeyMetadata.EntityKeyIndexStatus property, which transitions through the following states:

  1. Pending (0): The key definition has been saved to metadata, and the index creation job is queued.
  2. InProgress (1): The asynchronous system job is currently building the SQL Server index.
  3. Active (2): The index has been successfully created. The alternate key is now fully functional and can be used for data operations.
  4. Failed (3): The index creation failed. This typically occurs if the table already contains duplicate data for the columns defined in the key, or if the key size violates SQL Server constraints.

Developers must monitor this status programmatically or via the Maker Portal before attempting to use a newly created key in integration pipelines.

Supported Data Types and Constraints​

Not all Dataverse column types can participate in an alternate key. The platform restricts key columns to types that can be reliably indexed and compared. The supported column types and their corresponding metadata classes are:

Column TypeMetadata ClassDescription
Decimal NumberDecimalAttributeMetadataHigh-precision numeric values.
Whole NumberIntegerAttributeMetadataStandard 32-bit integers.
Single Line of TextStringAttributeMetadataText columns. Subject to length and byte constraints.
Date and TimeDateTimeAttributeMetadataDate/time values. Note that timezone conversions can affect uniqueness.
LookupLookupAttributeMetadataReferences to parent records, enabling composite keys across relationships.
Choice (Option Set)PicklistAttributeMetadataInteger-based option set values.

Physical and Platform Constraints​

When designing alternate keys, you must adhere to strict platform limits:

  • Maximum Keys per Table: A single Dataverse table can have a maximum of 10 alternate keys defined.
  • Maximum Columns per Key: A composite alternate key can include up to 16 columns.
  • SQL Index Size Limit: The total size of the columns participating in a single alternate key cannot exceed 900 bytes (the standard SQL Server index key size limit). For string columns, this limit is calculated based on the maximum character length. Since Dataverse uses UTF-8/Unicode encoding, a string column with a maximum length of 450 characters can potentially consume the entire 900-byte limit.
  • Field-Level Security (FLS): Columns with Field-Level Security enabled cannot be used in alternate keys. Uniqueness cannot be enforced if the executing security principal does not have read access to the data.
  • Virtual Tables: Alternate keys are not supported on virtual tables, as Dataverse does not control the underlying data store or index creation.
  • Null Values: If a row contains a NULL value in an alternate key column, uniqueness is not enforced for that row. To maintain strict data integrity, ensure that all columns participating in an alternate key are configured as Business Required.
  • Unicode Character Restrictions: If the data within an alternate key column contains any of the following characters: /, <, >, *, %, &, :, \\, ?, +, #, standard Web API operations (GET, PATCH) using the key reference will fail due to URL encoding and routing limitations. If you must use these characters for uniqueness, you can only perform operations using the SDK for .NET, or you must ensure the integration data avoids them.

The Mechanics of UpsertRequest​

The UpsertRequest message (and its Web API equivalent, PATCH with conditional headers) is an atomic operation that combines a read and a write. When Dataverse receives an UpsertRequest, it executes the following internal pipeline:

[UpsertRequest Arrives]
         │
         ▼
[Check Target Entity] ──► Has Primary Key GUID?
         │                      │
         │ No                   ├─► Yes ─► [Retrieve by GUID]
         ▼                      │
[Check KeyAttributes]           │
         │                      │
         ▼                      ▼
[Query Database using Key] ──► [Does Record Exist?]
                                │         │
                      No        │         │ Yes
             ┌──────────────────┘         └──────────────────┐
             ▼                                               ▼
     [Create Pipeline]                               [Update Pipeline]
  - Triggers Pre-Validation                       - Triggers Pre-Validation
  - Triggers Pre-Operation                        - Triggers Pre-Operation
  - Inserts Record                                - Updates Record
  - Triggers Post-Operation                       - Triggers Post-Operation
             │                                               │
             └──────────────────┬────────────────────────────┘
                                │
                                ▼
                        [UpsertResponse]
                 - RecordCreated (True/False)
                 - Target (EntityReference with GUID)
  1. Identification: The platform checks the Target entity. If the Target contains a primary key GUID in the Id property, Dataverse attempts to locate the record using that GUID. If the Id is empty, it inspects the KeyAttributes collection for alternate key values.
  2. Evaluation: Dataverse queries the database using the provided alternate key.
  3. Branching:
    • If the record exists: The platform extracts the primary key GUID of the existing record, populates the Target.Id property with this GUID, and routes the request to the standard Update pipeline.
    • If the record does not exist: The platform routes the request to the standard Create pipeline.
  4. Response: The platform returns an UpsertResponse containing:
    • RecordCreated: A boolean indicating whether a new record was created (true) or an existing record was updated (false).
    • Target: An EntityReference containing the logical name and the resolved primary key GUID of the record.

Composing Requests: The "Attributes" vs. "KeyAttributes" Rule​

A common architectural mistake is including the alternate key columns in both the KeyAttributes collection (used to identify the record) and the Attributes collection (used to write data).

  • If the operation results in an Update: Dataverse blocks updates to the alternate key columns that are being used to identify the record. If those columns are in the Attributes collection, they are ignored or can throw an exception.
  • If the operation results in a Create: If the alternate key columns are missing from the Attributes collection, Dataverse automatically copies the values from the KeyAttributes collection into the new record's attributes.

Best Practice: Keep alternate key identification values strictly within the KeyAttributes collection (or use the appropriate entity constructor) and exclude them from the Attributes collection unless you are explicitly changing the key value of an existing record using its primary GUID.

Bulk Upsert: ExecuteMultipleRequest vs. UpsertMultipleRequest​

When performing bulk data synchronization, executing individual UpsertRequest calls sequentially introduces massive network overhead. Dataverse provides two primary mechanisms for batching these operations:

1. ExecuteMultipleRequest (Heterogeneous Batching)​

ExecuteMultipleRequest is a generic container that accepts a collection of independent OrganizationRequest objects (such as CreateRequest, UpdateRequest, DeleteRequest, and UpsertRequest).

  • Transaction Boundary: Each request within the Requests collection executes in its own independent database transaction. If request #5 fails, requests #1 through #4 remain committed.
  • Concurrency: Requests are processed sequentially on the server unless parallel execution is leveraged at the client level.
  • Flexibility: It can mix different operations and target different tables in a single payload.

2. UpsertMultipleRequest (Homogeneous Bulk Upsert)​

Introduced to optimize high-throughput scenarios, UpsertMultipleRequest is a specialized message designed specifically for bulk upserting records of the same table type.

  • Transaction Boundary: Like ExecuteMultipleRequest, it processes records individually, but the execution pipeline is highly optimized.
  • Performance: It bypasses much of the overhead associated with parsing multiple distinct request objects. The platform processes the entire collection of target entities through a single, unified execution path, significantly reducing CPU and database lock contention.
  • Limitation: All records in the payload must belong to the same table.

2. Architecture & Decision Matrix​

When designing an integration architecture that utilizes alternate keys and upsert operations, you must select the appropriate execution context. The table below compares the four primary implementation patterns.

Implementation Comparison​

Feature / DimensionPower Automate (Dataverse Connector)Azure Functions / External .NET AppDataverse Plug-ins (C#)Client-Side Web API (JS/TS / PCF)
ComplexityLow (No-code/Low-code drag-and-drop)Medium (Standard .NET development)High (Requires deep knowledge of pipeline)Medium (JavaScript/TypeScript)
ScalabilityModerate (Subject to API limits/throttling)High (Can scale horizontally, handle threading)Low for bulk (Subject to 2-minute timeout)Low (Bound by browser thread and network)
Execution ContextAsynchronous, out-of-processAsynchronous, externalSynchronous or Asynchronous, in-processAsynchronous, client-side
Offline SupportNoneNoneSupported (via Mobile Offline sync engine)Supported (via client-side offline store)
LicensingPower Automate per-user/per-flow or Pay-as-you-goAzure consumption + Dataverse API limitsIncluded in Dataverse licenseIncluded in Dataverse license
PL-400 Exam RelevanceLow (Focus is on developer extensibility)High (Integration scenarios, external API calls)Critical (Pipeline stages, transaction boundaries)High (Web API syntax, @odata.bind lookups)

Architectural Decision Flowchart​

To determine the optimal approach for your specific scenario, follow this decision path:

                  [Start: Bulk Data Integration Required?]
                                     │
                                     ▼
                        Is the data source external 
                        and volume > 10,000 rows?
                                    / \
                            Yes    /   \   No
                            ┌─────┘     └─────┐
                            ▼                 ▼
                 Use Azure Functions /   Is logic triggered by 
                 External .NET App with  Dataverse events?
                 UpsertMultipleRequest       / \
                                     Yes    /   \   No
                                     ┌─────┘     └─────┐
                                     ▼                 ▼
                               Use Dataverse      Use Power Automate
                               Sync Plug-in       with Upsert Action

3. Step-by-Step Implementation Guide​

Step 1: Defining Alternate Keys in the Power Apps Maker Portal​

To define an alternate key using the modern maker portal:

  1. Navigate to the Power Apps Maker Portal and select your environment.
  2. In the left navigation pane, select Solutions and open your unmanaged development solution.
  3. Under Objects, expand Tables and select the table where you want to define the key (e.g., Account).
  4. In the Schema section, select Keys.
  5. Click New key from the command bar.
  6. In the panel that appears:
    • Enter a Display name (e.g., External Account Key).
    • The Name will auto-populate with your publisher prefix (e.g., crabc_ExternalAccountKey).
    • Under Select key columns, check the boxes next to the columns that define uniqueness (e.g., crabc_externalid).
  7. Click Save.

Step 2: Defining Alternate Keys Programmatically​

For automated deployments or dynamic schema configurations, you can define alternate keys programmatically using the SDK for .NET.

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

public static void CreateAlternateKey(IOrganizationService service, string tableLogicalName, string keySchemaName, string keyDisplayName, string[] attributeLogicalNames)
{
    // 1. Instantiate the EntityKeyMetadata object
    EntityKeyMetadata keyMetadata = new EntityKeyMetadata
    {
        SchemaName = keySchemaName,
        DisplayName = new Label(keyDisplayName, 1033),
        KeyAttributes = attributeLogicalNames
    };

    // 2. Create the request
    CreateEntityKeyRequest request = new CreateEntityKeyRequest
    {
        EntityLogicalName = tableLogicalName,
        EntityKey = keyMetadata
    };

    // 3. Execute the request
    try
    {
        service.Execute(request);
        Console.WriteLine($"Alternate key '{keySchemaName}' creation request submitted successfully.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Failed to submit key creation request: {ex.Message}");
        throw;
    }
}

Step 3: Monitoring Index Creation​

After submitting a key creation request, you must verify that the index has been successfully built before executing upsert operations.

Programmatic Verification​

You can query the metadata of the table to check the EntityKeyIndexStatus.

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

public static EntityKeyIndexStatus GetAlternateKeyStatus(IOrganizationService service, string tableLogicalName, string keySchemaName)
{
    RetrieveEntityRequest retrieveEntityRequest = new RetrieveEntityRequest
    {
        EntityFilters = EntityFilters.Keys,
        LogicalName = tableLogicalName
    };

    RetrieveEntityResponse response = (RetrieveEntityResponse)service.Execute(retrieveEntityRequest);
    
    EntityKeyMetadata keyMetadata = response.EntityMetadata.Keys
        .FirstOrDefault(k => k.SchemaName.Equals(keySchemaName, StringComparison.OrdinalIgnoreCase));

    if (keyMetadata == null)
    {
        throw new Exception($"Alternate key '{keySchemaName}' not found on table '{tableLogicalName}'.");
    }

    return keyMetadata.EntityKeyIndexStatus;
}

Step 4: Reactivating a Failed Key​

If index creation fails (status is Failed), you must resolve the underlying data issue (e.g., delete duplicate records) and then reactivate the key.

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

public static void ReactivateKey(IOrganizationService service, string tableLogicalName, string keySchemaName)
{
    ReactivateEntityKeyRequest request = new ReactivateEntityKeyRequest
    {
        EntityLogicalName = tableLogicalName,
        LogicalName = keySchemaName.ToLower() // Logical name is typically lowercase
    };

    service.Execute(request);
}

Step 5: Composing and Executing an UpsertRequest​

To execute a single upsert operation using an alternate key, instantiate an Entity object using the constructor that accepts a key name and value.

using System;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;

public static void ExecuteSingleUpsert(IOrganizationService service)
{
    // Define the target entity using the alternate key 'crabc_externalid'
    Entity account = new Entity("account", "crabc_externalid", "ACC-9999");

    // Set the attributes to update or create
    account["name"] = "Contoso Enterprise Solutions";
    account["telephone1"] = "555-0199";

    UpsertRequest request = new UpsertRequest
    {
        Target = account
    };

    UpsertResponse response = (UpsertResponse)service.Execute(request);

    Console.WriteLine($"Record Created: {response.RecordCreated}");
    Console.WriteLine($"Resolved GUID: {response.Target.Id}");
}

Step 6: Setting Lookups using Alternate Keys​

One of the most powerful features of alternate keys is the ability to set lookup columns on child records without querying Dataverse to find the parent record's GUID.

using Microsoft.Xrm.Sdk;

public static void CreateContactWithParentAlternateKey(IOrganizationService service)
{
    Entity contact = new Entity("contact");
    contact["firstname"] = "Jane";
    contact["lastname"] = "Doe";

    // Reference the parent account using its alternate key
    EntityReference parentAccountRef = new EntityReference("account", "crabc_externalid", "ACC-9999");
    
    // Set the lookup attribute
    contact["parentcustomerid"] = parentAccountRef;

    service.Create(contact);
}

4. Complete Code Reference​

The following production-grade C# class provides a comprehensive implementation of alternate key management, single upserts, lookup resolution, and high-throughput bulk operations using both ExecuteMultipleRequest and UpsertMultipleRequest.

//-----------------------------------------------------------------------
// <copyright file="DataverseUpsertManager.cs" company="Enterprise Solutions">
//     Copyright (c) Enterprise Solutions. All rights reserved.
// </copyright>
// <summary>
// Production-grade utility class for managing Dataverse alternate keys,
// executing atomic upserts, and optimizing bulk throughput.
// </summary>
//-----------------------------------------------------------------------

namespace DataverseIntegration.Core
{
    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Linq;
    using System.ServiceModel;
    using System.Threading.Tasks;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Messages;
    using Microsoft.Xrm.Sdk.Metadata;
    using Microsoft.Xrm.Sdk.Query;

    public class DataverseUpsertManager
    {
        private readonly IOrganizationService _service;

        /// <summary>
        /// Initializes a new instance of the <see cref="DataverseUpsertManager"/> class.
        /// </summary>
        /// <param name="service">An authenticated Dataverse organization service instance.</param>
        public DataverseUpsertManager(IOrganizationService service)
        {
            _service = service ?? throw new ArgumentNullException(nameof(service));
        }

        /// <summary>
        /// Creates an alternate key and blocks execution until the index is active or fails.
        /// </summary>
        public bool CreateAndValidateAlternateKey(string entityLogicalName, string keySchemaName, string[] fields, int timeoutSeconds = 300)
        {
            EntityKeyMetadata keyMetadata = new EntityKeyMetadata
            {
                SchemaName = keySchemaName,
                DisplayName = new Label($"Key for {keySchemaName}", 1033),
                KeyAttributes = fields
            };

            CreateEntityKeyRequest createRequest = new CreateEntityKeyRequest
            {
                EntityLogicalName = entityLogicalName,
                EntityKey = keyMetadata
            };

            try
            {
                _service.Execute(createRequest);
            }
            catch (FaultException<OrganizationServiceFault> ex)
            {
                // Error code 0x80040237 indicates the key already exists
                if (ex.Detail.ErrorCode == -2147220937)
                {
                    return true;
                }
                throw;
            }

            // Poll for index activation
            Stopwatch stopwatch = Stopwatch.StartNew();
            while (stopwatch.Elapsed.TotalSeconds < timeoutSeconds)
            {
                RetrieveEntityRequest retrieveRequest = new RetrieveEntityRequest
                {
                    EntityFilters = EntityFilters.Keys,
                    LogicalName = entityLogicalName
                };

                RetrieveEntityResponse response = (RetrieveEntityResponse)_service.Execute(retrieveRequest);
                EntityKeyMetadata createdKey = response.EntityMetadata.Keys
                    .FirstOrDefault(k => k.SchemaName.Equals(keySchemaName, StringComparison.OrdinalIgnoreCase));

                if (createdKey != null)
                {
                    switch (createdKey.EntityKeyIndexStatus)
                    {
                        case EntityKeyIndexStatus.Active:
                            return true;
                        case EntityKeyIndexStatus.Failed:
                            throw new InvalidOperationException($"Index creation failed for key '{keySchemaName}' on table '{entityLogicalName}'. Check system jobs.");
                        case EntityKeyIndexStatus.Pending:
case EntityKeyIndexStatus.InProgress:
                            break;
                    }
                }

                Task.Delay(5000).Wait(); // Wait 5 seconds before polling again
            }

            throw new TimeoutException($"Timed out waiting for alternate key '{keySchemaName}' index to activate.");
        }

        /// <summary>
        /// Executes a single Upsert operation using a composite alternate key.
        /// </summary>
        public UpsertResponse UpsertWithCompositeKey(string entityLogicalName, Dictionary<string, object> keyAttributes, Dictionary<string, object> dataAttributes)
        {
            KeyAttributeCollection keys = new KeyAttributeCollection();
            foreach (var key in keyAttributes)
            {
                keys.Add(key.Key, key.Value);
            }

            Entity targetEntity = new Entity(entityLogicalName, keys);
            foreach (var attr in dataAttributes)
            {
                // Ensure we do not include key attributes in the write payload
                if (!keyAttributes.ContainsKey(attr.Key))
                {
                    targetEntity[attr.Key] = attr.Value;
                }
            }

            UpsertRequest request = new UpsertRequest
            {
                Target = targetEntity
            };

            return (UpsertResponse)_service.Execute(request);
        }

        /// <summary>
        /// Optimizes bulk upserts using ExecuteMultipleRequest with concurrency and custom logic bypass.
        /// </summary>
        public void BulkUpsertWithExecuteMultiple(List<Entity> entities, int batchSize = 200, bool bypassCustomLogic = true)
        {
            if (entities == null || !entities.Any()) return;

            OrganizationRequestCollection requestCollection = new OrganizationRequestCollection();
            foreach (Entity entity in entities)
            {
                UpsertRequest upsertRequest = new UpsertRequest { Target = entity };
                
                if (bypassCustomLogic)
                {
                    // Modern approach to bypass synchronous custom plug-ins and workflows
                    upsertRequest.Parameters.Add("BypassBusinessLogicExecution", "CustomSync");
                }

                requestCollection.Add(upsertRequest);
            }

            int totalCount = requestCollection.Count;
            for (int i = 0; i < totalCount; i += batchSize)
            {
                var batch = requestCollection.Skip(i).Take(batchSize).ToList();
                OrganizationRequestCollection batchRequests = new OrganizationRequestCollection();
                batchRequests.AddRange(batch);

                ExecuteMultipleRequest executeMultipleRequest = new ExecuteMultipleRequest
                {
                    Settings = new ExecuteMultipleSettings
                    {
                        ContinueOnError = true,
                        ReturnResponses = true
                    },
                    Requests = batchRequests
                };

                try
                {
                    ExecuteMultipleResponse response = (ExecuteMultipleResponse)_service.Execute(executeMultipleRequest);
                    ProcessExecuteMultipleResponses(response);
                }
                catch (FaultException<OrganizationServiceFault> ex)
                {
                    // Handle batch-level faults (e.g., batch size limit exceeded)
                    throw new InvalidOperationException($"Batch execution failed: {ex.Message}", ex);
                }
            }
        }

        /// <summary>
        /// High-performance homogeneous bulk upsert using the modern UpsertMultipleRequest.
        /// </summary>
        public void BulkUpsertWithUpsertMultiple(string entityLogicalName, List<Entity> entities, bool bypassCustomLogic = true)
        {
            if (entities == null || !entities.Any()) return;

            EntityCollection targetCollection = new EntityCollection
            {
                EntityName = entityLogicalName
            };
            targetCollection.Entities.AddRange(entities);

            UpsertMultipleRequest request = new UpsertMultipleRequest
            {
                Targets = targetCollection
            };

            if (bypassCustomLogic)
            {
                request.Parameters.Add("BypassBusinessLogicExecution", "CustomSync");
            }

            try
            {
                UpsertMultipleResponse response = (UpsertMultipleResponse)_service.Execute(request);
                // UpsertMultiple does not return individual success payloads by default to optimize speed,
                // but it will throw an exception if the entire operation fails.
            }
            catch (FaultException<OrganizationServiceFault> ex)
            {
                throw new InvalidOperationException($"UpsertMultiple failed: {ex.Message}", ex);
            }
        }

        private void ProcessExecuteMultipleResponses(ExecuteMultipleResponse response)
        {
            foreach (var responseItem in response.Responses)
            {
                if (responseItem.Fault != null)
                {
                    // Log or handle individual record failures
                    Console.WriteLine($"Request index {responseItem.RequestIndex} failed. Error: {responseItem.Fault.Message} (Code: {responseItem.Fault.ErrorCode})");
                }
                else
                {
                    UpsertResponse upsertResponse = (UpsertResponse)responseItem.Response;
                    Console.WriteLine($"Request index {responseItem.RequestIndex} succeeded. Created? {upsertResponse.RecordCreated}. ID: {upsertResponse.Target.Id}");
                }
            }
        }
    }
}

Solution Customization XML Reference​

When alternate keys are packaged into a Dataverse solution, they are defined within the customizations.xml file. Below is a complete, schema-compliant XML snippet showing how an alternate key is defined on the Account table.

<?xml version="1.0" encoding="utf-8"?>
<ImportExportXml xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Entities>
    <Entity>
      <Name LocalizedName="Account" OriginalName="Account">Account</Name>
      <ObjectTypeCode>1</ObjectTypeCode>
      <Keys>
        <Key Name="crabc_ExternalAccountKey">
          <LogicalName>crabc_externalaccountkey</LogicalName>
          <DisplayName>
            <descriptions>
              <description description="External Account Key" languagecode="1033" />
            </descriptions>
          </DisplayName>
          <Attributes>
            <Attribute>crabc_externalid</Attribute>
          </Attributes>
        </Key>
      </Keys>
    </Entity>
  </Entities>
</ImportExportXml>

5. Configuration & Environment Setup​

To execute the C# SDK code, you must configure your project dependencies and environment settings correctly.

NuGet Dependencies​

Ensure your .csproj file references the latest official Dataverse Client SDK package:

<ItemGroup>
  <PackageReference Include="Microsoft.PowerPlatform.Dataverse.Client" Version="1.2.10" />
</ItemGroup>

Environment Variables & Connection Strings​

Store your connection details securely. Do not hardcode credentials. Use the standard Dataverse connection string format:

DATAVERSE_CONNECTIONSTRING="AuthType=ClientSecret;Url=https://orgabc123.crm.dynamics.com/;ClientId=00000000-0000-0000-0000-000000000000;ClientSecret=MySecretValue"

Azure Key Vault Integration (Bicep Snippet)​

For enterprise deployments, store the connection string in Azure Key Vault and retrieve it using a Managed Identity. Below is the Bicep template to provision the Key Vault and grant secrets access to your integration service (e.g., Azure Function).

param location string = resourceGroup().location
param keyVaultName string = 'kv-dataverse-prod'
param principalId string // Object ID of the Azure Function's Managed Identity

resource keyVault 'Microsoft.KeyVault/vaults@2023-07-01' = {
  name: keyVaultName
  location: location
  properties: {
    sku: {
      family: 'A'
      name: 'standard'
    }
    tenantId: subscription().tenantId
    accessPolicies: [
      {
        tenantId: subscription().tenantId
        objectId: principalId
        permissions: {
          secrets: [
            'get'
            'list'
          ]
        }
      }
    ]
    enabledForTemplateDeployment: true
  }
}

resource dataverseSecret 'Microsoft.KeyVault/vaults/secrets@2023-07-01' = {
  parent: keyVault
  name: 'DataverseConnectionString'
  properties: {
    value: 'AuthType=ClientSecret;Url=https://orgabc123.crm.dynamics.com/;ClientId=00000000-0000-0000-0000-000000000000;ClientSecret=@Microsoft.KeyVault(SecretUri=...)'
  }
}

6. Security & Permission Matrix​

To manage alternate keys and execute upsert operations, security principals must be granted specific privileges. The table below outlines the required permissions.

PrincipalPermission / PrivilegeScopeReason
Integration Service PrincipalRead (prvReadAccount)GlobalRequired to query the table to check if the record exists during upsert.
Integration Service PrincipalWrite (prvWriteAccount)GlobalRequired to update the record if it exists.
Integration Service PrincipalCreate (prvCreateAccount)GlobalRequired to create the record if it does not exist.
Integration Service PrincipalprvBypassCustomBusinessLogicGlobalRequired to use the BypassBusinessLogicExecution parameter to optimize throughput.
Deployment Service PrincipalprvAmendMetadataGlobalRequired to programmatically create or delete alternate keys.
Deployment Service PrincipalprvWriteEntityGlobalRequired to modify table definitions to add keys.

Field-Level Security (FLS) Restriction​

If any column participating in an alternate key has Field-Level Security enabled, the index creation will fail, or runtime operations will throw an access denied exception. Ensure that FLS is disabled for all key columns:

[Maker Portal] ──► [Table] ──► [Column] ──► [Advanced Options] ──► [Enable Column Security = Unchecked]

7. Pipeline Execution Internals​

Understanding how Dataverse processes an upsert operation within its event execution pipeline is critical for writing plug-ins and avoiding infinite loops.

Pipeline Stages and Message Translation​

An Upsert message does not have its own dedicated execution pipeline stages for data modification. Instead, Dataverse acts as a router:

[Client Sends UpsertRequest]
             │
             ▼
┌────────────────────────────────────────┐
│         Pre-Validation Stage           │  <-- Triggers on "Upsert" message
└────────────────────────────────────────┘
             │
             ▼
[Dataverse checks database for key match]
             │
             ├──► Record Found?
             │         │
             │ Yes     └─► Route to [Update Pipeline]
             │             - Stage 20: Pre-Operation (Update)
             │             - Stage 30: Main Operation (SQL Update)
             │             - Stage 40: Post-Operation (Update)
             │
             └─► No ─────► Route to [Create Pipeline]
                           - Stage 20: Pre-Operation (Create)
                           - Stage 30: Main Operation (SQL Insert)
                           - Stage 40: Post-Operation (Create)
  1. Pre-Validation (Stage 10): Triggers on the Upsert message. This is the only stage where you can intercept the raw UpsertRequest before the platform determines whether to create or update.
  2. Main Operation (Stage 30): Dataverse queries the database.
    • If a match is found, the platform cancels the Upsert pipeline and initiates a new Update pipeline.
    • If no match is found, the platform cancels the Upsert pipeline and initiates a new Create pipeline.
  3. Subsequent Stages: Plug-ins registered on Create or Update will execute normally. A plug-in registered on Post-Operation (Update) will trigger if the record existed, even though the client sent an UpsertRequest.

Transaction Boundaries​

The entire upsert operation (the internal read and the subsequent write) executes within a single SQL Server transaction. This ensures atomicity. However, if you execute an ExecuteMultipleRequest containing 100 upserts, each of those 100 upserts runs in its own separate transaction. If request #50 fails, transactions #1 through #49 are already committed to the database.

Depth and Loop-Guard Limits​

Dataverse has a built-in execution depth limit of 10 to prevent infinite loops. If a plug-in registered on Post-Operation (Update) of the Account table executes an UpsertRequest on the same Account record, it will increment the execution depth. If this occurs recursively, the platform will terminate the transaction with a MaxDepthExceeded exception.

8. Error Handling & Retry Patterns​

When executing upserts and managing alternate keys, your code must handle specific platform exceptions.

Common Failure Modes and Error Codes​

Error NameHex CodeDecimal CodeRoot CauseRemediation
DuplicateRecordEntityKey0x80060892-2147088238A record with the same alternate key value already exists, but a SQL constraint or race condition prevented the update.Catch the exception, retrieve the existing record's GUID, and retry as an explicit update.
DuplicateRecord0x80040237-2147220937General SQL integrity violation. Often occurs when creating a key on a table that already contains duplicate data.Clean up duplicate data in the table before activating the key.
ConcurrencyVersionMismatch0x80048402-2147088254Optimistic concurrency check failed. The record was modified by another process since it was retrieved.Retrieve the latest RowVersion, merge changes, and retry.
ServerBusy0x80044401-2147204095Service protection limits exceeded (too many concurrent requests or high CPU usage).Implement exponential back-off using the Retry-After header value.

Robust Retry Pattern with Polly​

The following C# sample demonstrates how to implement a resilient upsert pipeline using the Polly library to handle transient faults and service protection limits.

using System;
using System.ServiceModel;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Polly;
using Polly.Retry;

public class ResilientUpsertClient
{
    private readonly IOrganizationService _service;
    private readonly AsyncRetryPolicy _retryPolicy;

    public ResilientUpsertClient(IOrganizationService service)
    {
        _service = service;

        // Define a retry policy for transient Dataverse faults
        _retryPolicy = Policy
            .Handle<FaultException<OrganizationServiceFault>>(ex => IsTransientFault(ex))
            .WaitAndRetryAsync(
                retryCount: 3,
                sleepDurationProvider: (retryCount, exception, context) =>
                {
                    // Check if Dataverse provided a specific Retry-After duration
                    if (exception is FaultException<OrganizationServiceFault> xrmFault &&
                        xrmFault.Detail.ErrorDetails.ContainsKey("Retry-After"))
                    {
                        return (TimeSpan)xrmFault.Detail.ErrorDetails["Retry-After"];
                    }
                    
                    // Fallback to exponential back-off
                    return TimeSpan.FromSeconds(Math.Pow(2, retryCount));
                },
                onRetryAsync: async (exception, timespan, retryCount, context) =>
                {
                    await Task.CompletedTask; // Log retry attempt
                    Console.WriteLine($"Transient error encountered. Retrying attempt {retryCount} after {timespan.TotalSeconds} seconds...");
                });
    }

    public async Task<UpsertResponse> ResilientUpsertAsync(Entity targetEntity)
    {
        return await _retryPolicy.ExecuteAsync(async () =>
        {
            UpsertRequest request = new UpsertRequest { Target = targetEntity };
            return (UpsertResponse)await Task.Run(() => _service.Execute(request));
        });
    }

    private static bool IsTransientFault(FaultException<OrganizationServiceFault> ex)
    {
        int errorCode = ex.Detail.ErrorCode;
        
        // -2147204095 = ServerBusy (Service Protection Limit)
        // -2147088254 = ConcurrencyVersionMismatch
        return errorCode == -2147204095 || errorCode == -2147088254;
    }
}

9. Performance Optimisation & Limits​

To achieve maximum throughput when synchronizing millions of rows, you must optimize your integration architecture to work within Dataverse platform limits.

Service Protection API Limits​

Dataverse enforces service protection limits per user account, per web server node, evaluated over a sliding 5-minute window:

  1. Request Count Limit: 6,000 requests per 5 minutes.
  2. Execution Time Limit: 20 minutes (1,200 seconds) of combined CPU execution time per 5 minutes.
  3. Concurrent Request Limit: 52 concurrent requests.

Optimization Strategies​

1. Bypass Custom Business Logic​

If the data being imported has already been validated by the source system, you can bypass custom synchronous plug-ins and workflows to reduce CPU execution time on the Dataverse servers.

  • Modern Parameter: Add BypassBusinessLogicExecution with the value CustomSync to the request parameters.
  • Required Privilege: The executing user must have the prvBypassCustomBusinessLogic privilege.
UpsertRequest request = new UpsertRequest { Target = account };
request.Parameters.Add("BypassBusinessLogicExecution", "CustomSync");

2. Leverage Parallelism and x-ms-dop-hint​

Instead of sending requests sequentially, execute them in parallel using multiple threads. Dataverse returns a response header named x-ms-dop-hint which provides a recommended Degree of Parallelism (DOP) for your environment.

ParallelOptions options = new ParallelOptions
{
    MaxDegreeOfParallelism = 16 // Adjust based on x-ms-dop-hint
};

Parallel.ForEach(entities, options, entity =>
{
    _service.Execute(new UpsertRequest { Target = entity });
});

3. Optimize Batch Sizes​

When using ExecuteMultipleRequest, the maximum batch size is 1,000 requests. However, sending 1,000 complex upserts in a single batch can cause database lock contention and trigger execution time limits.

Best Practice: Start with a batch size of 200 and adjust based on performance testing. If you are using the modern UpsertMultipleRequest, you can scale the batch size higher as the platform handles homogeneous payloads much more efficiently.

10. ALM & Deployment Checklist​

Deploying alternate keys across environments (Dev -> Test -> Prod) requires careful planning because index creation is asynchronous and can fail if target environments contain duplicate data.

Ordered Deployment Steps​

  1. Data Cleansing (Pre-deployment): Run a duplicate detection job in the target environment (e.g., Production) to ensure no records share the same values for the columns that will define the new alternate key.
  2. Export Solution: Package the table customizations (including the key definitions) into your unmanaged solution. Export the solution as Managed for downstream environments.
  3. Deploy Solution: Import the managed solution into the target environment.
  4. Verify Key Status (Post-deployment): Do not start integration pipelines immediately after solution import. Run a post-deployment script or check the Maker Portal to verify that the key status has transitioned to Active.
  5. Handle Failures: If the key status is Failed, execute a data cleansing script to remove duplicates, then call ReactivateEntityKeyRequest programmatically or click Reactivate in the Maker Portal.

Azure DevOps Pipeline Snippet​

The following YAML snippet demonstrates how to automate solution import and execute a post-deployment PowerShell script to verify alternate key activation.

trigger:
- main

pool:
  vmImage: 'windows-latest'

steps:
- task: PowerPlatformToolInstaller@2
  inputs:
    DefaultVersion: true

- task: PowerPlatformImportSolution@2
  inputs:
    authenticationType: 'PowerPlatformSPN'
    PowerPlatformSPN: 'Dataverse-Prod-Connection'
    SolutionInputFile: '$(Build.ArtifactStagingDirectory)/MyCustomizations_managed.zip'
    AsyncOperation: true
    MaxAsyncWaitMinutes: 60

- task: PowerShell@2
  displayName: 'Verify Alternate Key Activation'
  inputs:
    targetType: 'inline'
    script: |
      $connString = "$(DATAVERSE_CONNECTIONSTRING)"
      # Load SDK assemblies dynamically or run a dotnet tool
      # Query RetrieveEntityRequest for key status
      # If status -ne 'Active', throw error to fail the pipeline
      Write-Host "Verifying alternate key status..."
      # Implement polling logic here

11. Common Pitfalls & Troubleshooting Guide​

Pitfall 1: Including Key Attributes in the Write Payload​

  • Symptom: Updates to records fail with metadata errors, or key values are unexpectedly overwritten.
  • Root Cause: The alternate key columns were included in the Entity.Attributes collection during an update operation.
  • Fix: Remove the key columns from the Attributes collection. Keep them exclusively in the KeyAttributes collection or use the specific entity constructor.

Pitfall 2: Upserting with Null Values​

  • Symptom: Duplicate records are created in the database despite having an active alternate key.
  • Root Cause: One or more columns in the alternate key contained NULL values. Dataverse does not enforce uniqueness constraints on null values.
  • Fix: Configure all columns participating in the alternate key as Business Required and implement client-side validation to block null values.

Pitfall 3: Unicode Characters Breaking Web API Calls​

  • Symptom: Web API requests return 404 Not Found or 400 Bad Request when referencing records via alternate keys.
  • Root Cause: The key values contain restricted characters like /, ?, or # which interfere with URL routing.
  • Fix: Avoid using these characters in key columns, or URL-encode the values before making Web API calls (e.g., accounts(crabc_externalid='ACC%2F9999')).

Pitfall 4: Solution Import Fails due to Duplicate Data​

  • Symptom: Solution import succeeds, but the alternate key remains in a Failed state.
  • Root Cause: The target environment contains pre-existing duplicate data for the key columns, preventing SQL Server from building the unique index.
  • Fix: Run a bulk deletion or merge job to resolve duplicates, then execute ReactivateEntityKeyRequest.

Pitfall 5: Using ExecuteMultipleRequest Inside Plug-ins​

  • Symptom: Plug-in execution times out (exceeds 2-minute limit), or database transactions roll back unexpectedly.
  • Root Cause: ExecuteMultipleRequest was executed within a plug-in context. This blocks execution threads and can cause severe database deadlocks.
  • Fix: Never use ExecuteMultipleRequest or UpsertMultipleRequest inside plug-ins. Execute operations sequentially or offload bulk processing to an external asynchronous service (e.g., Azure Service Bus + Azure Function).

Pitfall 6: Exceeding the 900-Byte SQL Index Limit​

  • Symptom: Key creation fails with an error stating the key size exceeds the maximum limit.
  • Root Cause: The combined maximum character length of the string columns in the key exceeds 450 characters (900 bytes).
  • Fix: Reduce the maximum length of the string columns in metadata before creating the key.

Pitfall 7: Applying Field-Level Security to Key Columns​

  • Symptom: Key creation fails, or users receive access denied errors during upsert operations.
  • Root Cause: Field-Level Security is enabled on one of the key columns.
  • Fix: Disable Field-Level Security on all columns participating in the alternate key.

Pitfall 8: Assuming UpsertResponse.RecordCreated is Populated in Web API​

  • Symptom: The Web API response returns 204 No Content and does not indicate whether the record was created or updated.
  • Root Cause: By default, the Web API does not return representation data for performance reasons.
  • Fix: Add the Prefer: return=representation header to your Web API request. This will return 201 Created for creations and 200 OK for updates.

Pitfall 9: Infinite Loops in Plug-ins Triggered by Upsert​

  • Symptom: Plug-in execution fails with a MaxDepthExceeded exception.
  • Root Cause: A plug-in registered on Update or Create executes an Upsert on the same record, triggering a recursive loop.
  • Fix: Implement depth checks at the beginning of your plug-in code: 
    if (context.Depth > 1) return;

Pitfall 10: Concurrency Violations on High-Frequency Upserts​

  • Symptom: High-frequency integration runs fail with ConcurrencyVersionMismatch errors.
  • Root Cause: Multiple threads are attempting to upsert the same record simultaneously.
  • Fix: Implement a retry policy with jitter, or serialize the requests for highly contested records.

12. Exam Focus: Key Facts & Edge Cases​

For the PL-400: Microsoft Power Platform Developer exam, memorize the following critical facts and edge cases:

  • Platform Limits:
    • Maximum of 10 alternate keys per table.
    • Maximum of 16 columns per key.
    • Maximum index size of 900 bytes (SQL Server constraint).
  • Supported Column Types: Decimal, Whole Number, String, DateTime, Lookup, and Choice.
  • Field-Level Security: Columns with FLS enabled cannot be used in alternate keys.
  • Null Behavior: Uniqueness is not enforced if any column in the key contains a null value.
  • Index Status Lifecycle: Know the transitions: Pending (0) -> InProgress (1) -> Active (2) or Failed (3). You must use ReactivateEntityKeyRequest to retry a failed index.
  • Pipeline Stages: An UpsertRequest triggers the Pre-Validation stage of the Upsert message, but then routes to either the Create or Update pipeline for subsequent stages (Pre-Operation, Post-Operation).
  • Web API Syntax:
    • Single key lookup: /accounts(crabc_externalid='ACC123')
    • Composite key lookup: /contacts(firstname='Joe',emailaddress1='joe@test.com')
    • Lookup with alternate key: /_primarycontactid_value=GUID (use the single-valued navigation property naming convention).
  • Bypass Custom Logic: Use the BypassBusinessLogicExecution parameter with the value CustomSync to optimize bulk throughput. This requires the prvBypassCustomBusinessLogic privilege.
  • Plug-in Restrictions: Do not use ExecuteMultipleRequest or UpsertMultipleRequest inside plug-ins or custom workflow activities.