AI Agents for Data Analysis: Natural Language to Insights

Imagine anyone asking questions in plain English and getting accurate answers with visualizations: 'What were our top-selling products last quarter, broken down by region?' No SQL knowledge required. AI agents that translate natural language to SQL make this possible.

Architecture: From Question to Answer

async function handleDataQuestion(question, context, schema) {
  // Parse natural language
  const parsed = await agent.understandQuery(question, context)
  
  // Generate SQL
  const sql = await agent.generateSQL(parsed, schema)
  
  // Validate before execution
  const validation = agent.validateQuery(sql)
  if (!validation.safe) throw new Error(validation.reason)
  
  // Execute with safety limits
  const result = await agent.executeQuery(sql)
  
  // Generate visualization
  const visualization = await agent.visualize(result, parsed.intent)
  
  // Explain in natural language
  const explanation = await agent.explainResults(result, question)
  
  return { sql: sql.query, result, visualization, explanation }
}

Text-to-SQL: Rich Schema Context

function buildSchemaPrompt(schema) {
  let prompt = 'Database Schema:\n\n'
  
  for (const table of schema.tables) {
    prompt += `Table: ${table.name}\nDescription: ${table.description}\nColumns:\n`
    for (const column of table.columns) {
      const samples = schema.sampleValues[table.name]?.[column.name]
      prompt += `  - ${column.name} (${column.type}): ${column.description}`
      if (samples) prompt += ` | Examples: ${samples.slice(0, 3).join(', ')}`
      prompt += '\n'
    }
  }
  
  // Business terminology mapping
  prompt += '\nBusiness Terminology:\n'
  for (const entry of schema.businessGlossary) {
    prompt += `  - "${entry.term}" = ${entry.sqlExpression}\n`
  }
  
  return prompt
}

Sandboxing Code Execution Safely

class SafeQueryExecutor {
  async execute(sql, options) {
    // Check for forbidden operations
    const forbidden = ['DELETE', 'UPDATE', 'INSERT', 'DROP', 'ALTER']
    for (const op of forbidden) {
      if (sql.toUpperCase().includes(op)) {
        throw new SecurityError(`Forbidden operation: ${op}`)
      }
    }
    
    // Wrap in read-only transaction
    const wrappedSQL = `BEGIN TRANSACTION READ ONLY; ${sql}; COMMIT;`
    
    // Execute with timeout
    const result = await Promise.race([
      this.readOnlyConnection.query(wrappedSQL),
      this.timeout(options.timeout)
    ])
    
    // Limit rows
    if (result.rows.length > options.maxRows) {
      return { ...result, rows: result.rows.slice(0, options.maxRows), truncated: true }
    }
    return result
  }
}

Automatic Visualization Selection

function determineVisualization(result, queryIntent) {
  const analysis = analyzeResultStructure(result)
  
  // Single value → Metric card
  if (analysis.rowCount === 1 && analysis.columnCount === 1) {
    return { type: 'metric', data: { value: result.rows[0][0] } }
  }
  
  // Time series → Line chart
  if (analysis.hasDateColumn && analysis.hasNumericColumn) {
    return { type: 'line', config: { xAxis: analysis.dateColumn, yAxis: analysis.numericColumns[0] } }
  }
  
  // Category + Value → Bar or Pie
  if (analysis.hasCategoryColumn && analysis.hasNumericColumn) {
    if (analysis.uniqueCategories <= 6 && queryIntent.includes('distribution')) {
      return { type: 'pie' }
    }
    return { type: 'bar' }
  }
  
  return { type: 'table' }
}

Key Takeaways

Schema context is everything. Rich descriptions and business glossaries improve accuracy.

Safety cannot be bolted on. Read-only connections, validation, and limits must be architectural.

Explain in business terms. Technical accuracy means nothing if users can't understand results.