An event-driven operational automation system that uses AI to classify, route, and pre-validate incoming work requests — replacing manual triage with intelligent, auditable, two-tier review pipelines.
Built entirely within a cloud data warehouse (no external compute), this architecture demonstrates how to combine change data capture, AI classification, scoring-based routing, and automated issue validation into a fully autonomous operations pipeline.
Operational teams drown in incoming requests: IT tickets, change requests, service inquiries, data issues. Each one needs to be:
- Classified — What type of request is this? What system does it affect?
- Prioritized — Is this urgent, or can it wait?
- Routed — Who should handle this? Which specialist has the right expertise?
- Validated — Is the reported issue real? Can we confirm it before assigning someone?
- Tracked — What's the status? Who's responsible? Is it done?
Today, this triage is manual — a senior team member reads each request, mentally classifies it, picks an assignee based on institutional knowledge, and tracks progress in their head or a spreadsheet.
The result:
- Inconsistent routing (depends on who's triaging that day)
- Delayed response (waits for a human to read and classify)
- No data on routing accuracy (was the right person assigned?)
- Senior talent spent on triage instead of problem-solving
┌─────────────────────────────────────────────────────────────────┐
│ Source System (ITSM/Ticketing) │
│ Incidents · Change Requests · Service Tasks │
└──────────────────────────┬──────────────────────────────────────┘
│ ETL (periodic sync)
▼
┌─────────────────────────────────────────────────────────────────┐
│ Data Warehouse (Landing Table) │
└──────────────────────────┬──────────────────────────────────────┘
│ Change Stream (CDC)
▼
┌─────────────────────────────────────────────────────────────────┐
│ Scheduled Task Chain (DAG) │
│ │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ Classify │──►│ Route │──►│ Validate │──►│ Notify │ │
│ │ (AI) │ │ (Score) │ │ (AI+SQL) │ │ (Optional)│ │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │
│ │
└──────────────────────────┬──────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ Operational Dashboard + Review Queue │
│ Architect review · Status tracking · Accuracy metrics │
└─────────────────────────────────────────────────────────────────┘
Key architectural property: Everything runs inside the data warehouse. No external services, no additional infrastructure, no API keys to manage. The LLM, the task scheduler, the stream processing, and the dashboard all live in the same platform.
The pipeline uses change data capture (streams) rather than polling the full source table. This means:
- Zero compute cost when nothing is happening
- Near-real-time processing when new requests arrive
- No risk of reprocessing old records
- Natural exactly-once semantics
Classification uses a deterministic-first, AI-fallback pattern:
- First: pattern matching (subject line prefixes, known keywords) — zero-cost, 100% accurate when patterns match
- Then: AI classification for ambiguous cases — handles novel phrasing, multi-topic requests
- This avoids wasting LLM calls on easily-classifiable requests
Team member routing uses a weighted scoring algorithm, not hard-coded rules:
- Config item match: high weight
- Keyword match: moderate weight per keyword
- Product line match: moderate weight
- Highest scorer gets the assignment
This is more resilient than rules because:
- Adding a new team member = adding their skills to the scoring table (no code changes)
- Changing expertise areas = updating weights (no pipeline rebuild)
- Ties are broken deterministically
Every request goes through a senior reviewer before assignment to an implementer:
- Architect reviews, documents root cause, defines acceptance criteria, and recommends an engineer
- Engineer receives a structured handoff (not a vague ticket)
This prevents junior engineers from receiving unscoped work and ensures quality documentation.
For data-related requests ("this report shows wrong numbers"), the system:
- Uses AI to generate a validation query based on the issue description
- Executes the query to confirm/deny the issue exists
- Attaches the validation result to the review record
This eliminates "phantom issues" (tickets where the user was wrong) before they consume engineering time.
| Document | What You'll Learn |
|---|---|
| Event-Driven Pipeline | Stream → Task Chain design, scheduling, idempotency |
| AI Classification & Routing | Classification strategy, scoring algorithm, team routing |
| Two-Tier Review Model | Architect/engineer separation, structured handoffs, state machine |
| Document | What You'll Learn |
|---|---|
| Feedback Loops | How to measure and improve AI routing accuracy over time |
| Issue Replication | AI-generated validation queries, safe dynamic SQL execution |
| Dimension | Script/POC | This System |
|---|---|---|
| Triggering | Manual run or cron | Event-driven (stream + guarded task) |
| Idempotency | Re-run = duplicates | Every script is CREATE OR REPLACE; fully rerunnable |
| Failure handling | Pipeline stops | Exception handling per step; partial failures don't block |
| Routing logic | Hardcoded names | Configurable scoring table; no code changes for team updates |
| Accuracy tracking | None | Built-in accuracy measurement (predicted vs actual disposition) |
| Human oversight | None | Dashboard-first design; architects review before engineers act |
| Audit trail | None | Full state machine with timestamps and ownership at every transition |
| Layer | Technology |
|---|---|
| Processing | Cloud data warehouse (stream + task native) |
| AI Classification | Built-in LLM (no external API) |
| Routing | SQL scoring functions |
| Validation | AI-generated SQL + dynamic execution |
| Dashboard | Native BI dashboard (10-tile operational view) |
| Notification | Optional email integration |
| Languages | SQL (entire pipeline) |
- Eliminates manual triage — classification and routing happen automatically within minutes of ticket creation
- Structured handoffs — engineers receive documented root cause, affected objects, and acceptance criteria (not vague ticket descriptions)
- Measurable accuracy — routing accuracy tracked and improvable via feedback loops
- Zero additional infrastructure — runs entirely within the existing data warehouse platform
- Fully idempotent deployment — any script can be re-run safely at any time
I'm Amar Chatterjee — I designed and built this system to automate operational triage for a finance data engineering team. The architecture eliminates manual classification and routing while maintaining senior oversight via the two-tier review model. The pattern is generalizable to any team receiving structured work requests that need classification, routing, and validation.
MIT — see LICENSE for details.