Aligning AI agents for analytics dashboard generation

The Problem: When AI Agents Know SQL But Not Your SQL

“Generate a dashboard from a business requirement.” It sounds like a natural-language-to-SQL problem. It isn’t.

In a typical enterprise analytics platform, a single dashboard widget is not just a chart. It is a contract that must simultaneously satisfy six different runtime systems: an API gateway that validates payloads, a schema registry that enforces type mappings, a SQL extraction layer that pulls raw data, an aggregation engine that computes measures, a query compiler that executes the SQL, and a browser-based rendering client that turns data into pixels.

The painful part is that these systems validate at different times. The API gateway catches malformed payloads immediately, so the agent gets an error and can fix it. But the query compiler, the aggregation engine, and the rendering client only run when a user opens the dashboard. By then, the AI agent that generated it is long gone.

Six layers of widget execution

Warning: Layers 1–2 provide immediate feedback: the widget is rejected before it is saved. Layers 3–6 produce silent failures: the widget is saved successfully, looks valid in every file, and only breaks when a human opens it in a browser. The AI agent never sees the error. This asymmetry is the core engineering challenge — and the root cause of three distinct classes of hallucination.

The three hallucination classes that emerged were not random bugs. They were systematic alignment failures traceable to gaps between how the agent was trained and what the runtime actually requires:

• Column name invention — the agent generates plausible field names that do not exist in the actual schema
• SQL dialect mismatch — the agent defaults to BigQuery or PostgreSQL syntax when the query engine runs a different dialect
• Requirement misinterpretation — the agent produces valid SQL against a real schema but answers the wrong question

---

What Naive Generation Actually Produced

Before building any infrastructure, the obvious approach was to give the agent a business requirement, the widget JSON schema, and the platform-specific rules, then ask it to generate a widget. Here is what happened.

Incident: The widget that passed everything: The agent was asked to generate a bar chart of tickets grouped by priority. It produced clean JSON. It passed the API gateway. It was persisted to the platform. The dashboard opened and showed nothing — a blank widget frame with a tiny error icon. The browser console said: Cannot read properties of undefined (reading 'split').

The cause? The x and y fields in the visualization config were strings instead of arrays of objects. The agent wrote "x": ["data.priority"] instead of "x": [{"reference_name": "data.priority", "label": "Priority"}]. The gateway did not validate this. The schema registry did not validate this. Only the JavaScript rendering client cared, and it crashed silently.

What the agent generated

{
  "visualization": {
    "bar": {
      "x": ["data.priority"],
      "y": ["data.count"]
    }
  }
}

What it needed to be

{
  "visualization": {
    "bar": {
      "x": [{"reference_name": "data.priority",
             "label": "Priority"}],
      "y": [{"reference_name": "data.count",
             "label": "Count",
             "color": {"type": "static",
               "static": "chart-category-1"}}]
    }
  }
}

Incident: The column that didn’t exist: The agent generated a query referencing ticket_priority when the actual column was severity_level. It also used assignee_name instead of owner_display_name. The query compiled locally because the schema registry silently mapped unknown fields to NULL. The widget rendered — but showed blank values for every row. No error, no warning. Just empty data where the chart should have had labels.

Incident: The SQL dialect trap: The agent used CAST(view_count AS INTEGER) and FLOAT type names, drawn from BigQuery and PostgreSQL conventions in its training data. The platform’s query engine requires BIGINT (not INTEGER), DOUBLE (not FLOAT), and TRY_CAST for safe type conversions. These queries passed the API gateway because the gateway does not compile SQL. They only failed at query execution time — when a user opened the dashboard and the engine rejected the syntax.

Note: The first version worked about 30% of the time. The other 70% produced widgets that passed every upstream check and silently rendered blank screens. The lesson: syntactic validity and production correctness are entirely different things. The agent was generating JSON that looked right to a human, passed schema validation, and was accepted by the API — but broke in the browser because the rendering client expected specific object shapes and type conventions that no upstream validator enforced.

---

