DEV Community: Atsushi Hara

Can We Stop Running Recurring Analysis as a Scheduled LLM Prompt?

Atsushi Hara — Wed, 19 Aug 2026 04:25:34 +0000

Every major assistant now ships some version of scheduled prompts. You write "every Monday at 9am, analyze last week's signup funnel and tell me what changed," pick a cadence, and it runs.

For a news briefing, this is great. For analysis, I think it's a mistake — and not a small one. I want to argue that the problem isn't the scheduler. It's where the LLM sits relative to it.

What a recurring analysis is actually for

Nobody schedules an analysis because they want a report. They schedule it because they want to know what changed.

That's the whole purpose. A one-off analysis answers "what is the state of things." A recurring analysis answers "is the state of things moving, and in which direction." The second question is the only reason the cadence exists.

And detecting change requires one thing above all else: the instrument has to hold still.

When the ruler changes length

If the query is regenerated from a natural language prompt on every run, then a movement in your number has two possible causes:

The world changed.
The measurement changed.

And you have no way to separate them. This is textbook confounding, and you've built it into the foundation of your monitoring.

The tempting response is "modern models are good at SQL, it'll be fine." But accuracy isn't really the issue — consistency is. Even a model that writes correct SQL every single time can write differently correct SQL each run. Here is the failure mode that actually bites:

-- Run 1
SELECT COUNT(DISTINCT u.user_id)
FROM users u
LEFT JOIN subscriptions s ON s.user_id = u.user_id
WHERE u.created_at >= '2026-08-01' AND u.created_at < '2026-09-01';

-- Run 2 — same prompt, two weeks later
SELECT COUNT(DISTINCT u.user_id)
FROM users u
INNER JOIN subscriptions s ON s.user_id = u.user_id   -- users with no subscription silently vanish
WHERE u.created_at > '2026-08-01' AND u.created_at <= '2026-09-01';  -- window shifted by a day

Neither query is broken. Neither throws an error. Both are defensible readings of the same English sentence. The number just moves by a few percent.

The usual suspects, in my experience:

LEFT JOIN quietly becoming INNER JOIN
Period boundaries flipping between >= / > and < / <=
NOT IN vs NOT EXISTS and how each treats NULL
COUNT(*) vs COUNT(DISTINCT ...) on a fanned-out join
Timezone: UTC vs local, and whether the day boundary matches the rest of the org

This is worse than an outright failure. A crash is loud and you fix it. This is silent, plausible, and it corrupts the time series. Six months later, somebody asks why March looks odd, and you cannot reconstruct what query produced March's number — because it no longer exists anywhere.

LLM as compiler, not as runtime

The fix isn't to stop using LLMs. It's to move the LLM from runtime to build time.

Concretely, split the work into phases:

1. Explore — interactive, LLM-heavy. This is where "analyze the signup funnel" belongs. Iterate, argue with the model, throw things away. Nondeterminism is a feature here; you want different angles.

2. Freeze — emit code. Once you know what you're measuring, have the model write it out as SQL or a script, and put it in version control. The metric definition is now an artifact with a name and a hash.

3. Review — a human reads the diff. This step looks bureaucratic and it is the single most valuable one. It converts "the metric definition changed" from an invisible accident into an explicit, attributable, reviewable event. A scheduled prompt can never give you this, because there is no diff to look at — the change happens inside a sampling distribution.

4. Run — cron, or whatever your orchestrator is. Deterministic code, deterministic schedule.

5. Interpret — LLM again, downstream. More on this below.

If this sounds familiar, it's because the BI world has already had this argument and reached the same conclusion. dbt's Semantic Layer, Omni, Dremio, Cortex Analyst — the shared thesis is that metric definitions must be codified so the LLM chooses which metric rather than how to compute it. dbt make the point directly: with a semantic layer, the model can't produce correct-looking numbers that differ subtly between runs, because the logic is fixed.

What strikes me is that this conversation is happening almost entirely inside data engineering, in the vocabulary of text-to-SQL and governance. Meanwhile the "schedule an AI task" conversation is happening somewhere else entirely, in productivity blogs, and the two have not met. They're the same problem.

Where the LLM legitimately stays

I don't want this to read as "keep LLMs out of the pipeline." There are two places they belong, and once you look closely they're the same place.

Interpreting the numbers. The metric is computed by frozen code. Then you hand the resulting numbers to a model and ask for commentary: "conversion fell 12% week over week, concentrated in the paid-social cohort." That's a judgment, it's genuinely hard to express as code, and — crucially — the model is consuming numbers, not producing them. The nondeterminism stays downstream of the measurement.

Understanding unstructured input. If a step in your pipeline classifies free-text support tickets by theme, or extracts entities from a PDF, no amount of SQL is going to replace it. This is exactly what language models are for.

The unifying shape: in both cases the LLM's job is natural language in, fixed structure out. It's a function with a declared output contract. What it must never be is the thing that decides the contract.

So the line I'd draw isn't "LLM or not." It's:

The LLM may consume or produce values. It may not define how values are computed.

