14.2 Entity Change Tracking & Recurring Integrations

14.2 Entity Change Tracking & Recurring Integrations

Quick Answer: Entity Change Tracking is Dynamics 365 Finance and Operations' mechanism for capturing data modifications at the database layer to enable incremental (delta) exports, eliminating costly full table scans. Powered by SQL Server Change Tracking, DMF supports three tracking scopes: Primary table (tracks root table changes only), Entire entity (tracks modifications across all joined tables in the entity), and Custom query (uses an AOT query for complex relationship graphs). For automated, programmatic file exchanges, DMF provides Recurring Data Jobs—asynchronous message queues identified by a unique Activity ID and scheduled via the F&O batch engine. External middleware (Logic Apps, Azure Functions, MuleSoft) interacts with these queues using the DMF Package REST API via /api/connector/enqueue, /api/connector/dequeue, and /api/connector/ack endpoints authenticated through Microsoft Entra ID.

---

1. SQL Server Change Tracking & Entity Delta Exports

In high-volume enterprise environments, external analytics platforms, data warehouses, and peripheral e-commerce systems require frequent updates of ERP master and transactional data. Performing recurring full exports of 5 million customer or inventory rows every hour causes severe database I/O bottlenecks, network saturation, and lock contention. DMF solves this using Incremental (Delta) Exports.

SQL Server Change Tracking Engine & Delta Export Mechanics

Transactional D365 F&O Operations
 ├── User inserts Customer 'CUST-101' in CustTable
 ├── User updates Address on 'CUST-100' in LogisticsPostalAddress
 └── User deletes Customer 'CUST-099' in CustTable
         │
         ▼ (SQL Server Internal Side Tables)
┌─────────────────────────────────────────────────────────────┐
│         sys.change_tracking_<Table_ObjectId>                │
│  • Records Primary Key + SYS_CHANGE_VERSION + Operation (I/U/D)│
│  • Lightweight: Stores change metadata, NOT old/new data    │
└──────────────────────────────┬──────────────────────────────┘
                               │ DMF Delta Query Engine
                               ▼
┌─────────────────────────────────────────────────────────────┐
│               DMF Export Project Execution                  │
│  • Queries: WHERE SYS_CHANGE_VERSION > @LastSyncVersion     │
│  • Extracts ONLY modified/inserted/deleted records          │
│  • Updates @LastSyncVersion to current DB version on success│
└─────────────────────────────────────────────────────────────┘

The Underlying SQL Mechanism

DMF change tracking relies directly on native SQL Server Change Tracking:

• Synchronous Tracking: Whenever an INSERT, UPDATE, or DELETE commits against an enabled table, SQL Server records the table primary key and the transaction's unique SYS_CHANGE_VERSION inside a dedicated internal tracking table (sys.change_tracking_<TableId>).
• Zero Payload Overhead: Unlike SQL Server Change Data Capture (CDC) or database audit triggers, Change Tracking does not store historical column images or before/after row copies. It records only that a row changed and its operation type, making it ultra-lightweight.
• Synchronization Baseline: During the initial export execution of a data project, DMF performs a full baseline extract and stores the highest recorded SYS_CHANGE_VERSION. On all subsequent runs with incremental tracking active, the DMF engine filters records using SQL joins against the change tracking tables where SYS_CHANGE_VERSION > @LastSyncVersion.
• Retention Period & Invalidation: SQL Server Change Tracking enforces a retention period (configured in days, typically 3 days). If an incremental export job is paused or fails for longer than the retention window, the internal side tables purge the older versions. When the export resumes, DMF detects that the synchronization baseline has expired and raises a fatal synchronization error. To recover, administrators must disable and re-enable change tracking on the entity and perform a full baseline export.

---

2. Change Tracking Configuration Scopes

In the Data Management workspace, under Data entities, administrators and developers configure change tracking per entity by selecting Change tracking on the ActionPane. DMF provides three distinct tracking scopes:

[!WARNING] Real-World Exam Trap: The Missing Address Update A classic MB-500 scenario asks: "An integration exports customer deltas every 15 minutes. When users update customer credit limits, the records export correctly. However, when users update customer delivery addresses, the changes are never exported. Why?" The root cause is that Change Tracking was configured with Primary table scope instead of Entire entity. CustTable was not modified during the address change; the update occurred in LogisticsPostalAddress.

---

