Prepare data with Gemini

This document describes how to generate and manage SQL code suggestions for your data preparations in BigQuery.

For more information, see Introduction to BigQuery data preparation.

Before you begin

• Set up Gemini in BigQuery.

• Give the required Identity and Access Management (IAM) roles and permissions.

Open the data preparation editor in BigQuery

You can open the data preparation editor in BigQuery by creating a new data preparation, creating one from an existing table, or opening an existing data preparation. For more information about what happens when you create a data preparation, see Data preparation entry points.

On the BigQuery page, you can go to the data preparation editor in the following ways:

Add data preparation steps

You prepare data in steps. You can preview or apply steps suggested by Gemini. You can also improve the suggestions, or apply your own steps.

Apply and improve suggestions by Gemini

When you open the data preparation editor for your table, Gemini inspects the data and schema from the table you loaded and generates filter and transformation suggestions. The suggestions appear on cards in the Steps list.

The following image shows where you can apply and improve steps suggested by Gemini:

To apply a suggestion by Gemini as a data preparation step, do the following:

1. In the data view, click a column name or a particular cell. Gemini generates suggestions for filtering and transforming the data.

2. Optional: To improve the suggestions, edit the values of one to three cells in the table to demonstrate what the values in a column should look like. For example, enter a date the way you want to format all dates. Gemini generates new suggestions based on your changes.

   The following image shows how you can edit values to improve the steps suggested by Gemini:

   

3. Select a suggestion card.

   1. Optional: To preview the result of the suggestion card, click Preview.
   2. Optional: To modify the suggestion card using natural language, click Edit.

4. Click Apply.

Add steps with natural language or SQL expressions

If no suggestions meet your needs, add your own by clicking Add step, selecting a step type, and entering a prompt in natural language describing your intent.

Add a transformation

1. In the data view, click Add step and choose the Transformation option.
2. In the Description field, enter a prompt, such as Convert the state column to uppercase.

3. Click send Send.

   Gemini generates a SQL expression and a new description based on your prompt.

4. In the Target column list, select or enter a column name.

5. Optional: To update the SQL expression, revise the prompt and click send Send, or manually enter a SQL expression.

6. Optional: Click Preview and review the step.

7. Click Apply.

Filter rows

To add a filter that removes rows, follow these steps:

1. In the data view, click Add step > Filter.
2. In the Description field, enter a prompt, such as Column ID should not be NULL.
3. Click Generate. Gemini generates a SQL expression and a new description based on your prompt.
4. Optional: To update the SQL expression, revise the prompt and click send Send, or enter a SQL expression manually.
5. Optional: Click Preview and review the step.
6. Click Apply.

Filter expression format

SQL expressions for filters retain rows that match the specified condition. This is equivalent to a SELECT … WHERE SQL_EXPRESSION statement.

For example, to retain records where the column year is greater than or equal to 2000, the condition is year >= 2000.

Expressions must follow the BigQuery SQL syntax for the WHERE clause.

Configure the error table and add a validation rule

You can add a filter that creates a validation rule, which sends errors to an error table or fails the data preparation run.

Configure the error table

To configure your error table, follow these steps:

1. In the data view, go to the toolbar and click More > Error table.
2. Click Enable error table.
3. Define the table location.
4. Optional: Define a maximum duration for keeping errors.
5. Click Save.

Add a validation rule

To add a validation rule, follow these steps:

1. In the data view, click Add step > Filter.
2. Enter a description for the step.
3. Enter a SQL expression, in the form of a WHERE clause.
4. Optional: If you want the SQL expression to act as a validation rule, select the Failed validation rows go to error table checkbox. You can also change a filter to a validation in the data preparation toolbar by clicking More > Error table.
5. Optional: Click Preview and review the step.
6. Click Apply.

Delete a column

To delete a column from a data preparation, follow these steps:

1. In the schema view, click more_vert Menu next to the column to be deleted.
2. Click Delete. A new applied step is added for the deleted column.

Add a join operation with Gemini

To add a join operation step between two sources in your data preparation, follow these steps:

1. In the data view for a node in your data preparation, go to the Steps list, and click Add step > Join.
2. In the Add join dialog, click Browse, and then select the other table involved in the join operation (referred to as the right side of the join).
3. Optional: Select the type of join operation that you want to perform, such as Inner join.

4. Review the Gemini-generated join key information in the following fields:

   • Join description: The natural language description of the SQL expression for the join operation. When you edit this description and click send Send, Gemini suggests new SQL join conditions.

   • Join conditions: The SQL expressions within the ON clause for the join operation. You can use the L and R qualifiers to refer to the left and right source tables, respectively. For example, to join the customer_id column from the left table to the customer_id column from the right table, enter L.customerId = R.customerId. These qualifiers aren't case-sensitive.

5. Optional: To refine the suggestions from Gemini, edit the Join description field, and then click send Send.

6. Optional: To preview the join operation settings of your data preparation, click Preview.

7. Click Apply.

   The join operation step is created. The source table that you selected (the right side of the join) and the join operation are reflected in the list of applied steps and in the nodes in the graph view of your data preparation.

Add or change a destination table

To add or change a destination table for the output of your data preparation, follow these steps:

1. In the data view, click Add > Destination.
2. Select the project where the destination table is stored.
3. Select one of the datasets, or load a new dataset.
4. Enter a destination table. If the table doesn't exist, the data preparation creates a new table on the first run. For more information, see Write mode.
5. Select your dataset as the destination dataset.
6. Click Save.

View the data sample and schema for an applied step

To view sample and schema details at a particular step in the data preparation, do the following:

1. In the data view, go to the Steps list and click Applied steps.
2. Select a step. The Data and Schema tabs appear, displaying the data sample and schema as of this particular step.

Edit an applied step

To edit an applied step, do the following:

1. In the data view, go to the Steps list and click Applied steps.
2. Select a step.
3. Next to the step, click more_vert Menu > Edit.
4. In the Edit Applied Step dialog, you can do the following:
   • Edit the description of the step.
   • Get suggestions from Gemini by editing the description and clicking send Send.
   • Edit the SQL expression.
5. In the Target column field, select a column.
6. Optional: Click Preview and review the step.
7. Click Apply.

Delete an applied step

To delete an applied step, do the following:

1. In the data view, go to the Steps list and click Applied steps.
2. Select a step.
3. Click more_vert Menu > Delete.

Run the data preparation

After you've added your data preparation steps, configured the destination, and fixed any validation errors, you can perform test runs on a sample of the data, or deploy the steps and schedule data preparation runs. For more information, see Schedule data preparations.

Refresh data preparation samples

Data in the sample isn't automatically refreshed. If data in the source tables for the data preparation has changed, but the changes aren't reflected in the data sample of the preparation, click More > Refresh sample.

What's next

• Learn how to schedule data preparations.
• Learn about managing data preparations.
• Learn about quotas and limits for Gemini in BigQuery.
• Review Gemini in BigQuery pricing.