Free SQL Help for Devs, DBAs, and the Curious

ETL (Extract, Transform, Load) processes form the backbone of any data pipeline in business intelligence and data warehousing scenarios. As these processes grow in complexity, there is an increasing need for robust logging mechanisms that keep track of all activities. This blog post will address the design of an ETL logging schema for multi-pipeline platforms using SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL.

For an ETL logging schema, the essential elements to capture include the pipeline name, the start and end time of each process, the number of rows affected, and any errors encountered. It is also beneficial to log the metadata of the source and target databases, the transformation logic applied, and the user who initiated the process. This kind of granular logging allows for comprehensive auditing and troubleshooting.

Let's start with a basic logging table structure in SQL Server. We create a table named 'ETL_Log' with columns corresponding to the crucial elements of each ETL process. This structure can be replicated across different SQL platforms with minor adjustments.

CREATE TABLE ETL_Log (
  LogID INT IDENTITY(1,1) PRIMARY KEY,
  PipelineName NVARCHAR(100),
  StartTime DATETIME,
  EndTime DATETIME,
  RowsAffected INT,
  ErrorDetails NVARCHAR(MAX),
  SourceMetadata NVARCHAR(MAX),
  TargetMetadata NVARCHAR(MAX),
  TransformationLogic NVARCHAR(MAX),
  InitiatingUser NVARCHAR(100)
);

BEGIN TRY
  DECLARE @StartTime DATETIME, @EndTime DATETIME, @RowsAffected INT;
  SET @StartTime = GETDATE();

  BULK INSERT MyTable
  FROM 'C:\MyCSVFile.csv'
  WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

  SET @EndTime = GETDATE();
  SET @RowsAffected = @@ROWCOUNT;

  INSERT INTO ETL_Log (PipelineName, StartTime, EndTime, RowsAffected, InitiatingUser)
  VALUES ('CSV to MyTable', @StartTime, @EndTime, @RowsAffected, SYSTEM_USER);
END TRY
BEGIN CATCH
  INSERT INTO ETL_Log (PipelineName, StartTime, EndTime, ErrorDetails, InitiatingUser)
  VALUES ('CSV to MyTable', @StartTime, @EndTime, ERROR_MESSAGE(), SYSTEM_USER);
END CATCH

Email

Email

Your Comments

Please enter the code from the image above in the box below.

• Search Here:

   

• Categories

  • SQL Tips (129)
  • SQL Server (126)
  • DB2 (72)
  • MySQL (71)
  • Data Types (8)
  • Azure (8)
  • T-SQL (8)
  • Security & Permissions (7)
  • Backup & Restore (5)
  • Fabric (4)

• Tags

  • SQL Server (2)
  • T-SQL (2)
  • Performance Tuning (2)
  • SSRS (1)
  • Delta Lake (1)
  • OpenAI (1)