Parameterized query tools usage examples#

This page provides complete examples of parameterized tool definitions, invocation, and rendered SQL. For more information, read this MCP tool’s documentation.

Customer analysis with static tables#

This example demonstrates static table resources, CONSTANT_LIST with allowedValues, COLUMN_REF with parent validation, FUNCTION_REF, and optional parameters with defaults.

name: customer_analysis
description: >
  Analyze customers by market segment with flexible filtering.
  Returns customer details and aggregated order information.

tables:
  - name: customers
    tableName: tpch.tiny.customer
  - name: orders
    tableName: tpch.tiny.orders

parameters:
  - name: segments
    type: CONSTANT_LIST
    description: Market segments to include in analysis
    sqlType: VARCHAR
    required: true
    constraints:
      minItems: 1
      maxItems: 5

  - name: min_balance
    type: CONSTANT
    description: Minimum account balance filter
    sqlType: DOUBLE
    required: false
    defaultValue: 0
    constraints:
      min: 0

  - name: group_by
    type: COLUMN_REF
    description: Column to group results by
    parent: customers
    required: true

  - name: agg_func
    type: FUNCTION_REF
    description: Aggregation function for order totals
    required: true
    constraints:
      allowedValues: ["SUM", "AVG", "COUNT"]

  - name: limit_rows
    type: CONSTANT
    description: Maximum rows to return
    sqlType: INTEGER
    required: true
    constraints:
      min: 1
      max: 1000

templateLogic: |
  SELECT
    {{ group_by }},
    COUNT(*) as customer_count,
    {{ agg_func }}(o.totalprice) as order_total
  FROM {{ customers }} c
  LEFT JOIN {{ orders }} o ON c.custkey = o.custkey
  WHERE c.mktsegment IN ({{ segments }})
    AND c.acctbal >= {{ min_balance }}
  GROUP BY {{ group_by }}
  ORDER BY order_total DESC
  LIMIT {{ limit_rows }}

Example invocation by the AI agent:

{
  "toolName": "customer_analysis",
  "parametersJson": {
    "segments": ["BUILDING", "AUTOMOBILE"],
    "min_balance": 1000,
    "group_by": "mktsegment",
    "agg_func": "SUM",
    "limit_rows": 10
  }
}

Dynamic table analysis with subquery filter#

This example demonstrates TABLE_REF and QUERY with requiredColumns constraints, COLUMN_REF validated against a dynamic TABLE_REF parent, pattern constraints, and DATE parameters.

name: dynamic_sales_report
description: >
  Generate a sales report from any table containing sales data.
  Filters results using a custom subquery for flexible data selection.
  The source table must have 'sale_date', 'amount', and 'region' columns.

parameters:
  - name: source
    type: TABLE_REF
    description: >
      Fully qualified table name (catalog.schema.table) containing sales data.
      Must include columns: sale_date, amount, region.
    required: true
    constraints:
      requiredColumns: ["sale_date", "amount", "region"]

  - name: filter_query
    type: QUERY
    description: >
      Subquery that returns region values to include in the report.
      Must output a 'region' column.
    required: true
    constraints:
      requiredColumns: ["region"]

  - name: group_column
    type: COLUMN_REF
    description: Column from the source table to group results by
    parent: source
    required: true

  - name: start_date
    type: CONSTANT
    description: Start date for the report period (YYYY-MM-DD)
    sqlType: DATE
    required: true

  - name: end_date
    type: CONSTANT
    description: End date for the report period (YYYY-MM-DD)
    sqlType: DATE
    required: true

  - name: region_pattern
    type: CONSTANT
    description: Regex pattern to filter region names (e.g., "AMERICA|EUROPE")
    sqlType: VARCHAR
    required: false
    defaultValue: ".*"
    constraints:
      maxLength: 100

templateLogic: |
  SELECT
    {{ group_column }},
    COUNT(*) as transaction_count,
    SUM(amount) as total_sales,
    AVG(amount) as avg_sale
  FROM {{ source }}
  WHERE sale_date BETWEEN {{ start_date }} AND {{ end_date }}
    AND region IN {{ filter_query }}
    AND regexp_like(region, {{ region_pattern }})
  GROUP BY {{ group_column }}
  ORDER BY total_sales DESC

Example invocation by the AI agent:

{
  "toolName": "dynamic_sales_report",
  "parametersJson": {
    "source": "analytics.public.sales_transactions",
    "filter_query": "SELECT DISTINCT region FROM analytics.public.active_regions WHERE is_active = true",
    "group_column": "region",
    "start_date": "2024-01-01",
    "end_date": "2024-12-31",
    "region_pattern": "AMERICA|EUROPE"
  }
}

Rendered SQL output:

EXECUTE IMMEDIATE '
  SELECT
    "region",
    COUNT(*) as transaction_count,
    SUM(amount) as total_sales,
    AVG(amount) as avg_sale
  FROM "analytics"."public"."sales_transactions"
  WHERE sale_date BETWEEN ? AND ?
    AND region IN (SELECT DISTINCT region FROM analytics.public.active_regions WHERE is_active = true)
    AND regexp_like(region, ?)
  GROUP BY "region"
  ORDER BY total_sales DESC
