Maintain row count(insert update delete ) Audit mechnism using ADF in datawarehouse

Question

Hi,

I want to use audit mechanism like how many rows are in inserted/updated/deleted into dim and fact and also fetch source count in ADF.

Can anyone guide me in ADF is there any row count functionality available ? or please guide me for logic/approach for the same.

table structure like below approximately

Table name Source count Insert count Update count Delete count

Thanks in advance.

Answer 1

Hey @Girish13 ,
Can you please provide us the data loading architecture which you have?
Because there are multiple ways to achieve SCD 2:

1. ELT process

ADF would copy the data into a staging table in the data warehouse.
The SCD logic would be implemented within a stored procedure in AEDW.
In that scenario, you can create variable within the stored procedure for insert, update,delete counts and log it into the logging table.

The copy activity provides only the rows transferred count and not update/delete counts.

2) You can even achieve SCD via data flow tasks and get the needed count via activity output :
https://mssqldude.wordpress.com/2019/04/15/adf-slowly-changing-dimension-type-2-with-mapping-data-flows-complete/

Everything depends on the architecture which you currently have