From brittle SQL pipelines to a Snowflake foundation that ships features in days

Where they were

The data stack had grown the way most do: one engineer, then three, each adding what they needed. By the time we arrived there were around sixty Airflow DAGs, most of them wrapping raw SQL files, and nobody was sure which ones still mattered. Dashboards were right most of the time, which is the worst kind of wrong — nobody trusts a number, but nobody can say why.

The trigger was a product launch. Finance wanted a revenue model that could handle a new pricing tier, and the estimate to add it safely was six weeks. Six weeks for one column is not a data problem; it’s a foundation problem.

What was actually hard

• Nothing was tested. A failed join produced fewer rows, not an error. Problems showed up two days later in a board deck.
• Warehouse spend had no owner. Three warehouses running 24/7, one of them sized for a backfill that finished in 2024.
• Every model depended on every other model, but only in people’s heads. Changing one meant reading five.

What we did

We did not start with a rewrite. We started with a dependency graph of what ran, what it read, and what anyone actually looked at. About a third of the DAGs had no downstream consumer. They went first.

Then, in order:

1. Ingestion — Fivetran for the SaaS sources, Snowpipe for the event stream, and one Python loader for the partner SFTP drop that no connector wanted to touch.
2. Transformation — dbt, staging → intermediate → marts, with not_null / unique / relationship tests on every key and freshness checks on every source. The six overlapping revenue DAGs became two documented models.
3. Cost — warehouses sized per workload, auto-suspend at 60 seconds, and a single query-history view so the team could see what each dashboard cost to refresh.

-- The revenue pipeline, after: one tested model instead of six scripts.
-- Every column documented; every key tested; freshness checked on every source.
select
  o.order_id,
  o.account_id,
  p.pricing_tier,
  o.amount_usd,
  o.ordered_at
from {{ ref('stg_orders') }} o
join {{ ref('dim_pricing_tier') }} p using (pricing_tier_id)

What changed

• 40+ pipelines migrated onto a tested foundation; the rest retired on purpose.
• −35% warehouse cost in month three, mostly from right-sizing and killing the always-on backfill warehouse.
• 6 Airflow jobs → 2 dbt models for revenue. The new pricing tier that was quoted at six weeks took two days, and the team did it themselves.

Handoff

Code, docs, a runbook, and a 90-minute walkthrough recorded for the next hire. They own everything. We still get the occasional question, which is how it should be.

Representative engagement — details anonymized and generalised.