Design a Reporting & Scheduled Jobs Framework

Problem statement

Design a reporting and scheduled-jobs framework in which users define recurring queries or analytics scripts, the system executes them on a schedule against a warehouse or lake, produces PDF, Excel, CSV, or HTML output, and distributes results by email, Slack, or secure dashboard link. The product must author and version report definitions, evaluate schedules across time zones and daylight-saving transitions, decide what happens when a fire time is missed, acquire execution capacity under tenant quotas, run queries with bounded cost, render artifacts deterministically, deliver to subscribed recipients with per-channel retry, and retain auditable evidence of every run and delivery.

This is not a wrapper around a cron daemon. A single-node cron process loses triggers during restarts, double-fires after a failover, and cannot express concurrency policy, misfire policy, or tenant fairness. The design therefore separates trigger correctness from execution success from delivery success. Trigger correctness is an invariant: for every definition and every wall-clock slot in its schedule, exactly one run row is created, or an explicit SKIPPED or MISSED record explains why. Execution success is a retryable workflow with leases, quotas, and timeouts. Delivery success is an at-least-once channel pipeline made effectively-once by a per-recipient delivery ledger.

Why the problem is distinctive

A notification service can retry a push. A reporting system must decide whether a report that is forty minutes late should still be sent, sent with a staleness banner, or suppressed because the recipient already received a newer one. Time itself is adversarial: 2:30 AM does not exist on spring-forward day and exists twice on fall-back day; a scheduler that stores UTC instants without a wall-clock policy silently double-sends or skips. Warehouse compute is a shared, billable resource, so an unchecked report is a denial-of-wallet attack on its own tenant. The attached brief requires a scheduling layer, a query runner, output formatting, and distribution, plus scalability, resource management, reliability, and a status UI. Public systems prove the category: Airflow was created at Airbnb to schedule and monitor data pipelines; Shopify publicly reports running more than 150,000 Airflow runs per day with roughly 400 concurrent tasks; Netflix's Genie orchestrates several thousand big-data jobs daily processing hundreds of terabytes; Pinterest built Pinball as a MySQL-backed workflow manager; and Looker ships scheduled dashboard deliveries as a first-class product feature.

The four architectural planes

1. Scheduling control plane: definition registry, schedule compiler, slot watermarking, misfire and concurrency policy, distributed trigger generation.
2. Execution plane: run queue, worker leases, warehouse admission and quotas, query runner, result materialization.
3. Delivery plane: render farm, artifact store, channel adapters (email, Slack, link), delivery ledger, preferences and suppression.
4. Governance plane: versioning, RBAC and row-level security, audit, retention, cost attribution, SLA monitoring.

A strong interview answer keeps these planes separate: a render outage must not corrupt scheduling watermarks, and a warehouse throttle must not destroy the delivery ledger.