Every new load should not require inventing another logging table and retry rule. A simple ETL framework gives the next pipeline a dependable starting point.
Keep the ETL Framework Smaller Than the Work
A framework should solve repeated operational problems: which source to read, which procedure to call, where progress is stored, and who receives failure alerts. It should not become a language for describing every possible transformation. SQL Server tables and procedures can cover a small team’s needs without building a second programming platform.
I start by listing what every pipeline really shares. Pipeline name, enabled state, schedule reference, owner, last successful checkpoint, run status, and step messages usually make the list. Source-specific transformations stay in source-specific procedures. That separation keeps common code understandable.
What would a new DBA need to restart a failed load? If the answer requires reading a framework manual before looking at the error, the design is too clever. The framework should make the work visible, not hide it behind layers.
Put Stable ETL Framework Settings in Configuration
Store pipeline settings in a table with a clear key and data types. Examples include source identifier, target name, batch size, timeout, and alert owner. Do not store secrets in plain text. Use a supported secret store or credential mechanism, then put only a reference in configuration.
Give settings an effective state. An enabled flag and a last changed timestamp help distinguish an intentionally paused load from one that failed to run. Audit changes to settings that alter extraction boundaries or destinations. A config value can change business output as surely as a code change.
I prefer a few named columns over a universal key-value table for core settings. A universal table is flexible until a misspelled key quietly changes behavior. Use a JSON or key-value extension only for settings that truly vary by connector, with validation before execution.
CREATE TABLE dbo.EtlPipeline
(
PipelineId int IDENTITY(1,1) PRIMARY KEY,
PipelineName sysname NOT NULL UNIQUE,
IsEnabled bit NOT NULL DEFAULT 1,
OwnerEmail nvarchar(320) NOT NULL,
BatchSize int NOT NULL CHECK (BatchSize > 0),
LastChangedAtUtc datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);Log Runs and Steps
Each execution gets a RunId. Each step gets a row with start time, finish time, status, row counts, and error details. Use UTC consistently. Keep retries as separate attempts so the previous failure remains visible. A successful rerun does not erase the evidence that the scheduled run failed.
Capture counts immediately after the data operation. If a procedure performs an INSERT, then updates its log, @@ROWCOUNT now refers to the log update. Use variables to preserve counts. Record read, written, updated, and rejected counts separately when their meanings differ.
I use the run log as the first screen during an incident. SQL Agent history tells me which job failed, then the run log tells me which step and source boundary were involved. Without that boundary, a restart becomes an educated guess.
Checkpoint Only Committed Work
A checkpoint says what input is safe to skip on a retry. It can be a file ID, API page token, source timestamp, or Change Tracking version. Update it only after the corresponding target transaction commits. Otherwise a failed target write can be followed by a successful checkpoint and permanently missed rows.
Keep the planned upper boundary with the run. A retry should replay the same interval, not recalculate it while the source changes. Stage pages or files with durable receipts when source and target cannot share a transaction. Make target writes idempotent using keys and constraints.
I have seen pipelines use the current time as the next watermark at the end of a run. That looks convenient and can skip changes that arrived during extraction. The checkpoint must describe processed source data, not the wall clock at which someone closed a connection.
SELECT PipelineName, LastCommittedWatermark, UpdatedAtUtc
FROM dbo.EtlCheckpoint
WHERE PipelineName = N'CustomerLoad';Make Restarts Explicit
Define states such as Pending, Running, Failed, and Succeeded for each step. A restart procedure should find the last durable checkpoint and refuse to overlap an active run for the same pipeline. Use an application lock or another clear concurrency guard if multiple schedulers can start the same pipeline.
Do not assume a failed step did no work. It can fail after target rows commit but before the controller receives success. A unique source key and an idempotent apply rule let the retry run safely. Test that uncertain outcome, not only a failure before the first INSERT.
A manual reset should require a reason and leave an audit row. Moving a checkpoint backward can replay input. Moving it forward can lose input. Both deserve a controlled operation and a query that shows the expected rows before execution.
Alert With an Actionable Message
Send an alert when the pipeline fails, misses its expected schedule, or succeeds with a severe quality warning. Include pipeline name, RunId, failed step, source boundary, error text, and a path to the operator query. Avoid sending a page for every transient retry that resolves within the run.
Route alerts to an owner and a backup. A shared mailbox with no on-call rule is an archive, not an alert path. Keep the signal narrow enough that people keep reading it. I would rather receive one message that says which file failed than twelve messages saying “ETL error.”
The alert process can fail independently. Query for failed runs without a sent alert and for stale Running rows. That check closes the gap where the same failure breaks both the load and its notification.
SELECT r.RunId, p.PipelineName, r.RunStatus, r.StartedAtUtc,
r.ErrorMessage
FROM dbo.EtlRun AS r
JOIN dbo.EtlPipeline AS p ON p.PipelineId = r.PipelineId
WHERE r.RunStatus = 'Failed'
AND r.AlertSentAtUtc IS NULL
ORDER BY r.StartedAtUtc;Prove the ETL Framework With One Real Load
Use one representative pipeline to test the framework. Run it successfully, then force a source timeout, invalid row, target rollback, and process crash after commit. Confirm that each case produces a clear log entry and a safe restart. A framework diagram has not passed a test until a failure does.
Keep operational queries beside the procedures. Show the latest run, open checkpoints, rejected rows, and stale executions. Index the log for those queries and set a retention policy. A framework that cannot answer “what happened last night?” has missed its simplest requirement.
Add features only after a second pipeline demonstrates the need. A small ETL framework can grow, but complexity has a maintenance cost. The goal is for a load author to reuse dependable operations while keeping the actual data logic visible.
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 framework is not a programming language, it is a small set of reliable operating rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
