Benchling’s Insights tool is powerful, and it supports a variety of reporting and data visualization. SQL Writer can help if you:
- Run into errors with queries
- Need help modifying existing queries to bring in additional data
- Have trouble creating new Insights blocks
SQL Writer allows you to use natural language prompts to build SQL queries directly in the block. The tool will analyze your SQL, comments, and your warehouse setup to suggest and update to your SQL.
This is a Benchling AI feature. See the AI at Benchling page for information on how to enable this and other AI-based features.
Use SQL Writer
Once you have the SQL Writer feature enabled, you will get a new purple sparkle button above your SQL in the Insights editor.
To distinguish between standard SQL comments -- and comments that you want directed towards the SQL Writer for help, use the --! comment format to explain what you are looking for or need help with. The SQL Writer will only run if at least one of these comments is found.
- Click the Sparkle icon in your SQL Block to open the tool
- Use natural language to describe your goal, the data that you want to use, and the desired format for your visualization
- Remember to use --! to generate a suggestion
- Click Generate
- Review the suggested query and preview
- Edit the query by adding additional comments with the --! comment to adjust the output
- Once you are happy with the query, click Accept SQL
If you leave the SQL Writer window without clicking accept SQL, none of the query will save, in this case you can use the SQL Writer tool again, but it does not have any context of what it has just done.
Because this feature relies on AI it can not be guaranteed to output SQL that perfectly matches your intent, or possibly even perfectly valid SQL. In the case the suggestion is determined to be invalid, the error will be inserted into the SQL. Often, just clicking "Generate" and letting it try to fix the error itself is sufficient to fix it.
When the SQL Writer generates a valid SQL suggestion, you will automatically get a preview of the results that you can use to help validate that the query is behaving as you expect.
If you want to see what changes are being made to a query, you can toggle on the Compare to original mode where you will see the original query in your Insights dashboard block on the left, and the latest suggestion on the right. The suggestion is fully editable, you may update it or add new --! comments and retry the suggestion using the suggestion as the starting point. We find that if the SQL Writer provides invalid SQL, a single retry is generally sufficient to fix the issue and get to working, valid SQL. Again, you will need to click Accept SQL to save any changes to your query.
Limitations
- The system is currently optimized around $raw tables and doesn't understand the corresponding views well. We recommend not mixing views and $raw tables in your queries to the SQL Writer
- Retrying the suggestion does not have the context of prior attempts, so you may need to re-add comments to get it to do what you want
- The amount of detail the SQL Writer gets about your warehouse decreases somewhat as the size of your warehouse increases (number of tables), so for best performance especially in particularly large warehouses (thousands of tables) we recommend explicitly mentioning or including the tables you know you will need in your initial query or comments
- Always validate both the SQL suggestion as well as the resulting output to ensure it matches your expectations. AI based systems, much like humans, are prone to errors, misunderstandings, and ambiguities
Feedback
We value your feedback to help us improve this new tool. Using the thumbs up/down buttons helps us get a sense of if the tool is being found useful. You can also click the Tell us more button after that to provide us with an explanation or more detail.
Security and privacy
For more information about the privacy and security for AI-powered features, please see Data protection and security for AI at Benchling.