01 — The AskWhich vehicles are fine, and which aren't

Partner operations teams running vehicle containment programs need one recurring answer: of everything enrolled, what resolved cleanly on its own, and what's stuck in an unresolved or failed state and needs a human to look at it. That's the entire report — two categories, one export, on a schedule.

The whole thing is a two-cell Python notebook: install a dependency, run two SQL queries against a shared source, write a dated Excel file to the workspace. No orchestration framework, no unnecessary moving parts.

02 — One CTE, No OverlapRanking each vehicle's latest run

Both queries build on the same common table expression, which ranks each vehicle's most recent processing record across all historical runs. Window functions handle the deduplication and forward-fill any missing reference fields, so every vehicle shows up exactly once across the two output tabs — never zero times, never twice.

That guarantee — exactly one row per vehicle, split cleanly between two tabs — is what makes the report trustworthy to a partner ops team that isn't going to double-check the SQL. If a vehicle can silently appear in both tabs or neither, the report stops being a source of truth.

03 — Health ClassificationTwo different time windows for two different questions

A CASE expression maps a fixed set of known failure resolution codes to "Unhealthy"; everything else is "Healthy" by default, rather than trying to enumerate every possible success path.

The two tabs use deliberately different time scoping, because they're answering different questions. The healthy tab is scoped to a rolling lookback window, filtering out stale resolved records that aren't relevant anymore. The unhealthy tab is scoped strictly to the most recent run date — yesterday — so it always reflects exactly yesterday's batch output, with no historical unhealthy records lingering in the view.

A healthy record can age out of relevance. An unhealthy one shouldn't — it should show up once, get worked, and leave. The two tabs need different clocks because they're tracking different lifecycles.

04 — Catching Bad Data FirstA print statement that earns its place

Before the file writes, a diagnostic check flags any records with a missing partner reference. It's a single print statement, not a formal validation framework — but it surfaces a real data quality issue before it reaches report consumers, rather than showing up as an unexplained blank cell in a partner's spreadsheet.

05 — The Output FileFormatted so it reads like a report, not a data dump

The Excel output is built programmatically with openpyxl: bold styled headers, full cell borders, auto-fitted column widths, and two named tabs. The filename is date-stamped at generation time, so every run produces its own versioned artifact rather than overwriting the previous one.

→ Compute

Runs on Serverless CPU, Python and Spark SQL only — appropriate for a lightweight, scheduled export where the goal is a clean file landing on time, not a heavy compute job.

06 — TakeawaysSmall reports deserve the same rigor as big pipelines

  • Share the ranking logic, split the filter. One CTE for "what's the latest state," then two different time filters for two different audiences.
  • Guarantee exactly-once, not just correct. A vehicle appearing in both tabs or neither is a worse failure mode than a wrong classification.
  • A print statement is a valid data quality gate when it runs before the artifact ships and someone's actually watching the logs.
  • Formatting isn't cosmetic when the consumer is a partner ops team opening the file cold, with no context on how it was built.