Option 1: Create a Stored Procedure Activity

The Stored Procedure activity will be used to invoke a stored procedure in a Synapse Analytics dedicated SQL pool.

For this scenario, you might want to maintain your pipeline_status and pipeline_date details as columns in your adf_db.dbo.pipeline_parameter table rather than having a separate log table. The downside to this method is that it will not retain historical log data but will simply update the values in the original pipeline_parameter table based on a lookup of the incoming files to records in the pipeline_parameter table. This gives a quick, yet not necessarily robust, method of viewing the status and load date across all items in the pipeline parameter table. Specific use cases for this approach might be to log the max folder date based on the incoming folder and file names and timestamps.

Begin by adding a Stored Procedure activity to your preliminary Copy data activity, which is within the ForEach loop activity, shown in Figure 8-2, to ensure that the process iterates and logs each table using the stored procedure. Notice the green arrow and line connector that indicates the stored procedure must run on the success of the Copy data activity.

After creating your stored procedure, confirm that it has been created in your corresponding database. Notice the confirmation in Figure 8-3, which is a screen capture within SSMS.

Next, return to the Data Factory pipeline and configure the Stored Procedure activity. In the Stored Procedure tab, select the stored procedure that was just created. Also add a new stored procedure parameter that references the destination name, which was configured in the Copy activity, as illustrated in Figure 8-4.

After saving, publishing, and running the pipeline, notice that the pipeline_date and pipeline_status columns have been updated as a result of the ADF Stored Procedure activity, shown in the pipeline_parameter table view in Figure 8-5. This is meant to be a lightweight testing pattern, which provides you with details on the status and load date for each table within a centralized location. I’ve noticed that ADF does not always provide robust details related to the problematic tables or columns.

Option 2: Create a CSV Log File in Data Lake Storage Gen2

Since your Copy-Table activity is generating snappy parquet files into hierarchical ADLS Gen2 folders, you may also want to create a CSV file containing pipeline run details for every parquet file in your ADLS Gen2 account. For this scenario, you could set up a Data Factory Event Grid trigger to listen for metadata files and then trigger a process to transform the source table and load it into a curated zone to somewhat replicate a near-real-time ELT process.

Begin designing the ADF pipeline to create a CSV log file in your ADLS Gen2 account by adding a Copy activity for creating the log files and connecting it to the Copy-Table activity, shown in Figure 8-6. Similar to the previous process, which looped through each table, this process will generate a CSV extension metadata file in a metadata folder per table.

To configure the source dataset, select the source on-premises SQL Server. Next, add the following query shown in Figure 8-7 as the source query. Notice that this query will contain a combination of pipeline activities, Copy-Table activities, and user-defined parameters.

Recall the notes from previous chapters that call out the embedding of source queries as code into the ADF pipeline merely for visual demonstration purposes. It is always a better and more efficient process to add the source query as a stored procedure and then call the stored procedure. This will help with ease of maintenance of the code in a centralized location.

The sink will be a CSV dataset with a CSV extension, as shown in Figure 8-8.

Figure 8-9 shows the connection configuration to use for the CSV dataset.

Figure 8-10 shows this folder as you will see it in ADSL Gen2.

Open the metadata folder and notice that there are CSV folders that have been created per day that the pipeline runs, as shown in Figure 8-11.

Finally, notice in Figure 8-12 that a metadata .csv file with the name of the table has been created.

Download and open the file and notice that all of the query results have been populated in the .csv file, as shown in Figure 8-13.

Option 3: Create a Log Table in Azure SQL Database

The final scenario in this chapter involves creating a log table in your database where the parameter table resides and then writing the data as records into the table. For this option, start by adding a Copy data activity connected to the Copy-Table activity, shown in Figure 8-14.

Similar to the last pipeline option, configure the on-premises SQL Server as the source and use the query code provided in option 2 as the source query, shown in Figure 8-15.

The sink will be a connection to the SQL database pipeline log table that was created earlier.

After saving, publishing, and running the pipeline, notice that the pipeline Copy activity records have been captured in the dbo.pipeline_log table, as shown in Figure 8-16.

Summary

In this chapter, I demonstrated how to log ADF pipeline data using a custom approach of extracting metrics for each table or file from the ADF pipeline and then either writing the results to the pipeline_parameter table, pipeline_log table, or a CSV file in ADLS Gen2. This custom logging process can be added to multiple ADF pipeline activity steps for a robust and reusable logging and auditing process that can be easily queried in SSMS through custom SQL queries and even linked to Power BI dashboards and reports to promote robust reporting on logged data. In the next chapter, I will demonstrate how to log error details from your ADF pipeline into an Azure SQL table.