Controlling Pipeline Cost

Scenario

Example company: all 120 dbt models are materialised as full tables and the whole project runs every hour "so data is fresh". Most models rebuild three years of history each time.

What you see in production

• Warehouse spend grows 15% a month while data grows 3% a month.
• The hourly run takes 50 minutes, so runs nearly overlap and queue behind each other.
• Nobody knows which models or teams drive the cost.

The naive code

# every model, every hour
models:
  +materialized: table
schedule: "0 * * * *"

Reproduce it

The model estimates monthly compute for one large fact table under different strategies.

history_days = 3 * 365
cost_per_day_processed = 0.02        # compute cost to process one day of data
lookback_days = 3                     # incremental runs reprocess recent days for late data

def monthly(runs_per_day, days_per_run):
    return runs_per_day * 30 * days_per_run * cost_per_day_processed

plans = {
    "full refresh, hourly":          monthly(24, history_days),
    "full refresh, daily":           monthly(1, history_days),
    "incremental (3-day lookback), hourly": monthly(24, lookback_days),
    "incremental (3-day lookback), daily":  monthly(1, lookback_days),
}
for name, cost in plans.items():
    print(f"{name:<38} {cost:>9.2f} per month")

full refresh, hourly                    15768.00 per month
full refresh, daily                       657.00 per month
incremental (3-day lookback), hourly       43.20 per month
incremental (3-day lookback), daily         1.80 per month

Root cause

Every run reprocessed all history, at a frequency nobody needed. Cost scales with data processed × runs, so both factors multiplied.

The fix

• Make large models incremental: process only new or changed partitions plus a lookback window for late data, using merge or partition overwrite (dbt incremental models). Schedule occasional full refreshes to catch drift.
• Schedule by need: run each model at the frequency its freshness target requires (see the batch-vs-streaming lesson). Hourly finance tables are rarely needed.
• Use views for cheap, rarely queried transformations and tables for heavily queried ones.
• Right-size compute: separate warehouses or queues for ETL and BI, auto-suspend idle compute, and set budgets and alerts.
• Attribute cost: tag queries with model, pipeline and team, and review the top spenders each month.

Trade-offs

• Incremental logic is more complex and can drift from a full rebuild; test it and full-refresh periodically.
• Less frequent runs mean staler data; agree targets with consumers first.

Detect it in production

• Cost per model or pipeline per day, with alerts on sudden jumps.
• Ratio of rows processed to rows changed: a high ratio means a model should be incremental.

Key points

• Cost ≈ data processed × run frequency; reduce both.
• Use incremental models with a lookback, plus periodic full refreshes.
• Schedule by freshness need, separate workloads, and attribute cost to owners.