AI dashboard karaoke from Coalesce 2023

"Hex Magic is now the umbrella for all of our AI-enabled features."

- Izzy Miller, Developer Advocate at Hex

Teammates from Hex, Izzy Miller, Developer Advocate, and Matt David, Product Marketing Lead, discuss the use of AI in generating SQL code and conducting complex analyses. They discuss how they built a system that uses AI, metadata, and a data workspace to generate SQL queries and conduct analysis. Izzy and Matt also demonstrate a new feature in beta testing that generates a complete analysis plan.

Hex’s AI tool, Hex Magic, uses a balance of context and machine learning to generate SQL and Python code

Hex’s AI tool, “Hex Magic,” was initially designed as a playful experiment but turned out to be surprisingly effective at generating AI code. Utilizing OpenAI's ChatGPT 3, Hex Magic takes a set of tokens (also known as a “prompt”) and predicts the next token or set of tokens, creating SQL queries.

Izzy and Matt explain that a key element to the success of Hex Magic is the specificity of the prompt. Matt notes, “The tricky part about this is if your prompt isn't specific enough, it doesn't actually know what you really want.” They also point out that the AI’s perception of its role can influence the quality of the code it generates. For example, instructing the AI to behave as an "award-winning data science professor" improved the accuracy of the SQL it generated.

The team at Hex has put a lot of work into testing and refining their prompts. They have developed an evaluation test harness that allows them to run hundreds of prompts against hundreds of different versions of context. This allows them to see how well the AI is generating the code they expect and if it is generating the correct data.

Hex’s AI tool utilizes a wealth of metadata to improve code generation

Hex Magic’s generation of SQL code is not only dependent on the initial prompt but also heavily influenced by metadata. Izzy explains, “This trick isn’t actually real magic…it just builds on all the things that Matt talked about: a good prompt, an advanced AI, a sweet data workspace–but it adds one crucial thing which is metadata.”

Metadata provides the crucial context for the AI to generate relevant and accurate code. Hex pulls this metadata from several sources including the actual data warehouse, the dbt project context from the Hex notebook, and the Hex data manager which allows for custom guidance. This metadata is stored as embeddings in a vector database, a system that allows for easy storage and querying of these embeddings.

However, the inclusion of too much metadata or irrelevant data can also lead to problems. Izzy highlights that it was important to narrow the scope of context in a deterministic and controlled way before passing the information off to the AI model. This balance allowed for a more accurate code generation while keeping the AI model from becoming confused or overwhelmed.

Hex’s AI tool has the potential for more complex code generation, including a complete analysis

Beyond generating individual SQL or Python queries, Hex Magic can also generate plans for complete analysis. This involves generating multiple SQL queries, building on the results of previous queries, and even creating charts.

Izzy presents an example where Hex Magic was asked to pull monthly revenue and cost information, broken down by category. The AI tool generated SQL queries that accurately pulled the relevant data, correctly identifying related columns based on the context of the question. The team at Hex is still working on developing this feature and refining its accuracy.

Izzy explains that the goal was for Hex Magic to be used as a tool to kickstart analytical projects, rather than replace human analysts. He clarifies, “The goal is not full human replacement…it’s happening in a workspace that we’ve spent years designing for humans to use to do data analysis.” By providing a starting point, the tool can help analysts get a jump on their projects, allowing them to then tweak and refine the generated code to fit their specific requirements.

Izzy and Matt’s key insights

• Hex uses AI to generate SQL queries, which saves time and effort in writing complex queries manually
• The system uses metadata to provide the AI with the necessary context to generate accurate and relevant SQL queries
• Hex has developed a system that narrows the scope of context in a controlled way before passing the information to the AI. This helps to balance control with the power of AI
• The system can generate multiple SQL queries and build charts, kickstarting an entire analytical project
• Hex is currently testing a new feature that generates a complete analysis plan which includes which cells and tables it needs to create and what it's going to do