Hallucination Class 1: Column Name Invention

Column name hallucination is the most common failure class. The agent’s training data contains millions of schema patterns. When given a domain like “support tickets,” it predicts plausible column names — ticket_priority, created_date, assignee_name — that match common patterns in training data but do not match the actual schema (severity_level, created_at, owner_display_name).

This is not a reasoning failure. It is an alignment failure: the agent’s internal model of what a “typical” support ticket schema looks like diverges from the platform’s actual schema.

Hallucinated vs actual column names

Hallucinated query

SELECT
  ticket_priority,
  assignee_name,
  created_date,
  resolution_time
FROM support_tickets
WHERE status = 'open'

Corrected query

SELECT
  severity_level,
  owner_display_name,
  created_at,
  time_to_resolution_seconds
FROM dim_ticket
WHERE stage_name = 'open'

Technique: Investigate Before Generating: The fix was requiring the agent to sample 10–50 rows of real data via a CLI tool before writing any SQL. Schema metadata alone is insufficient because it describes types but not actual values, naming patterns, or NULL rates. A schema might list a column as priority VARCHAR, but the data contains ["P0", "CRITICAL", "urgent"] — not ["p0", "p1", "p2"] as the agent would predict.

The skill file encodes this as a mandatory workflow step: “Before writing any SQL, run the data sampling command. Verify that the columns you plan to reference exist in the sample output. Check that enum values match what you expect. If a field has more than 20% NULLs, handle it with a safe type conversion in the extraction layer.”

The following diagram illustrates how the agent’s schema model, built from training data, diverges from the platform’s actual schema — and how mandatory data sampling bridges the gap.

Tip: Schema metadata lies. Real data tells the truth. The single most effective intervention against column name hallucination was making data sampling mandatory — not optional, not suggested, but a hard prerequisite in the skill workflow before any SQL is written.

---

Hallucination Class 2: SQL Dialect Mismatch

The second hallucination class involves the agent defaulting to SQL syntax from its dominant training data (BigQuery, PostgreSQL) when the platform’s query engine runs a different dialect. This is compounded by a two-layer query architecture where putting logic in the wrong layer causes silent runtime failures.

The SQL extraction layer (Layer 1) handles raw data extraction: SELECT, JOIN, WHERE, CAST, CASE, NULLIF, COALESCE. It runs the query engine’s SQL dialect. Output: shaped rows.

The aggregation engine (Layer 2) handles aggregation: column references, arithmetic, aggregate functions (COUNT, SUM, AVG), and window functions. It runs a restricted formula syntax — not full SQL. Output: chart-ready analytics.

Dialect mapping: agent defaults vs query engine requirements

Agent’s default (BigQuery/PostgreSQL)

SELECT
  CAST(view_count AS INTEGER),
  CAST(duration AS FLOAT)
FROM analytics_events
WHERE created_date > '2025-01-01'

Required (query engine dialect)

SELECT
  COALESCE(TRY_CAST(view_count AS BIGINT), 0),
  COALESCE(TRY_CAST(duration AS DOUBLE), 0.0)
FROM analytics_events
WHERE created_at > '2025-01-01'

The two-layer trap is the subtlest dialect issue. The agent must learn that SQL functions like NULLIF and CAST belong in the extraction layer, while aggregate functions like COUNT and AVG belong in the aggregation layer. Putting SQL functions in the aggregation layer causes silent render-time failures that no upstream validator catches.

NULLIF in the wrong layer

{
  "extraction": {
    "sql_query": "SELECT duration FROM tickets"
  },
  "measures": [{
    "sql_expression": "AVG(NULLIF(duration, 0))"
  }]
}
// Aggregation engine cannot execute NULLIF.
// Breaks silently at render time.

Pre-compute in extraction, aggregate in engine

{
  "extraction": {
    "sql_query": "SELECT NULLIF(duration, 0)
                  AS clean_duration FROM tickets"
  },
  "measures": [{
    "sql_expression": "AVG(clean_duration)"
  }]
}
// Complex logic in extraction layer.
// Aggregation gets a clean column reference.

