Your Pipeline Is Already a System

Shannon Lowder

Shannon Lowder

— 3 min read

A client called me in to "fix a broken SSIS package." One package. Singular. I opened it and found something that had been growing for three years: extraction, validation, transformation, error logging, archival, and a handful of hard-coded business rules buried in a Script Task someone had labeled DO NOT TOUCH. Seven hundred lines of XML, one Execute SQL Task feeding another, a control flow that looked like someone had played Minesweeper on graph paper and lost.

When it failed — and it was failing — nobody could tell which part failed first. The logs said the package errored. That was it. The actual root cause was somewhere inside the organism.

That package was not a tool. It was a system pretending to be one thing.

You Are Already Building Systems

Here is what most data folks do not say out loud: your pipeline is already a system. Even if it looks like a single stored procedure or a single SSIS package, it has stages, dependencies, and state. The question is not whether it is a system — it is whether you have acknowledged that and structured it accordingly.

When you do not, you get the seven-hundred-line organism. When that fails, you spend a day reading logs instead of fixing the actual problem.

There is a better way. It is not complicated. But it requires accepting one rule upfront: each unit does one thing, and each layer coordinates units.

Start With the Smallest Reliable Piece

In SQL Server 2012, that usually means a stored procedure with exactly one job:

-- Extracts raw orders from the source system into staging
CREATE PROCEDURE dbo.ExtractRawOrders
    @BatchDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Staging.RawOrders
        (OrderId, CustomerId, OrderDate, TotalAmount, ExtractedAt)
    SELECT
        o.OrderId, o.CustomerId, o.OrderDate, o.TotalAmount, GETUTCDATE()
    FROM SourceSystem.dbo.Orders o
    WHERE o.OrderDate = @BatchDate
      AND o.IsDeleted = 0;
END;

That is it. No transformation. No validation. No logging framework bolted on the side. Extract raw orders for a given date — done. Boring? Sure. But boring is what works at 3 a.m. when something breaks and you need to know exactly which piece failed.

The Naive Version and Why It Breaks

A single procedure that extracts, validates, transforms, and loads will run fine until the day the source system changes a column type. Now you have an error buried somewhere in three hundred lines of logic, with a stack trace pointing at the wrong place.

You cannot test pieces of it. You cannot run just the extraction step to verify it is pulling the right rows. You run the whole thing every time and hope the failure is reproducible. And you cannot reuse any of it — if two downstream consumers both need raw orders extracted, you are copying logic or coupling them at the implementation level.

Build the Layer on Top

Once you have a reliable extract procedure, you build a coordinator:

-- Coordinates the full order batch for a given date
CREATE PROCEDURE dbo.RunOrderBatch
    @BatchDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    EXEC dbo.ExtractRawOrders       @BatchDate = @BatchDate;
    EXEC dbo.ValidateRawOrders      @BatchDate = @BatchDate;
    EXEC dbo.TransformOrdersToFact  @BatchDate = @BatchDate;
END;

Now when RunOrderBatch fails, the error points at exactly which step failed. You can call ExtractRawOrders in isolation to verify what is coming in. You can swap out TransformOrdersToFact without touching extraction. You can reuse ValidateRawOrders in a completely different pipeline.

That is what a layer is: a coordinator that calls reliable pieces, does not know their internals, and fails clearly when something breaks.

Variation Is Just Another Layer

The real payoff comes when requirements change — and they always change. Say the source system now has two order types: standard and expedited, with different validation rules for each.

With the monolith, you crack it open and add a conditional. Now one procedure is responsible for routing and extracting and validating. The failure modes multiply.

With the layered approach, you add ValidateExpeditedOrders and update the coordinator to route by type. Extraction does not change. Transformation does not change. You touched exactly the piece that needed to change. Every new requirement is another unit or another layer — not another hundred lines jammed into the middle of something that already works.

The Gotcha: Coupling at the Wrong Level

The failure mode of this approach is smuggling the monolith back in through the side door. If your extraction procedure starts making decisions that belong in the coordinator — skip validation for expedited orders — the extract layer now knows about business rules it has no business knowing.

Trust me on this one: keep the layers dumb about each other. The coordinator decides what runs and in what order. The units do their job without worrying about what comes next. If a unit needs to make a routing decision, that is a signal it is doing too much. Split it.

What Is Next

Next time I will walk through adding the next layer of capability on top of a working baseline — parameterization and error handling without cracking open the units that already work. Same principle, applied to a system already in production. If you have been structuring your pipelines this way and have a pattern that works, I would love to hear it. As always, I am here to help.

Read more