3. Recurring Data Jobs: Architecture & Scheduling

While interactive Data Projects are suitable for manual uploads, enterprise application integration requires automated, scheduled, and unattended file exchanges. Recurring Data Jobs provide this capability by exposing asynchronous message queues built on the DMF engine.

Recurring Data Job Architecture

External Middleware (Logic App / Azure Function / BizTalk)
 │
 ├── POST /api/connector/enqueue/{activityId} (Upload File/Package)
 └── GET  /api/connector/dequeue/{activityId} (Retrieve Export Package)
         │
         ▼ (Queue Identified by Activity ID)
┌─────────────────────────────────────────────────────────────┐
│            Recurring Integration Message Queue              │
│  • Stores queued messages with unique Message IDs           │
│  • Decouples external API arrival from F&O batch processing │
└──────────────────────────────┬──────────────────────────────┘
                               │ F&O Batch Polling
                               ▼
┌─────────────────────────────────────────────────────────────┐
│             D365 F&O Batch Processing Engine                │
│  • Recurring Batch Job (e.g., runs every 5 minutes)         │
│  • Ingests messages -> Staging -> Target                    │
│  • Updates Message Status: Enqueued -> Processed / Failed   │
└─────────────────────────────────────────────────────────────┘

Setting Up a Recurring Data Job

1. In the Data management workspace, select an existing import or export Data Project.
2. Click Create recurring data job in the ActionPane.
3. Configure the job parameters:
   • Name & Description: Operational title (e.g., InboundSalesOrdersQueue).
   • Activity ID: A system-generated GUID that serves as the queue's permanent REST API address.
   • Recurrence: Standard batch schedule (e.g., repeat every 5 minutes, hourly, daily).
   • Supported Data Format: XML, CSV, Excel, or Data Package.
4. Save and enable the job. This registers a batch task in the F&O batch framework that continuously polls and drains the associated integration queue.

Message Status Lifecycle & Monitoring

Inside the Data Management workspace under Job history > Manage recurring data jobs, administrators can monitor queue telemetry:

• Enqueued: The file has been uploaded via the API and resides in Azure storage awaiting batch pickup.
• Processing: The F&O batch engine has dequeued the message and is writing to staging or target tables.
• Processed: The data has successfully loaded into the application tables.
• Failed: A data validation or parsing error occurred. The administrator can drill into the staging execution log to view the error detail.

---

4. Microsoft Entra ID Authentication for Integrations

External systems communicating with DMF endpoints do not use interactive username/password logins. They authenticate via service-to-service OAuth 2.0 client credentials grants using Microsoft Entra ID (formerly Azure AD).

Authentication Handshake Configuration

1. Register App in Entra ID: Create an App Registration in the customer's Microsoft Entra tenant (generating an Application (Client) ID and a Client Secret).
2. Register in Dynamics 365 Finance and Operations:
   • Navigate to System administration > Setup > Microsoft Entra ID applications.
   • Add a new record:
     • Client ID: Paste the Entra ID Application ID.
     • Name: Friendly identifier (e.g., MuleSoft_Integration_Service).
     • User ID: Select a dedicated non-interactive integration user account (e.g., IntegrationSvcUser).
3. Assign Security Roles: The mapped User ID must be assigned security roles granting access to the underlying data entities and DMF execution privileges (e.g., Data management migration user or entity-specific integration roles).

---

5. DMF Package REST API Endpoints

The Recurring Integration framework provides standardized REST endpoints for external systems to interact with integration queues programmatically.

1. Inbound Ingestion: The Enqueue Endpoint

Used by external middleware to push a data file or data package into an inbound recurring data job queue:

• Method: POST
• URI: https://<environment>.operations.dynamics.com/api/connector/enqueue/{activityId}?entity={entityName}&company={companyId}
• Headers:
  • Authorization: Bearer <OAuth_Token>
  • Content-Type: application/octet-stream (or application/json / application/zip depending on package format)
• Body: Binary stream of the data file or ZIP package.
• Response: HTTP 200 OK containing a generated MessageId (GUID). The message sits in the queue until the next scheduled batch execution picks it up.

2. Outbound Extraction: The Dequeue Endpoint

Used by external systems to pull exported files or data packages generated by an outbound recurring data job:

