SQL Query

Write a DuckDB SELECT against your own data. The result becomes the grid.

What it does

Your file is loaded into DuckDB in the browser. SQL Query gives you the engine directly.

Write a query. The app checks it, runs it, and replaces the grid with the result.

The query becomes a normal pipeline step. You can edit it, delete it, or put more steps after it.

Open the panel and run a query

  1. Type sql in the Search transforms box, in the Pipeline panel.
  2. Select SQL Query in the results.
  3. Write your query in the SQL Query box. The placeholder reads SELECT * FROM data.
  4. Click Apply.

A failed check shows the DuckDB message at the bottom of the panel. The step is not added, so nothing breaks.

Your table is called data

Whatever your file is named, the table this step reads is available as data.

SELECT * FROM data
LIMIT 100

That is the whole rule. No sanitized file name to look up, no internal view name to copy.

data works anywhere a table name works: in a subquery, inside a CTE, on both sides of a join, twice in the same query.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS r
  FROM data
)
SELECT region, customer_name, revenue
FROM ranked
WHERE r <= 3

It means this step's input, not the original file

Put a Filter step before your SQL step and data is the filtered rows. Put the SQL step first and data is the file as loaded.

So each step reads what the step above it produced. That is the same rule the visual operations follow, spelled out.

Edit an upstream step later and your SQL does not need touching. The pipeline reruns and data points at the new upstream result.

The real table name still works

Nothing about the old way changed. Every saved pipeline keeps running, and you can name the table directly whenever you prefer to.

The name comes from the file name.

  • The extension is dropped.
  • Every character outside a-z A-Z 0-9 _ becomes an underscore.
  • A duplicate name gets a numeric suffix, such as orders_2.
  • data is reserved, so data.csv loads as data_2.

So Q1 sales.csv becomes Q1_sales. The bundled sample file loads as sample_data.

In a pipeline, that name means the same thing data does. References to it are rewritten to the previous step's output before the step runs.

You need the real name for one job: joining across files. Only the current step's input answers to data, so the other file goes in by name.

What if my file is called data.csv?

It loads as data_2, and data keeps meaning your step's input. The name is reserved so the alias works the same for every file you open.

One exception: a workspace saved before that rule can restore a table already named data. On that table's own view nothing changes. On another file's view the panel hint tells you to name that table directly.

What the validator allows

Two checks run before the step is added.

The first is static:

  • The query must start with SELECT or WITH. Anything else gives Only SELECT queries are allowed.
  • Write keywords are rejected: DROP, DELETE, UPDATE, INSERT, ALTER, TRUNCATE.
  • Engine and file keywords are rejected too: ATTACH, DETACH, COPY, EXPORT, IMPORT, LOAD, INSTALL.
  • One statement per step. A second statement after a semicolon is rejected.

The second check executes your query with LIMIT 0. A syntax error or an unknown column stops the apply and shows the DuckDB error.

Example queries

These run against the sample file. It loads as sample_data, and answers to data like every other table does.

Aggregation by category:

SELECT
  category,
  COUNT(*) AS orders,
  ROUND(SUM(revenue), 2) AS revenue,
  ROUND(AVG(revenue), 2) AS avg_order
FROM data
GROUP BY category
ORDER BY revenue DESC

A window function that ranks orders inside each region:

SELECT
  region,
  customer_name,
  revenue,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region,
  SUM(revenue) OVER (PARTITION BY region) AS region_total
FROM data

A CTE that reshapes columns without a join:

WITH margins AS (
  SELECT
    order_id,
    product,
    revenue - cost AS margin,
    DATE_TRUNC('month', order_date) AS order_month
  FROM data
)
SELECT
  order_month,
  COUNT(*) AS orders,
  ROUND(SUM(margin), 2) AS total_margin
FROM margins
GROUP BY order_month
ORDER BY order_month

A self-join, to show that data can appear more than once:

SELECT
  a.region,
  a.customer_name,
  a.revenue,
  ROUND(a.revenue - b.region_avg, 2) AS vs_region_avg
FROM data a
JOIN (
  SELECT region, AVG(revenue) AS region_avg
  FROM data
  GROUP BY region
) b USING (region)
ORDER BY vs_region_avg DESC

Notes

  • DuckDB read syntax is available inside a SELECT. That includes QUALIFY, EXCLUDE, list functions, and JSON operators.
  • Other loaded files are separate tables. Query them by name to join across files. data is always the current step's own input.
  • If one of your columns is also called data, qualify it: SELECT data.data FROM data, or give the table an alias and use d.data.
  • A step that uses data keeps its result in memory rather than recomputing it on every read. Results are identical either way.
  • Use TRY_CAST instead of CAST. A bad value then becomes NULL instead of an error.
  • The panel has a Generate SQL from intent button. It needs an LLM API key, set in Settings.
  • Prefer the visual operations for routine work. They stay readable for the next person who opens the pipeline.
Try SQL Query with sample data →

Related Operations