# SQL nodes (/docs/analytical-apps/pipeline/sql-nodes) A **SQL** node is a step that transforms your data. It reads the output of earlier nodes and reshapes it into something new, for example combining two tables, filtering rows, or calculating a total. SQL is the standard language for working with data, and a SQL node uses a query that reads from the nodes feeding into it. ## You do not need to know SQL [#you-do-not-need-to-know-sql] This is the important part: you do not have to write SQL yourself. Describe the transformation you want in plain language, for example "keep only this year's orders and add up revenue per customer", and **Vera** writes the query for you. Vera is the no-code path; this page explains the node for when you want to read or adjust what it produced. See [Build with Vera](../build-with-vera.mdx). ## Working in a SQL node [#working-in-a-sql-node] If you do want to work with the query directly: 1. In the editor, open the SQL node. 2. Write or adjust the query. It reads from the nodes connected into this one. 3. Run the query to see its output and the columns it produces. A SQL node's results feed any nodes connected after it, and your Visualisation widgets. Because downstream steps depend on this node's output, they only have fresh data once the pipeline has run successfully. If the query has not been run yet, nodes that rely on it will not have data to work with until you click **Run**. Running a query in a SQL node is a permission of its own, separate from reading the app. It comes with the **Contributor**, **Analyst**, **App builder** and **Admin** roles on the whole workspace - a role on a single space or app does not give it - and you always hold it in your own personal space. Without it, the node shows its query with no **Run** button and a short note saying why. If the app could not check whether you hold it, the note says so instead; reload the page to try again. ### What the Run button can read [#what-the-run-button-can-read] Running a query in the editor reads **only this app's own nodes**, named directly by their id - the same rule the SQL editor and the SQL widget follow. A query may name a node, or a `WITH` clause it defines itself, and nothing else. An [indicator data](./indicator-data.mdx) or [analytic app data](./analytic-data.mdx) node can be read once the pipeline has run: the query reads the copy of its rows that run took. Before the app has a run there is no such copy, and the name is refused like any other name with nothing behind it. A local development stack never offers these two node types to a query and refuses their names; read one there through a SQL node that selects from it. These are refused before anything runs, with a message saying what to change: * a name with a prefix, such as `other_app.orders` or `main.orders` - write `orders`; * a name that is not a node of this app, such as another app's table, a system table like `duckdb_tables`, or a file path; * a node whose id is also the name of one of the database's own catalog tables, such as `duckdb_tables`, `pg_class` or `sqlite_master` - rename the node to read it; * a table function in a `FROM` clause, such as `read_parquet(...)`, `range(...)` or `unnest(...)` - `unnest(...)` in the `SELECT` list is fine; * `DESCRIBE`, `SHOW` or `SUMMARIZE` used as a subquery, `PIVOT` and `UNPIVOT`, reading an earlier version with `AT (VERSION => ...)`, a `WITH ... AS MATERIALIZED` clause, and an aggregate's internal state with `EXPORT_STATE`; * a function that reads settings, session details or the database catalog, runs SQL handed to it as text, or changes something - for example `current_setting(...)`, `current_database()`, `json_serialize_plan(...)` or `nextval(...)`. Ordinary functions for text, numbers, dates, lists and JSON, aggregates and window functions all work. A function written after a value, as in `name.upper()`, is refused too - write `upper(name)`; * a type that belongs to a schema, such as `CAST(x AS other_app.mood)`, or that is not one of DuckDB's built-in types; * a number too large for the database to hold, such as `1e400` - the query cannot be checked, so it is not run. The SQL keywords written without parentheses - `current_user`, `current_schema`, `current_date` and the like - still work, including as column names. They report only this query's own connection, never another app. A pipeline run and the live preview of a node hold its saved query to the same rule. Saving a query that uses one of these forms is not refused, but when the pipeline runs that node fails with the same message, and the nodes that read from it are skipped. Rewrite it without them - a conditional `sum(CASE WHEN ...)` in place of a `PIVOT`, or `SELECT unnest(range(10)) AS n` in place of `FROM range(10)`, for example. To use another app's data, add an [analytic app data](./analytic-data.mdx) node and read that node by its id. ### How much a page of results can hold [#how-much-a-page-of-results-can-hold] A query's results are shown one page at a time, with a count of every row the query produced. One page may hold at most **4 MB** of data and at most **262,144 values**. A value is measured as it would be shown as text, so a long text, a large list or a value nested inside another counts in full; and every element of a list, every field of a struct and every key and value of a map counts as a value of its own. A query whose page is over either limit is refused with a message saying so. Nothing is cut short: you never see part of a value, or some of the rows, presented as if it were the whole result. To bring the page under the limits: * select fewer columns, or shorter ones - for a long text, `left(text, 1000)` returns its first 1,000 characters and `length(text)` its length, and `len(list)` the length of a long list; * filter to fewer rows with a `WHERE` clause. A few column types cannot be shown as a query result at all: a `UNION`, a `VARIANT` and a fixed-size `ARRAY`, on their own or inside a list, struct or map. A query returning one is refused with a message naming the column. Convert it in the query - `CAST(... AS VARCHAR)` shows its value as text, and `CAST(... AS INTEGER[])` turns a fixed-size array into a list. The limits apply wherever a query's results are shown straight away: the **Run** button, the SQL editor, a SQL widget, the AI tools that query an app, and the preview of a SQL node described below. They limit what one page holds, not how many rows a query may produce. ## Seeing what a query produces before you run [#seeing-what-a-query-produces-before-you-run] You do not have to run the pipeline to see whether a query is right. Open a SQL node's **Data** tab when there is no run to show and it previews the node's CURRENT query over the CURRENT contents of the app's datasets, right then. A strip above the rows says so, because these are not run results: nobody else sees them, your Visualisation does not use them, and they change the moment you edit the query or the data. That makes the ordinary loop tight. Edit the query, look at the rows, fix it, look again - and only click **Run** when it is right. Type a value into a dataset's grid and the next preview already reflects it. A few things to know about it: * **It is for people who can edit the app.** A viewer sees the run results, as before. * **It shows the first page, in the query's own order.** Sorting, filtering and the search box are not offered here - a preview that quietly ignored a sort would be worse than one that does not offer it. Run the pipeline for the full grid. * **The columns come from what the query actually returns**, not from the Output columns list, so a query you have just rewritten shows its new columns immediately. * **If the query cannot run, it says why.** The message names the relation that did not resolve, which is usually a typo or a missing entry in `manifest.yml` (see below). ### What a preview can read [#what-a-preview-can-read] A preview binds **every dataset node in the app**, exactly as a run does - so a query reading a dataset works whether or not `manifest.yml` mentions it. The **SQL nodes** it binds are the ones your `manifest.yml` declares, and their inputs in turn. So a query that reads ANOTHER SQL node's output without listing that node in `manifest.yml` will not preview, and the message names it. Add it to `input_tables` and the preview works - which is worth doing anyway, since that entry is also what draws the arrow on the canvas and what tells the pipeline to run the two steps in the right order. Two kinds of input cannot be previewed at all, and the message says so rather than looking like a broken query: an [analytic-data import](./analytic-data.mdx) and an [indicator-data](./indicator-data.mdx) bridge. Their rows are fetched when the pipeline runs, so there is nothing live to read. Run the pipeline to see a node that depends on one. ## `manifest.yml` has to match the query [#manifestyml-has-to-match-the-query] `manifest.yml` is where a SQL node declares which other nodes it reads. That list is not a convenience: it is the only thing that draws the arrows on the canvas and the only thing that tells a run which step has to finish before this one starts. A query that reads a node the list does not mention still runs, but the app's picture of itself is then wrong, and two steps that must run in order are free to run in the wrong one. So saving a SQL node checks the two against each other, and refuses the save with a message naming the node and the table when they disagree: * **The query reads a node `input_tables` does not list.** Add it to the list. * **`input_tables` lists a node the query never reads.** Remove it, or read it in the query. * **`input_tables` names something that is not a node of this app.** Usually a typo in the id. A node that reads no other node needs no list, and an empty `manifest.yml` is right for it. If you build apps through Vera or through an AI connector, this is handled for you: the tools require the list and are told the same rule, so the arrows are there from the first version rather than after you ask for them. ## Reading a dataset's row id [#reading-a-datasets-row-id] Every dataset node's table carries three extra columns after your own: | Column | Meaning | | ------------- | ------------------------------------------------------ | | `_row_id` | The row's stable id - the same one the data grid uses. | | `_created_at` | When the row was first created. | | `_updated_at` | When the row was last changed. | They sit AFTER every column the dataset declares, in the order you declared them, and they can never clash with one of your own columns: a column name you write always starts with a letter. `SELECT * FROM orders` returns them alongside your columns. They do not become the node's output columns, though - a SQL node's **Output columns** list ignores them, so a query that selects `_row_id` saves and runs exactly like one that does not. The row id is what lets you join a row back to something said ABOUT that row - most usefully the [validation results](./validation.mdx). The validation node is normally called `validation_errors`, but an app where one of your own nodes already holds that name gets a numbered variant instead, so use the name your app actually shows: ```sql title="orders_with_findings.sql" SELECT o.*, e.validation_error FROM orders o LEFT JOIN validation_errors e ON e.dataset_technical_id = 'orders' AND e.row_id = o._row_id ``` It is available wherever you write SQL against the app - in a SQL node's script, in the node's preview, and in the ad-hoc query box - so the same query works in all three. A dataset with no rows yet still has the columns, so a query reading them returns no rows rather than failing. Rows uploaded before row ids existed read as empty: a made-up id that looked stable would be worse than an absent one. ## Output columns and how they are shown [#output-columns-and-how-they-are-shown] The **Output columns** list shows one entry per column the query produces. You cannot add, remove or reorder them here - that is what the query decides. What you can set is how each column READS: * **Display name** and **Description**, shown in the Data tab and in any picker that offers this column. * **Type**, which decides how the column's cells are rendered and filtered. A `Currency` column shows a currency symbol and grouped digits, a `Yes / No` column shows a check or cross instead of the words true and false, a date renders in your language's format, and an email column offers a mail link. Numeric types filter with the number filter, dates with the date filter, and the rest with a text filter that suggests the values actually present in the results. * **Currency code** and **Decimals**, shown for a `Currency` or `Percent` column. ### Every column the query returns needs a usable name [#every-column-the-query-returns-needs-a-usable-name] A column's identifier comes straight from the query, so the query has to give each one a name that can be used as an identifier: lowercase letters, digits and underscores, starting with a letter, up to 32 characters. `order_id`, `revenue` and `region_2024` are all fine. Most queries already satisfy this, because selecting a column keeps its name. What does not is an expression you have not named. `SELECT count(*)` returns a column literally called `count_star()`, and `SELECT a + b` returns one called `(a + b)`. Give those an alias: ```sql SELECT count(*) AS order_count ``` Saving a node whose query returns a name that cannot be used is refused, and the message says which column it means. You will usually see it sooner than that: as soon as you run the query, a line above the results names the columns that need an alias. The alias is worth having for its own sake, not only to satisfy the rule. Later nodes and your Visualisation widgets refer to this column BY NAME, so `order_count` is something you can read and reference, while `count_star()` is not. Adding one costs nothing - you are naming the column you already meant. ### The type list is narrowed to what the query returns [#the-type-list-is-narrowed-to-what-the-query-returns] Under the type picker, a line states what the query returns for that column - text, a whole number, a decimal number, yes/no, a date, a time, or a date and time. The picker offers only the display types that fit it: | The query returns | You can display it as | | ----------------- | ------------------------------------------------------------ | | Text | Text, Long text, Email, Phone number, Dropdown, Multi-select | | Whole number | Whole number, Decimal, Percent, Currency, Year | | Decimal number | Decimal, Percent, Currency | | Yes / No | Yes / No | | Date | Date | | Time | Time | | Date and time | Date and time | The restriction is not cosmetic. A column's filter is applied by the database, so displaying a number as text would send text comparisons to a numeric column and the filter would stop working. A node that has never been run yet has no detected type, so it offers every display type until the first run tells it what the column really is. You do not have to set anything. Leave a column's type alone and it follows what the query returns, which is already better than an unformatted result: whole numbers are grouped, dates use your language's format, and yes/no values show a glyph. ### When the query changes under a display type [#when-the-query-changes-under-a-display-type] The display type is yours; the detected type belongs to the query. Rewriting the SELECT can therefore change what a column returns while its display type stays as you set it - a money column that becomes text, for instance. That never fails a run. The data is correct and only its label is stale, so refusing to show you your own data would be the wrong answer. Instead the run stays successful and says so in four places: the node's card on the canvas turns amber with a tooltip, the column shows a **Type mismatch** chip in the Output columns list, its detail pane shows an amber note, and a strip appears above the results grid. Picking a display type that fits - or changing the query back - clears all four immediately, with no re-run needed.