azuretriggersazure-data-factory

Azure Data Factory - Suggested way for ADF to trigger pipeline by SQL table change

I have a tracking SQL table which has following schema:

CREATE TABLE [dbo].[TEST_TABLE](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [value] [nvarchar](50) NULL,
    [status] [nvarchar](50) NULL,
    [source] [nvarchar](50) NULL,
    [timestamp] [datetime] NULL
)

My application code will automatically maintain the table by inserting record and updating the field status.

My target is to trigger an ADF pipeline based on the result of following query:

SELECT COUNT(1) AS cnt FROM [dbo].[TEST_TABLE] WHERE [status] = 'active'

If the result is >0, then trigger an ADF pipeline.

Current status:

My current work:

  1. set up an Stored procedure SP_TEST to return 1 if condition is filled; otherwise 0
  2. setup an pipeline like below: enter image description here

the result of SP is parsed and used for routing to trigger later stages (which will mark the SQL table status to 'inactive' to avoid duplicate processing) 3. associate the pipeline with a scheduling trigger every 5 minutes.

My current work is "working", in the sense that it can detect for whether there is DB change every 5 minutes and execute subsequent processing.

Problem:

However, the scheuling trigger may be too frequent and cost activity run unit on every execution, which could be costly. Is there any trigger like "SQL table change trigger"?

what I have tried:

Appreciate any insights/directions in advance.

Solution

  • Polling using ADF can be expensive, we want to avoid that. Instead have the polling take place within an Azure Logic App, it's much cheaper. Here are the steps to listen to a SQL Server DB (Azure included) then trigger an ADF pipeline if a table change is found.

    Azure Logic App Design

    Here is the pricing for Azure Logic App:

    Azure Logic App Pricing

    I believe this means that every trigger is using a standard connector, so it will be 12.5 cents (USD) per 1000 firings of the app, and 2.5 cents (USD) per 1000 actions triggered.

    For ADF it is $1 (USD) per 1000 activities, so it's much more expensive for ADF

    Please let me know if you have any issues at all!