Diagnostic Stripe
sigma scheduled query runs time out and email nothing
Finance asks where the Monday numbers went, and it turns out they have not arrived for six weeks. Nobody raised it earlier because the alert for this failure is an email that does not appear, and an email that does not appear looks exactly like a week where nothing happened.
Page GET /v1/sigma/scheduled_query_runs?limit=100 and flag every run whose status is not completed. The four terminal states are completed, canceled, failed and timed_out; the last is the one that arrives gradually, as a query that used to fit its execution budget stops fitting.
Then check two things the status alone will not tell you. Whether a run exists at all for each expected data_load_time, because a schedule that stopped producing runs looks identical to one that never failed. And whether result_available_until has already passed on runs whose CSV you still need, because results expire whether or not anyone downloaded them.
The problem in plain words
A Sigma schedule delivers by email, and email is a channel with no failure signal. When the query completes, a message with a link arrives. When it times out, nothing arrives. There is no bounce, no error, no red mark anywhere the recipient looks, and the recipient is usually in finance rather than engineering, so the first few missing weeks get filed as "must have been quiet".
The timeout itself is a slow-motion failure. The query was written against a small table and fits comfortably. Volume grows month over month, the run time climbs, and one week it crosses the execution budget. From then on it fails every time, because nothing about the query has changed except the amount of data it now has to touch, and that only goes up.
Underneath that is a second, quieter loss. Even successful runs produce a file that expires at result_available_until. A pipeline that downloads results a few days late, or a person who comes back to an old email, finds the link dead. The run says completed and the data is gone, which is a confusing combination to debug from the Dashboard.
Why it happens
The absence of an email is not an event. Everything else in a Stripe integration fails loudly enough to reach a log line. This one fails by not happening. Unless something is asserting that a run occurred, the failure has no representation anywhere in your systems.
Timeouts arrive by growth, not by change. Nobody edited the query. That is why the investigation always starts in the wrong place: the schedule is unchanged, the SQL is unchanged, and the deploy history is clean. The variable that changed is the size of the tables the query scans, and it changes every day by a little.
The terminal states are not all equally visible. failed usually carries a message in error. timed_out tells you the budget was exceeded but not which part of the query spent it. canceled is usually a human. Collapsing all three into "not completed" loses the distinction between "fix the SQL", "narrow the SQL", and "somebody stopped it on purpose".
Results expire on a clock nobody watches. result_available_until is on every run object and is almost never read, because the workflow it was designed for — click the link in the email today — never encounters it. Any workflow that downloads on a schedule, or retries after a failure, will.
The fix, as a flow
The script reads the terminal state of every run, then checks the one thing no status can show: whether runs are still being produced at all.
How to fix it
List the runs and sort them by terminal state
GET /v1/sigma/scheduled_query_runs?limit=100, paginated. Read status, error, data_load_time, title and result_available_until on each. Report timed_out, failed and canceled separately, because they lead to three different repairs.
Assert that runs are still being produced
Compare the newest run's data_load_time against the cadence you expect. If a weekly schedule's newest run is nineteen days old, the schedule has stopped producing runs entirely and no per-run status will ever show it. This is the check that catches the six-week silence.
Check whether the results you still need have expired
result_available_until in the past means the file is gone even though the run completed. If you are downloading results programmatically, this is the deadline your job is racing, and it is worth reporting separately from a failure.
Narrow the query rather than retrying it
A timeout is a budget problem and re-running spends the same budget. In Dashboard → Data → Sigma, add a created >= bound so the query scans a window rather than all history, drop the wide joins, and select fewer columns. Then re-save the schedule.
Stop depending on the email
Subscribe an endpoint to sigma.scheduled_query_run.created, and on receipt download data.object.file.url through GET https://files.stripe.com/v1/files/{FILE_ID}/contents before result_available_until. A webhook that stops arriving is something your monitoring already knows how to notice; an email is not.
How to check it worked
Re-run the script after narrowing the query and adding the subscription. Every run should be completed, the newest one should be inside the cadence, and the run event should be subscribed.
python3 stripe_sigma_runs.py --cadence-hours 168
# clear 12 run(s), all completed, newest 6.2h old, results consumed by webhookThe full code
One paginated GET for the runs and one for the endpoints, both reads — a restricted key with read access to Sigma and Webhook Endpoints is enough. Two pure functions carry the judgement: one classifies a single run, including the expiry that a status of completed hides, and one decides whether a schedule that has produced nothing recently is quiet or dead.
"""Report Sigma scheduled query runs that failed, timed out, or stopped happening.
Read only. Two paginated GETs and no writes: give this a RESTRICTED key with read
access to Sigma and Webhook Endpoints. The repair is printed, never performed,
because this script holds a credential to a live payments account.
"""
import argparse
import logging
import os
import sys
import time
import requests
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("stripe_sigma_runs")
API = "https://api.stripe.com/v1"
RUN_EVENT = "sigma.scheduled_query_run.created"
# A schedule that has produced nothing for twice its cadence is not quiet, it has
# stopped. One missed run can be a blip; two in a row never is.
MISSED_CADENCES = 2.0
def run_state(status, error, seconds_until_expiry):
"""Classify one scheduled query run. Pure, so it can be tested offline.
`seconds_until_expiry` is result_available_until minus now, or None when the
run has no result. Returns (state, detail).
"""
if status == "completed":
if seconds_until_expiry is not None and seconds_until_expiry <= 0:
return ("expired",
"completed, but the result expired %.1f hour(s) ago; the run "
"succeeded and the file is gone"
% (-seconds_until_expiry / 3600.0))
return ("completed", "completed with a result still available")
if status == "timed_out":
return ("timed_out",
"the query ran past its execution budget; it will keep doing that "
"until it is narrowed, because the data only grows")
if status == "failed":
return ("failed", error or "failed with no error message on the run")
if status == "canceled":
return ("canceled", "canceled, which is usually a person rather than a fault")
return ("unknown", "unrecognised status %r" % (status,))
def verdict(states, hours_since_newest, cadence_hours, run_event_subscribed):
"""Fold run states, schedule liveness and webhook coverage into one verdict.
Pure. `hours_since_newest` is None when there are no runs at all.
"""
broken = states.count("timed_out") + states.count("failed")
expired = states.count("expired")
if not states:
return ("silent",
"no scheduled query runs at all; either no schedule exists or it "
"has never produced a run")
if broken:
return ("failing",
"%d of %d run(s) ended in timed_out or failed; narrow the query "
"rather than retrying it" % (broken, len(states)))
if (hours_since_newest is not None
and hours_since_newest > MISSED_CADENCES * cadence_hours):
return ("missing",
"no run for %.1f hour(s) against a cadence of %.0f hour(s); the "
"schedule has stopped producing runs"
% (hours_since_newest, cadence_hours))
if expired:
return ("expired_results",
"%d completed run(s) whose result has already expired; the data is "
"gone even though nothing failed" % expired)
if not run_event_subscribed:
return ("email_only",
"%d run(s), all completed, but nothing subscribes to %s, so a run "
"that stops happening has no signal at all" % (len(states), RUN_EVENT))
return ("clear",
"%d run(s), all completed, results consumed by webhook" % len(states))
def get(session, path, params):
r = session.get(API + path, params=params, timeout=30)
if r.status_code == 401:
raise SystemExit("401 from Stripe: the key is wrong, or is for the other mode")
if r.status_code == 403:
raise SystemExit("403 from Stripe: the restricted key has no read access to "
"Sigma, or Sigma is not enabled on this account")
r.raise_for_status()
return r.json()
def page_all(session, path, params, cap=2000):
out = []
params = dict(params)
while True:
page = get(session, path, params)
data = page.get("data", [])
out.extend(data)
if not data or not page.get("has_more") or len(out) >= cap:
return out
params["starting_after"] = data[-1]["id"]
def run_event_is_subscribed(endpoints):
for ep in endpoints:
if ep.get("status") == "disabled":
continue
events = ep.get("enabled_events") or []
if RUN_EVENT in events or "*" in events:
return True
return False
def main():
ap = argparse.ArgumentParser(description=__doc__)
ap.add_argument("--cadence-hours", type=float, default=24.0,
help="how often you expect a run, in hours (168 for weekly)")
ap.add_argument("--limit", type=int, default=200,
help="stop after this many runs")
args = ap.parse_args()
key = os.environ.get("STRIPE_API_KEY")
if not key:
log.error("set STRIPE_API_KEY (use a restricted, read-only key)")
return 2
s = requests.Session()
s.headers.update({"Authorization": "Bearer " + key})
now = time.time()
runs = page_all(s, "/sigma/scheduled_query_runs", {"limit": 100}, cap=args.limit)
states = []
newest = None
for r in runs:
until = r.get("result_available_until")
left = None if until is None else until - now
state, detail = run_state(r.get("status"), r.get("error"), left)
states.append(state)
loaded = r.get("data_load_time")
if loaded is not None and (newest is None or loaded > newest):
newest = loaded
if state not in ("completed",):
log.warning(" %-9s %s %s %s", state, r.get("id"),
r.get("title") or "<untitled>", detail)
hours_since = None if newest is None else (now - newest) / 3600.0
endpoints = page_all(s, "/webhook_endpoints", {"limit": 100})
subscribed = run_event_is_subscribed(endpoints)
state, detail = verdict(states, hours_since, args.cadence_hours, subscribed)
line = "%-11s %s" % (state, detail)
if state == "clear":
log.info(line)
return 0
log.warning(line)
if state in ("failing", "missing", "silent"):
log.warning(" narrow the query in Dashboard > Data > Sigma: add a created >= "
"bound, drop wide joins, select fewer columns, then re-save it")
if not subscribed:
log.warning(" consume results programmatically instead of by email:")
log.warning(" POST %s/webhook_endpoints/<we_id> enabled_events[]=%s",
API, RUN_EVENT)
log.warning(" then GET https://files.stripe.com/v1/files/<file_id>/contents "
"before result_available_until")
return 1
if __name__ == "__main__":
sys.exit(main())
/**
* Report Sigma scheduled query runs that failed, timed out, or stopped happening.
*
* Read only. Two paginated GETs and no writes: give this a RESTRICTED key with
* read access to Sigma and Webhook Endpoints. The repair is printed, never
* performed.
*/
const API = 'https://api.stripe.com/v1';
export const RUN_EVENT = 'sigma.scheduled_query_run.created';
// A schedule that has produced nothing for twice its cadence has stopped.
export const MISSED_CADENCES = 2.0;
/**
* Classify one scheduled query run. Pure, so it can be tested offline.
* `secondsUntilExpiry` is result_available_until minus now, or null.
*/
export function runState(status, error, secondsUntilExpiry) {
if (status === 'completed') {
if (secondsUntilExpiry !== null && secondsUntilExpiry !== undefined
&& secondsUntilExpiry <= 0) {
return ['expired',
`completed, but the result expired ${(-secondsUntilExpiry / 3600).toFixed(1)} ` +
'hour(s) ago; the run succeeded and the file is gone'];
}
return ['completed', 'completed with a result still available'];
}
if (status === 'timed_out') {
return ['timed_out',
'the query ran past its execution budget; it will keep doing that until it ' +
'is narrowed, because the data only grows'];
}
if (status === 'failed') {
return ['failed', error || 'failed with no error message on the run'];
}
if (status === 'canceled') {
return ['canceled', 'canceled, which is usually a person rather than a fault'];
}
return ['unknown', `unrecognised status ${JSON.stringify(status)}`];
}
/** Fold run states, schedule liveness and webhook coverage into one verdict. Pure. */
export function verdict(states, hoursSinceNewest, cadenceHours, runEventSubscribed) {
const count = (s) => states.filter((x) => x === s).length;
const broken = count('timed_out') + count('failed');
const expired = count('expired');
if (states.length === 0) {
return ['silent',
'no scheduled query runs at all; either no schedule exists or it has never ' +
'produced a run'];
}
if (broken) {
return ['failing',
`${broken} of ${states.length} run(s) ended in timed_out or failed; narrow ` +
'the query rather than retrying it'];
}
if (hoursSinceNewest !== null && hoursSinceNewest !== undefined
&& hoursSinceNewest > MISSED_CADENCES * cadenceHours) {
return ['missing',
`no run for ${hoursSinceNewest.toFixed(1)} hour(s) against a cadence of ` +
`${cadenceHours.toFixed(0)} hour(s); the schedule has stopped producing runs`];
}
if (expired) {
return ['expired_results',
`${expired} completed run(s) whose result has already expired; the data is ` +
'gone even though nothing failed'];
}
if (!runEventSubscribed) {
return ['email_only',
`${states.length} run(s), all completed, but nothing subscribes to ${RUN_EVENT}, ` +
'so a run that stops happening has no signal at all'];
}
return ['clear',
`${states.length} run(s), all completed, results consumed by webhook`];
}
async function get(key, path, params) {
const url = new URL(API + path);
for (const [k, v] of Object.entries(params)) url.searchParams.set(k, v);
const res = await fetch(url, { headers: { Authorization: `Bearer ${key}` } });
if (res.status === 401) {
throw new Error('401 from Stripe: the key is wrong, or is for the other mode');
}
if (res.status === 403) {
throw new Error('403 from Stripe: no read access to Sigma, or Sigma is not enabled');
}
if (!res.ok) throw new Error(`${res.status} from ${url.pathname}`);
return res.json();
}
async function pageAll(key, path, params, cap = 2000) {
const out = [];
const p = { ...params };
for (;;) {
const page = await get(key, path, p);
const data = page.data ?? [];
out.push(...data);
if (data.length === 0 || !page.has_more || out.length >= cap) return out;
p.starting_after = data[data.length - 1].id;
}
}
export function runEventIsSubscribed(endpoints) {
return endpoints.some((ep) => ep.status !== 'disabled'
&& (ep.enabled_events ?? []).some((e) => e === RUN_EVENT || e === '*'));
}
async function main() {
const key = process.env.STRIPE_API_KEY;
if (!key) {
console.error('set STRIPE_API_KEY (use a restricted, read-only key)');
process.exitCode = 2;
return;
}
const cadenceHours = Number(process.env.CADENCE_HOURS ?? 24);
const now = Date.now() / 1000;
const runs = await pageAll(key, '/sigma/scheduled_query_runs', { limit: 100 }, 200);
const states = [];
let newest = null;
for (const r of runs) {
const until = r.result_available_until;
const left = until === undefined || until === null ? null : until - now;
const [state, detail] = runState(r.status, r.error, left);
states.push(state);
if (r.data_load_time != null && (newest === null || r.data_load_time > newest)) {
newest = r.data_load_time;
}
if (state !== 'completed') {
console.warn(` ${state.padEnd(9)} ${r.id} ${r.title ?? '<untitled>'} ${detail}`);
}
}
const hoursSince = newest === null ? null : (now - newest) / 3600;
const endpoints = await pageAll(key, '/webhook_endpoints', { limit: 100 });
const subscribed = runEventIsSubscribed(endpoints);
const [state, detail] = verdict(states, hoursSince, cadenceHours, subscribed);
const line = `${state.padEnd(11)} ${detail}`;
if (state === 'clear') { console.log(line); return; }
console.warn(line);
if (['failing', 'missing', 'silent'].includes(state)) {
console.warn(' narrow the query in Dashboard > Data > Sigma: add a created >= ' +
'bound, drop wide joins, select fewer columns, then re-save it');
}
if (!subscribed) {
console.warn(' consume results programmatically instead of by email:');
console.warn(` POST ${API}/webhook_endpoints/<we_id> enabled_events[]=${RUN_EVENT}`);
console.warn(' then GET https://files.stripe.com/v1/files/<file_id>/contents ' +
'before result_available_until');
}
process.exitCode = 1;
}
// Only run when invoked directly, so importing this module from the test file
// does not fire main() and fail the suite on the missing key.
if (import.meta.url === `file://${process.argv[1]}`) {
main().catch((err) => { console.error(err.message); process.exitCode = 2; });
}
Add a test
Two things need pinning here, and neither is visible from a single run's status. That a completed run can still have lost its data, and that a list of nothing but successes is a failure when the newest one is older than the cadence. The pure functions decide both, so the tests get at them without Sigma, a schedule, or a key.
from stripe_sigma_runs import run_state, verdict
def test_completed_run_with_a_live_result_is_fine():
state, _ = run_state("completed", None, 3 * 86400.0)
assert state == "completed"
def test_completed_but_expired_is_its_own_state():
# status says completed and the data is gone. Collapsing this into "completed"
# is how a pipeline reports success while downloading nothing.
state, detail = run_state("completed", None, -7200.0)
assert state == "expired"
assert "2.0 hour(s) ago" in detail
def test_timed_out_is_distinguished_from_failed_and_canceled():
assert run_state("timed_out", None, None)[0] == "timed_out"
assert run_state("failed", "syntax error at or near FROM", None)[1].startswith("syntax")
assert run_state("canceled", None, None)[0] == "canceled"
def test_all_completed_but_the_schedule_has_stopped():
# Weekly cadence, newest run 19 days old: every run succeeded and the schedule
# is dead. This is the six-week silence, caught by arithmetic rather than status.
state, detail = verdict(["completed"] * 8, 456.0, 168.0, True)
assert state == "missing"
assert "stopped producing runs" in detail
def test_completed_runs_with_no_subscriber_are_not_clear():
assert verdict(["completed"], 6.0, 24.0, False)[0] == "email_only"
assert verdict(["completed"], 6.0, 24.0, True)[0] == "clear"
assert verdict([], None, 24.0, True)[0] == "silent"
import { test } from 'node:test';
import assert from 'node:assert/strict';
import { runState, verdict } from './stripe-sigma-runs.mjs';
test('completed run with a live result is fine', () => {
assert.equal(runState('completed', null, 3 * 86400)[0], 'completed');
});
test('completed but expired is its own state', () => {
const [state, detail] = runState('completed', null, -7200);
assert.equal(state, 'expired');
assert.match(detail, /2\.0 hour\(s\) ago/);
});
test('timed out is distinguished from failed and canceled', () => {
assert.equal(runState('timed_out', null, null)[0], 'timed_out');
assert.match(runState('failed', 'syntax error at or near FROM', null)[1], /^syntax/);
assert.equal(runState('canceled', null, null)[0], 'canceled');
});
test('all completed but the schedule has stopped', () => {
const [state, detail] = verdict(Array(8).fill('completed'), 456, 168, true);
assert.equal(state, 'missing');
assert.match(detail, /stopped producing runs/);
});
test('completed runs with no subscriber are not clear', () => {
assert.equal(verdict(['completed'], 6, 24, false)[0], 'email_only');
assert.equal(verdict(['completed'], 6, 24, true)[0], 'clear');
assert.equal(verdict([], null, 24, true)[0], 'silent');
});
FAQ
What are the terminal states of a scheduled query run?
completed, canceled, failed and timed_out. Only completed produces a result file. Treat the other three separately: failed usually carries a message in error, timed_out means the execution budget was exceeded, and canceled is normally a person who stopped it.
Why does a query that worked for a year start timing out?
Because nothing about the query changed and everything about the data did. A query that scans all history gets slower every day. Once it crosses the execution budget it fails every run from then on, so re-running it is not a fix; bounding what it scans is.
A run says completed but the file link is dead. Why?
result_available_until has passed. Results expire on their own clock regardless of whether anyone downloaded them, so a run can be a genuine success with nothing left to fetch. Any workflow that downloads on a schedule rather than on receipt has to treat that timestamp as a deadline.
How do I detect a schedule that just stopped?
By arithmetic, not by status, because there are no failed runs to find. Take the newest run's data_load_time and compare it against the cadence you expect. Twice the cadence with no run is a schedule that has stopped, not a quiet week.
Should I keep using email delivery?
Use it if people want it, but do not rely on it as the signal. Subscribe an endpoint to sigma.scheduled_query_run.created and download data.object.file.url from the files API on receipt. A webhook that stops arriving is something your monitoring can notice; a missing email is not.
Related field notes
- A report run fails after the 200 and no CSV lands
- A report run past data_available_end returns short data
- Events fire that no endpoint subscribes to
Sources
Every figure in this note is traced to one of these. Prices are list rates and change — check them for your own region before acting.
- The scheduled query run object — Stripe API reference
- Schedule queries — Stripe Docs
- Types of events — Stripe API reference
- Receive Stripe events in your webhook endpoint — Stripe Docs
If your setup is misbehaving in a way this note does not cover, message me on LinkedIn with what you are seeing.