Technique: Decision Trees Over Prose: Rather than explaining the two-layer architecture in paragraphs, the skill file encodes explicit decision trees: “Does the expression contain NULLIF, CAST, CASE, or COALESCE? It MUST go in the extraction layer. Does it contain COUNT, SUM, AVG, or OVER()? It MUST go in the aggregation layer.” No ambiguity. The agent follows a deterministic path instead of interpreting prose.

Warning: The API gateway does not compile SQL. It validates JSON structure and field names but passes the SQL query through as an opaque string. This means dialect errors and layer violations are invisible at creation time. The widget is saved successfully and only breaks at query execution time — when a user opens the dashboard.

---

Hallucination Class 3: Requirement Misinterpretation

The subtlest failure class: the agent produces valid SQL against a real schema but answers the wrong question. The widget works, the data is real, and the chart renders. It just shows the wrong thing.

Incident: The ticket age ambiguity: A user asked for “average ticket age” on a support team standup dashboard. The agent computed AVG(DATEDIFF(created_at, NOW())) across all tickets in the database. The user meant “average age of currently open tickets.” The dashboard showed 47 days instead of 3.2 days. Both queries are syntactically valid and run against the correct schema. The difference is a single WHERE clause — WHERE stage_name IN ('open', 'in_progress') — that the agent did not infer from the context.

What the agent built

-- "Average ticket age"
SELECT AVG(
  DATEDIFF('day', created_at, NOW())
) AS avg_age
FROM dim_ticket
-- No filter: includes all tickets
-- Result: 47 days

What the user meant

-- "Average age of open tickets"
SELECT AVG(
  DATEDIFF('day', created_at, NOW())
) AS avg_age
FROM dim_ticket
WHERE stage_name IN ('open', 'in_progress')
  AND is_deleted = false
-- Result: 3.2 days

Requirement misinterpretation breaks down into three sub-types:

Stage 1: Metric Definition Ambiguity

The agent interprets a business metric differently than the user. “Active tickets” could mean stage_name = 'open' or stage_name IN ('open', 'in_progress') or simply is_deleted = false. Without clarification, the agent picks the most statistically common interpretation from its training data — which may not match this organization’s definition.

Stage 2: Filter Scope Mismatch

The agent applies the wrong time range, team filter, or status filter. “This quarter’s tickets” gets interpreted as calendar quarter versus rolling 90 days. “My team” could reference a specific team ID, a department, or the currently logged-in user’s org unit.

Stage 3: Aggregation Granularity Error

The agent aggregates at the wrong level. “Tickets per team” gets rendered as a single total number instead of a grouped bar chart, or the agent groups by individual team member instead of team. The visualization type and the GROUP BY clause must align, and the agent has to infer that alignment from context.

Technique: Planning as Interactive Clarification: The fix was introducing a mandatory planning phase before generation. The planner skill walks the agent through a structured workflow: understand the user’s intent, discover candidate datasets, sample real data, assess viability through a PASS/WARN/BLOCK gate, and ask clarification questions when ambiguity exists. The plan — not the user’s raw request — becomes the generation prompt.

When the planner encounters ambiguity (“You mentioned ‘ticket age.’ Do you mean time since creation for all tickets, or time since creation for currently open tickets only?”), it surfaces the question to the user before proceeding. This interactive clarification, built into the skill workflow, prevents downstream misinterpretation more effectively than any amount of prompt tuning.

---

The Solution: Agent Skills as Alignment Mechanisms

An agent skill is a structured instruction set — a markdown file with metadata and a workflow body — that teaches an agent correct domain-specific behavior. Skills are not reference manuals. They are executable specifications that say: “First, you will do X. Then you will do Y. If Z fails, here is how to fix it.”

The system uses three specialized skills, each aligned with a phase of dashboard creation:

Stage 1: Planner Skill

