BigQuery Data Canvas prompting

[https://cloud.google.com/blog/products/data-analytics/how-to-write-prompts-for-bigquery-data-canvas](https://cloud.google.com/blog/products/data-analytics/how-to-write-prompts-for-bigquery-data-canvas)

# Prompting Best Practices for BigQuery Data Canvas

## By Christine De Sario (Customer Engineer - Data & Analytics, Google) and Layolin Jesudhass (Global Generative AI Solutions Architect, Google)

## Introduction

Traditionally, strong SQL knowledge has been critical for data professionals. However, generative AI is now helping data professionals of all skill levels use natural language prompts to perform various data tasks, democratizing access to data and insights and promoting collaboration.

In the BigQuery ecosystem, Gemini is the foundation model that enables users to:

- Find data
- Create SQL
- Generate charts
- Create data summaries

These capabilities are commonly referred to as NL2SQL (Natural Language to SQL) and NL2Chart (Natural Language to Chart), and are available through **BigQuery data canvas**, a versatile tool that helps developers, data scientists, and analysts share their query building processes and insights.

Many data professionals perceive that out-of-the-box large language models (LLMs) are not good at generating SQL. However, with the right prompting techniques, Gemini in BigQuery via data canvas can generate complex SQL queries with the context of your data corpus.

## Five Tips for Refining Natural Language Prompts

### 1. Clarity is Key

When writing prompts, try to be as clear as possible to avoid confusion that leads to incomplete or inaccurate SQL.

#### Precision over Ambiguity

State your request clearly and avoid being vague.

**DON'T**: "Tell me about my sales data."
**DO**: "Show me a breakdown of total sales revenue by product category for the last quarter."

#### Context Matters

Provide any relevant background information to help Gemini understand your request.

**DON'T**: "What's the trend?"
**DO**: "What's the trend of monthly active users for our mobile app over the past year?"

#### Complete Sentences

Use full sentences with more details to avoid misinterpretation.

**DON'T**: "Show me full month name for comitter's date for the repo like 'cloud-vision'"
**DO**: "Convert months in integers to its complete month name for committer's date. List commit, repo name, author name, committer name, author date and year, committer date and year for repo like 'cloud-vision'. Use '%' in like"

### 2. Ask Direct Questions

When prompting an LLM, it's important to be straightforward and unambiguous.

#### Ask One Question at a Time

For the most precise answer, focus on asking a single question with detailed instructions. Try to be as descriptive as possible in executing one transformation.

#### Avoid Overloading

Keep prompts concise to avoid overwhelming Gemini in BigQuery with too much information. Consider adding a new node for the next transformation if needed.

**DON'T**: "List all the columns except parent, trailer, and difference. Get only the repo name for the first entry in the repeated column and convert both author and committer date in seconds to timestamp seconds to a date. Include commit, repo, author, and committer names too."

**DO**: Separate requests into two separate nodes in series:

```text
# prompt: List all the columns except parent, trailer, and difference. Get only the repo name for the first entry in the repeated column
```

```text
# prompt: convert both author and committer date in seconds to timestamp seconds to a date. Include commit, repo, author and commiter names too
```

### 3. Give Focused and Explicit Instructions

LLMs thrive on specificity.

#### Emphasize Key Terms

You can highlight important terms to guide Gemini's understanding.

Example:

```text
# prompt: list all columns by unnesting author and committer by prefixing the record name to all the columns. match repo name for the first entry only in the repeated column that has name like GoogleCloudPlatform. Make sure to use "%" for like
```

### 4. Specify Order of Operations

Providing instructions in a clear and ordered manner is important for Gemini to process and respond effectively.

#### Example of How Prompt Structure Affects SQL Output

**Original prompt**:

```text
# prompt: list all columns by unnesting author and committer by prefixing the record name to all the columns. match repo name for the first entry only in the repeated column that has name like GoogleCloudPlatform.
```

**After reversing the order** (results in extra OFFSET syntax and no data displayed):

```text
# prompt: match repo name for the first entry only in the repeated column that has name like GoogleCloudPlatform. list all columns by unnesting author and committer by prefixing the record name to all the columns.
```

**Adding explicit instructions** (causes an error):

```text
# prompt: Make sure to use "%" for like and match repo name for the first entry only in the repeated column that has name like GoogleCloudPlatform. list all columns by unnesting author and committer by prefixing the record name to all the columns.
```

This resulted in an error: "No matching signature for operator LIKE for argument types: ARRAY<STRING>, STRING. Supported signatures: STRING LIKE STRING; BYTES LIKE BYTES"

### 5. Refine and Iterate

If at first you don't succeed, try again.

- **Experimenting**: Try different phrasings and approaches to see what yields the best results.
- **Learning from feedback**: Use Gemini's responses to adjust your prompts and improve future interactions.

## Creating Charts with NL2Chart

When it comes to visualizations, NL2Chart can transform natural language descriptions into insightful charts and graphs. All the preceding advice still applies, plus these additional guidelines:

### Key Guidelines for NL2Chart

1. **Be specific and concise**: Clearly state the chart type and the specific data to visualize
2. **Define the data**: Specify the exact data points, variables, or categories
3. **Set your timeframe**: Clearly define the time period for analysis
4. **Units and labels**: Mention the units of measurement and desired labels
5. **Customization**: Include preferences for colors, styles, or formatting options

### Example Prompts

- **Basic**: "Create a bar chart showing the sales revenue of each product category in the last quarter."
- **Intermediate**: "Generate a line chart comparing the stock prices of Apple and Microsoft from January 1, 2020, to December 31, 2023."
- **Advanced**: "Produce a scatter plot illustrating the relationship between advertising spending and website traffic for the past year, with a trendline and R-squared value."

### Additional Tips

- **Experiment**: Try different phrasings and levels of detail
- **Iterate**: Refine your prompt if the initial chart isn't what you expected
- **Be aware of limitations**: NL2Chart might not understand highly complex requests

### Customization Options

You can customize:

- Chart color schemes (trendlines, bars, pie areas, data series)
- Chart axes, axis labels, titles, and legends
- Data sorting (increasing, decreasing, or alphabetical order)

You can also use data canvas to generate insightful summaries that interpret and explain chart findings.

## Conclusion

Learning to prompt in data canvas requires practice but becomes more intuitive once you're familiar with the NL2SQL model. To get the most accurate and helpful responses:

1. Be clear and specific in your prompts
2. Use complete sentences
3. Provide relevant context
4. Ask direct questions
5. Experiment with different phrasings
6. Learn from Gemini's feedback

By following these guidelines, you'll unlock the full potential of Gemini in BigQuery and get the most accurate and helpful outputs in BigQuery data canvas.

## Resources

- Access BigQuery data canvas to put these tips into practice
- Find sample data canvas prompts on [Github](https://github.com/GoogleCloudPlatform/bigquery-data-studio-apps/tree/main/sample_data_canvas_prompts)
- Check out "Data Canvas - Transforming Data into Insights with AI" to see a real example using the Github repositories public dataset