Write queries with Gemini assistance

Preview — Gemini in Dataform

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

You can use Gemini, an AI-powered collaborator in Google Cloud, to generate SQL and Dataform core code inside .sqlx files in Dataform.

Learn how and when Gemini for Google Cloud uses your data.

Only English language prompts are supported for Gemini in Dataform.

This document is intended for data analysts, data scientists, and data developers who work with SQL workflows in Dataform. It assumes you have knowledge of Google SQL syntax and how to create Dataform SQL workflow actions.

Before you begin

1. In the Google Cloud console, go to the project selector page.

   Go to project selector

2. Select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.
3. Activate Gemini in BigQuery.

Generate a query

As an early-stage technology, Gemini for Google Cloud products can generate output that seems plausible but is factually incorrect. We recommend that you validate all output from Gemini for Google Cloud products before you use it. For more information, see Gemini for Google Cloud and responsible AI.

You can provide Gemini with a natural language statement (or prompt) to generate a SQL or Dataform core query based on SQL workflow actions defined in your repository. For example, you can use Gemini to generate a SQL SELECT statement in a .sqlx table definition file.

To generate a SQL or Dataform core query, follow these steps:

1. In the Google Cloud console, go to the Dataform page.

   Go to the Dataform page

2. Select or create a repository, and then select or create a workspace.

3. In the Files pane, select or create a .sqlx file.

4. In the file tab, click pen_spark Gemini.

5. In the Gemini dialog, enter a natural language prompt.

   If you know the SQL action that you want to use, then you can specify the action name in backticks (`) in your prompt.

6. Click Generate.

   Gemini reviews the SQL actions defined in your repository to find actions that might be relevant to your prompt and suggests a query.

7. Optional: To provide feedback, click thumb_up Like suggestion, thumb_down Dislike suggestion, or chat_info Give more feedback

8. To accept the suggestion, click Insert.

Tips for query generation

The following tips can improve suggestions that Gemini in Dataform provides:

• Provide the SQL action name enclosed in backticks (`), such as `action_name`.
• If the column names or their semantic relationships are unclear or complex, then you can provide context in the prompt to guide Gemini towards the answer that you want. This technique is known as prompt engineering. For example, to encourage a generated query to reference a column name, describe the column name and its relevance to the answer that you want. To encourage an answer that references complex terms like lifetime value or gross margin, describe the concept and its relevance to your data to improve SQL generation results.

Gemini and Dataform data

Gemini in Dataform can access the metadata of the tables that you have permission to access. This can include the table names, column names, data types, and column descriptions. Gemini in Dataform cannot access the data in your tables, views, or models. For more information on how Gemini uses your data, see How Gemini for Google Cloud uses your data.

What's next

• For information about Gemini for Google Cloud, see Gemini for Google Cloud overview.
• For information about the Gemini data policy, see How Gemini for Google Cloud uses your data.