' USING DATE '2024-01-01', DATE '2024-12-31', 'AMERICA|EUROPE'

Data profiling with `FUNCTION_REF` signature constraints#

This uses a QUERY as a primary data source, COLUMN_REF validated against dynamic query output, FUNCTION_REF with both allowedValues and signature constraints, Pebble conditionals, and Pebble filters.

name: profile_column
description: >
  Profile a numeric column from any query result. Computes statistical metrics
  including count, null percentage, distinct values, and configurable aggregations.
  Use this to understand data distribution before analysis.

parameters:
  - name: data_query
    type: QUERY
    description: >
      SQL query returning the data to profile. Must include a numeric column
      to analyze. Example: SELECT amount, region FROM sales WHERE year = 2024
    required: true

  - name: profile_column
    type: COLUMN_REF
    description: The numeric column from data_query to compute statistics on
    parent: data_query
    required: true

  - name: central_tendency_func
    type: FUNCTION_REF
    description: Function to measure central tendency (typical value)
    required: true
    constraints:
      allowedValues: ["AVG", "APPROX_PERCENTILE"]

  - name: dispersion_func
    type: FUNCTION_REF
    description: Function to measure data spread/dispersion
    required: true
    constraints:
      allowedValues: ["STDDEV", "VARIANCE", "STDDEV_POP", "VAR_POP"]
      functionType: scalar

  - name: percentile_value
    type: CONSTANT
    description: Percentile to compute (0.0 to 1.0), used when central_tendency_func is APPROX_PERCENTILE
    sqlType: DOUBLE
    required: false
    defaultValue: 0.5
    constraints:
      min: 0.0
      max: 1.0

  - name: sample_limit
    type: CONSTANT
    description: Maximum rows to sample for profiling (0 for no limit)
    sqlType: INTEGER
    required: false
    defaultValue: 0
    constraints:
      min: 0
      max: 10000000

templateLogic: |
  WITH source_data AS (
    {{ data_query }}
  ),
  sampled_data AS (
    SELECT * FROM source_data
    {% if sample_limit > 0 %}LIMIT {{ sample_limit }}{% endif %}
  ),
  base_stats AS (
    SELECT
      COUNT(*) as total_rows,
      COUNT({{ profile_column }}) as non_null_count,
      COUNT(*) - COUNT({{ profile_column }}) as null_count,
      COUNT(DISTINCT {{ profile_column }}) as distinct_count,
      MIN({{ profile_column }}) as min_value,
      MAX({{ profile_column }}) as max_value,
      {{ central_tendency_func }}(
        {% if central_tendency_func == 'APPROX_PERCENTILE' %}
        {{ profile_column }}, {{ percentile_value }}
        {% else %}
        {{ profile_column }}
        {% endif %}
      ) as central_value,
      {{ dispersion_func }}({{ profile_column }}) as dispersion_value
    FROM sampled_data
  )
  SELECT
    total_rows,
    non_null_count,
    null_count,
    ROUND(100.0 * null_count / NULLIF(total_rows, 0), 2) as null_percentage,
    distinct_count,
    ROUND(100.0 * distinct_count / NULLIF(non_null_count, 0), 2) as uniqueness_percentage,
    min_value,
    max_value,
    max_value - min_value as value_range,
    central_value as {{ central_tendency_func | lower }}_value,
    dispersion_value as {{ dispersion_func | lower }}_value
  FROM base_stats

Example invocation by the AI agent:

{
  "toolName": "profile_column",
  "parametersJson": {
    "data_query": "SELECT totalprice, orderstatus FROM tpch.tiny.orders WHERE orderdate >= DATE '2024-01-01'",
    "profile_column": "totalprice",
    "central_tendency_func": "AVG",
    "dispersion_func": "STDDEV"
  }
}

Customer profile analysis with `STRUCT_LIST` and Pebble macros#

This uses STRUCT_LIST with mixed field types (EXPRESSION, TABLE_REF, COLUMN_REF, CONSTANT, CONSTANT_LIST), Pebble macros for reusable SQL fragments, and optional STRUCT_LIST parameters with EXPRESSION and ALIAS fields.

This example shows the full power of parameterized tools: the AI agent can define multiple population segments, each with its own source table, filter date, and segment values. The tool computes a standard set of demographic and behavioral metrics for each population, and any additional ad-hoc metrics the agent requests.

name: customer_profile_analysis
description: |
  Analyzes customer profiles across user-defined population segments. Computes
  standard demographic, channel engagement, product ownership, and financial
  metrics for each population, plus any additional metrics requested by the user.

  The LLM provides:
  1. Population definitions via 'populations' - each defines a label expression,
     source table, date column, filter date, and segment values. These are
     combined with UNION ALL to form the base CTE.
  2. Additional metrics via 'additional_metrics' - each defines an aggregate
     SQL expression and column alias to append beyond the standard metric set.

