Beyond Static Dashboards: Creating a Dynamic AI Data Analyst

At Hatch, we specialize in creating data dashboards that turn complex datasets into clear, compelling visual stories. These dashboards serve a crucial purpose: they align teams around shared KPIs and present a consistent, carefully constructed story. 

However, they're also inherently static, excellent at showing what is happening at an aggregated level, but they often struggle to explain why certain trends emerge or predict what's next. This is where AI steps in.

This blog shares our journey in building an AI assistant that enables natural, conversational data exploration. Users can ask analytical questions, and the assistant responds by analyzing the data and clearly articulating its reasoning and conclusions.

Note: To get the most out of this post, a basic understanding of AI concepts like Retrieval-Augmented Generation (RAG), embeddings, and relational databases will be helpful. We won’t dive into the fundamentals, but we’ll build on them to explore more advanced ideas and practical applications.

Say Hello to Our Dataset

Throughout this blog, we use a fictional dataset designed to reflect a realistic scenario. Rather than working with a single flat table, we provided it with a relational database model that requires the AI to perform joins across multiple tables—encouraging deeper reasoning and more sophisticated insight generation.

Our dataset consists of employee wellbeing survey results, combining demographic questions (such as age, gender, and tenure) with scaled sentiment questions. These sentiment questions are grouped into eight Dimensions, each falling into one of two categories: Practices, factors the company directly controls, like Leadership and Work-Life Balance and Symptoms, indirect outcomes influenced by the organization, such as Stress and Motivation. Each Dimension consists of four questions, with survey results collected quarterly to support performance tracking over time.

If you are anything like me, this helps:

Eventually we want to be able to ask the AI questions like:

• Which Practices have the biggest influence on Stress within our organization?

• Are there any particular groups that suffer more from certain symptoms versus others?

• Are there any trends that stand out based on previous cycles?

but let’s first lay the foundation before we raise the roof.

To RAG or Not To RAG, That is the Question

When building AI solutions with private data, many teams have the same knee-jerk reaction: ‘Let’s build a RAG-based solution.’ While RAG is a powerful approach, it comes with fundamental limitations, especially when dealing with structured, fragmented data spread across multiple tables.

The core problem is that tabular data consist of headers and raw data with minimal semantic context, making embedding-based retrieval ineffective. Unlike natural language documents where meaning builds across sentences, tabular data is highly structured but semantically sparse. Each cell represents an isolated value, creating poor retrieval granularity.

That said, it is possible to contextualize the CSV data, and doing so can improve RAG performance in these scenarios.

There are two approaches to achieve this:

1. Context-Rich Formatting:

   Transform raw rows into header-inclusive strings

2. Natural Language Summaries: Convert data into semantically rich descriptions

However, even with these improvements, LLM context windows and token input costs constrain analysis to small subsets (5-10 rows), preventing statistically meaningful insights. This led us to our preferred solution: database-driven analysis.

SQL to the rescue

To achieve true analytical capability, you need a system that can analyze and reason over entire datasets rather than small subsets. This requires the AI to generate executable code that performs calculations, then feed those results back for interpretation and insight synthesis.

This approach offers two primary strategies:

1. Code Generation: AI creates Python scripts for comprehensive analysis

2. SQL Generation: AI produces database queries for direct data access

These strategies can also be combined, using SQL for initial data retrieval and Python for advanced statistical analysis or complex visualizations that exceed SQL's capabilities.

During our implementation, we chose SQL generation as it provides direct database access with excellent performance and security controls. This allowed us to leverage existing database infrastructure while maintaining strict read-only permissions.

Amazon Bedrock: A Managed AI Solution

As with many more complex processes, the key to success is to break them up into more manageable pieces. Enter Amazon Bedrock.

Amazon Bedrock offers a compelling solution as AWS's managed platform for generative AI development. The framework simplifies AI agent creation by providing access to various foundation models that can be easily swapped as needed, along with live editing and testing capabilities. 

But most importantly, Bedrock enables custom "actions" backed by AWS Lambda functions - serverless code that executes on demand. This allows your agent to call APIs, run calculations, or in our case, query databases. The framework handles the complexity by letting you define each action's purpose, inputs, and outputs, so the agent understands exactly what tools are available.

In our example, we set up sample data in AWS RDS PostgreSQL and created a Lambda function for SQL query execution. The Lambda function accepts query parameters generated by the AI and returns results. After linking this function as an action and selecting Amazon's Titan model (Nova Pro), the technical connection was established. 

However, the key to success lies in providing comprehensive instructions. Without proper guidance, the AI lacks understanding of your data's structure and business context.

AI Can’t read minds (yet)

Success with AI-driven database analysis requires instruction design that goes beyond basic prompts. The AI needs a few layers of guidance to function effectively.

1. High-Level Guidance

Role Definition
Helps the AI in establishing its primary function.

2. Operational Guidance

Conceptual Description
Provides business meaning

Example:

Database Schema
Critical in order to generate valid SQL queries

Example:

CREATE TABLE scale_dimensions ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, type TEXT NOT NULL)

