Logging ETL Runs in a Table

A job history row says the nightly load failed. Logging ETL runs in a table tells you which step failed, what it finished, and where to restart.

Make the Run the Unit of Work

Start with one row for the whole load. Give it a RunId, pipeline name, start time, final time, status, and an error summary. Each retry should have its own attempt ID or a clearly recorded attempt number. That makes a rerun visible instead of quietly replacing yesterday’s failure.

I look for the run identifier in every related table. It should connect stage rows, reject rows, file receipts, and step messages. Without that key, a DBA has to infer relationships from timestamps. That works until two runs overlap, which is exactly when the log matters most.

Store UTC timestamps when jobs cross servers or regions. Present local time in the report if readers prefer it. Mixing server local times in one log can put an end time before a start time. The database did not travel backward. The clock convention did.

Record Each Step Separately When Logging ETL Runs

A step row should say what operation ran, when it started, when it ended, its state, and how many rows it read, wrote, updated, deleted, or rejected. Avoid a single generic RowsProcessed number when those meanings differ. An extract and a target apply can both report a count without doing the same work.

Add a sequence or dependency field if steps can run in parallel. Two steps with overlapping times are not necessarily an error. A parent step ID can group parallel branches under a load phase. Keep the structure simple enough that operators can query it without a custom dashboard.

I have seen logs record only the last SQL statement’s @@ROWCOUNT. A later UPDATE to the log table then becomes the reported business count. Capture the count immediately after the operation, before running another statement. The log deserves the right number, even on a quiet Tuesday.

DECLARE @rows_inserted bigint;
INSERT dbo.StageCustomer (SourceKey, CustomerName)
SELECT SourceKey, CustomerName
FROM dbo.SourceCustomer;
SET @rows_inserted = @@ROWCOUNT;
SELECT @rows_inserted AS rows_inserted;

Create a Small Durable Schema

A run table and a step table cover most basic needs. Use identity keys or sequences for local IDs, and retain a source file or request identifier when one exists. Keep error text long enough to hold the useful message. Store structured error number and procedure name separately if the load uses T-SQL error handling.

Protect the log from casual updates. A failed attempt should remain failed after a successful retry. The successful attempt gets a new row. If you allow manual annotations, keep them separate from recorded facts so nobody edits history to make the dashboard green.

The table design should support one simple question: what happened last night? If the answer requires joining six tables and decoding a bit mask, the logging system has become another ETL project. Start small and add fields when an actual investigation needs them.

CREATE TABLE dbo.EtlRun
(
    RunId bigint IDENTITY(1,1) PRIMARY KEY,
    PipelineName sysname NOT NULL,
    StartedAtUtc datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    FinishedAtUtc datetime2(3) NULL,
    RunStatus varchar(20) NOT NULL,
    ErrorMessage nvarchar(4000) NULL
);
CREATE TABLE dbo.EtlStep
(
    StepId bigint IDENTITY(1,1) PRIMARY KEY,
    RunId bigint NOT NULL REFERENCES dbo.EtlRun(RunId),
    StepName sysname NOT NULL,
    StartedAtUtc datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    FinishedAtUtc datetime2(3) NULL,
    StepStatus varchar(20) NOT NULL,
    RowsRead bigint NULL,
    RowsWritten bigint NULL,
    RowsRejected bigint NULL,
    ErrorMessage nvarchar(4000) NULL
);

Finish Logging ETL Runs Even When Work Fails

Wrap each step with TRY and CATCH where the step runs in T-SQL. On success, update the step status and finish time. On error, capture ERROR_NUMBER, ERROR_MESSAGE, and the step name, then mark the run failed and rethrow. Do not swallow the error just to write a friendly log row. SQL Agent must still know the job failed.

Transactions complicate error logging. If a transaction is uncommittable, roll it back before trying to update the log. A log update inside a doomed transaction cannot survive. For work outside SQL Server, have the controller write the failure after it catches the exception.

What if the process dies without reaching a CATCH block? A watchdog query can find running rows with stale heartbeats and mark them for investigation. Do not label them failed automatically without checking whether a long step is still active. A stale state is evidence, not a verdict.

Use Counts as Reconciliation Clues

Rows read, staged, accepted, rejected, and applied should have understandable relationships. They do not always match. An update can touch a row already present, while a reject never reaches the target. Document those meanings so a report does not treat every difference as a failure.

Save control totals from the source when available. A file’s declared record count or an API’s total can be compared with accepted and rejected rows. Count distinct business keys when duplicates are possible. A simple count of stage rows can look fine while one customer appears twice and another is absent.

I check the log query before declaring a pipeline finished. A green status with an unexpected zero written count deserves a look. The status says the code ran. The counts tell you whether the run did useful work. Neither replaces a targeted data quality check.

SELECT r.RunId, r.PipelineName, r.RunStatus,
       s.StepName, s.StepStatus, s.RowsRead,
       s.RowsWritten, s.RowsRejected, s.ErrorMessage
FROM dbo.EtlRun AS r
JOIN dbo.EtlStep AS s ON s.RunId = r.RunId
WHERE r.StartedAtUtc >= DATEADD(day, -1, SYSUTCDATETIME())
ORDER BY r.RunId DESC, s.StepId;

Answer the Morning Questions

Build an operator query around a time range and pipeline name. Show the latest attempt, failed step, elapsed time, row counts, and error. Include successful runs with unusual counts, not just red status rows. A report that hides zero row loads can miss a silent source outage.

Retain detail long enough to compare normal runs with failures. Purge by a documented schedule, and archive longer history if trend analysis matters. Index RunId and the start time so the morning query does not scan years of log rows. Avoid logging every row as a separate message unless an audit rule demands it.

The log should point to the next action. A failed extract suggests checking the source. A rejected row count points to the reject table. A target timeout points to the transaction and blocking chain. A useful error message reduces the number of screens an operator opens.

Keep Logging ETL Runs Honest

Log writes can fail too. Decide whether the load should stop when its run row cannot be created. For most production pipelines, running invisibly is worse than failing early. Use a dedicated login with narrow rights to write logs, and alert when it cannot.

Test the log with a deliberate validation failure and a deliberate transaction rollback in a nonproduction environment. Confirm that the error survives and SQL Agent reports failure. Then rerun and confirm the earlier failure remains visible. I want that evidence before anyone calls the logging complete.

Keep the first design for logging ETL runs modest. A run table, a step table, and a clear query answer most questions. Add metrics because somebody uses them, not because the schema has room. The point is to make a bad night explainable by breakfast.

Related reading on this blog: Data Lineage Tracking in ETL Processes: Notes from the Field #124 and Incremental Loads: Moving Only What Changed.

An ETL log is not decoration for a job, it is the record that makes recovery possible.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.