parameters:
  - name: populations
    type: STRUCT_LIST
    required: true
    description: |
      Population segments to analyze. Each population defines a labeled slice
      of the customer profile data. Segments are combined with UNION ALL.

      Example:
        - label: "'Overall Consumer'"
          source_table: catalog.schema.profile_table
          date_col: xtrct_dt
          date: "2025-12-31"
          segment_list: ["1 retail", "2 preferred"]
    constraints:
      minItems: 1
    fields:
      - name: label
        type: EXPRESSION
        description: "Population label expression, e.g. 'Retail' or CASE WHEN type = 'C' THEN age_group ELSE 'N/A' END"
      - name: source_table
        type: TABLE_REF
        description: Fully qualified profile table name (catalog.schema.table)
      - name: date_col
        type: COLUMN_REF
        parent: source_table
        description: Date column to filter on (e.g. xtrct_dt)
      - name: date
        type: CONSTANT
        sqlType: DATE
        description: Extract date to filter on (YYYY-MM-DD)
      - name: segment_list
        type: CONSTANT_LIST
        sqlType: VARCHAR
        description: "Segment values to include, e.g. '1 retail', '2 preferred'"

  - name: additional_metrics
    type: STRUCT_LIST
    required: false
    defaultValue: []
    description: |
      Additional aggregate metrics beyond the standard set. Each metric defines
      an aggregate SQL expression and a column alias.

      Example:
        - expr: "AVG(CASE WHEN chk_fl = 1 THEN chk_bl_am END)"
          alias: avg_checking_balance
    fields:
      - name: expr
        type: EXPRESSION
        description: "Aggregate SQL expression"
      - name: alias
        type: ALIAS
        description: Column alias for the metric

templateLogic: |
  {% macro pct(condition, alias) %}CAST(SUM(CASE WHEN {{ condition }} THEN 1 ELSE 0 END) AS DECIMAL(38,5)) / COUNT(pty_id) * 100.0 AS {{ alias }}{% endmacro %}
  WITH base AS (
    {% for pop in populations %}
    SELECT {{ pop.label }} AS population, *
    FROM {{ pop.source_table }}
    WHERE CAST({{ pop.date_col }} AS DATE) = {{ pop.date }}
      AND segment IN ({{ pop.segment_list }})
    {% if not loop.last %}UNION ALL{% endif %}
    {% endfor %}
  )
  SELECT
    population,
    CAST(xtrct_dt AS DATE) AS extract_date,
    COUNT(pty_id) AS client_count,
    AVG(tot_rev_3ma_am) AS avg_total_revenue,
    {{ pct("dig_actv_in = 1", "pct_digital_active") }},
    {{ pct("call_fl = 1", "pct_contact_center") }},
    {{ pct("chk_fl = 1", "pct_checking") }},
    {{ pct("dep_fl = 1", "pct_deposit") }},
    {{ pct("sav_fl = 1", "pct_savings") }},
    {{ pct("crcrd_fl = 1", "pct_credit_card") }}{% for m in additional_metrics %},
    {{ m.expr }} AS {{ m.alias }}{% endfor %}
  FROM base
  GROUP BY population, CAST(xtrct_dt AS DATE)
  ORDER BY population

Patterns that were demonstrated in the example file:

Pebble macro ({% macro pct %}): Defines a reusable SQL fragment that computes a percentage metric. The macro is invoked multiple times with different conditions and aliases, which avoids duplicating complex aggregate expressions.
STRUCT_LIST iteration: The {% for pop in populations %} loop emits one SELECT per population and combines them with UNION ALL. Each struct element supplies:
- a TABLE_REF auto‑quoted
- a COLUMN_REF validated against the sibling TABLE_REF
- a CONSTANT bound as a single value
- a CONSTANT_LIST bound as multiple values
Optional STRUCT_LIST with defaultValue: []: The additional_metrics parameter defaults to an empty list, so the standard metrics are always computed. When the AI agent provides additional metrics, they are appended with EXPRESSION, SQL-parser-validated, and ALIAS safe identifier fields.
EXPRESSION fields: Used for both the population label, which can be a string literal or a CASE expression, or for additional metric expressions, which can be any valid aggregate expression.

Example invocation by the AI agent:

{
  "toolName": "customer_profile_analysis",
  "parametersJson": {
    "populations": [
      {
        "label": "'Overall Consumer'",
        "source_table": "analytics.derived.customer_profile",
        "date_col": "xtrct_dt",
        "date": "2025-12-31",
        "segment_list": ["1 retail", "2 preferred"]
      },
      {
        "label": "'Retail Only'",
        "source_table": "analytics.derived.customer_profile",
        "date_col": "xtrct_dt",
        "date": "2025-12-31",
        "segment_list": ["1 retail"]
      }
    ],
    "additional_metrics": [
      {
        "expr": "AVG(CASE WHEN chk_fl = 1 THEN chk_bl_am END)",
        "alias": "avg_checking_balance"
      }
    ]
  }
}

Parameterized query tools usage examples#

Customer analysis with static tables#

Dynamic table analysis with subquery filter#

Data profiling with FUNCTION_REF signature constraints#

Customer profile analysis with STRUCT_LIST and Pebble macros#

Data profiling with `FUNCTION_REF` signature constraints#

Customer profile analysis with `STRUCT_LIST` and Pebble macros#