01 — Root CauseA window that never overlapped with the data

Commercial Ops needed real-time Slack pings when a case came in, when a customer replied, or when a case got reassigned. The job ran hourly against a Siebel activity view and looked back 60 minutes for anything new. It almost never found anything — not because nothing was happening, but because the source view itself, gccx_actv_svcreq_uv_vw2, had a roughly 4.5-hour lag between an activity being created in Siebel and that activity appearing in the view.

A 60-minute lookback window run every hour was, structurally, always querying for data that hadn't arrived yet. Before touching the window size, I ruled out the obvious alternate explanation — timezone misalignment — by confirming the Spark session and all timestamp fields were consistently in UTC. The lag was real, not a comparison artifact.

02 — Alert TypesThree triggers, one team

Once the timing was right, three alert types covered the actual workflow gaps analysts were hitting:

  • New Case — an inbound email on an Open case assigned to a team member.
  • Customer Replied — an inbound email on a Pending Customer Reply case, arriving at least 5 minutes after the last outbound, to filter out auto-acknowledgments.
  • New Case Assigned — ownership changed from the shared COMMERCIAL_SUPPORT queue to a named analyst.

03 — Message FormatConsistent, scannable, directly actionable

All three alert types share one Slack Block Kit layout: an @mention above the header so the ping actually reaches the right person, then two-column fields — Case Number / Assigned To, Case Status / Date — followed by a description section. A SLACK_USER_MAP resolves emp_full_name to a display name, and a separate SLACK_MENTION_MAP resolves it to the actual <@USERID> so the mention pings, rather than just prints, the analyst's name.

04 — DeduplicationThree layers, because a wide window invites repeats

Widening the lookback window to 12 hours solved the missed-alert problem but created a new one: the same activity could now qualify in several consecutive hourly runs. Three layers of protection handle that:

  • A persistent watermark file (slack_alert_watermark.json) stored in the workspace, not DBFS — Serverless compute blocks DBFS writes, so the watermark had to live somewhere the job could actually reach.
  • Per-send writes — the watermark is updated after every individual successful send, not once at the end of the batch. A mid-run infrastructure crash can't cause the next run to resend everything, because everything sent before the crash is already marked.
  • Cross-alert priority — if one case qualifies for more than one alert type in the same run, only the highest-priority one fires: Assigned before Customer Replied before New Case.

The job itself also runs with max_retries: 0, so the platform never silently reruns a job that already sent alerts before failing.

Widening a window to catch missed data is the easy half of the fix. Making the widened window safe to rerun is the other half — and it's the half that actually gets tested by a real crash.

05 — The Duplicate IncidentWhat happens when the watermark doesn't save

The design got its real test when an INTERNAL_ERROR killed the job mid-run before the batch-end watermark save. The next hourly run had no record of what had already gone out, so it resent all 17 alerts from the crashed run.

The fix followed directly from the failure mode: move the watermark save from batch-end to per-send, move the watermark itself from a DBFS Delta table to a Serverless-compatible workspace JSON file, and set max_retries: 0 at the job level so a failure doesn't trigger an automatic rerun on top of a partial send. The 17 already-sent alerts were manually seeded into the new watermark file so the fix didn't itself cause one more duplicate wave.

→ Failure handling

Slack API failures and infrastructure crashes both raise a RuntimeError, which marks the job FAILED and fires the existing job-level email alert — no retry, no silent resend, and no separate Slack failure notification layered on top of a channel that's already getting real alerts.

06 — TakeawaysThe window size is a symptom, not the disease

  • Measure the lag before touching the window. A missed-alert bug is often a mismatch between schedule and source latency, not a logic bug.
  • A wider window needs an equally wider safety net. Catching more data means the same record can qualify more than once — plan the dedup layer at the same time as the window fix, not after the first incident.
  • Save state incrementally, not at the end. Batch-end saves assume the batch finishes. It won't, eventually.
  • Let failures fail loudly. A job that quietly retries after a partial send is how you get 17 duplicate pings in a support channel.

None of this is exotic engineering. It's the same lesson as most reliability work: find out what's actually true about your data's timing, then build the retry and dedup logic to match that truth — not the schedule you wish were true.