Lost in Data Caught in the Trace

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...

Change Data Capture in Delta Tables is a great way to look at changes. This post does not discuss the details of using this Change Feed but limits itself the how we will enable it. In an ideal world, we will be looking at setting up Change Data Capture as part of a planned deployment and we have a couple of options in that case. 1. All tables will capture Change Data Capture. In this scenario, if we want all tables to have Change Data Capture to be set we can enable this default.  NB: this applies only to the current session. To apply this across all sessions we must add this property to the Pool Configuration. spark.conf.set( "spark.microsoft.delta.properties.defaults.enableChangeDataFeed" , "true" ) In Synapse this can be specified in a configuration file. This file can be applied to any pool. Setting this in Fabric is at the Environment Level - note Environments are still in Preview.

  Data Flow Gen 2 Diagram View