Interprets natural language requirements, discovers candidate datasets, samples real data, assesses viability through a PASS/WARN/BLOCK gate, asks clarification questions when ambiguity exists, and produces a validated executable plan. Addresses requirement misinterpretation by forcing clarification before generation.

Stage 2: Widget Generator Skill

Takes a plan specification and produces a single widget artifact. Encodes the two-layer architecture rules, type mappings, dialect constraints, and visualization bindings. Addresses column hallucination through mandatory data sampling and dialect mismatch through inline decision trees.

Stage 3: Dashboard Assembler Skill

Takes validated widgets and composes a dashboard layout with grid positioning, tab hierarchies, shared filters, and drill-through navigation. Only works with pre-validated widgets — never raw requirements.

Each skill maps its teaching to the specific hallucination classes it prevents:

Four teaching pillars mapped to hallucination classes

A critical design decision was progressive disclosure. The skill body stays under 500 lines and covers the essential workflow. Detailed references — type mappings, error tables, templates — live in separate files loaded on demand. This prevents context window bloat while keeping comprehensive knowledge accessible.

Note: The initial attempt used a single mega-prompt with every rule packed into one system message. The agent could not hold all constraints simultaneously — it would get the SQL right but botch the type mappings, or nail the visualization structure but forget required fields. Decomposing into three specialized skills with focused contexts was the architectural fix that made reliability possible.

---

Designing the Validation Pipeline

The insight from the silent-failure problem: pull render-time errors forward into creation time. If the platform would not catch these errors until a user opened the dashboard, the system needed local validators that would catch them at generation time.

• Structural — JSON schema, required fields, valid enums, object shapes (<0.1s)
• Semantic — SQL layer rules, type mappings, dialect checks, viz structure (~0.5s)
• API Acceptance — Push to platform, check for creation errors (~1-2s)

Each semantic validation rule maps to a specific runtime layer that would otherwise fail silently:

Semantic validation rules

The shift from prompt-based to hook-triggered validation was the most impactful architectural decision:

Before: prompt-based validation

System prompt instruction: “After writing each widget JSON file, run the validation script.”

Result: The agent forgets ~30% of the time. When it does validate, it sometimes ignores errors and moves on. No guaranteed feedback loop.

After: hook-triggered validation