• Method: GET
• URI: https://<environment>.operations.dynamics.com/api/connector/dequeue/{activityId}
• Headers: Authorization: Bearer <OAuth_Token>
• Response: HTTP 200 OK with the data package stream in the body, accompanied by a custom response header: MessageId: <GUID>.

3. Acknowledgment: The Ack Endpoint

After successfully downloading and verifying a dequeued message, the client must acknowledge receipt:

• Method: POST
• URI: https://<environment>.operations.dynamics.com/api/connector/ack/{activityId}
• Headers: Authorization: Bearer <OAuth_Token>, Content-Type: application/json
• Body: { "messageId": "<GUID_From_Dequeue_Header>" }
• Significance: Calling /ack marks the message as completed and removes it from the active queue. If the client fails to call /ack, the next call to /dequeue will retrieve the exact same message again, causing duplicate processing.

Ad-Hoc Package REST API (Execution ID Lifecycle)

For non-recurring, large-scale ad-hoc file imports, DMF exposes the Data Package API:

1. GetAzureWriteUrl: Returns a temporary Azure Blob SAS URL.
2. Client pushes ZIP package to the SAS URL via HTTP PUT.
3. ImportFromPackage: Initiates execution against a DMF project and returns an ExecutionId.
4. GetExecutionStatus: Polled by client using ExecutionId until status reaches Succeeded, PartiallySucceeded, or Failed.
5. GetExportedPackageUrl: Used in ad-hoc export scenarios to obtain the SAS download link once export finishes.

---

6. Scenario Walk-Through: Hybrid Cloud Integration Pipeline

Business Scenario

An international enterprise integrates an external Shopify e-commerce platform with Dynamics 365 Finance. Every 10 minutes, an Azure Logic App extracts customer orders from Shopify and uploads them into F&O. Concurrently, every 30 minutes, the Logic App extracts newly generated shipment and invoice confirmations from F&O to notify customers.

Step-by-Step Execution Architecture

1. Inbound Pipeline:
   • Logic App converts Shopify JSON payload into a DMF-compliant CSV file.
   • Logic App obtains an Entra ID OAuth 2.0 token and issues an HTTP POST to /api/connector/enqueue/{activityId}?entity=SalesOrderHeadersV2&company=USMF.
   • F&O returns HTTP 200 with MessageId: 8a4b2c1d-....
   • F&O recurring batch job runs on its 5-minute recurrence, processes the staging records into target sales orders, and sets status to Processed.
2. Outbound Pipeline:
   • In F&O, an export project for CustomerInvoices has change tracking enabled with Entire entity scope.
   • The export project runs in batch every 15 minutes, generating delta packages.
   • Logic App executes HTTP GET to /api/connector/dequeue/{exportActivityId}.
   • F&O returns the package binary along with response header MessageId: 9f8e7d6c-....
   • Logic App parses the ZIP file, updates customer portals, and immediately issues HTTP POST to /api/connector/ack/{exportActivityId} passing { "messageId": "9f8e7d6c-..." }.
   • The queue deletes the completed message, preventing duplicate processing on subsequent runs.

---

7. Real-World Exam Traps: Change Tracking & Recurring Integrations

[!WARNING] Exam Trap 1: Primary Table Scope Omitting Address and Contact Edits When an exam question specifies that updating customer credit limits or names triggers delta exports, but updating addresses fails to trigger exports, the tracking scope is set to Primary table. Because address information lives in LogisticsPostalAddress and DirPartyTable, the scope must be changed to Entire entity.

[!WARNING] Exam Trap 2: Infinite Duplicate Delivery Due to Missing /ack If external middleware repeatedly receives the exact same export package on every scheduled execution of /api/connector/dequeue, the client application is omitting the /api/connector/ack call. The DMF queue keeps the message active until an acknowledgment is received.

[!WARNING] Exam Trap 3: Entra ID App Not Registered in F&O System Administration An external client that successfully authenticates against Microsoft Entra ID and obtains a valid JWT Bearer token will still receive HTTP 401 Unauthorized or 403 Forbidden from F&O endpoints if the Application (Client) ID has not been added to System administration > Setup > Microsoft Entra ID applications and mapped to an active F&O User ID.

[!WARNING] Exam Trap 4: Change Tracking Sync Errors After Retention Expiration When change tracking side tables are purged by SQL Server cleanup because an export job was inactive for longer than the retention window, the job will fail with an invalid version error. The fix is to disable change tracking on the entity, re-enable it, and perform an initial full baseline export.