Create a Stored Procedure for Logging in the Warehouse

Let's create a Stored Procedure that will define what log details to capture and specify the table where these details will be stored.

Open the warehouse you created, and from the top menu, select New SQL query.
Execute the following SQL code to create the Stored Procedure and verify its creation from the Explorer tab.

(Take a moment to go through the SQL code below. The comments are provided to guide you through each part for better understanding.)

CREATE PROCEDURE LogPipelineRun
    @PipelineName     VARCHAR(100),  -- Name of the pipeline being logged
    @RunId            VARCHAR(100),  -- Unique identifier for the pipeline run
    @ExecutionStatus  VARCHAR(50)    -- Status of the pipeline execution 
AS
BEGIN
    -- Check if the PipelineLogs table exists, create it if it doesn't
    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'PipelineLogs')
    BEGIN
        CREATE TABLE PipelineLogs (
            PipelineName     VARCHAR(100),  -- Name of the pipeline
            RunId            VARCHAR(100),  -- Identifier for the pipeline run
            ExecutionStatus  VARCHAR(50)    -- Status of the pipeline execution
        );
    END;

    -- Insert the pipeline run details into the PipelineLogs table
    INSERT INTO PipelineLogs (PipelineName, RunId, ExecutionStatus)
    VALUES (@PipelineName, @RunId, @ExecutionStatus);
END;

Code Explanation: This Stored Procedure first checks if the PipelineLogs table exists and creates it if it doesn’t. It then inserts the pipeline run details into this table. The log details are set as parameters within the Stored Procedure, and we'll pass these parameter values from the pipeline in the upcoming steps.

Exploring Data Pipeline Activities

Data Ingestion and Semantic Models with Microsoft Fabric

Praktische interactieve oefening