Freshness versus cost

Every pipeline schedule is a bet about two curves. One is what it costs to run more often. The other is how stale the data is when someone actually reads it. I have rarely seen a team plot either, and I have never seen one plot both. So here they are, as a model you can push around.

A model, not a bill

The numbers above are a model with example dollars, not a measurement and not anyone’s price list. The shape is what matters, and the shape comes from two facts that hold on every warehouse I have used.

First, each run has a fixed cost you pay whether or not anything changed: the orchestrator waking up, the warehouse warming up, the minimum billing increment that gets charged even for a two-second query. Second, the variable cost is proportional to the rows that changed since the last run. Run twice as often and each run touches half the rows. Over a month the variable part comes out the same regardless of cadence.

That is the whole trick. Cadence does not change how much work you do. It changes how many times you pay to wake up.

Where the knee is

With the default sliders the daily run costs about $372 a month and the hourly run about $648. Every five minutes is about $3,816. Every minute is $17,640. Median staleness goes twelve hours, about thirty minutes, two minutes, and effectively zero.

I mark the knee at the cadence where wake-up overhead equals the cost of the work itself, which is where the monthly bill is double its floor. With the defaults that is every 48 minutes. Shorter than that and you are mostly paying to wake machines up. Longer and the bill barely moves, so the only question left is staleness.

Moving the sliders moves the knee. A bigger fixed cost per run pushes it toward hours. More rows per hour or a higher unit price pushes it toward minutes, because the work is big enough to make the overhead look small. Your knee is somewhere specific, and you can find it from two numbers in your own billing.

Measure when people actually read

The staleness curve is where most teams guess wrong, because they assume readers are uniform over the day. They are not. Your warehouse keeps a query history table. So does your BI tool. Group reads of the table you care about by hour of day and by user, drop the service accounts, and look at the histogram.

SELECT
  HOUR(start_time) AS hour_of_day,
  COUNT(*)         AS reads
FROM query_history
WHERE query_text ILIKE '%fct_orders%'
  AND user_name NOT IN ('dbt_prod', 'bi_refresh')
  AND start_time >= DATEADD('day', -30, CURRENT_DATE)
GROUP BY 1
ORDER BY 1;

What you will almost always find is a hump between nine and five in one or two time zones, and near silence otherwise. Feed that into the model with the toggle and the daily run stops looking like twelve hours of staleness. If the run finishes before the first reader arrives, the median is closer to seven. A second run at 18:00 buys those readers twelve minutes, because it refreshes data nobody looks at until the morning run has already replaced it.

Real time is usually a marketing word

Nobody I have worked with reads a dashboard in real time. They open it at the start of a meeting, or when a number in a Slack thread looks wrong. The dashboard caches for five or fifteen minutes. The source system batches its exports. The run takes a few minutes before the first byte lands. Add it up and “real time” is a pipeline that costs like every minute and delivers like every ten.

There are real cases: fraud checks, on-call alerts, a trading desk. They are event driven and they do not sit behind a cron. If your readers are humans looking at charts, the honest floor is the slowest hop in the chain, and it is rarely the pipeline.

What to do

• Pull thirty days of query history for your three most read tables and plot reads by hour of day. This takes twenty minutes and settles most arguments.
• Find your fixed cost per run and what one minute of changed rows costs to process, both from one month of billing. Divide the first by the second and you have your knee in minutes.
• Move the daily run so it finishes before the first reader arrives. This is the cheapest freshness improvement you will ever ship.
• For anything scheduled faster than the knee, write down who reads it between runs. If the answer is a dashboard cache, slow it down.
• Reserve “real time” for the pipelines that are actually event driven, and say “hourly” out loud for the rest.

Cadence is a number someone typed into a cron expression once. It deserves the same scrutiny as the warehouse size.