Querying and Charting with AI Blocks

This tutorial builds a pipeline that answers a question about a table whose columns are not known until it runs. A pivot turns one row per tree into one column per species; you cannot write SQL against those columns in advance, because which species appear depends on the data. ai.sql can: it reads the pivoted table's schema when the pipeline runs and writes a query fitted to it.

You will need a model key stored as a secret. Locally:

spade secret set my-anthropic-key

In the cloud, add it on the secrets page (or spade secret set --remote my-anthropic-key).

1. Read and pivot🔗

trees.csv has one row per tree: plot_id, species, biomass. Pivot it to one row per plot and one column per species:

name: biomass-by-plot
version: "1.0"
blocks:
  - id: "@trees"
    name: data.read
    inputs: []
    args:
      uri: /data/trees.csv

  - id: "@pivot"
    name: base.pivot
    inputs: ["@trees"]
    args:
      direction: wider
      id_columns: plot_id
      names_from: species
      values_from: biomass

2. Have ai.sql write the query🔗

  - id: "@write_query"
    name: ai.sql
    inputs:
      - { block: "@pivot", output: result, as: tables }
    args:
      description: >-
        For every plot, the total biomass across all the species columns
        (missing counts as zero) and the species with the most biomass.
        Columns: plot_id, total_biomass, top_species.
      provider: anthropic
      model: claude-opus-5-5
      share: schema
    secrets:
      llm: my-anthropic-key

A few things to notice:

• The description names the output columns. You know what you want out even when you don't know what goes in; saying so makes the query -- and anything downstream that uses those columns -- predictable.
• share: schema sends the table's column names and types and nothing else. That is enough here: every species is a column name. A question that filters on a value the model has never seen would need summary. See AI Blocks and Your Data.
• The pivoted table is t0. Tables connected to tables are always t0, t1, ... in order.

ai.sql drafts a query, tries it against the table, and submits it; the block accepts it only once it runs. Its output is a file like this:

-- spade:tables 1
-- spade:duckdb 1.5
-- Generated by ai.sql (anthropic / claude-opus-5-5).
-- Description:
--   For every plot, the total biomass across all the species columns ...
SELECT plot_id,
       coalesce(acru, 0) + coalesce(abba, 0) + coalesce(pist, 0) AS total_biomass,
       ...
FROM t0

3. Run the query🔗

  - id: "@run_query"
    name: duckdb.query_file
    inputs:
      - { block: "@write_query", output: query, as: query }
      - { block: "@pivot", output: result, as: tables }

duckdb.query_file runs with no network access, on a connection that can read the connected tables and nothing else. as: names the inputs: a bare reference would be ambiguous, since the query is a file and a file can also fill the tables collection.

4. Chart it🔗

  - id: "@chart"
    name: viz.bar
    inputs:
      - { block: "@run_query", output: result, as: table }
    args:
      x: top_species
      y: total_biomass
      aggregate: count
      title: Plots by dominant species

viz.bar also outputs the Vega-Lite spec it built. To change something the form does not offer, take that spec, edit it, and render it with viz.vegalite -- or replace viz.bar with ai.viz and describe the chart instead.

Run it🔗

spade run biomass-by-plot.yaml

Look in the run's outputs for @write_query's query.sql -- the query the model wrote -- and its provenance.json, the record of every result the agent's tools sent to the model.

Keeping the query🔗

Once the query is right, you may not want a model in the loop at all. Save query.sql, upload it (spade data upload query.sql), and replace @write_query with a platform.upload_file block pointing at it. duckdb.query_file runs it exactly as before; the pipeline no longer calls a model, and reruns are fully deterministic.

Running the pipeline again unchanged locally replays the cached query rather than asking the model again. To get a fresh draft with nothing else changed, set ai.sql's nonce argument to any new value.