Boring Data Pipelines Are the Good Kind

In Rockets and Horses I said design decisions propagate. Nothing propagates like a data pipeline. Every dashboard, model and Monday meeting downstream inherits whatever you decided on the way in.

So I want pipelines to be boring. Nobody thinks about a boring pipeline, the same way nobody thinks about a style guide once a linter enforces it. Here's what boring takes.

The shape

Most of what I build looks like this. The tools change; the shape doesn't.

Sources flow into a loader, then raw tables, staging models, marts, and dashboards, with tests under each layer. — Raw stays exactly as it arrived. Everything after it is code in git, so any layer can be rebuilt from the one before.

1. Run it twice, get the same answer

If a job dies halfway and you rerun it, nothing should double. That's idempotency, and it's the rule I'd keep if I could only keep one, because every failure becomes "just run it again."

Appending the same batch twice doubles the rows. Merging on a key twice leaves the same rows. — Same batch, loaded twice.

In dbt that's an incremental model with a unique_key, so a rerun updates rows instead of adding them:

{{ config(
  materialized = 'incremental'
  , unique_key = 'order_id'
  , incremental_strategy = 'merge'
) }}

select
  order_id
  , customer_id
  , order_total
  , updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

2. Fail loud, and early

The worst pipeline isn't the one that breaks. It's the one that quietly loads wrong numbers that someone finds in a board deck three days later.

A bad row arrives Monday. With a test, the pipeline stops Monday morning. Without one, someone finds it Thursday. — A test is cheap. Three days of a wrong number isn't.

The tests that catch most of it are the boring ones:

models:
  - name: orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'returned']

Make the error say what broke and where. "Test failed" at 6 a.m. is a puzzle. "orders.order_id is not unique: 14 duplicates" is a fix.

3. Build for ten times today, not a thousand

Plan for the next order of magnitude. Past that you're building for a company you aren't yet, and paying for it in complexity every day until then.

On a log scale: today, ten times today, which is what to plan for, and a thousand times today, which isn't. — A warehouse plus a scheduler covers a lot more than people expect.

4. Never move in one big bang

When you replace a pipeline, run the old one and the new one side by side and compare them until the differences are zero, or explained. Then switch. The comparison is a query you can leave running:

select
  'only in old' as side
  , count(*) as row_count
from (
  select * from old_marts.orders
  except
  select * from new_marts.orders
)

union all

select
  'only in new' as side
  , count(*) as row_count
from (
  select * from new_marts.orders
  except
  select * from old_marts.orders
)

Two zeros and you can turn the old one off.

Boring is the goal

None of this is clever. That's the point. A pipeline that's safe to rerun, stops at the first bad row, fits the next couple of years and changed gradually is one nobody has to think about, which leaves time for the work people actually notice.