{"id":1019,"date":"2021-01-25T02:16:18","date_gmt":"2021-01-25T02:16:18","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=1019"},"modified":"2021-02-05T14:35:32","modified_gmt":"2021-02-05T14:35:32","slug":"streaming-etl-with-azure-data-factory-and-cdc-setting-up-audit-tables","status":"publish","type":"post","link":"https:\/\/jackofalltradesmasterofsome.com\/blog\/2021\/01\/25\/streaming-etl-with-azure-data-factory-and-cdc-setting-up-audit-tables\/","title":{"rendered":"Streaming ETL with Azure Data Factory and CDC – Setting up Audit Tables"},"content":{"rendered":"\n

In this series we look at building a Streaming ETL with Azure Data Factory and CDC – Setting up Audit Tables. 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

    Setting up Audit Tables<\/p>\n\n\n\n

    1. Audit tables will be required to track what has\nbeen loaded and sent to the streaming process of the ETL. The second step will\nseed this table with the first row of data.<\/li><\/ol>\n\n\n\n

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

      — Create Separate Offset Table to Manage\nLast Position\/Row Sent to Azure<\/p>\n\n\n\n

      CREATE TABLE [dbo].[Audit_Streaming_ETL]<\/p>\n\n\n\n

      (<\/p>\n\n\n\n

       [TableName] [varchar](50) NOT NULL,<\/p>\n\n\n\n

       [MaxVal] [binary](10) NOT NULL,<\/p>\n\n\n\n

       [LastUpdateDateTime] [datetime] NOT NULL\nDEFAULT getdate(),<\/p>\n\n\n\n

       [LastCheckedDateTime] [datetime] NOT NULL\nDEFAULT getdate(),<\/p>\n\n\n\n

       CONSTRAINT [PK_Audit_Streaming_ETL] PRIMARY\nKEY NONCLUSTERED <\/p>\n\n\n\n

       (<\/p>\n\n\n\n

       [TableName] ASC<\/p>\n\n\n\n

       ) <\/p>\n\n\n\n

      ) <\/p>\n\n\n\n

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

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

      SELECT ”, 0x0000000000000000000, ‘1900-01-01 00:00:00’, ‘1900-01-01 00:00:00’<\/p>\n\n\n\n

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

      In this series we look at building a Streaming ETL with Azure Data Factory and CDC – Setting up Audit Tables. 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 […]<\/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-1019","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":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1019","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=1019"}],"version-history":[{"count":2,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1019\/revisions"}],"predecessor-version":[{"id":1084,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1019\/revisions\/1084"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/822"}],"wp:attachment":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=1019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=1019"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=1019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}