Operational Constraints
Ensures safe and effective query generation:

Example:

3. Guidance on Methodology

The sophistication of your AI's analysis depends heavily on how you guide its reasoning process. Here are key techniques for achieving deeper insights:

Strategic Query Approach: Guide the AI through a proven two-step process rather than allowing direct string matching. First, retrieve all available dimension names to understand the data landscape, then execute targeted calculations based on verified matches. This prevents errors from assumption-based queries and ensures comprehensive data exploration.

Encouraging Analytical Depth: For open-ended questions, instruct the AI to move beyond simple calculations. When users ask "What can you tell me about X?", guide the system to explore correlations with other dimensions, provide demographic breakdowns with clear rationale, and interpret patterns for business implications.

Balancing Guidance and Autonomy: Too much restriction can limit analytical value, while too little guidance can produce unfocused results. The key is providing structured frameworks that encourage exploration while maintaining analytical rigor.

Understanding AI Reasoning with Bedrock Traces

One of Bedrock's valuable features is trace analysis: detailed logging of each step the AI takes during query processing. This visibility helps you understand how the AI interprets prompts, plans actions, and generates results. Bedrock layers its own system-level instructions around your custom prompts, providing additional guidance on response formatting.

Let's explore how this works by examining the traces generated for the question: "Tell me about the work life balance scores."

To get to its final answer the system generates five distinct traces:

Trace 1

The AI begins with an initial reasoning step. As intended, it recognizes that before proceeding, it needs to determine how “work life balance” is actually represented in the database. It generates a query to retrieve all available scale dimensions.

Trace 2

After receiving the results from the first query, the AI correctly identifies that the database contains a dimension named "Work-Life Balance". It decides to calculate the average score for this dimension and writes a new query to do so.

Trace 3

Following the instructions to explore deeper relationships, the AI generates a sophisticated multi-table query to examine correlations between Work-Life Balance and other Practice dimensions:

WITH work_life_balance_scores AS (
    SELECT
    	respondent_ext_id,
    	AVG(CAST(value AS INTEGER)) AS average_score
	FROM scale_responses
	WHERE scale_question_id IN (
    	SELECT id
    	FROM scale_questions
    	WHERE scale_dimension_id = 6
	)
	GROUP BY respondent_ext_id
),
practice_scores AS (
	SELECT
    	respondent_ext_id,
    	scale_dimension_id,
    	AVG(CAST(value AS INTEGER)) AS average_score
	FROM scale_responses
	JOIN scale_questions
    	ON scale_responses.scale_question_id = scale_questions.id
	WHERE scale_questions.scale_dimension_id IN (
    	SELECT id
    	FROM scale_dimensions
    	WHERE type = 'Practice'
	)
	GROUP BY respondent_ext_id, scale_dimension_id
)
SELECT
	p.scale_dimension_id,
	d.name,
	CORR(w.average_score, p.average_score) AS correlation
FROM work_life_balance_scores w
JOIN practice_scores p
	ON w.respondent_ext_id = p.respondent_ext_id
JOIN scale_dimensions d
	ON p.scale_dimension_id = d.id
GROUP BY p.scale_dimension_id, d.name
ORDER BY ABS(CORR(w.average_score, p.average_score)) DESC

Trace 4

The agent then initiates a follow-up analysis, incorporating demographic data to provide additional context for interpreting the results.

Trace 5

Finally, having gathered and analyzed the necessary information, the agent synthesizes its findings and returns the final response.

This trace visibility enables you to understand exactly how your instructions are being interpreted and executed, allowing for targeted improvements to agent behavior.

Conclusion

Patterns

Through this implementation, several important patterns emerge that inform successful AI-driven data analysis:

RAG vs Relational Database: Embedding-based retrieval conflicts fundamentally with tabular data structure. The lack of semantic context in something like a CSV format, combined with context window limitations that restrict analysis to small subsets, makes database-driven approaches significantly more effective for analytical use cases.

Instruction Design Considerations: Effective instructions require balancing specificity with flexibility. Instructions tend to be model-specific, raising important questions about portability when upgrading or changing foundation models. Additionally, detailed instructions increase token usage, directly impacting operational costs in production environments.

Quality and Consistency Factors: AI-generated analysis can vary significantly between identical queries. In testing, we observed noticeable output variation and occasional failures even with identical prompts. This variability is an important consideration for applications requiring consistent results.

Model Selection Impact: Foundation model choice significantly affects autonomous reasoning capability. More advanced models demonstrate greater analytical independence, while simpler models require more explicit procedural guidance to achieve sophisticated analysis.

Principles

These patterns naturally lead to the following key principles.

Start Simple, Iterate Systematically: Begin with basic query generation and gradually add analytical sophistication through systematic testing across different query types.

Design for Instruction Portability: Structure instructions to work across model versions while planning for model-specific optimization as requirements evolve.

Balance Depth with Cost: Find the optimal point between comprehensive instructions and operational efficiency, especially important for multi-user environments.

Plan for Variability: Build validation and consistency checks into workflows where reliable outputs are critical for business decisions.