Early Warning Databricks app: giving a school principal Monday morning back

[Santhimaddipudi]

Early Warning: giving a school principal Monday morning back

Built for the Databricks Community Contest — Genie Powered App Challenge, Track A

The problem

Every Monday, a handful of people at a high school ask the same six questions. Which students crossed 10% absence this month? Is 9th grade attendance getting worse? Who has repeated unexcused absences with no counselor contact logged? Right now, the answer is an email to a district analyst and a wait of a day or two for a spreadsheet — by which point the students who needed a call home on Monday are getting it on Wednesday, if at all.

Chronic absence is the single strongest early predictor of dropout. It is also one of the most mechanically simple things to compute correctly — and one of the easiest to get wrong in a way that looks right. That combination is what made it the right domain for this challenge: the audience genuinely cannot write the SQL, the questions are the same every week, and the cost of the delay is not abstract. It's a kid who doesn't get a phone call.

Early Warning removes the analyst from that loop. It is a natural-language attendance app for one school's front office, and every number it shows comes from a live Genie conversation against real Unity Catalog tables — not a dashboard, not a cached report. Take Genie out of it and there is nothing left: no briefing, no follow-up, no evidence behind a single name on a call sheet.

Who it's for

One school. A principal, a counselor, an attendance clerk. Not a district office, not an analyst — people who ask a question in plain English and need an answer they can act on before the day is over, not a table they have to interpret.

The three-mode loop

Most "chat with your data" entries stop at the chat. The part of the rubric that actually rewards effort is whether the app moves someone from a question to a decision inside the product. Early Warning does that in three modes, all on one Genie conversation:

Briefing. On load, five curated questions fire against Genie and render as cards — the Monday-morning summary the front office would otherwise wait days for: who crossed the chronic-absence line, where attendance is trending, who has unexcused absences with no outreach behind them, which grade needs attention, and the weekly pattern. Every card shows the exact question that produced it, so provenance is never in doubt.

Ask. A free-form box for whatever the briefing didn't cover, on the same conversation, so a follow-up like "and just the 9th graders" resolves against what was just asked — no re-explaining context.

Act. Any student row from any answer, briefing or ad hoc, can be added to an intervention call sheet with a reason attached. It's a real write, through the SQL warehouse, to a Delta table — and it downloads as a CSV a clerk can work from that afternoon. This is the mode that turns an answer into something that happened.

Architecture

Data model

Synthetic but realistic — one school, ~400 students, one academic year:

Table	Purpose
students	id, grade, enrollment date, home language, transport mode, program flags
attendance_daily	one row per student per school day: present / absent excused / absent unexcused / tardy
school_calendar	instructional days, holidays, testing days, weather closures
contact_log	counselor and clerk outreach: date, student, method, outcome
interventions	active support plans with start date and type
grades_term	term GPA per student, so attendance can be tied to outcomes

The app itself contains no attendance logic — no formula for absence rate, no join to the calendar table, nothing. Its only jobs are to ask Genie the right questions, render whatever comes back, and let staff write selected rows to a call sheet. Everything a principal sees is something Genie computed, live, against students, attendance_daily, school_calendar, contact_log, interventions, and grades_term — six tables covering one academic year for 400 synthetic students and roughly 70,000 attendance rows.

Two signals are planted in the synthetic data on purpose, and the app is never told about either: a cohort of 26 ninth graders whose attendance falls off a cliff the week school returns from winter break, and a tardy spike on one bus route starting the exact week its route changed. Genie has to find both from the raw data, on request, the same way it would have to find a real pattern in a real school's numbers.

What makes it more than a text-to-SQL demo

Attendance is a domain where a plausible-looking wrong answer is the default outcome, not an edge case. A model that divides absences by a raw date range instead of instructional days returns a number that looks completely reasonable and is wrong for every enrolled student. Getting the Genie space's instructions right was where most of the actual engineering went:

• Chronic absence means 10% or more of instructional days a student was enrolled for — not calendar days, not the full school year for a student who started in October.
• A tardy counts as present. Left undefined, tardies got folded into the absence count and quietly inflated every rate in the school.
• Every attendance calculation has to join to school_calendar and respect is_instructional_day — weekends, holidays, and testing days are not absences waiting to happen.
• A student's denominator starts at their own enrollment date. Eighteen students in the synthetic data enroll late specifically to catch a denominator computed from the wrong start date.

None of this is enforced by the schema. It's enforced by instructions written into the Genie space, and it's the difference between an app that answers questions and an app that answers them correctly.

The guardrail that's a design decision, not a limitation

The Genie space is explicitly instructed not to characterize students — never "troubled" or "at risk," only facts: "12 absences," "below 90% attendance." And it's instructed to refuse individual-level breakdowns by demographic field: asked which ethnic group has the worst attendance, it returns the aggregate pattern and nothing else — no student named in that context. Nothing in the data model forces this. It was a deliberate choice about what this kind of tool should and shouldn't be willing to say about a child, and it's the paragraph of this write-up most likely to be remembered.

What deploying this actually taught me

The data generator broke silently before it broke loudly. The notebook that builds the synthetic school year used # MAGIC %md section headers followed directly by code, with no # COMMAND ---------- separator between them. Databricks folded each header's code into the markdown cell and rendered it as flattened text — nothing after the first two cells ever ran as Python. The job still reported SUCCESS, because nothing had thrown an exception; nothing had executed either. The fix was mechanical once diagnosed — insert the missing cell boundaries — but it's a reminder that a green checkmark on a notebook job is not the same claim as "the tables got built."

On-behalf-of-user auth looked like the right call and wasn't, in practice. The natural design has each principal's own Databricks identity flow through to Genie, so query access follows real permissions. Databricks Apps supports this via OAuth scopes (genie, sql) that the signed-in user consents to. In this workspace, that consent never actually landed on the forwarded token — even after the documented fix (declare the scope, restart the app, force a fresh sign-in). The real failure, a 403 straight from the Genie API, made the tradeoff decision easy: the app now runs on a single service credential — a personal access token stored as a Databricks Apps secret resource — rather than keep fighting a Public Preview consent flow under a deadline. For a one-school pilot with a handful of staff sharing one login context, that's a reasonable trade. It would need revisiting before this became a multi-school product with real per-user access boundaries.

The instructions file earned more of the score than the app code did. Every one of the bugs above was invisible from the Streamlit side. The app just renders whatever Genie returns; the actual correctness — chronic absence computed right, tardies not miscounted, late enrollees handled — lives entirely in the Genie space's instructions and the table/column comments in Unity Catalog. That's the intended shape of a Genie-powered app, and it's worth saying plainly: the least glamorous file in this repo is the one doing the most work.

Try it yourself

Open cold, run the briefing, and watch five cards fill in one at a time — point out that each one names the question that produced it. Switch to Ask, type "whose attendance dropped after winter break," get the 26-student cohort. Follow up with "and are any of them already in an intervention" to show the conversation carrying context. Add four names to the call sheet with a reason, open the Call sheet tab, download the CSV. Close by asking which demographic group has the worst attendance, and show Genie hand back the aggregate with no names attached — the guardrail, working exactly as instructed.