How to audit loads in a Data Warehouse: step by step

João Barros 11 de August de 2026 4 min read

This tutorial shows how to implement a load auditing table in a Data Warehouse to record successes, failures and times. Knowing what went well or wrong in the loads is useful for problem detection, re-execution and continuous improvement.

Prerequisites

Basic SQL knowledge (SELECT, INSERT, UPDATE).
A Data Warehouse environment with access to create tables and run jobs (for example SQL Server, Azure Synapse, PostgreSQL).
A simple ETL/ELT pipeline or load process that can invoke SQL statements before and after execution.

Step 1: Concept and essential columns

Before creating the table, define what we want to record: load identifier, start and end datetime, status (SUCCESS/ERROR), rows read/written counts, duration and error message. This enables analyses of failure frequency and average load times.

Step 2: Create the audit table

Create a simple table to store the records of each load execution. Keep enough fields for diagnosis and aggregation.

CREATE TABLE load_audit (
  load_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_name     VARCHAR(200) NOT NULL,
  start_time   TIMESTAMP NOT NULL,
  end_time     TIMESTAMP NULL,
  status       VARCHAR(20) NOT NULL,
  rows_read    BIGINT NULL,
  rows_written BIGINT NULL,
  error_msg    VARCHAR(2000) NULL
);

Step 3: Insert record at the start of the load

At the start of the ETL/ELT job insert a record with start_time and status = 'RUNNING'. This creates the reference to update at the end. In pipelines that support variables, store the returned load_id.

INSERT INTO load_audit (job_name, start_time, status)
VALUES ('daily_customer_load', CURRENT_TIMESTAMP, 'RUNNING')
RETURNING load_id;

Step 4: Update at the end on success

If the load finishes without errors, update the record with end_time, status = 'SUCCESS' and the counts. Use the load_id obtained in the previous step to point to the correct record.

UPDATE load_audit
SET end_time = CURRENT_TIMESTAMP,
    status = 'SUCCESS',
    rows_read = 12345,
    rows_written = 12000
WHERE load_id = 42;

Step 5: Record errors and failure messages

If a failure occurs, capture the error message and update the record with status = 'FAILED'. Include a reduced stack trace or error code for investigation.

UPDATE load_audit
SET end_time = CURRENT_TIMESTAMP,
    status = 'FAILED',
    error_msg = 'Timeout reading source: connection reset'
WHERE load_id = 42;

Step 6: Automate in pipelines (conceptual example)

Integrate the insert/update logic in your orchestrator (Azure Data Factory, Azure Synapse Pipelines, Airflow). Use transactions or try/catch blocks to ensure the record is updated even on error.

# Pseudocode conceptual
load_id = INSERT ... RETURNING load_id
try:
  run_etl_process()
  UPDATE load_audit SET ... WHERE load_id = load_id
except Exception as e:
  UPDATE load_audit SET status='FAILED', error_msg=substr(e.message,1,2000) WHERE load_id = load_id
  raise

Step 7: Metrics and reports

With the table in use, create queries for metrics such as average duration, success rate and top errors. These reports help prioritize fixes and monitor SLAs.

-- Average duration of loads per job
SELECT job_name,
       AVG(EXTRACT(EPOCH FROM (end_time - start_time))) AS avg_seconds,
       SUM(CASE WHEN status='FAILED' THEN 1 ELSE 0 END) AS failures
FROM load_audit
GROUP BY job_name;

Verify the result

Confirm that for each job execution there is a record in the load_audit table with start_time and end_time populated and an appropriate status. Verify that failures appear with error_msg and that metric queries return consistent values.

Conclusion

Implementing load auditing in a Data Warehouse is a simple step with great returns in diagnosis and reliability. Next step: add automatic alerts for recurring failures or times above SLA. Tip: also record the job version or code commit to ease root cause identification.