Trying to come up with architecture of creating SQL data validation (table counts) email

Question

I am trying to come up with an architecture of some type of notification of Azure SQL data inserts on a daily basis, but I am not sure which architecture would be best.

So, what I am trying to do is:

1. Run several Stored Procedures (trying to get row counts of last two days of certain tables (at least 20)
2. Send all results in one email.

I have done creating email notification using Azure Data Factory and Azure Logic App like below:

Below is inside ADF (Azure Data Factory), but the outcome is triggered with Azure Logic App.

I guess my challenge is, how to put multiple results into one email (and as each query would take some time)?

Should I insert outcome of results into one SQL table, and just send out results all at once?

Answer 1

King Java

Your approach is correct. To summarize, please follow the steps below:

Store Results in a Table

• Create a table (DailyTableCounts) to store row counts from the last two days.
  • Each stored procedure will insert results into this table with columns like TableName, DateChecked, and RowCount.

Run Stored Procedures in Parallel

• Use Azure Data Factory (ADF) to execute multiple stored procedures in parallel to improve performance.
• Each procedure will fetch row counts and insert them into DailyTableCounts.

Aggregate Results

• After all procedures run, a final stored procedure will fetch all results from DailyTableCounts and format them into a structured output.

Send a Single Email

• Use Azure Logic Apps to pull the aggregated results and send a single email with a formatted table of all counts.

By following these steps, you can ensure that all the results are consolidated into one email, making it easier to monitor the data inserts.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.