Welcome to the nineteenth and penultimate post in my series focused on providing a set of revision notes for the PL-400: Microsoft Power Platform Developer exam. Previously, we examined what events are and how they can provide a streamlined mechanism for executing complex business logic outside of Microsoft Dataverse. In today’s post, we conclude our review of the Develop Integrations area of the exam by looking at our final exam topic - Implement data synchronisation. For this, Microsoft expects candidates to demonstrate knowledge of the following:

Implement data synchronization

• configure and use entity change tracking
• read entity change records by using platform APIs
• create and use alternate keys

For a long time now, Microsoft has provided tools that can perform complex or straightforward integrations involving data that resides within Microsoft Dataverse. In some situations, the out of the box capabilities offered to us can negate the need to implement solutions to track data changes or lookup table rows based on alternative identifiers. In discussing the topics listed above today, we will see how these features can achieve these objectives and more.

As with all posts in this series, the aim is to provide a broad outline of the core areas to keep in mind when tackling the exam, linked to appropriate resources for more focused study. Ideally, your revision should involve a high degree of hands-on testing and familiarity in working with the platform if you want to do well in this exam.

Change Tracking Overview

If you have any previous experience working with SQL Server, you may be familiar with a built-in capability called Change Tracking. You may infer what this involves quickly from the title. Still, to explain clearly, this is a feature you can enable within your databases/tables to more straightforwardly track when your database records are added, modified or removed. Change Tracking can be beneficial for us in several different scenarios. For example, let’s say we have a batch data synchronisation activity. It will typically be more efficient only to process any actual changes that have taken place instead of processing thousands if not millions of records each day. Using Change Tracking, we can easily append the appropriate filters to our query to get the precise set of changes we want. Also, by leveraging this “out of the box” capability, we don’t need need to spend time building out our own change tracking capability, meaning we can focus our development efforts on more critical activities.

So you may be wondering at this stage - why are we talking about something that’s nothing to do with the Power Platform? Well, fun fact - Microsoft Dataverse uses SQL Server behind the scenes, and the change tracking capability available to us here is based on the same one we can leverage in SQL Server. We can enable this for any table by simply ticking the appropriate option on the relevant properties page:

We can then query change tracking information via the Web API and the RetrieveEntityChangesRequest if we are working with a C# application. Based on the actual changes that have occurred, we will then receive back a list of new rows, updated rows (along with the columns that have changed) and any rows that users may have removed since we last checked. The critical element as part of this is either our delta token (for Web API) or version number (for the Organisation Service), which we include as part of any subsequent requests we make to our Microsoft Dataverse to detect the changes made since the last time. The platform supports multiple token/version numbers existing at any time but, keep in mind that tokens will expire after 90 days. In theory, if your integration processes changes daily or weekly, this should never be a problem; simply ensure you store the relevant portions of the delta link to continue to return only changes you need.

Demo: Working with Change Tracking in the Microsoft Dataverse Web API

To see how to enable change tracking capability and then query delta changes from Microsoft Dataverse using this, please watch the video below where I showcase the steps involved:

Alternate Keys: What are they, and why should I use them?

Typically, when integrating alongside an external system, it will contain unique record identifiers that differ from the globally unique identifier (GUID) for each Microsoft Dataverse row. It could be this identifier is an integer, string or a mixture of different attribute values (e.g. the address and name of a customer). Due to the myriad of different systems available not just through Microsoft but also through other vendors, it can be challenging to find a straightforward solution to handle this.

Enter stage left alternate keys, a helpful feature we can enable on one or several columns on a table. By creating them, you are informing Microsoft Dataverse that the contents of this field must always be unique within the system. After an alternate key is defined, the application will create a unique index in the database, covering the specified columns. Therefore, the hidden benefit of this is that queries targeting alternate key values will always run faster. When writing this post, it was possible to use both the Power Apps maker portal and classic interface to create alternate keys; where possible, I would advise you to use the maker portal. An additional benefit with alternate keys is that the SDK supports several capabilities that developers can easily leverage alongside them. For example, it’s possible to create new records by specifying its alternate key value and execute Web API Retrieve requests using one or multiple Alternate Key values.

Keep in mind some of the limitations concerning alternate keys:

• The platform enforces some rules not only on the size of the alternate key (in bytes) but also on whether certain illegal Unicode characters exist in the field value. If any attribute value breaks these rules, errors will likely occur.
• You can only have a maximum of 10 per table. Microsoft recently increased this limit.
• The application relies on a system job to create the appropriate index for each newly defined alternate key. The processing time for this job can take a considerable amount of time to complete, depending on the size of your database. You can navigate into the classic interface at any time to track progress and also evaluate whether any errors have occurred.
• You are limited to the following data types when defining an attribute as an alternate key:
  • Date and Time
  • Decimal
  • Lookup
  • Option Set
  • Single Line of Text
  • Whole Number

Demo: Working with Alternate Keys in the Microsoft Dataverse Web API

To understand how to define and then query data from the platform using alternate keys, check out the video below:

And that’s a wrap…

…you might expect me to say at this stage. True enough, we’ve now covered all of the content within the PL-400 exam. What I wanted to do in next week’s post is perform a round-up post that collects together all of the posts/videos published during the series and share some general advice relating to the exam. Catch you again next week! 😉