Introduction
Azure Synapse Link for Dataverse enables you to get near real-time insights over your data in Microsoft Dataverse. With a tight seamless integration between Dataverse and Azure Synapse Analytics, Azure Synapse Link enables you to run analytics, business intelligence and machine learning scenarios on your data.
However, there could still be a few use cases & scenarios where data would be required in relational database like Azure SQL Database. For example, there could be downstream systems which would pull data from the Azure SQL Database at regular intervals. To assist with similar use cases where Dataverse data from Azure Data Lake Storage (ADLS) gen 2 needs to be pushed to Azure SQL Database, an Azure Data Factory (ADF) template has been introduced which leverages append-only incremental feature Query and analyze the incremental updates - Power Apps | Microsoft Docs and will move data as well as sync Dataverse metadata to destination Azure SQL tables.
Guiding Principles
Cost effective
- Trigger based
- Number of triggers based on time period selected
- Network egress optimization: only the latest incremental updates are pushed to destination DB
Low maintenance
- Automated table schema sync
- DeleteLog support
Automatic infrastructure management
- Combine pipeline intent w/ resource allocation & mgmt.
- Globally available data movement capability
Operational Excellence:
- Telemetry systems, usage optimization, etc.
Reuse existing SQL assets (DES)
- Table schema in similar pattern like DES tables.
- Minimal disruption experience.
Solution Overview
Azure Data Factory templates are predefined Azure Data Factory pipelines that allow you to get started quickly with Data Factory. Templates are useful when you're new to Data Factory and want to get started quickly. These templates represent code samples and guidance to test out functionality and reduce the development time for building data integration projects, improving developer productivity.
The ADF template has been designed to incrementally load new or updated rows from a ADLS Gen 2 to Azure SQL by using Azure Synapse Link for Dataverse – Incremental Updates that provides incremental folders with incremental updates made to Dataverse data during a user-specified time interval.
It is recommended to have all the Azure resources in the same region to reduce latency.
ADF Template Overview
This new ADF template retrieves files that are provided under the partitions section of model.json from ADLS Gen2 source. Then it iterates over each file in the source and copies the file to the destination data store.
Please download the ADF template from Azure Data Factory Gallery. For configuring the template, please refer to the documentation: https://docs.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-pipelines
Please also refer to https://techcommunity.microsoft.com/t5/azure-data-factory-blog/copy-dataverse-data-from-adls-gen2-to-azure-sql-db-leveraging/ba-p/3606635
More details on the ADF template FAQs & Best Practices will be shared in part 2.
Authors:
-Vidyasagar Chitchula (Principal Solution Architect)
-Kuntal Ghoshal (Senior Solution Architect)
-Sanjeev Kumar (Senior Program Manager)
