Lost in Data Caught in the Trace

  This blog will look the capabilities and configuration of the Power Apps Azure Synapse Link for Dataverse. It will ·         Review each of the destinations ·         Explore the resulting data structure and supporting architecture. ·         Consider the options available for specifying the data structure. ·         Look at any issues or considerations which should be addressed during implementation. This is not a ‘How To’ document and will not describe the steps required to implement the solution unless it is pertinent to the explanation of a feature, issue or consideration that is being discussed. For How-to information, I’m including these links. ·         Azure Data Lake Storage Gen2 See details here . ·         Azure Synapse Workspace. See details here...

What is Meant by CDC? Change Data Capture ( CDC ) is a mechanism to record the effect of a transaction on a table within a database or lake house. It records the details of insertions, updates, and deletions to data. It is separate from the table data and has its own lifecycle. It is expected to contain data and metadata for these actions. CDC is available for SQL Server and Azure SQL. It is also available for Delta Tables within Lake Houses. There are differences between these two versions of CDC. We will look at the SQL CDC and what, if any comparison feature exists for Delta Tables. Compare CDC Feature SQL Delta Enable for the database This permits the CDC to be enabled on a database table. It does not enable this for any table. It can be set to default for all tables in a database as they are created either during a session or in a Spark Pool’s Configuration. Enable for the table Req...

Previously  I identified the destination options for Dataflow as having only insert capability with either Append or Replace and no updates. As such, I started thinking about the nature of the ingestion and how it restricts data loading into more complex processes. However, could we use this limitation to our advantage and leverage it to process limited sets of data. In this blog we will explore a pattern of ingestion: Load Raw Data  Capture the CDC of this Data Stream the CDC into a table Apply further transformations  We will review the process to answer: How does CDC perform on tables where data is append? What happens to CDC when data is replaced? How can we stream the data into our transform stage? Approach First off let's look at enabling CDC on the tables. I did not set my Lake House to enable by default so needed to do this per table.  I have previously posted on how this can be achieved . I set up a power query in Dataflow Gen 2 which will load data from [Ad...