Northwind Ambient Ops — BI Engineer take-home

Reconciliation of the Q2 FY26 Ambient Ops review, an Audit Triage Workbench (Retool app), and a stranded-escalation recovery workflow (Retool Workflow), with the reasoning written down.

| Deliverable | Where | |---|---| | Part 1 — reconciliation, waterfalls, Decisions A/B | RECONCILIATION.md | | Part 2 — Workbench rationale, interaction count, keymap | UX_RATIONALE.md · Retool app: ▶ link | | Part 3 — recovery workflow, audit trail DDL, reconciliation query | sql/05_recovery/ · Retool workflow: ▶ link | | Stakeholder conflicts | DECISIONS.md | | Architecture decisions | docs/adr/ | | AI disclosure | AI_USAGE.md | | Checklist | SUBMISSION_CHECKLIST.md | | Video | ▶ link |

Quick start for a new joiner

Prerequisites: Node 22, PostgreSQL 15+ (or Retool Database), the seven CSVs from the assessment packet placed in data/ (not committed; they are the packet's, not mine).

npm ci
npm test                      # 52 unit tests: tz boundary, dedupe, effective-dated lookup, idempotency key, backoff, scoring
npm run build:retool          # -> dist/nwops.js, the library Retool loads

# Local Postgres (any 15+; the SQL is plain PostgreSQL, not ported)
createdb nwops
psql nwops -f sql/00_load/schema.sql
cd data && for t in note note_audit clinician mds sla_config rubric_weight escalation; do psql nwops -c "\copy $t FROM '$t.csv' CSV HEADER"; done; cd ..
psql nwops -f sql/02_provided/PROVIDED_QUERIES.sql          # control: reproduces the memo exactly (1,705 / 79.9% / 12.8% / 149 / 28)
psql nwops -f sql/03_corrected/00_canonical_views.sql       # the corrected building blocks
psql nwops -f sql/04_workbench/01_triage_tables.sql -f sql/04_workbench/00_queue_view.sql
psql nwops -f sql/05_recovery/01_recovery_tables.sql -f sql/05_recovery/02_detect_and_claim.sql
for f in sql/01_profile/*.sql sql/03_corrected/0[1-6]*.sql; do psql nwops -f $f; done   # every number in the docs

Retool setup (database import, views, app queries, workflow blocks, release, sharing) is in docs/retool-build-guide.md.

Repository map

sql/00_load/         schema.sql as shipped (no keys — see ADR-003)
sql/01_profile/      grain, uniqueness, derived-column and stranded-pattern checks (RECONCILIATION §1)
sql/02_provided/     the five original queries, untouched, as the control
sql/03_corrected/    00 canonical views · 01–05 one waterfall per headline metric · 06 downstream coverage
sql/04_workbench/    triage_decision DDL · v_triage_queue (R10, R5 provenance)
sql/05_recovery/     recovery_* DDL (W7) · detect+claim (W1, W6) · per-attempt bookkeeping · reconcile (W8)
src/                 pure JS modules: businessDay, dedupeNotes, effectiveDated, idempotencyKey, backoff, scoring
test/                Vitest boundary tests, run in GitHub Actions
dist/nwops.js        the bundle Retool imports as a Library (built, committed; published to npm as @nahidreza99/nwops)
api/slack-mock.js    Vercel function: fail-on-demand endpoint for demonstrating backoff and dead-lettering
docs/adr/            three ADRs
docs/                retool-build-guide.md

Conventions that matter

• The quarter is 1 Apr – 30 Jun 2026 in the America/Chicago business day, as the half-open UTC interval [2026-04-01 05:00, 2026-07-01 05:00). v_quarter is the single source.
• Join v_note_canonical, never note. note is one row per ingestion; 62 notes have two.
• Read composite/pass_fail from v_audit_scored, not from note_audit. The stored columns are wrong on 206 in-quarter rows (ADR-001).
• SLA targets are effective-dated. v_note_sla joins the row in force on the note's Chicago submit date.
• Every number in the markdown deliverables is produced by a named file under sql/. Run them.

Retool

• App: ▶ URL · Releases: Retool's Free plan has no Releases feature, so the release chain is RELEASES.md (Retool History point ↔ git tag ↔ npm version), with the version shown in the app footer.
• Workflow: ▶ URL
• Library: npm package @nahidreza99/nwops (published by CI on each GitHub Release). App loads https://cdn.jsdelivr.net/npm/@nahidreza99/[email protected]/dist/nwops.js; workflow lists "@nahidreza99/nwops": "0.1.2" in its package.json.
• Recovery endpoint (W2): https://webhook.site/b32fb8db-acad-4c66-a469-cb7d4b247e03 for the happy path; https://commure-bi-engineer.vercel.app/api/slack-mock?fail=429|500|flaky=1 to demonstrate backoff and dead-lettering.

Hours

▶ Honest log, by day, totalled. Stop at 10.

What is wrong with this assessment (≤150 words)

▶ Draft, in the candidate's voice — edit freely:

It measures forensic SQL and tool fluency well, and reasoning under ambiguity better than most. It does not measure the part of a BI Engineer's job that decides whether any of this gets used: discovering what the Ops Director actually means by "see the whole queue", negotiating a metric definition that QA, Product and Compliance will all sign, and keeping that definition stable across quarters in a semantic layer that other people query. The 10-hour cap combined with a keyboard-first 3,000-row Retool UI rewards prior Retool muscle memory over BI judgment; a candidate who has never used Retool spends a third of the budget on component APIs. I would replace Part 2's UI constraints with a 30-minute stakeholder interview role-play, and add a "here is last quarter's number, defend or retract it in front of the VP" exercise, because that is the conversation the memo's author never had.