Query tables

Tables built from SQL over your other tables — DreamSheets' answer to pivot tables, and quite a bit more.

A query table is a table whose columns and rows come from a SQL query over the document's other tables (or an external database). The query stays attached, so the table can be refreshed whenever its sources change. It's how you reshape data in DreamSheets: grouping, joining, filtering, ranking.

Don't write SQL? You may not have to. The table menu's Summarize…, Combine with another table…, and Reshape ▸ Pivot / Unpivot build the common query tables from a small form — no SQL in sight, and what they produce is an ordinary query table you can open and edit later. See cleaning and reshaping. Beyond those, describe what you want in plain language and let the AI generator write the query, or install the SQL Builder plugin (Tools → Plugin Manager → Browse Plugins), which gives you pivot-table-style fields — Group by, Values, Filters — and writes the SQL for you, with a SQL tab so you can see exactly what it produced and take it over whenever you want.

Creating one

Insert → Linked Table… opens the query editor:

1. Pick a source — this document's tables, or a saved external database connection.
2. Write SQL in the highlighted editor (autocomplete included — and hovering a table name in the SQL highlights that table on the canvas). Reference tables by their exact names; quote names containing spaces: FROM "Q1 Sales".
3. Or click the AI generator and describe what you want in plain language — "total amount by region, largest first" — and it writes the SQL, which you can inspect and edit. The prompt is saved with the query so you can see later what produced it.
4. Run. The result becomes a table on the canvas.

An AI tool connected over MCP can create both kinds for you: create_query_table for a query over this document's tables, and create_external_query_table — with list_connections to find the connection — for one that runs on a saved database connection. What it makes is an ordinary query table: same card, same Refresh query, same undo.

Queries run in an embedded DuckDB engine, so the full modern SQL surface is available: joins, GROUP BY, window functions, CTEs, QUALIFY, and friends. Calculated columns are materialized before the query runs, so SQL can read them like any other column.

SELECT Region, SUM(Amount) AS Revenue, COUNT(*) AS Orders
FROM "Sales"
GROUP BY Region
ORDER BY Revenue DESC

Tiles and named values in SQL

A tile or named value is a name in SQL, just as it is in a formula — so a query can read the value a user types into a KPI tile:

SELECT *
FROM "Transactions"
WHERE "Date" > "Budget Month"

Write the name the way SQL writes any name: bare (BudgetMonth) or in double quotes ("Budget Month"). Single quotes are text in SQL — 'Budget Month' is the words Budget Month, not the tile. The autocomplete offers tiles and named values wherever a column can go.

• A column wins. If a table the query reads has a column with the same name, the name means the column — exactly the formula rule. Aliases and names the query defines itself (AS Total) win too.
• The value is typed. A date tile (or a tile formatted as a date, or a formula like EOMONTH(…)) compares as a real date; numbers, text and true/false as themselves. It is passed as a value, never pasted into the SQL, so a tile holding '; DROP … is just text.
• A broken tile stops the query with a message naming it, rather than quietly comparing against nothing.
• Changing the tile marks the query out of date (the same badge a changed source table gives), and it refreshes shortly after you stop editing. If the tile is a formula over another table, editing that table counts too.
• External databases work the same way, except they never refresh on their own: the table is badged and waits for Refresh query. Plugin connectors can't take tile values yet.

Linked vs. static

The "Linked — keep the query so this table can be refreshed" toggle (on by default) is the difference between:

• Linked: the query is stored; Refresh query in the table's menu re-runs it against current data. Lineage arrows show which tables feed it. Selecting several query tables refreshes them in one batch.
• Static: a one-time snapshot — the result data with no attached query.

A linked query over this document's tables also refreshes automatically: edit a source table and every query reading it re-runs as part of that same edit — a chain (A feeds B feeds C) settles in one pass, and undo takes the edit and the recomputes back together. One guard: a query table you've added your own columns to is marked out of date instead of being overwritten, so the automatic pass never deletes your work — refreshing it explicitly asks first. External-database queries are the exception: they're snapshots with manual refresh (see below).

Formats and sizing

Result columns inherit sensible presentation automatically: a column that matches a source table's column by name inherits its display format (your Amount currency column stays currency after GROUP BY), and columns are auto-sized to their content when the table is created (capped so one long text column can't blow the card out). Everything remains overridable — a query table is a real table, so all table features apply: formats, summary rows, conditional formatting, charts over it.

Query tables vs. pivot tables

If you're coming from Excel, the query table is the pivot table's replacement — the same job with a different (and, we'd argue, better) contract:

The trade: you (or a form, or the AI generator) write a query instead of dragging fields. What you get back is composability — RevenueByRegion is a real named table that formulas can LOOKUP into and other queries can JOIN against, which a pivot cage never gives you.

A classic pivot, as a query table:

SELECT Product,
       SUM(CASE WHEN Quarter = 'Q1' THEN Amount END) AS Q1,
       SUM(CASE WHEN Quarter = 'Q2' THEN Amount END) AS Q2,
       SUM(CASE WHEN Quarter = 'Q3' THEN Amount END) AS Q3,
       SUM(CASE WHEN Quarter = 'Q4' THEN Amount END) AS Q4
FROM "Sales"
GROUP BY Product
ORDER BY Product

Query tables from external databases

Insert → Table from Database… points the same editor at a saved connection (File → Database Connections… — SQLite, DuckDB/Parquet files, PostgreSQL, MySQL/MariaDB, SQL Server, plus any kind a plugin connector adds). Three things work differently from an internal query, and they're the ones worth knowing up front:

• The SQL runs on that database, in its dialect. The embedded engine over your document isn't involved, so you write PostgreSQL, MySQL or T-SQL. The autocomplete switches with the source: pick a database and it offers that database's tables and columns, not your document's. (A DuckDB-file connection is the one that reads in DuckDB's dialect — but against that file, still not your document.)
• The result is a snapshot. Internal query tables recompute when their sources change; an external one can't — nothing tells DreamSheets the server changed. Use Refresh query in the table's menu. A result that won't fit in the memory this document has left — over 100,000 rows, or wide enough that fewer rows cost as much — imports in full but lands as a read-only Big table; filter or aggregate into a smaller table when you need to edit the result. (SQL Server tops out at 500,000 rows and plugin connectors at 100,000, with an error rather than a truncation — details under Import & export.)
• The AI generator is internal-only. It builds its schema context from this document, so it's hidden when the source is a database.

Dates come across as real date columns, and MySQL DECIMAL / PostgreSQL NUMERIC / SQL Server MONEY as numbers — the result is an ordinary table, so everything on this page applies to it. The document stores only the connection id, never credentials; on a machine without that connection the table keeps its last refreshed data. Full capabilities, limits and the credential/TLS story: Import & export.

When to use what

• One more column on an existing table → a calc column with LOOKUP.
• A new table shaped differently — grouped, joined, filtered, ranked → a query table.
• Logic beyond SQL — iteration, external libraries, machine learning → a script.