> For the complete documentation index, see [llms.txt](https://docs.omnata.com/omnata-product-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.omnata.com/omnata-product-documentation/omnata-sync-for-snowflake/apps/microsoft-dynamics-365-synapse-link.md). # Microsoft Dynamics 365 Synapse Link Microsoft Dynamics 365 Synapse Link is a powerful integration that connects Dynamics 365 applications with Azure Data Lake Storage Gen2. It enables near real-time data replication from Dataverse into Azure Data Lake Storage Gen2 for advanced analytics reporting and machine learning without impacting the performance of the source systems. ## Prerequisites ### Azure Synapse Link Configuration Before connecting to Omnata, you must configure Azure Synapse Link for Dataverse in your Microsoft Dynamics 365 environment. This process involves linking your Dynamics 365 environment to Azure Data Lake Storage Gen2 and selecting which tables to export. **Key Configuration Requirements:** The Synapse Link must be configured with the **incremental update folder structure** enabled. This is critical for the Omnata plugin to function correctly: 1. When creating or editing your Synapse Link configuration, select **Advanced**, then **Show advanced configuration settings** 2. Enable the option **Enable incremental update folder structure** 3. In the **Time interval** field, choose your desired frequency for reading incremental data (this determines how the system partitions data into time-stamped folders) 4. Select the Dataverse tables you want to sync. You can also select Finance and Operations tables if applicable **Important Notes:** * Data files are always appended * Data is partitioned yearly * The "Append only" and "Partition" options at the table level are ignored **Configuration Resources:** For detailed step-by-step instructions on configuring Azure Synapse Link, refer to Microsoft's official documentation: * [Azure Synapse Link for Dataverse Overview](https://learn.microsoft.com/en-us/power-apps/maker/data-platform/export-to-data-lake) - Comprehensive guide to setting up Synapse Link * [Link Finance and Operations with Power Platform](https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-select-fno-data#link-your-finance-and-operations-environment-with-microsoft-power-platform) - Required for F\&O environments * [Enable Change Tracking](https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-select-fno-data#add-configurations-in-a-finance-and-operations-apps-environment) - Configure SQL row version change tracking for F\&O apps * [Choose Finance and Operations Data](https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-select-fno-data) - Select and configure F\&O tables for export ### Supported Products Azure Synapse Link is supported across several Dynamics 365 products built on Microsoft Dataverse: **Dynamics 365 Customer Engagement (CE) Apps:** * Dynamics 365 Sales * Dynamics 365 Customer Service * Dynamics 365 Marketing * Dynamics 365 Field Service * Dynamics 365 Project Operations **Dynamics 365 Finance and Operations (F\&O) Apps:** * Dynamics 365 Finance * Dynamics 365 Supply Chain Management * Dynamics 365 Commerce * Dynamics 365 Human Resources ### System Requirements * Microsoft Dynamics 365 with cloud-based Tier-2 or higher environment * Azure Data Lake Storage Gen2 account * Snowflake account with appropriate permissions * Azure AD tenant access ### Required Permissions * **Azure**: Storage Blob Data Reader role on the target storage account * **Snowflake**: ACCOUNTADMIN or equivalent to create storage integrations * **Dynamics 365**: System Administrator role for Synapse Link configuration ## Authentication ### Azure side configuration Omnata uses a two-step authentication approach to securely access your Azure Data Lake Storage. You need to complete these steps **before** you create the connection in Omnata. 1. **Snowflake Storage Integration** - For secure access to Azure Data Lake 2. **Azure AD Service Principal** - For programmatic access to storage resources #### Step 1: Create Snowflake Storage Integration Create a storage integration in your Snowflake account to enable secure access to your Azure Data Lake: ```sql CREATE STORAGE INTEGRATION dynamics_365_integration TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'AZURE' ENABLED = TRUE AZURE_TENANT_ID = '' STORAGE_ALLOWED_LOCATIONS = ('azure://.blob.core.windows.net//'); ``` #### Step 2: Grant Integration Permissions Grant usage permissions to the Omnata applications: {% code overflow="wrap" %} ```sql GRANT USAGE ON INTEGRATION dynamics_365_integration TO APPLICATION OMNATA_SYNC_ENGINE; GRANT USAGE ON INTEGRATION dynamics_365_integration TO APPLICATION OMNATA_DYNAMICS_365_SYNAPSE_LINK; ``` {% endcode %} #### Step 3: Retrieve Integration Details Get the integration details needed for Azure configuration: ```sql DESCRIBE INTEGRATION dynamics_365_integration; ``` Note the following values from the output: * `AZURE_MULTI_TENANT_APP_NAME` * `AZURE_CONSENT_URL` #### Step 4: Authorize Storage Integration Visit the `AZURE_CONSENT_URL` from Step 3 to grant consent in your Azure AD tenant. This authorizes Snowflake to access your Azure storage. #### Step 5: Create Azure AD App Registration 1. Navigate to Azure Portal → Azure Active Directory → App registrations 2. Click **New registration** 3. Provide a descriptive name (e.g., "Omnata-Dynamics365-Sync") 4. Under **Certificates & secrets**, create a new client secret Save the following values for the connection configuration: * Tenant ID * Client ID (Application ID) * Client Secret #### Step 6: Assign RBAC Permissions In your Azure Storage Account, assign the **Storage Blob Data Reader** role to both: * The App Registration created in Step 5 (using the Client ID) * The Snowflake Storage Integration (using the `AZURE_MULTI_TENANT_APP_NAME` from Step 3) This can be done in the Azure Portal under your Storage Account → Access Control (IAM) → Add role assignment. ### Create a connection in the Omnata UI With the previous Azure-side steps complete, you can Create a Connection in the Omnata UI. The connection form will require the following: * TenantId * ClientId * Client Secret * Storage Account * Container * Storage Integration ## Inbound Syncs The plugin supports ingestion of any entities exported to Azure Data Lake via Synapse Link. ### Supported Sync Strategies **Incremental** - Uses change tracking based on folder timestamps and record version numbers. Recommended for most entities to efficiently sync only new and changed data. **Full Refresh** - Complete data replacement on each sync. Required for kernel/system tables that don't support incremental exports. ### Supported Streams All entities configured in your Azure Synapse Link export are available for syncing, including: **Business Entities (Incremental Sync):** * Customer data (accounts, contacts, leads) * Transaction data (sales orders, invoices, payments) * Product data (items, categories, price lists) * Operational data (inventory, production, logistics) * Custom entities enabled in Synapse Link **Kernel/System Tables (Full Refresh Only):** The following system tables don't support incremental exports and must use Full Refresh: ``` dataarea, userinfo, securityrole, securityuserrole, sqldictionary, partitions, securityprivilege, timezoneslist, securityduty, securitysubrole, securityuserrolecondition, databaselog, securityroleruntime, securityroleprivilegeexplodedgraph, securityroledutyexplodedgraph, timezonesrulesdata, securityroleexplodedgraph, userdataareafilter, sysinheritancerelations ``` ### Schema Management The plugin automatically detects and maps schemas from Common Data Model (CDM) definitions: * **Automatic Type Mapping**: Converts CDM types to Snowflake-compatible data types * **Primary Key Detection**: Identifies and handles composite primary keys from CDM metadata * **Column Ordering**: Maintains field positions from the source system * **Relationship Handling**: Processes field constraints and relationships ### Metadata Tracking Microsoft Azure Synapse Link publishes metadata fields that track synchronization operations. The plugin provides flexible handling of this metadata to suit different use cases. **When Metadata Inclusion is Enabled:** * `SinkCreatedOn` field is included in the unique identifier * Creates separate records for each sync occurrence of the same data * Provides complete audit trail of all sync operations * Useful for change detection and historical analysis **When Metadata Inclusion is Disabled:** * Only the latest version of each record is maintained * Subsequent syncs overwrite previous entries * Reduces storage overhead for high-frequency syncs * Suitable for real-time operational reporting **Additional Metadata Fields:** When metadata inclusion is enabled, these fields are added to the payload: | Field | Description | Example | | ------------------- | -------------------------------------- | ------------------------------ | | `SinkCreatedOn` | Record creation timestamp in Synapse | `2024-01-15 14:30:25` | | `SinkModifiedOn` | Record modification timestamp | `2024-01-15 14:30:25` | | `source_stage` | Snowflake stage containing source file | `dynamics.mystorage_container` | | `source_filename` | Source CSV file name | `account/20240115T143025Z.csv` | | `source_row_number` | Row position in source file | `1247` | This metadata enables complete data lineage tracking from Azure Data Lake through to Snowflake, supporting audit requirements and troubleshooting. ### Managing Performance #### Parallel Processing For optimal performance with large datasets: * **Configurable Batch Size**: Default of 24 date folders per batch * **Multi-threaded Processing**: Concurrent folder processing for faster syncs * **State Management**: Maintains processing state across sync runs for failed folder recovery ## Advanced Features ### F\&O hard-delete reconciliation Azure Synapse Link's incremental export captures updates and soft-deletes reliably, but for certain Finance & Operations entities, specifically **General Journal Entries** and **General Journal Account Entries,** hard-deletes in the source system are not always propagated to the data lake. For customers using these entities as the basis for financial reporting or reconciliation, undetected hard-deletes in Snowflake can compromise ledger integrity. To address this, the plugin includes an **optional monthly reconciliation pass** that verifies the record set in Snowflake against the live Dynamics 365 Finance & Operations OData API. Records that exist in Snowflake but are absent from the source system are removed from the destination tables. **When to enable this feature** Enable hard-delete reconciliation if: * You sync `generaljournalentry` and/or `generaljournalaccountentry` streams * Downstream reporting requires ledger-accurate reconciliation between Snowflake and D365 * You have finance or audit stakeholders who need assurance that hard-deleted journal entries do not persist in Snowflake Only these F\&Os streams are supported at this stage, [get in touch](https://omnata.com/contact-us) if you need this for others. **Prerequisites** The Azure service principal you configured for blob access must also have permission to read the following Dynamics 365 F\&O OData endpoints: * `/data/GeneralJournalEntries` * `/data/GeneralJournalAccountEntries` This typically requires the service principal to be assigned a security role in D365 F\&O with **read** access to those entities. The `System Administrator` role is sufficient; a purpose-built role is preferable for production. If the service principal cannot authenticate against the OData API, reconciliation will fail with an authorization error and the audit table will not be updated. **Configuration** Reconciliation is controlled by two sync parameters set on the inbound sync:
ParameterDescriptionDefault
enable_gr_reconciliationSet to true to enable monthly reconciliation.false
reconciliation_day_of_month
(Default: 1)		The earliest day of the calendar month on which reconciliation is permitted to run. Values outside 1–31 are clamped to the last day of the current month.1
Reconciliation runs at most **once per calendar month** per sync configuration. **How it works** When enabled, an eligible sync run executes three phases in sequence: 1. **Incremental ingestion** — normal reading of new and changed records from the Synapse Link folder structure across all configured streams. 2. **Accounting date backfill** — General Journal Account Entries inherit their accounting date from their parent General Journal Entry. This phase joins GJAE records to previously-landed GJE records in Snowflake to attach the accounting date used by the reconciliation window. 3. **Reconciliation** — the plugin authenticates to the D365 F\&O OData API using your service principal, retrieves the key set for GJE and GJAE within the reconciliation window, and compares those keys against the keys currently present in Snowflake. Any records present in Snowflake but absent from the source are enqueued as deletes and `OMNATA_IS_DELETED` is flagged the destination tables. Each reconciliation run is logged to an internal audit table (`OMNATA_AZURE_SYNAPSE_LINK_PLUGIN.DYNAMICS.RECONCILIATION_AUDIT`), which the plugin consults to enforce the once-per-month cadence. **Reconciliation window** The comparison is scoped by the accounting date on GJE records. Records with future or heavily backdated accounting dates that fall outside the window may not be reconciled in a given run — they will be picked up in subsequent monthly runs as the window rolls forward. Customers who make significant backdated adjustments should be aware that reconciliation may lag by up to one calendar month. **Considerations and limitations** * **Scope is limited to two entities.** The feature does not reconcile any stream other than `generaljournalentry` and `generaljournalaccountentry`. If you require hard-delete detection for other entities, contact Omnata to discuss options. * **Monthly cadence only.** The cadence is intentional, reconciliation requires additional D365 API traffic and is designed as a periodic integrity check rather than a real-time verification. * **Additional API traffic against D365.** Reconciliation makes OData calls against your D365 environment. On high-volume ledgers this can be non-trivial; consider aligning reconciliation to a low-traffic day of the month. * **Requires a healthy first sync.** Allow at least one prior successful incremental sync to complete before enabling reconciliation. * **Service principal permissions are checked at runtime.** If the SP loses access to the OData endpoints after being granted, reconciliation will fail silently at the API-fetch step. **Reconciliation troubleshooting** If reconciliation appears not to be running: 1. Confirm `enable_gr_reconciliation` is set to `true` on the sync. 2. Check today's date against `reconciliation_day_of_month`. 3. Query `OMNATA_AZURE_SYNAPSE_LINK_PLUGIN.DYNAMICS.RECONCILIATION_AUDIT,` if a row exists for the current calendar month, reconciliation has already run. 4. Filter sync logs for `RECONCILIATION` — the plugin emits progress messages for each phase and each stream. If reconciliation runs but produces no deletes when you expect them: 1. Verify the deleted records fall within the reconciliation window (based on GJE accounting date). 2. Confirm the records were hard-deleted in D365, not soft-deleted (soft-deletes are already handled by the standard incremental flow). 3. Confirm the D365 OData API returns the expected records when queried directly with your service principal credentials. ## Troubleshooting #### Authentication Failures **Symptoms**: `ClientAuthenticationError` or `403 Forbidden` errors **Solutions**: * Verify Azure AD app registration has a valid client secret * Check Storage Blob Data Reader role assignment for both the app registration and Snowflake integration * Ensure Snowflake storage integration is properly authorized via the consent URL * Validate tenant ID matches between Snowflake integration and Azure configuration #### Missing Data or Empty Syncs **Symptoms**: Sync completes successfully but no data appears in Snowflake **Solutions**: * Verify `model.json` exists in the Azure Data Lake folders * Confirm entity names match between Dynamics 365 and plugin configuration * Check that Synapse Link export is active and has recent data * Ensure incremental update folder structure is enabled in Synapse Link configuration ## Limitations **Current Limitations:** * Outbound sync is not currently supported * Near real-time sync only (depends on Synapse Link export frequency configured in Step 3 of prerequisites) * Custom entities must be explicitly enabled in the Synapse Link export configuration * Some complex Dynamics 365 data types may require manual mapping **Scale Considerations:** * Files larger than 100MB may impact sync performance * Very frequent exports may cause resource contention * Recommended maximum of 64 concurrent streams * Consider Azure Data Lake retention policies for cost optimization *** Last updated --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.omnata.com/omnata-product-documentation/omnata-sync-for-snowflake/apps/microsoft-dynamics-365-synapse-link.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.