AI in editor

The editor is an integral part of people's development workflow, and this document outlines its AI capabilities. The AI capabilities are in their infancy but provide users the ability to generate, explain, and edit models. The output is imperfect and may require manual intervention; that said, the outputs have proven to be very useful internally at Quary. The initial output is often correct, and if it isn't, it's often a decent start.

Requirements

The AI capabilities are available only for users of Visual Studio Code installed locally and who have GitHub Copilot installed and enabled. This is because the AI capabilities are powered by the GitHub Copilot Chat extension and the models hosted by GitHub.

Functionality

At the moment the AI/code generation capabilities allow for the following three methods generate, explain and edit. All of them are available in the GitHub Copilot Chat extension. The following image shows the extension in action, where to find it and where to type your query.

In addition to the methods, you can also include models in your query by referencing them by typing #file: which opens a file picker to select the model you want to use. The following image shows how to reference a model in your query.

Generate

Generate generates a SQL query given an English prompt. It works in two ways: either you provide the models you want to use in the query, or you don't. If you provide models, the agent will use them to generate the query. If you don't provide models, the agent will first look for models it believes are most relevant to the prompt and generate the query. The generated code can then be copied and pasted into the editor.

So for example if you want the total sales for each product, you can provide the models sales and products like so with the prompt.

@quary /generate #file:sales.sql #file:products.sql can you show me the total sales for each product?

You may also try to generate a query without providing the models like so:

@quary /generate can you show me the total sales for each product?

The ability to specify specific models is useful when you know which models you want to use in the query. This reduces the likelihood of the AI generating a query that is not what you want.

Explain

Explain as it name implies let's you explain a model. By referencing a model and asking a question, Quary will explain the transformation that the model is doing. This is a powerful feature that allows you to understand the transformations that are happening in your DAG. See the below video for a demo of how we explained the latest_account_value model.

@quary /explain #file:latest_account_value.sql can you explain what this model is doing?

Edit

Edit, as the name implies, edits an existing model that must be referenced in the query. For example, the following query references a model called latest_account_value:

@quary /edit #file:latest_account_value.sql can you add a valid date column that's human readable.