How to audit data loads in ELT: step by step

Recording who loaded what, when, and with what outcome is what separates a trustworthy ELT pipeline from a black box. Auditing data loads in ELT lets you investigate errors quickly, prove where every number came from, and answer an audit without guessing.

Prerequisites

• A SQL Server or Azure SQL database (the examples use T-SQL).
• An existing target table that you load data into.
• Permissions to create and alter tables (CREATE TABLE, ALTER TABLE).
• Basic SQL knowledge: INSERT, UPDATE and SELECT.

Step 1: Decide what to audit

Before writing any SQL, define the useful minimum. In an ELT load there are four questions that always matter: where the data came from, when it arrived, how many rows were processed, and whether the load finished successfully. We will answer all of them with two simple pieces: audit columns on each target table and a central log table that keeps the history of runs.

Keep the log table separate from your business data. That way you can archive or clean it without touching your business tables.

Step 2: Add audit columns to the target table

Audit columns travel with each row and tell you which load that row came from. Add a batch identifier, the load timestamp, and the source system.

ALTER TABLE vendas
    ADD batch_id       BIGINT,
        data_carga     DATETIME2,
        sistema_origem VARCHAR(50);

From now on, every loaded row is "stamped" and can be tied back to a specific run.

Step 3: Create the load log table

The log table is your pipeline's diary: one row per run. Create it only once.

CREATE TABLE etl_load_log (
    batch_id       BIGINT IDENTITY(1,1) PRIMARY KEY,
    tabela_destino VARCHAR(100),
    inicio         DATETIME2,
    fim            DATETIME2,
    linhas         INT,
    estado         VARCHAR(20)
);

Step 4: Record the start of the load

At the start of each load, we insert a row with the RUNNING status and keep the generated batch_id to reuse it in the next steps.

DECLARE @batch_id BIGINT;

INSERT INTO etl_load_log (tabela_destino, inicio, estado)
VALUES ('vendas', SYSDATETIME(), 'RUNNING');

SET @batch_id = SCOPE_IDENTITY();

Step 5: Load the data and stamp the rows

Now run your usual transformation and load, filling the audit columns with the current run's batch_id. Also store the number of inserted rows with @@ROWCOUNT.

DECLARE @linhas INT;

INSERT INTO vendas (id, valor, batch_id, data_carga, sistema_origem)
SELECT s.id, s.valor, @batch_id, SYSDATETIME(), 'loja_online'
FROM   staging_vendas AS s;

SET @linhas = @@ROWCOUNT;

Step 6: Close the record with the outcome

When the load finishes, we update the same log row with the end time, the number of affected rows, and the final status.

UPDATE etl_load_log
SET    fim    = SYSDATETIME(),
       linhas = @linhas,
       estado = 'SUCCESS'
WHERE  batch_id = @batch_id;

If any step fails, wrap the load in a TRY...CATCH block and write the FAILED status instead of SUCCESS. That way you tell the problem runs apart immediately.

Verify the result

Query the log table to see the history of recent loads. You should see one row per run, with start, end, row count, and the SUCCESS status.

SELECT batch_id, tabela_destino, inicio, fim, linhas, estado
FROM   etl_load_log
ORDER  BY batch_id DESC;

To confirm traceability, pick a batch_id and count the matching rows in the target table — the total should match the value stored in the log.

SELECT COUNT(*) FROM vendas WHERE batch_id = 1;

Conclusion

With audit columns and a log table you now know, for every row, where it came from and which load it entered in — the foundation for investigating errors and trusting the numbers you report. The natural next step is to build a small dashboard over etl_load_log showing failed loads and the average duration of each run. Which metric would be more useful to watch first in your pipeline: the loads that fail or the ones that take too long?