# SkillPatch skill: bigquery-ai-ml

This skill enables agents to leverage BigQuery's built-in AI and ML capabilities directly within SQL queries. It covers time-series forecasting, anomaly detection, key driver analysis, and generative AI content generation using functions like AI.FORECAST, AI.DETECT_ANOMALIES, AI.KEY_DRIVERS, and AI.GENERATE powered by Vertex AI and Gemini models.

You (the agent) were given this URL and asked to use this skill. This is a **public** skill — no SkillPatch account, API key, or prior setup is required. Two ways to use it:

**1. Use it right now** — the skill's complete file tree (SKILL.md + all reference files) is inlined below; read `SKILL.md` first, then follow it, consulting the other files as it directs.

**2. Install the exact package onto disk** (recommended if you can run a shell — this reproduces the skill byte-for-byte, including any binary assets that can't be inlined):

```bash
mkdir -p .claude/skills/bigquery-ai-ml
curl -sSL https://skillpatch.dev/install_skill/bigquery-ai-ml | tar -xz -C .claude/skills/
```

(`.claude/skills/` is Claude Code's convention; use whatever directory your agent loads skills from.)


---

## Skill files (5)

- `SKILL.md`
- `references/ai_detect_anomalies.md`
- `references/ai_forecast.md`
- `references/ai_generate.md`
- `references/ai_key_drivers.md`


### `SKILL.md`

```markdown
---
name: bigquery-ai-ml
metadata:
  category: AiAndMachineLearning
description: >-
  Leverages BigQuery's built-in machine learning and GenAI capabilities
  for advanced data analytics. Use when you need to write SQL queries
  that perform time-series forecasting, detect outliers, find key drivers, or leverage generative AI capabilities in BigQuery.
---

# BigQuery AI & ML

BigQuery integrates with Vertex AI to provide powerful machine learning and
generative AI capabilities directly within SQL queries using built-in functions
like `AI.FORECAST`, `AI.KEY_DRIVERS`, `AI.DETECT_ANOMALIES`, and `AI.GENERATE`.

## Reference Directory

-   [AI Forecast](references/ai_forecast.md): Leveraging pre-trained
    TimesFM model for forecasting without custom training.

-   [AI Detect Anomalies](references/ai_detect_anomalies.md): Identify
    deviations in time series data using pre-trained TimesFM model.

-   [AI Generate](references/ai_generate.md): General-purpose text and
    content generation using Gemini models.

-   [AI Key Drivers](references/ai_key_drivers.md): Automatically identify
    dimensional segments most responsible for driving changes in a metric.

## Related Skills

- [BigQuery Basics Skill](../bigquery-basics):
  SKILL.md file for core BigQuery concepts, resource management, CLI,
  and client libraries.

```


### `references/ai_detect_anomalies.md`

````markdown
# BigQuery AI.Detect_Anomalies

`AI.DETECT_ANOMALIES` uses the pre-trained **TimesFM** model to identify
deviations in time series data without needing to train a custom model.

## Syntax Reference

This function compares a target dataset against a historical dataset to identify
anomalies.

```sql
SELECT *
FROM AI.DETECT_ANOMALIES(
  { TABLE `project.dataset.history_table` | (SELECT * FROM history_query) },
  { TABLE `project.dataset.target_table` | (SELECT * FROM target_query) },
  data_col => 'DATA_COL',
  timestamp_col => 'TIMESTAMP_COL'
  [, model => 'MODEL']
  [, id_cols => ID_COLS]
  [, anomaly_prob_threshold => ANOMALY_PROB_THRESHOLD]
)

```

### Input Arguments

Argument                     | Requirement  | Type          | Description
:--------------------------- | :----------- | :------------ | :----------
**`historical_data`**        | **Required** | Table/Query   | The source table or subquery containing historical data for training context.
**`target_data`**            | **Required** | Table/Query   | The source table or subquery containing data to analyze for anomalies.
**`data_col`**               | **Required** | String        | The numeric column to analyze.
**`timestamp_col`**          | **Required** | String        | The column containing dates/timestamps.
**`id_cols`**                | Optional     | Array<String> | Grouping columns for multiple series (e.g., `['store_id']`).
**`anomaly_prob_threshold`** | Optional     | Float64       | Threshold for anomaly detection (0 to 1). Defaults to 0.95.
**`model`**                  | Optional     | String        | Model version. Defaults to `'TimesFM 2.0'`.

### Output Schema

| Column | Type | Description |
| :--- | :--- | :--- |
| **`id_cols`** | (As Input) | Original identifiers for the series. |
| **`time_series_timestamp`** | TIMESTAMP | Timestamp for the analyzed points. |
| **`time_series_data`** | FLOAT64 | The original data value. |
| **`is_anomaly`** | BOOL | TRUE if the point is identified as an anomaly. |
| **`lower_bound`** | FLOAT64 | Lower bound of the expected range. |
| **`upper_bound`** | FLOAT64 | Upper bound of the expected range. |
| **`anomaly_probability`** | FLOAT64 | Probability that the point is an anomaly. |
| **`ai_detect_anomalies_status`** | STRING | Error messages or empty string on success. A minimum of 3 data points is required. |

## Examples

### Basic Anomaly Detection

Detect anomalies in daily bike trips for a specific 2-month window based on
prior history.

```sql
WITH bike_trips AS (
  SELECT EXTRACT(DATE FROM starttime) AS date, COUNT(*) AS num_trips
  FROM `bigquery-public-data.new_york.citibike_trips`
  GROUP BY date
)
SELECT *
FROM AI.DETECT_ANOMALIES(
  -- Historical context (Training data equivalent)
  (SELECT * FROM bike_trips WHERE date <= DATE('2016-06-30')),
  -- Target range (Data to inspect for anomalies)
  (SELECT * FROM bike_trips WHERE date BETWEEN '2016-07-01' AND '2016-09-01'),
  data_col => 'num_trips',
  timestamp_col => 'date'
);

```

### Multivariate Detection (Multiple Series)

Use `id_cols` to detect anomalies separately for different user types (e.g.,
Subscriber vs. Customer) in the same query.

```sql
WITH bike_trips AS (
    SELECT
      EXTRACT(DATE FROM starttime) AS date, usertype, gender,
      COUNT(*) AS num_trips
    FROM `bigquery-public-data.new_york.citibike_trips`
    GROUP BY date, usertype, gender
  )
SELECT *
FROM
  AI.DETECT_ANOMALIES(
    # Historical data from a query
    (SELECT * FROM bike_trips WHERE date <= DATE('2016-06-30')),
    # Target data from a query
    (SELECT * FROM bike_trips WHERE date BETWEEN '2016-07-01' AND '2016-09-01'),
    data_col => 'num_trips',
    timestamp_col => 'date',
    id_cols => ['usertype', 'gender'],
    model => "TimesFM 2.5",
    anomaly_prob_threshold => 0.8);

```

````


### `references/ai_forecast.md`

````markdown
# BigQuery AI.Forecast

`AI.FORECAST` leverages the pre-trained **TimesFM** foundation model to generate
forecasts without the need to train and manage custom models.

## Syntax Reference

```sql
SELECT
  *
FROM
  AI.FORECAST(
    { TABLE `project.dataset.table` | (QUERY_STATEMENT) },
    data_col => 'DATA_COL',
    timestamp_col => 'TIMESTAMP_COL'
    [, model => 'MODEL']
    [, id_cols => ID_COLS]
    [, horizon => HORIZON]
    [, confidence_level => CONFIDENCE_LEVEL]
    [, output_historical_time_series => OUTPUT_HISTORICAL_TIME_SERIES]
    [, context_window => CONTEXT_WINDOW]
  )
```

### Input Arguments

| Argument | Requirement | Type | Description |
| :--------------------- | :----------- | :------------ | :---------------- |
| **`input_data`** | **Required** | | The source table or subquery containing historical data. |
| **`data_col`** | **Required** | String | The numeric column to predict. |
| **`timestamp_col`** | **Required** | String | The column containing dates/timestamps. |
| **`id_cols`** | Optional | Array<String> | Grouping columns for multiple series (e.g., `['store_id']`). |
| **`horizon`** | Optional | Int64 | Number of future points to predict. Defaults to 10. The valid input range is [1, 10,000]. |
| **`confidence_level`** | Optional | Float64 | Confidence interval (0 to 1). Defaults to 0.95. |
| **`model`** | Optional | String | Model version. Defaults to `TimesFM 2.0`. |
| **`context_window`** | Optional | Int64 | The number of historical data points the model uses to forecast. The min value is 64 and the max value is 2048 for `TimesFM 2.0`. If not set, the model determines this automatically. |

### Output Schema

The schema adjusts based on the `output_historical_time_series` flag.

Column                                | Type       | Included if output_historical_time_series=FALSE | Included if output_historical_time_series=TRUE | Description
:------------------------------------ | :--------- | :---------------------------------------------- | :--------------------------------------------- | :----------
**`id_cols`**                         | (As Input) | Yes                                             | Yes                                            | Original identifiers for the series.
**`forecast_timestamp`**              | TIMESTAMP  | **Yes**                                         | No                                             | Timestamp for predicted points.
**`forecast_value`**                  | FLOAT64    | **Yes**                                         | No                                             | The 50% quantile (median) prediction.
**`time_series_timestamp`**           | TIMESTAMP  | No                                              | **Yes**                                        | Uniform timestamp column for both history and forecast.
**`time_series_data`**                | FLOAT64    | No                                              | **Yes**                                        | Merged column: actual values for history, median for forecast.
**`time_series_type`**                | STRING     | No                                              | **Yes**                                        | Label: `'history'` or `'forecast'`.
**`prediction_interval_lower_bound`** | FLOAT64    | Yes                                             | Yes                                            | Lower bound (NULL for historical rows).
**`prediction_interval_upper_bound`** | FLOAT64    | Yes                                             | Yes                                            | Upper bound (NULL for historical rows).
**`confidence_level`**                | FLOAT64    | Yes                                             | Yes                                            | The constant confidence level used.
**`ai_forecast_status`**              | STRING     | Yes                                             | Yes                                            | Error messages or empty string on success. A minimum of 3 data points is required.

## Examples

### Forecasting with History

```sql
WITH
  citibike_trips AS (
    SELECT EXTRACT(DATE FROM starttime) AS date, usertype, COUNT(*) AS num_trips
    FROM `bigquery-public-data.new_york.citibike_trips`
    GROUP BY date, usertype
  )
SELECT *
FROM
  AI.FORECAST(
    TABLE citibike_trips,
    data_col => 'num_trips',
    timestamp_col => 'date',
    id_cols => ['usertype'],
    horizon => 30,
    output_historical_time_series => true);
```

````


### `references/ai_generate.md`

````markdown
# BigQuery AI.Generate

`AI.GENERATE` is a general-purpose function for text and content generation.

## Syntax Reference

```sql
AI.GENERATE(
  [ prompt => ] 'PROMPT',
  [, endpoint => 'ENDPOINT']
  [, model_params => 'MODEL_PARAMS']
  [, output_schema => 'OUTPUT_SCHEMA']
  [, connection_id => 'CONNECTION_ID']
  [, request_type => 'REQUEST_TYPE']
)
```

### Input Arguments

| Argument            | Requirement  | Type   | Description           |
| :------------------ | :----------- | :----- | :-------------------- |
| **`prompt`**        | **Required** | String | The prompt text or instruction for the model. |
| **`connection_id`** | Optional     | String | The connection ID. Optional if configured via other means or testing. |
| **`endpoint`**      | Optional     | String | The model name, e.g., `'gemini-2.5-flash'`. |
| **`output_schema`** | Optional     | String | Schema definition for structured output, e.g., `'answer BOOL, reason STRING'`. |
| **`request_type`**  | Optional     | String | `'DEDICATED'` or `'SHARED'`. |
| **`model_params`**  | Optional     | JSON   | JSON object for model parameters (e.g., `temperature`, `max_output_tokens`). |

### Output Schema

Returns a `STRUCT` with the following fields:

| Column Name         | Type                 | Description                    |
| :------------------ | :------------------- | :----------------------------- |
| **`result`**        | `STRING` (or Custom) | The generated content. If `output_schema` is used, this field is replaced by the schema's fields. |
| **`status`**        | `STRING`             | API response status (empty on success). |
| **`full_response`** | `JSON`               | The complete raw JSON response from the model (including safety ratings, usage metadata). |

## Examples

### Basic Text Generation

```sql
SELECT
  AI.GENERATE(
    'Summarize this article: ' || article_content,
    connection_id => 'my-project.us.my-connection',
    endpoint => 'gemini-2.5-flash'
  ) as summary
FROM `dataset.articles`
LIMIT 5;
```

### Structured Output Generation

```sql
SELECT
  AI.GENERATE(
    'Extract the date and amount from this invoice: ' || invoice_text,
    output_schema => 'date DATE, amount FLOAT64'
  ) as extracted_data
FROM `dataset.invoices`;
```

### Process images in a Cloud Storage bucket

```sql
CREATE SCHEMA IF NOT EXISTS bqml_tutorial;

CREATE OR REPLACE EXTERNAL TABLE bqml_tutorial.product_images
  WITH CONNECTION DEFAULT OPTIONS (
    object_metadata = 'SIMPLE',
    uris = ['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png']);

SELECT
  uri,
  STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS signed_url,
  AI.GENERATE(
    ("What is this: ", OBJ.GET_ACCESS_URL(ref, 'r')),
    output_schema =>
      "image_description STRING, entities_in_the_image ARRAY<STRING>").*
FROM bqml_tutorial.product_images
WHERE uri LIKE "%aquarium%";
```

````


### `references/ai_key_drivers.md`

````markdown
# BigQuery AI.Key_Drivers

`AI.KEY_DRIVERS` automatically identifies the key dimensional segments most
responsible for driving changes in a specified metric between a defined interest
group and a reference group.

## Syntax Reference

```sql
SELECT
  *
FROM
  AI.KEY_DRIVERS(
    { TABLE TABLE | (QUERY_STATEMENT) },
    metric_col => 'METRIC_COL',
    dimension_cols => DIMENSION_COLS,
    interest_label_col => 'INTEREST_LABEL_COL'
    [, min_apriori_support => MIN_APRIORI_SUPPORT]
    [, top_k => TOP_K]
    [, enable_pruning => ENABLE_PRUNING]
  )
```

### Input Arguments

Argument                  | Requirement  | Type            | Description
:------------------------ | :----------- | :-------------- | :----------
**`input_data`**          | **Required** |                 | The source table or subquery containing the data to analyze.
**`metric_col`**          | **Required** | `STRING`        | Metric column name. Must be of type: INT64, NUMERIC, BIGNUMERIC, or FLOAT64.
**`interest_label_col`**  | **Required** | `STRING`        | Boolean column name: `TRUE` for interest group, `FALSE` for reference group.
**`dimension_cols`**      | **Required** | `ARRAY<STRING>` | 1-12 dimension columns (INT64, BOOL, STRING); cannot be `metric_col` or `interest_label_col`.
**`min_apriori_support`** | Optional     | `FLOAT64`       | Minimum apriori support threshold [0, 1] for output segments. Default: 0.1. Cannot be used with `top_k`.
**`top_k`**               | Optional     | `INT64`         | Return top k insights [1, 1M] by apriori support. If unset, uses `min_apriori_support=0.1`. Cannot be used with `min_apriori_support`.
**`enable_pruning`**      | Optional     | `BOOL`          | If `TRUE` (default), redundant insights are pruned. If `FALSE`, all insights meeting thresholds are returned. Two segments are redundant if two conditions are met: 1) their metric values are equal 2) The dimensions and corresponding values of one row are a subset of the dimensions and corresponding values of the other. In this case, the row with more dimensions (the more descriptive row) is kept.