What freezing doesn't fix, and what to do about it

Freezing the query removes one source of drift. It doesn't make the pipeline safe, and I think it's worth being honest that two failure modes survive — one on the data side, one on the model side.

Data-side drift

Your schema and your semantics keep moving even when your SQL doesn't.

Someone adds a new value to an enum. An event gets renamed and the old name is dual-written for a month. A column's meaning quietly changes because a new signup flow populates it differently. Your frozen query keeps running, keeps returning numbers, and keeps being wrong.

Note that this is the same silent-plausible-drift problem as before, just relocated. Freezing moved it, it didn't kill it.

Countermeasure: assertions alongside the metric. Every scheduled run should check its own inputs and outputs:

# Guard the metric, not just the job. A green exit code means nothing
# if the underlying data changed shape.
assert 8_000 <= row_count <= 20_000, f"row count outside expected band: {row_count}"
assert null_rate("plan_type") < 0.02, "null rate spiked — upstream schema change?"
assert set(observed_categories) <= KNOWN_CATEGORIES, (
    f"unknown category values: {set(observed_categories) - KNOWN_CATEGORIES}"
)

The important part is what happens when these fire. Don't treat it as a flaky job to be retried. Treat it as a signal that the metric definition needs to be revisited by a human. The architecture is: freeze, plus an automated trigger to reconsider the freeze.

Countermeasure: version the metric itself. Stamp a metric_version on every output row and surface it on the dashboard. When you revise a definition, you then have to make an explicit decision — backfill history under the new definition, or break the series and show the break — instead of silently splicing two incompatible time series together and squinting at the result.

Model-side drift

If an LLM inference step remains in the pipeline, it has its own drift, from two directions.

The provider changes the model under you. Pin model versions explicitly. Treat an unpinned model in a scheduled pipeline the same way you'd treat pip install without a lockfile.

The input distribution changes under a pinned model. Pinning is necessary but not sufficient. If your ticket classifier suddenly reports 30% more "billing" tickets, is that because customers are complaining more about billing, or because the phrasing of incoming tickets shifted in a way that pushes the classifier around? Same confound as at the start of this post, one layer down.

Countermeasure: a golden set on every run. Keep a fixed, hand-labeled sample and push it through the classifier alongside the real data each time. If the golden set's outputs move, the classifier moved. If the golden set is stable and production numbers moved, the world moved. Now you can actually tell the two apart — which was the entire point of scheduling the thing in the first place.

What else breaks? I'd genuinely like to know

These two are the failure modes I've run into myself, which almost certainly means the list is incomplete rather than complete. I'm sure there are whole categories here I haven't hit yet — late-arriving data and how it interacts with a frozen window, DST and the days that have 23 or 25 hours in them, warehouse engine upgrades changing float or sort behavior, upstream backfills silently rewriting history under a metric you already published, whatever else is out there.

If you've operated something like this in production and watched it break in a way I haven't described, please leave a comment. I'd like to turn this section into something more like a proper catalogue, and I'll learn more from your war stories than from anything else. Disagreement is very welcome too — if you think scheduled prompts are fine for analysis and I've overstated the risk, I want to hear the case.

The one-line version

The scheduler was never the problem. Cron is fine. Airflow is fine. The problem is putting a probabilistic process where a fixed instrument needs to be.

Let the LLM write the pipeline once. Don't let it be the pipeline.

You can unit test your code. But how do you test your prompts?

Atsushi Hara — Wed, 12 Aug 2026 08:43:27 +0000

I've been building a web app to manage rolling stock - a preparedness habit where you rotate everyday household food items as emergency supplies, rather than keeping a separate untouched stash.
The app is simple. You take a photo of a product label, and an LLM (currently using Gemini Flash Lite) automatically extracts the product name and expiration date. You can see how many days are left until expiration - enough to know when something needs to be eaten or when to restock.
The implementation went surprisingly smoothly. The LLM's recognition accuracy was more than sufficient. While building it, I thought, "This is pretty solid - the core feature is basically done."
but I was wrong.

A Gap I Found During Validation

While validating the app with real product labels, I noticed something odd.
A label that only showed "August 2026" was being registered as August 1st, 2026.
In Japan, the convention for expiration dates is that a year-month-only format means the product is valid until the last day of that month. So the correct value should be August 31st, 2026. But the LLM was returning the minimum valid date - the 1st.
For users who know this convention, this means manually correcting the date every single time. For users who don't, it could cause them to rotate food stocks weeks earlier than necessary.

Deciding on a Fix

I considered two approaches.
Option A - Instruct via prompt: Tell the LLM to return the last day of the month. The concern of this approach is trusting the LLM with leap year and 30/31-day logic feels risky. LLM outputs are probabilistic - even if it appears to work during testing, I can't be fully confident it will do so 100% of the time in practice.
Option B - Handle in code: Have the LLM return YYYY-MM when only year and month are available, and calculate the last day in code. The concern of this approach is the impact of changing the prompt is unclear.
I went with Option B. Calculating the last day of a month is deterministic logic - there's no reason to leave it to a probabilistic model. On top of that, finding real products with leap-year expiration dates to test with would itself be a challenge.
So I kept the prompt change to a single line:

// Before
"expiry_date": "Date in YYYY-MM-DD format"

// After
"expiry_date": "Return YYYY-MM-DD if the full date is known, or YYYY-MM if only year and month are available"

And handled the rest in code:

const normalizeExpiryDate = (date: string): string => {
  if (/^\d{4}-\d{2}$/.test(date)) {
    const [year, month] = date.split('-').map(Number);
    const lastDay = new Date(year, month, 0).getDate();
    return `${date}-${String(lastDay).padStart(2, '0')}`;
  }
  return date;
};

A clean fix. But this is where the real problem emerged.

How Do You Confirm "It's Fixed"?

I changed one line of the prompt. But then a new challenge surfaced: how do you actually verify that the updated prompt works correctly?
The natural answer is to re-photograph every product and check manually. But that approach has serious problems.
If the updated prompt still fails on some cases, I have to revise it again and restart testing from scratch. To build confidence, I'd need more test cases. And since LLM outputs are probabilistic, even the same image with the same prompt can produce different results - meaning I'd need multiple test runs per product. How many times is enough?
Repeating this entire process every time I tweak a prompt is not realistic. And this, I believe, is a universal challenge in building apps that use LLMs.
You can unit test your code. But how do you test a prompt change?

Solving It with PromptProof

That's where I used PromptProof - a platform for statistically validating LLM prompt accuracy. By integrating it into your app, you can post LLM inputs and outputs to the platform, manually annotate ground truth labels, and reuse that dataset to run prompt experiments.
Here's how I used it:
Post product images and extracted data from the app (photos taken just once)
Annotate ground truth labels in PromptProof (for year-month-only labels, the last day of the month is the correct answer)
Run experiments with both the original and revised prompts
Compare accuracy with confidence intervals

The Results - and a Surprise

The results before the prompt change was like following.

Overall accuracy: 81% (confidence interval: 78–84%)
Product name extraction: 71% (100/140 correct) [CI: 63–78%]
Expiration date extraction: 57% (80/140 correct) [CI: 49–65%]
Expiration type: 100% (140/140 correct) [CI: 97–100%]

And after changed prompt was following.

Overall accuracy: 90% (confidence interval: 88–93%)
Product name extraction: 57% (80/140 correct) [CI: 49–65%] ⬇️
Expiration date extraction: 100% (140/140 correct) [CI: 97–100%] ⬆️
Expiration type: 100% (140/140 correct) [CI: 97–100%]

The fix achieved its goal. Expiration date accuracy went from 57% to 100%. The confidence intervals - [49–65%] before versus [97–100%] after - don't overlap at all. A clear, statistically significant improvement.
But something else happened, product name accuracy dropped from 71% to 57%.
Looking at the per-field confidence intervals tells a more nuanced story,
Before: 71% [63–78%]
After: 57% [49–65%]

The intervals overlap slightly between 63% and 65%, so we can't call this regression statistically significant - not yet. More samples are needed to say so with confidence. That said, a 14-point decline is hard to ignore. The direction is clear even if the certainty isn't. This is a concrete hypothesis to investigate next, and precisely the kind of thing that gets missed without statistical tooling.
Reviewing the outputs gave a clue: for well-known products, some results included the manufacturer name and others didn't. The root cause wasn't the prompt change itself - it was that the prompt had never clearly specified what "product name" should include. The experiment surfaced a gap that had always been there.
If I had validated by manually re-photographing products, I might have caught the improvement in expiration date accuracy. But this regression would likely have gone unnoticed entirely.

A Bonus: Token and Latency Insights

Accuracy isn't the only thing PromptProof surfaces. The latency and token usage distributions turned out to be equally useful.
Output tokens were remarkably stable - P50 of 48, max of 51 across 140 trials. That tells you exactly how much output budget to reserve when configuring max_tokens. No guesswork.
Latency was more interesting. The first prompt showed a P50 of 1.42s but a max spike of 4.38s - a 3x gap worth knowing about. After the prompt change, the max dropped to 2.56s despite slightly longer input tokens (1.1k → 1.2k).
When evaluating a new model, this kind of distribution data answers two practical questions before you commit:
Is the latency acceptable for your use case? A P50 of 1.5s might be fine for a background job, but not for a real-time interaction.
What's the minimum output token budget you actually need? Over-provisioning max_tokens wastes money; under-provisioning causes silent truncation failures.

Running experiments before locking in a model means you're making that decision with data, not intuition.

Conclusion

Building apps that use LLMs has made me feel that validation is genuinely harder than implementation.
Even a single-line prompt change can have wide-ranging effects. Being able to reduce the cost of re-testing and say with statistical confidence that "this change was correct" - and to do so without friction - is what it takes to build LLM-powered apps that are reliable enough to ship.

Note

PromptProof, the platform I used in this article, is a service I'm building myself. It's free to get started - if you're facing similar challenges, I'd love for you to try it out.
👉 PromptProof