Build the Next Layer, Not the Whole Thing

Shannon Lowder

Shannon Lowder

— 2 min read

There is a version of this story in every client engagement I have been on. The first pipeline works, so someone immediately wants to add retry logic, parameterization, alerting, data quality checks, and a metadata-driven configuration framework — all at once, in the same sprint.

The result is always the same. The thing that worked stops working, nobody is sure why, and the scope of the problem is now twice as large as when you started.

Here is the discipline that prevents it: build the next layer, not the whole thing.

What You Already Have Is Enough to Start

Pick up from the last post. You have a coordinator procedure, RunOrderBatch, calling three focused units: extract, validate, transform. It runs. You have tested it. It does what it says.

That is your baseline. It is worth more than any half-finished abstraction you could add to it today.

Adding parameterization and error handling at the same time means you will not know which change broke something when something breaks. And something always breaks.

Add One Layer: Parameterization

Make your coordinator data-driven — one bounded change:

CREATE TABLE Orchestration.BatchConfig (
    BatchId       INT IDENTITY PRIMARY KEY,
    BatchDate     DATE         NOT NULL,
    PipelineName  VARCHAR(100) NOT NULL,
    Status        VARCHAR(20)  NOT NULL DEFAULT 'pending',
    CreatedAt     DATETIME     NOT NULL DEFAULT GETUTCDATE(),
    StartedAt     DATETIME     NULL,
    CompletedAt   DATETIME     NULL
);

Now your top-level coordinator reads from this table instead of taking a hard-coded date:

CREATE PROCEDURE dbo.RunNextPendingBatch
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @BatchId INT, @BatchDate DATE;

    SELECT TOP 1 @BatchId = BatchId, @BatchDate = BatchDate
    FROM Orchestration.BatchConfig
    WHERE Status = 'pending' ORDER BY BatchDate ASC;

    IF @BatchId IS NULL BEGIN PRINT 'No pending batches.'; RETURN; END;

    UPDATE Orchestration.BatchConfig
    SET Status = 'running', StartedAt = GETUTCDATE()
    WHERE BatchId = @BatchId;

    EXEC dbo.RunOrderBatch @BatchDate = @BatchDate;

    UPDATE Orchestration.BatchConfig
    SET Status = 'complete', CompletedAt = GETUTCDATE()
    WHERE BatchId = @BatchId;
END;

That is one new layer: a coordinator above your coordinator. RunNextPendingBatch knows about batch scheduling. RunOrderBatch knows about orders. Separation intact.

Simple, Right?

Before you add error handling, sit with what you just got for free: you can queue multiple batch dates by inserting rows into BatchConfig, inspect run status, and see exactly when each batch started and finished. None of that was in the original pipeline. It emerged from adding one clean layer on top of what already worked.

Each layer gives you more than the capability itself — observability, control surface, and a place to attach the next layer.

Add Error Handling — As Its Own Layer

With parameterization stable, wrap execution in TRY/CATCH. The constraint: RunOrderBatch and the three units below it do not change.

    BEGIN TRY
        EXEC dbo.RunOrderBatch @BatchDate = @BatchDate;
        UPDATE Orchestration.BatchConfig
        SET Status = 'complete', CompletedAt = GETUTCDATE()
        WHERE BatchId = @BatchId;
    END TRY
    BEGIN CATCH
        UPDATE Orchestration.BatchConfig
        SET Status = 'failed', CompletedAt = GETUTCDATE()
        WHERE BatchId = @BatchId;
        RAISERROR(ERROR_MESSAGE(), 16, 1);
    END CATCH;

You added capability above the existing system without touching the existing system. That is the discipline.

The Gotcha: Layers That Leak

The failure mode is smuggling coupling back in. If RunNextPendingBatch sets a flag that ExtractRawOrders reads to decide whether to skip records, the extract layer now knows about scheduling logic it has no business knowing. You can no longer test it independently.

Keep information flowing one direction: down. Coordinators pass parameters to units. Units do not read state set by coordinators. If a unit needs to make a routing decision, that is the coordinator's job — not the unit's.

What Is Next

The next post flips direction entirely. Instead of starting from what works and building up, we start from what you want the system to do and decompose it down — epics to stories to tasks, applied to a data pipeline project. Same principle, opposite entry point. As always, I am here to help.

Read more