How to create a load audit table in a Data Warehouse

This tutorial explains how to create a load audit table in a Data Warehouse to record ETL runs, row counts, durations and errors — useful to track load history, diagnose failures and ensure data quality.

Prerequisites

• Basic knowledge of SQL (SELECT, INSERT, UPDATE).
• Data Warehouse environment with SQL support (e.g., SQL Server, PostgreSQL, Azure Synapse).
• Permissions to create tables and run jobs/ETL.

Step 1: Define the purpose and columns of the audit table

Decide what information is needed: load identifier, source, target, timestamps, counts, status and error message. This helps answer "when" and "why" a load failed.

-- Exemplo de colunas essenciais para auditoria de cargas
-- job_id: identificador da execução
-- process_name: nome do processo ETL
-- source_system: origem dos dados
-- target_table: tabela destino
-- start_time / end_time: tempos
-- rows_loaded: número de linhas carregadas
-- status: SUCCESS / FAILED
-- message: detalhes do erro

Step 2: Create the audit table

Create the table in an appropriate schema (e.g., dbo, staging). The SQL example below is compatible with most SQL systems; adjust types if needed.

CREATE TABLE dbo.etl_audit (
  audit_id BIGINT IDENTITY(1,1) PRIMARY KEY,
  job_id NVARCHAR(100) NOT NULL,
  process_name NVARCHAR(200) NOT NULL,
  source_system NVARCHAR(100),
  target_table NVARCHAR(200),
  start_time DATETIME2 NOT NULL,
  end_time DATETIME2 NULL,
  rows_expected BIGINT NULL,
  rows_loaded BIGINT NULL,
  status NVARCHAR(20) NOT NULL,
  message NVARCHAR(MAX) NULL,
  created_at DATETIME2 DEFAULT SYSUTCDATETIME()
);

Step 3: Instrument the ETL process to record start and end

Add inserts/updates at the beginning and end of the ETL job. At the start insert a row with status 'RUNNING'; at the end update to 'SUCCESS' or 'FAILED' with counts and message.

-- Inserir a execução ao iniciar
DECLARE @job_id NVARCHAR(100) = 'job_20260914_01';
DECLARE @process NVARCHAR(200) = 'Carga_Clientes';
DECLARE @start DATETIME2 = SYSUTCDATETIME();

INSERT INTO dbo.etl_audit (job_id, process_name, source_system, target_table, start_time, status)
VALUES (@job_id, @process, 'CRM', 'dw.dim_customers', @start, 'RUNNING');

-- Após a carga (exemplo de sucesso): actualizar com contagens e estado
DECLARE @end DATETIME2 = SYSUTCDATETIME();
DECLARE @rows BIGINT = 1250;

UPDATE dbo.etl_audit
SET end_time = @end,
    rows_loaded = @rows,
    status = 'SUCCESS',
    message = NULL
WHERE job_id = @job_id AND status = 'RUNNING';

-- Em caso de erro, registar falha
UPDATE dbo.etl_audit
SET end_time = SYSUTCDATETIME(),
    status = 'FAILED',
    message = 'Erro: ligação ao source falhou.'
WHERE job_id = @job_id AND status = 'RUNNING';

Step 4: Record validations and discrepancies (expected counts)

If the ETL has expectations (for example, a file with a header that indicates rows), record rows_expected and compare. This practice helps identify incomplete or duplicate loads.

-- Exemplo de comparação simples durante o ETL
DECLARE @expected BIGINT = 1300;
DECLARE @loaded BIGINT = 1250;

IF @loaded < @expected
BEGIN
  UPDATE dbo.etl_audit
  SET rows_expected = @expected,
      rows_loaded = @loaded,
      status = 'FAILED',
      message = 'Contagem menor que a esperada'
  WHERE job_id = @job_id;
END
ELSE
BEGIN
  UPDATE dbo.etl_audit
  SET rows_expected = @expected,
      rows_loaded = @loaded,
      status = 'SUCCESS'
  WHERE job_id = @job_id;
END

Step 5: Useful queries and alerts

Create queries to monitor recent loads, failures and long runtimes. These queries feed dashboards or alerts via e-mail/Teams.

-- Cargas falhadas nas últimas 24 horas
SELECT *
FROM dbo.etl_audit
WHERE status = 'FAILED' AND start_time > DATEADD(day,-1,SYSUTCDATETIME())
ORDER BY start_time DESC;

-- Duração média por processo
SELECT process_name, AVG(DATEDIFF(second,start_time,end_time)) AS avg_seconds
FROM dbo.etl_audit
WHERE status = 'SUCCESS' AND end_time IS NOT NULL
GROUP BY process_name;

Verify the result

Verify that audit rows appear when starting and updating the job. Test success and failure cases: entries should change from 'RUNNING' to 'SUCCESS' or 'FAILED' and include counts and messages. Use the queries above to confirm alerts and metrics.

Conclusion

A simple load audit table lets you track ETL, diagnose errors and measure performance. Next steps: integrate with monitoring tools, store file signatures and record hashes for duplicate detection. Tip: start small and add fields as needed for failure investigation.