PostToolUse hook: Fires automatically on every file write matching widgets/*.json.

Result: Every write triggers validation. Errors returned as structured content. Agent must address errors to proceed. 100% enforcement, zero prompt reliance.

How each rule was born: None of these rules were designed upfront. Every single one came from a specific failure that was debugged in production. The CHART_ARRAY_STRUCTURE rule exists because of an hour spent staring at a blank bar chart before opening browser developer tools. The MISSING_REQUIRED_FIELD rule exists because the API error said “Missing required field: name” — but it meant the name field on each dimension object, not the widget’s top-level name. Every validation rule is a scar from a past incident.

Tip: When a hook detects a validation error, it does not just fail. It returns structured repair guidance: the rule that failed, the value found, the value expected, and a step-by-step fix. The agent reads the guidance, applies the correction, and rewrites the file — triggering validation again automatically. This is deterministic self-correction, not probabilistic “check your work.”

---

Iteration Log: Refining Skills Through Failure

The path from 30% to 91% success was not a single breakthrough. It was eight iterations, each driven by specific failure patterns observed in benchmark runs.

Iteration 1: Baseline — naive generation (Fail)

Change: Gave the agent a business requirement, the widget JSON schema, type mappings, and platform rules in one prompt. Asked it to generate a complete widget.

Result: 7 out of 24 benchmark widgets rendered correctly. The others had column name hallucinations, type cast errors, layer violations, and visualization binding failures.

Root cause: The agent could not hold all constraints simultaneously. It would get the SQL right but botch the type mappings, or nail the visualization structure but forget required fields.

Action: Split into a multi-skill architecture with sequential workflow steps.

Iteration 2: Schema-only generation (Fail)

Change: Gave the agent the dataset schema (column names, types, enum values) and let it generate from metadata alone, without requiring data sampling.

Result: The schema said priority enums were ["p0", "p1", "p2"], but the actual data contained ["P0", "CRITICAL", "urgent"]. Fields listed as non-null had 68% NULL rates in practice.

Root cause: Schema metadata describes structure, not reality. Column names matched the schema, but enum values, NULL rates, and actual data patterns diverged.

Action: Added mandatory data sampling step to the widget generator skill: “Sample 10–50 rows before writing any SQL. Verify fields exist, check NULL rates, confirm enum values.”

Iteration 3: Dialect fixes in separate reference file (Fail)

Change: Created a type-mappings.md reference file with the complete dialect mapping table (INTEGER→BIGINT, FLOAT→DOUBLE, etc.) and linked it from the skill.

Result: The agent still used INTEGER and FLOAT in 40% of generated widgets. It read the skill body but skipped the reference file.

Root cause: Separate reference files get skipped. The agent loads the skill body and treats it as the complete instruction set. It navigates to reference files only when explicitly blocked or when the skill body says “you must read X before proceeding.”

Action: Inlined the critical type constraints directly into the SKILL.md body. Reserved reference files for detailed examples and edge cases, not essential rules.

Key Finding: Inline Critical Knowledge: This matches findings from open-source agent skill research: agents skip auxiliary files. Essential constraints — type mappings, decision trees, hard rules — must live in the main skill body. Reference files are for supplementary detail, not for anything the agent must know to succeed.

Iteration 4: Prose explanation of two-layer architecture (Fail)

Change: Added a detailed paragraph in the skill body explaining the two-layer query architecture — which functions belong in extraction versus aggregation, and why.

Result: The agent still put NULLIF(duration, 0) in the aggregation layer in 25% of widgets. It understood the concept but applied it inconsistently.

Root cause: Prose descriptions are too ambiguous for deterministic compliance. The agent interprets paragraphs probabilistically, sometimes following the guidance and sometimes reverting to training-data patterns.

Action: Replaced prose with explicit decision trees: “Does the expression contain NULLIF, CAST, CASE, or COALESCE? → It MUST go in the extraction layer. Does it contain COUNT, SUM, AVG, or OVER()? → It MUST go in the aggregation layer.”

Iteration 5: Wrong/correct paired examples for visualization (Pass)

Change: Added wrong/correct paired examples for every visualization type in the skill body. Each pair shows the hallucinated output and the correct structure side by side.

Result: Chart array structure errors dropped from 60% to 5%. The remaining 5% were metric widgets where y must be an array even though it holds a single value.

Root cause: The agent learns by contrast more effectively than by instruction alone. Seeing what is wrong alongside what is right creates a stronger signal than positive examples alone.

Action: Added per-widget-type visualization rules. For metric widgets specifically: “y MUST be an array of objects, even for a single value. Never use a bare object.”

Iteration 6: Planner skill — mandatory planning phase (Pass)

Change: Introduced the planner skill as a mandatory prerequisite before generation. The agent must analyze requirements, discover datasets, sample data, assess viability, and ask clarification questions before any widget is generated.

Result: Semantic correctness (the widget answers the right question) jumped from 65% to 88%. The planning phase caught 4 ambiguous requirements that would have produced technically valid but semantically wrong widgets.

Root cause: This iteration addressed hallucination class 3 directly. By forcing the agent to surface ambiguity before generation, it could no longer silently pick the wrong interpretation.

Action: Made planning mandatory. The execution command accepts a validated plan, never raw user requirements.

Iteration 7: Hook-triggered validation replaces prompt-based checking (Pass)

Change: Moved from prompt-based validation (“after generating, run the validator”) to automatic PostToolUse hooks that fire on every file write matching widget or dashboard JSON paths.

Result: Validation enforcement went from ~70% to 100%. Repair loop completion rate went from ~50% to ~95%. Widgets that previously passed generation with errors were now caught and repaired before the dashboard was assembled.

Root cause: Prompt instructions are suggestions. System hooks are guarantees. The agent cannot skip a hook the way it can skip an instruction.

Action: Deployed hooks for all widget and dashboard JSON writes. Each hook returns structured repair guidance when errors are detected.

Iteration 8: Fixture calibration — updating evaluation ground truth (Fixture)

Change: Analyzed persistent benchmark failures across all runs using log analysis tooling. Three test cases failed consistently even after all skill improvements.

Result: Investigation revealed the ground-truth widgets in the test fixtures used deprecated field patterns. The agent’s output was actually more correct than the fixture — it used the current schema conventions while the fixtures were written against an older version.

Root cause: Ground truth is a hypothesis, not an axiom. Evaluation datasets can become stale.

Action: Updated three fixture files. Added a process rule: “When the agent consistently disagrees with the fixture and the output passes all validation, re-examine the fixture before assuming the agent is wrong.”

Evaluation Insight: Ground Truth Needs Calibration: Benchmark fixtures are not permanent. They represent the team’s best understanding of correctness at the time they were written. When an agent consistently produces output that is valid, passes all validation, but disagrees with the fixture — the fixture may be wrong. Building a process for fixture review prevents stale ground truth from blocking real improvements.

---

Evaluation and Results

The latest benchmark achieves 91% end-to-end success across 24 widgets. But the layered metrics tell a more nuanced story.

The three-condition comparison shows where the value comes from:

Three-condition comparison

Each hallucination class responded to different interventions:

Per-hallucination improvement breakdown

The evaluation process itself was a flywheel — each cycle produced sharper failure data for the next iteration:

Note: The gap between 100% agent validation and 91% end-to-end success represents the knowledge boundary of the system. Agent validation tests what has been encoded into rules. API validation tests what the platform actually enforces. The delta between them is exactly where the system needs to learn next — and the logging pipeline makes that delta visible and actionable with each benchmark cycle.

Two types of evaluation grading: Deterministic grading is used wherever the contract is rigid: structural validation, semantic rule checks, expected-property assertions. Failures are binary and reproducible. Model-based grading is used where correctness requires judgment: does the visualization match the business intent? Is the SQL logically equivalent to the ground truth even if structurally different? The combination provides both precision and coverage — deterministic grading for regression signals, model-based grading for semantic regressions no rule can express.

---

Guidance for Skill Creators

Building agent skills to prevent hallucination and misalignment comes down to three meta-principles:

Stage 1: Teach Agents to Investigate First

Skills should mandate data sampling, field verification, and viability assessment before any generation step. Schema metadata describes structure, not reality. Real data tells the truth. The single most effective intervention against column hallucination was making data sampling a hard prerequisite — not optional, not suggested, but a workflow gate.

Stage 2: Keep Essential Rules in the Main Skill File

Agents skip separate reference files. Type mappings, dialect constraints, decision trees, and hard rules belong in the SKILL.md body. Reserve auxiliary files for supplementary examples, edge cases, and detailed troubleshooting. If the agent must know it to succeed, it must be in the main file.

Stage 3: Make Validation Unavoidable

Use system-level hooks, not prompt instructions, for validation. Hooks fire automatically on every file write — the agent cannot skip them. Combined with structured repair guidance (the rule that failed, the value found, the value expected, and a step-by-step fix), hooks transform self-correction from a probabilistic hope into a deterministic workflow.

How much autonomy the agent should have depends on the domain:

Degrees of freedom by domain

Tip: Positive instructions beat prohibitions. Saying “use TRY_CAST for type conversions” is more reliable than “do not use CAST.” Saying “visualization x/y must be an array of objects with reference_name and label” works better than “do not use strings in x/y arrays.” The agent follows a clear target more consistently than it avoids a prohibited pattern.

Tip: Test across conditions, not just configurations. Always compare at least three conditions: baseline (prompt only), tools-only (CLI and sampling but no skills), and tools + skills + hooks. The delta between each condition shows where the value is coming from. If tools-only closes most of the gap, the skills need to teach different things. If skills close the gap but tools-only does not, the skills are doing real work.