{"id":1022,"date":"2021-01-27T01:23:51","date_gmt":"2021-01-27T01:23:51","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=1022"},"modified":"2023-02-28T14:52:27","modified_gmt":"2023-02-28T14:52:27","slug":"streaming-etl-with-azure-data-factory-and-incremental-pipeline-azure-data-factory","status":"publish","type":"post","link":"http:\/\/jackofalltradesmasterofsome.com\/blog\/2021\/01\/27\/streaming-etl-with-azure-data-factory-and-incremental-pipeline-azure-data-factory\/","title":{"rendered":"Streaming ETL with Azure Data Factory and CDC – Creating an Incremental Pipeline in Azure Data Factory"},"content":{"rendered":"\n

In this series we look at building a Streaming ETL with Azure Data Factory and CDC – Creating an Incremental Pipeline in Azure Data Factory. This is Part 6, 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

    Side Note: Want to learn SQL <\/a>or Python <\/a>for free. In less then 10 minutes a day and less than an hour total? Signup for my free classes delivered daily right to your email inbox for free! <\/em><\/p>\n\n\n\n

    \"\"<\/a><\/figure>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n

    Now back to the article…<\/p>\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

    Creating an Incremental Pipeline in Azure Data Factory<\/p>\n\n\n\n

    1. Create a new Dataset as a SQL Server and name it \u201cIncremental_Load\u201d<\/li><\/ol>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      • From the Activities, get the lookup and move it to the main window. This task will be used to track the new changes in the CDC table for a certain time frame. Name the Lookup \u201cGetChangeCount\u201d <\/li><\/ul>\n\n\n\n
        \"\"<\/figure>\n\n\n\n
        • In the properties, add the customer query and\nreplace with the correct table name. The query \u201cSELECT capture_instance FROM\ncdc.change_tables\u201d will give you the names of the CDC tables and can be tested\nin SQL Management Sudio <\/li><\/ul>\n\n\n\n

          DECLARE  @from_lsn\nbinary(10), @to_lsn binary(10);  <\/p>\n\n\n\n

          SET @from_lsn =sys.fn_cdc_get_min_lsn(‘dbo_DimProduct’);  <\/p>\n\n\n\n

          SET @to_lsn = sys.fn_cdc_map_time_to_lsn(‘largest less\nthan or equal’,  GETDATE());<\/p>\n\n\n\n

          SELECT count(1) changecount FROM\ncdc.fn_cdc_get_all_changes_dbo_DimProduct(@from_lsn, @to_lsn, ‘all’)<\/p>\n\n\n\n

          • Preview data will show the result of this query. This will show how many changes have been recorded in<\/li><\/ul>\n\n\n\n
            \"\"<\/figure>\n\n\n\n
            • In the activities, expand iteration and add the If condition to the flow.<\/li><\/ul>\n\n\n\n
              \"\"<\/figure>\n\n\n\n
              • Name it \u201cHasChangedRow\u201d and in the Properties window add the code \u201c@greater(int(activity(‘GetChangeCount’).output.firstRow.changecount),0)\u201d and select the Pencil next to the True Condition. <\/li><\/ul>\n\n\n\n
                \"\"<\/figure>\n\n\n\n
                • Add the Copy Activity from the Move & Transform Activity and name it \u201cCopy Incremental Data\u201d.<\/li><\/ul>\n\n\n\n
                  \"\"<\/figure>\n\n\n\n
                  • In the Source Tab, set the Source to your SQL Dataset and use the following query replacing the highlighted if needed. Select Preview to see the results.<\/li><\/ul>\n\n\n\n
                    \"\"<\/figure>\n\n\n\n

                    DECLARE @from_lsn binary(10),\n@to_lsn binary(10); <\/p>\n\n\n\n

                    SET @from_lsn\n=sys.fn_cdc_get_min_lsn(‘dbo_DimProduct’);\n<\/p>\n\n\n\n

                    SET @to_lsn = sys.fn_cdc_map_time_to_lsn(‘largest\nless than or equal’, GETDATE());<\/p>\n\n\n\n

                    SELECT * FROM\ncdc.fn_cdc_get_all_changes_dbo_DimProduct(@from_lsn, @to_lsn, ‘all’)<\/p>\n\n\n\n

                    • In the Sink tab, select your CSV Blob source. <\/li><\/ul>\n\n\n\n
                      \"\"<\/figure>\n\n\n\n
                      • <\/li><\/ul>\n\n\n\n
                        1. You can now debug the main pipeline and check in your storage account to see if data was move to capture the rows from CDC. <\/li><\/ol>\n\n\n\n
                          \"\"<\/figure>\n\n\n\n

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

                          In this series we look at building a Streaming ETL with Azure Data Factory and CDC – Creating an Incremental Pipeline in Azure Data Factory. This is Part 6, 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 […]<\/p>\n","protected":false},"author":1,"featured_media":822,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,28,27,11,1],"tags":[104],"class_list":["post-1022","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analytics","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\/1022","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=1022"}],"version-history":[{"count":3,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1022\/revisions"}],"predecessor-version":[{"id":1315,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1022\/revisions\/1315"}],"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=1022"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=1022"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=1022"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}