### Output Schema

Returns a `STRUCT` with the following fields:

Column Name                          | Type            | Description
:----------------------------------- | :-------------- | :----------
**`drivers`**                        | `ARRAY<STRING>` | Provides a list of drivers, or dimension values of interest, which describes each of the segments.
**`metric_interest`**                | `NUMERIC`       | The sum of the metric_column for the data in the interest segment.
**`metric_reference`**               | `NUMERIC`       | The sum of the metric_column for data in the reference segment.
**`difference`**                     | `NUMERIC`       | The difference between the interest and reference metric values for a segment.
**`relative_difference`**            | `NUMERIC`       | The relative change of a segment, calculated as the difference divided by the reference metric value.
**`unexpected_difference`**          | `NUMERIC`       | Measures deviation of segment from the rest of the population's growth. Calculated as: (segment relative_difference - complement relative_difference) * segment reference metric.
**`relative_unexpected_difference`** | `NUMERIC`       | The unexpected_difference divided by the expected interest metric value for a segment.
**`contribution`**                   | `NUMERIC`       | Contains the absolute value of the difference value: `ABS(difference)`.
**`apriori_support`**                | `NUMERIC`       | Segment size relative to the total population (filters small segments).

## Examples

### Identifying Key Drivers in 2024 H2 Liquor Sales

```sql
WITH InputData AS (
  SELECT
    sale_dollars,
    city,
    category_name,
    vendor_name,
    (date > '2024-07-01') AS IS_H2
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  WHERE EXTRACT(YEAR FROM DATE) = 2024
)
SELECT *
FROM AI.KEY_DRIVERS(
  TABLE InputData,
  metric_col => 'sale_dollars',
  dimension_cols => ['city', 'vendor_name', 'category_name'],
  interest_label_col => 'IS_H2',
  min_apriori_support => 0
);
```

### Finding Top 5 Key Drivers in Bottle Costs

```sql
SELECT *
FROM AI.KEY_DRIVERS(
  (
    SELECT
      state_bottle_cost,
      city,
      category_name,
      (EXTRACT(MONTH FROM date) >= 7) AS is_h2
    FROM `bigquery-public-data.iowa_liquor_sales.sales`
    WHERE EXTRACT(YEAR FROM date) = 2024
  ),
  metric_col => 'state_bottle_cost',
  dimension_cols => ['city', 'category_name'],
  interest_label_col => 'is_h2',
  top_k => 5
);
```


````