Freshness SLA Breach Hidden by Volume-Only Monitoring
A subscription analytics team publishes a customer-usage mart every morning. The SLA says the mart must be refreshed by 06:00 UTC. The pipeline depends on an Oracle extract that starts at 02:00 and normally takes 90 minutes, a cloud workflow that runs transformation jobs, and a BI extract. In the last two weeks, four SLA breaches occurred, each 40–90 minutes late. Monitoring alerts on row-count deviation, not freshness, so the team learned about breaches from users. The Oracle extract's runtime has grown from 70 to 140 minutes as a source table grew, and the workflow retries a failed task up to three times with a 15-minute backoff. Two of the four breaches coincided with a 03:00 maintenance window on the source database. The team proposes adding more retries and moving the SLA to 08:00.
1. Why did monitoring fail to detect the breaches, and what would you instrument instead?
Read the worked answer
Row-count deviation answers 'did we load a plausible volume', not 'is the data current'. A pipeline can load a full partition of stale data and pass a volume check. The mart's contract is time-based, so instrument freshness at each boundary: source extract completion time, workflow task completion, mart table max(load_timestamp), and BI extract completion, all compared with the 06:00 SLA. Emit a single freshness SLI per stage and an end-to-end SLI, and alert on the end-to-end SLI burn rather than on individual task retries, because retries are a symptom. Also record the source's high-water mark, such as max(updated_at) in Oracle, so you can distinguish 'job late' from 'job on time but no new data'. Publish the freshness status where users see the dashboard, with the last successful refresh and the data-through timestamp. Add a synthetic canary row with a known timestamp to verify end-to-end visibility, not just job success.
2. Evaluate the two proposals-adding retries and moving the SLA to 08:00-and recommend an alternative.
Read the worked answer
Adding retries treats the runtime growth as transient. The extract has doubled from 70 to 140 minutes as a source table grew; three retries with 15-minute backoff can add 45 minutes or more and still fail if the cause is volume or a source lock during the 03:00 maintenance window. Retries also make the SLA less predictable and can overlap the maintenance window, worsening contention. Moving the SLA to 08:00 may be legitimate if business consumers agree, but it hides a 140-minute trend that will breach 08:00 later; it is a negotiation, not a fix. Better: profile the extract, move incremental logic to a watermark on an indexed column, shift the schedule before the maintenance window or exclude that window, and parallelize or partition the large table read. Set a runtime budget of 90 minutes with alerting at 75 minutes. If the SLA must change, do it with a documented capacity plan and a review date, and publish the actual refresh time meanwhile. The decision should be based on consumer need and trend, not on masking retries.
3. Define the ownership and contract changes needed so freshness is enforceable.
Read the worked answer
Freshness is a cross-team property, so assign a single accountable owner for the end-to-end SLI and stage owners for each boundary. Write an internal data contract with the source team: data-through timestamp expected by 03:30, extract completion by 04:30, mart publication by 05:30, with a 30-minute buffer before the 06:00 SLA. Include escalation paths and a maintenance-window calendar so the workflow does not retry into a known outage. Instrument the contract as automated checks, not dashboards only, and store results with timestamps so breach history is auditable. Define breach severity by consumer impact-board reporting versus operational dashboards-and require a post-incident note for repeated breaches. Review the contract monthly against runtime trend and source growth. Finally, make the SLA visible to consumers with a data-through timestamp; an SLA that users cannot verify is not enforceable, and the team will keep learning about breaches from users instead of monitoring.