{"id":1014,"date":"2021-01-25T02:09:32","date_gmt":"2021-01-25T02:09:32","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=1014"},"modified":"2021-02-05T14:37:44","modified_gmt":"2021-02-05T14:37:44","slug":"streaming-etl-with-azure-data-factory-and-cdc-enabling-cdc","status":"publish","type":"post","link":"http:\/\/jackofalltradesmasterofsome.com\/blog\/2021\/01\/25\/streaming-etl-with-azure-data-factory-and-cdc-enabling-cdc\/","title":{"rendered":"Streaming ETL with Azure Data Factory and CDC – Enabling CDC"},"content":{"rendered":"\n

In this series we look at building a Streaming ETL with Azure Data Factory and CDC – Enabling CDC. This is Part 1, The rest of the series is below.<\/p>\n\n\n\n

  1. Enabling CDC<\/a><\/li>
  2. Setting up Audit Tables<\/a><\/li>
  3. Provisioning Azure Data Factory<\/a><\/li>
  4. Provisioning Azure Blog Storage<\/a><\/li>
  5. Create Data Source Connection in ADF<\/a><\/li>
  6. Create Incremental Pipeline in ADF<\/a><\/li>
  7. Create a Parameter Driven Pipeline<\/a><\/li>
  8. Create a Rolling Trigger<\/a><\/li><\/ol>\n\n\n\n

    This series uses the Adventureworks database. For more information on how to get that set up see my Youtube video for Downloading <\/a>and Restoring<\/a> the database. <\/p>\n\n\n\n

    Enabling CDC on SQL Server<\/p>\n\n\n\n

    1. First, enable Change Data Capture at the\nDatabase and Table Level using the following scripts. More information is\navailable on the Microsoft Site. If you are not using SQL Server or a tool that\nhas organic CDC build it, a similar process can be hand built a read only view\nby leveraging timestamps on last created date and last updated date.<\/li><\/ol>\n\n\n\n

      \/********Code**********\/<\/p>\n\n\n\n

      sys.sp_cdc_enable_db<\/p>\n\n\n\n

      EXECUTE sys.sp_cdc_enable_table <\/p>\n\n\n\n

         \n@source_schema = N’dbo’<\/p>\n\n\n\n

        ,\n@source_name = N’DimProduct’ <\/p>\n\n\n\n

        ,\n@role_name = N’cdc_Admin’; <\/p>\n\n\n\n

      GO<\/p>\n\n\n\n

      • This step will enable CDC on the database as well as add it to the table \u201cDimProduct\u201d. SQL Agent must be running as two jobs are created during this process, one to load the table and one to clean it out. This is a new process that will consume resources to be sure to do your research before turning this on for all tables. <\/li><\/ul>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        • Once the CDC is enabled, SQL Server will\nautomatically create the following tables \u201cschema_tablename_CT\u201d in the System\nfolder section. This table will now automatically track all data changes that\noccur on that table. You can reference the _$operationcode to determine what\nchange occurred with the legend below. If you wish to capture changes to only\ncertain fields, see the Microsoft documentation on CDC to see how that can be\nset. If you are handwriting your SQL, this can also be programmed in when\nbuilding your staging query.<\/li><\/ul>\n\n\n\n

          1 = delete<\/p>\n\n\n\n

          2 = insert<\/p>\n\n\n\n

          3 = update (captured column values are\nthose before the update operation). This value applies only when the row filter\noption \u2018all update old\u2019 is specified.<\/p>\n\n\n\n

          4 = update (captured column values are those after the update operation)<\/p>\n\n\n\n

          \"\"<\/figure>\n\n\n\n
          • Now once you add, edit or delete a record, you\nshould be able to find it in the new CDC table. Next, we will look at scanning\nthis table and turning the data to JSON to send to an Event Hub! Run the code\nbelow to insert and update into the DimProduct table.<\/li><\/ul>\n\n\n\n

            Insert<\/strong><\/p>\n\n\n\n

            INSERT INTO [dbo].[DimProduct]<\/p>\n\n\n\n

                       ([ProductAlternateKey]<\/p>\n\n\n\n

                       ,[ProductSubcategoryKey]<\/p>\n\n\n\n

                       ,[WeightUnitMeasureCode]<\/p>\n\n\n\n

                       ,[SizeUnitMeasureCode]<\/p>\n\n\n\n

                       ,[EnglishProductName]<\/p>\n\n\n\n

                       ,[SpanishProductName]<\/p>\n\n\n\n

                       ,[FrenchProductName]<\/p>\n\n\n\n

                       ,[StandardCost]<\/p>\n\n\n\n

                       ,[FinishedGoodsFlag]<\/p>\n\n\n\n

                       ,[Color]<\/p>\n\n\n\n

                       ,[SafetyStockLevel]<\/p>\n\n\n\n

                       ,[ReorderPoint]<\/p>\n\n\n\n

                       ,[ListPrice]<\/p>\n\n\n\n

                       ,[Size]<\/p>\n\n\n\n

                       ,[SizeRange]<\/p>\n\n\n\n

                       ,[Weight]<\/p>\n\n\n\n

                       ,[DaysToManufacture]<\/p>\n\n\n\n

                       ,[ProductLine]<\/p>\n\n\n\n

                       ,[DealerPrice]<\/p>\n\n\n\n

                       ,[Class]<\/p>\n\n\n\n

                       ,[Style]<\/p>\n\n\n\n

                       ,[ModelName]<\/p>\n\n\n\n

                       ,[LargePhoto]<\/p>\n\n\n\n

                       ,[EnglishDescription]<\/p>\n\n\n\n

                       ,[FrenchDescription]<\/p>\n\n\n\n

                       ,[ChineseDescription]<\/p>\n\n\n\n

                       ,[ArabicDescription]<\/p>\n\n\n\n

                       ,[HebrewDescription]<\/p>\n\n\n\n

                       ,[ThaiDescription]<\/p>\n\n\n\n

                       ,[GermanDescription]<\/p>\n\n\n\n

                       ,[JapaneseDescription]<\/p>\n\n\n\n

                       ,[TurkishDescription]<\/p>\n\n\n\n

                       ,[StartDate]<\/p>\n\n\n\n

                       ,[EndDate]<\/p>\n\n\n\n

                       ,[Status])<\/p>\n\n\n\n

                \nVALUES<\/p>\n\n\n\n

                       (‘VV-2903’<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,’Test Product’<\/p>\n\n\n\n

                       ,’Test Product’<\/p>\n\n\n\n

                       ,’Test Product’<\/p>\n\n\n\n

                       ,0<\/p>\n\n\n\n

                       ,0<\/p>\n\n\n\n

                       ,’Black’<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL<\/p>\n\n\n\n

                       ,NULL)<\/p>\n\n\n\n

            GO<\/p>\n\n\n\n

            Update<\/strong><\/p>\n\n\n\n

            UPDATE [dbo].[DimProduct]<\/p>\n\n\n\n

            SET\nFinishedGoodsFlag = 1<\/p>\n\n\n\n

            WHERE [ProductAlternateKey] = ‘VV-2903’<\/p>\n\n\n\n

            • Now you can query the table \u201ccdc.dbo_DimProduct_CT\u201d to see the changes that were recorded.<\/li><\/ul>\n\n\n\n
              \"\"<\/figure>\n\n\n\n

              Streaming ETL with Azure Data Factory and CDC – Enabling CDC<\/p>\n","protected":false},"excerpt":{"rendered":"

              In this series we look at building a Streaming ETL with Azure Data Factory and CDC – Enabling CDC. This is Part 1, The rest of the series is below. Enabling CDC Setting up Audit Tables Provisioning Azure Data Factory Provisioning Azure Blog Storage Create Data Source Connection in ADF Create Incremental Pipeline in ADF […]<\/p>\n","protected":false},"author":1,"featured_media":822,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[52,28,27,11,1],"tags":[104],"class_list":["post-1014","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-app-dev","category-azure","category-big-data","category-data-warehouse","category-uncategorized","tag-azure-data-factory"],"_links":{"self":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1014","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=1014"}],"version-history":[{"count":2,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1014\/revisions"}],"predecessor-version":[{"id":1077,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1014\/revisions\/1077"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/822"}],"wp:attachment":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=1014"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=1014"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=1014"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}