Skip to content

Latest commit

 

History

History
35 lines (25 loc) · 2.39 KB

File metadata and controls

35 lines (25 loc) · 2.39 KB

What The Hack - Incremental Synapse Pipelines
Utilizing Change Data Capture in Azure SQL

Introduction

If you accept the challenge to attend, you and your teammates will work together to help take data from a transactional Azure SQL database and copy it into a Synapse Dedicated Pool to be used as a reporting warehouse.

But wait there’s more! As you add data to your transactional database, it needs to automatically make it to the proper tables in the reporting warehouse so we can see the changes in near real time in a report.

Four words… Trigger an Incremental Copy.

Learning Objectives

Upon completion of this Hack, you will be able to setup an Azure environment to incrementally copy data from an Azure SQL Database to a Synapse Dedicated Pool utilizing the Change Data Capture functionality within Azure SQL.

You will also have utilizized how to implement a watermark table to validate the last copied execution in case of a trigger failure.

From a SQL Dedicated Pool perspective, you will gain hands-on experience with regards to the use of Staging Tables and how to copy those to production tables and use the proper distribution model.

Challenges

Prerequisites

  • One Azure Subscription per group. One person must be the owner and the others the contributors.
  • Tool(s) to interact with Azure SQL and a Synapse Dedicated Pool. Plenty of options here.
  • Power BI Desktop

Contributors

Jack Bender
Chris Fleming