Challenge 2 - Create Incremental Load Pipelines
< Previous Challenge - Home - Next Challenge>
Prerequisite
Review the SQL MI to Dedicated Pool Proof of Concept document.
Introduction
For this section we need to add Change Data Catpure functionality to our source database and then create an incremental synapse pipeline to look for those changes and push them to the dedicated pool. We also need to employ a proper design in the dedicated pools so that it does not impact performance. Finally let’s visualize this data in Power BI.
Description
For this challenge we need to implement the following:
- Implement Change Data Capture on the Azure SQL Database and be able to articulate on the cdc tables and functions and their purpose.
- Create an incremental synapse pipeline that accounts for the following…
- For each time a pipeline is executed, it will only update the SQL Dedicated Pool with new row inserts, updates and deletions since the last time it was executed.
- You only need to do 2-3 tables so make sure your pipeline has a lookup to determine which tables to copy and where to land them in the Dedicated Pool. Please choose related tables so modifications can easily be viewed in the target Dedicated Pool and Power BI.
- Implement Staging and Production Tables in the Dedicated Pool and an automated methodology to update data from staging to production.
- Create a Power BI report that queries the production tables in the Dedicated Pool.
Do not setup a trigger in this challenge That will be addressed in the next challenge.
Success Criteria
Be able to show the data pipleine, meaning you can demonstrate executing the change in the SQL Database, validate the CDC functionality, and showcase the data flow through the dedicated pool to the report.
Learning Resources
The following links may be useful to achieving the success crieria listed above.