ERP Nightly Load and Live Partner Events

Two flows, two patterns - do not force one tool to do both.

Nightly ERP sync: Azure Data Factory with the Dataverse connector, upserting on an alternate key (ERP item number).

• Row-by-row Power Automate is wrong at 120,000 rows; even at 5,000 rows it consumes a licence budget and gives no restartability.
• Use a watermark or ERP change-timestamp to pull only changed rows, batch the writes, and parallelise by partition. For the monthly full reseed, run it as a separate, throttled pipeline that can be restarted from a checkpoint without duplicating rows - which the alternate-key upsert makes safe.
• Respect service protection limits; if you exceed them the platform throttles with 429s, so build backoff into the pipeline rather than retrying instantly.

Live partner events at 5/s (burst 40/s): partner → Azure API Management or Function → Service Bus → consumer that upserts to Dataverse.

• 5/s is 18,000/hour and is fine for the Web API, but the 40/s spike with partner retries is where a direct-to-Dataverse endpoint breaks: retries multiply load and the partner's timeout behaviour produces duplicates. A queue absorbs the spike and gives you the replay store operations asked for.

Verify: replay 1,000 duplicated partner events and confirm no duplicate status rows; kill the consumer mid-batch.

Design for at-least-once delivery and make the write idempotent; ordering is a per-shipment problem.

• Idempotency: the partner's event carries an event ID and a shipment ID. Upsert on an alternate key derived from the shipment plus a monotonic sequence, and store LastAppliedSequence on the Shipment. An event with a lower sequence than what is already stored is acknowledged and dropped - this is what prevents a stale retry from overwriting a newer status.
• Duplicates: enable Service Bus duplicate detection on the event ID with a window comfortably longer than the partner's retry interval. Duplicate detection is time-bounded, so the sequence check above is the real guarantee.
• Ordering: Service Bus sessions keyed on shipment ID give you ordered, single-consumer processing per shipment without serialising the whole feed. Global ordering is not needed and would destroy throughput at the 40/s spike.
• Alternate keys must be defined before go-live - they cannot be added to a table that already holds violating data, and changing one later is a migration.

Verify: deliver events 1, 2, 1, 3 for one shipment and confirm the stored status ends at 3 with no duplicate rows; then replay the whole dead-letter queue.

Application identity, vaulted secrets, and a correlation trail.

• Register an Entra ID application and create a Dataverse application user with a custom security role containing only the tables and operations the integration needs. Application users are not licensed interactive accounts and are not tied to a leaver's mailbox - this directly answers the objection.
• The partner obtains its own token via client credentials; do not share a secret across both integrations. Store the secret in Key Vault and reference it through an environment variable or pipeline secret, never in a solution or a flow step, and define a rotation owner and interval.
• Do not use a System Administrator role "because it works" - the integration then becomes a privileged credential with no owner, which is precisely what the security team is rejecting.
• Observability: stamp a correlation ID from the partner's event through the queue message to the Dataverse row; log every attempt with outcome and duration; alert on dead-letter depth and on 429/401 rates rather than only on failures. A 401 spike usually means secret rotation broke a consumer, and you want that discovered by an alert, not by the partner.

Verify: disable the app registration in Test and confirm the alert fires within the agreed window; review the audit log and confirm every write is attributed to the application user, not a person.