SQL AI Assist

SQL AI Assist is an AI feature designed to make your experience with SQL queries faster, easier, and more efficient. Whether you’re an experienced data analyst or just getting started with SQL, SQL AI Assist is here to help you write queries with less effort and more confidence.

What is Civis SQL AI Assist?

Civis SQL AI Assist is an AI-powered tool integrated directly into the Civis Query Tool and SQL Report pages. It’s designed to help you craft SQL queries by offering smart, context-aware suggestions and automating parts of the process, so you can spend more time analyzing data and less time writing complex queries.

Context Awareness

Table Metadata

SQL AI Assist understands your table structure. By using metadata from tables you have mentioned in your query, it provides suggestions which are relevant and specific to your dataset. This reduces the guesswork involved in writing SQL queries and makes the process more efficient. Context sent along to the LLM includes:

• Schema name
• Table name
• Whether or not the table is a view
• Table description, if present
• Table definition (i.e. create statement for the table, including column names & data types)
• Column descriptions, if present

Please note that this information is merely sent along with your request, and is not stored by the model anywhere. The AI model does not store this metadata, nor does it have access to the actual data in your tables.

Database Specific 

To ensure accurate syntax, SQL AI Assist is aware of your target database’s type, whether Redshift, Postgres, or BigQuery.

How to use SQL Generation

To generate SQL using AI, first describe what you want to do in the query editor. You can write this in plain language (e.g., “Show total sales for the last quarter”) or a mix of plain language and SQL (e.g. "select average sales from my_data.sales". Then either click the Generate SQL button on the left side of the editor, or use the keyboard shortcut Ctrl+Period (Cmd + Period on Mac) on your keyboard.

The generated SQL suggestion will render as a diff, showing what changed between your input and the AI-generated SQL. You can either accept or discard this suggestion via the buttons on the bottom right of the editor.

Best Practices

• Iterate on your prompts to get the best results. Remember that the AI will take a best guess given whatever is in the editor. Without enough information or context, its output may not be what you want. If you don’t like the SQL you get at first, add more detail and try again!
• For the most accurate results, include relevant schema-qualified table identifiers in your prompt, formatted like “schema.table”. The AI only has access to metadata for your tables which are explicitly referenced like this in the editor.
• Remember that AI models can occasionally generate inaccurate information. We recommend that you double check the queries it generates before running them, especially for high-stake tasks, such as table modifications. The AI will include an “Assumptions” section at the top of its suggestion to better help you check its work. 

Example Usecases

Translate Natural Language into SQL

Don’t know where to start your SQL, but have an idea of what you want to achieve? Let AI assist you! The Generate SQL button can help translate your plain English description or question into SQL. For example, you can tell the assistant to “Gather the average petal surface area by species type from scratch.iris”:

Note: referencing relevant tables with their schema-qualified name, e.g. scratch.iris, provides the most accurate results.

Complete Partial Queries

Perhaps you’re 90% there, but don’t quite know how to complete your query. Or you need a repetitive set of filters which you’d rather not fill out by hand. SQL AI Assist can help! In the query editor, augment your partial SQL with comments and or pseudo code, and let the assistant handle the rest. 

Fix Errors in your Query

When a query fails, you’ll see a “Fix with AI” button. This will run SQL Assist to correct the error:

Or, if you’re trying to debug some failing SQL that failed outside of the query page, copy the SQL and error message into the editor,

then run Generate SQL to resolve the error!