By Ali Al-Gburi
Imagine being able to ask your organization’s data questions in plain language and instantly receive the answer as a table or a chart. No SQL queries, no dashboard filters, no back-and-forth with analysts. That’s the idea behind a conversational data assistant powered by GenAI; a system that lets users talk directly to their data and receive intelligent, context-aware insights.
This approach combines natural language understanding, metadata-driven search, and query automation to turn data exploration into a dialogue. What began as an internal proof of concept evolved into a fully functional framework for natural language data interaction, one that teams can adapt to their own environments.
From Concept to Capability
At its core, the assistant functions as a chat interface connected to enterprise databases. Users can ask questions such as: “What percentage of items were translated last month?” and receive a data-backed answer visualized as a chart or table.
Instead of embedding or processing entire datasets, the system works with metadata, the descriptions, columns, and structures of the data. This metadata is vectorized and indexed, allowing the model to understand the dataset’s context and generate accurate SQL queries. The richer and more descriptive the metadata, the better the model performs.
This approach ensures security, scalability, and simplicity. As new datasets are documented, they automatically become available for natural language querying, without retraining the model or moving sensitive data.
Architecture Overview
1. Two-Part Design
The architecture consists of two key components:
- Indexer: A scheduled job extracts metadata from the data catalog, turns it into structured documents, generates vector indexes, and stores them in cloud storage.
- Core Engine: The conversational layer retrieves these indexes, interprets user queries, generates SQL, executes it on the database engine, and visualizes the results.
This separation ensures stability and maintainability. The indexer refreshes metadata regularly, while the core engine remains lean and responsive.
2. Technology Stack
The proof of concept was built using a modular, cloud-native stack:
- Frontend: Streamlit for the chat interface
- Metadata Source: OpenMetadata
- Language Models: Hosted via Amazon Bedrock
- Embeddings: Cohere multilingual model (supports both Dutch and English)
- Query Engine: AWS Athena
- Orchestration: PocketFlow (a lightweight alternative to LangChain)
- Scheduling: Apache Airflow for weekly metadata updates
This stack keeps the system portable and easy to extend, with minimal operational overhead.
How the System Thinks
The assistant’s reasoning process is powered by a set of specialized agents, each handling one part of the pipeline:
- Chatter: Handles natural language interaction and intent recognition.
- Retriever: Identifies the most relevant tables and columns for the user query.
- Checker: Validates whether retrieved metadata can answer the question and refines the query if necessary.
- SQL Generator: Produces executable SQL based on the refined prompt.
- Debugger: Detects and corrects SQL errors automatically.
- Metadata Explainer: Provides human-readable insights about tables and data structures.
- Data Displayer: Creates charts and visualizations using validated Python code.
The orchestration framework allows these agents to work sequentially or in parallel, making the system easy to experiment with and extend.
Optimizing Retrieval Accuracy
The system blends vector search with lexical search (BestMatching25) to improve retrieval precision. Results from both are fused and re-ranked using a cross-encoder, which ensures that the most relevant tables are prioritized for SQL generation.
This hybrid retrieval technique provides the balance between semantic understanding and keyword accuracy, effectively delivering both context and precision in table selection.
User feedback provides another optimization layer, allowing manual score boosting for the most reliable tables and refining retrieval over time.
Keeping It Simple
One of the most important lessons from the development process was the value of simplicity. Each additional processing step introduces potential failure points, so many early experiments, like column-level indexing, user question refining and data snippet embedding, were ultimately removed.
Maintaining a lightweight and direct orchestration framework significantly improved the system’s reliability and performance. The fewer unnecessary layers there are, the easier it is to maintain and scale the assistant.
The Power of Good Metadata
The model’s performance depends heavily on high-quality documentation. Clear table and column descriptions, along with small sets of sample categorical values (around 20), dramatically improve query accuracy.
Attempts to automate documentation using generative models produced mixed results often requiring manual revision. The best outcomes still come from human-authored metadata written by domain experts. In other words, AI thrives on well-documented data.
Security and Governance
The assistant operates under read-only permissions and connects only to non-sensitive datasets. All Python and SQL executions are sandboxed and validated before running, minimizing risk.
Authentication is currently network-based, with plans for session tracking and user-level logging. Future iterations may allow users to save queries and visualizations that automatically refresh as data updates, bridging the gap between conversational analytics and traditional BI tools.
Next Steps and Future Enhancements
Future development areas include:
- Evaluator Node: Automatically checks whether query results match user intent.
- Query History: Save, reuse, and schedule data questions.
- Integration with Issue Tracking: Enable data-driven forensics and debugging.
- Benchmark Testing: Introduce a GenAI evaluation framework for accuracy measurement.
- Model Optimization: Use smaller models for intent detection and larger ones for SQL generation.
These evolutions aim to make the system faster, smarter, and more adaptive to real-world data workflows.
Key Takeaways
- Metadata quality determines performance. Well-documented data leads to accurate results.
- Hybrid retrieval works best. Combining vector and lexical search increases reliability.
- Simplicity is stability. Minimize orchestration layers to reduce error rates.
- Conversational data access empowers users. It lowers barriers to insight without replacing analytics expertise.
- Governance remains essential. Secure access and validated execution keep GenAI systems trustworthy.
Conversational interfaces are redefining how organizations interact with data. By grounding large language models in metadata and enabling controlled automation of SQL generation, teams can build systems that make analytics more intuitive, inclusive, and efficient.
When designed carefully (with transparency, simplicity, and strong metadata foundations) GenAI becomes not just a tool for automation, but a bridge between human understanding and data complexity.
We’re continuously exploring how AI can make data more accessible and actionable.Want to discover how conversational intelligence can enhance your data strategy? 👉 Let’s connect! bizdev@dataroots.io