DataFusion SQL

Beetl runs your SQL on Apache DataFusion 54.1 with the PostgreSQL dialect. The same engine serves the SQL editor, the execute_query MCP tool and pipeline stages. Most PostgreSQL you already write will run as is. The differences show up in date formatting, a few missing functions and catalog queries, and this page covers them.

Beetl adds no functions of its own. You get DataFusion's built-in functions plus the JSON functions described in JSON extraction. For the complete list, see the DataFusion reference for scalar, aggregate and window functions.

Table names

In a query, refer to a dataset by its bare name, whatever its tier:

SELECT order_id, total FROM shop_orders LIMIT 100

The name qualified with your tenant's slug, such as acme.shop_orders, also works. Editor autocomplete offers both forms, and the MCP list_data_sets tool returns the qualified form as qualified_query_name. The two are interchangeable in queries.

A pipeline stage is different: there you use the source aliases you set in the builder and the names of earlier stages, not dataset names.

You cannot list tables from SQL. SHOW TABLES and information_schema are not available, so use the Tables button in the editor, the dataset list on Home or list_data_sets instead.

Identifiers and quoting

Unquoted identifiers are folded to lowercase, as in PostgreSQL. Dataset names are always lowercase, so they never need quotes. Columns are another matter: a CSV header such as Total or Order Date keeps its case and spaces, and you must quote it exactly.

SELECT "Order Date", "Total" FROM invoices

Writing Total without quotes looks for a column named total and fails. Text values take single quotes, and a single quote inside a value is doubled: 'it''s'.

Statements

Each run takes one statement. A trailing semicolon is fine, but 2 statements separated by a semicolon are rejected. Write queries with SELECT, WITH, UNION and the rest of the read-only syntax; EXPLAIN also works when you want to see a plan.

A query cannot change data. INSERT, UPDATE, DELETE, CREATE, DROP and COPY are refused before anything runs. To write data, build a pipeline.

CTEs and window functions

Common table expressions, subqueries, all join types and window functions work as in PostgreSQL. QUALIFY is also supported, which filters on a window function without a wrapping subquery:

SELECT order_id, status, updated_at
FROM orders
QUALIFY row_number() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1

FILTER (WHERE ...) on aggregates, string_agg, array_agg, median and approx_distinct are available too.

Types and casts

Both CAST(x AS type) and x::type work. A failed cast stops the whole query with a cast error, so when a column holds values that may not convert, use TRY_CAST, which returns NULL instead:

SELECT TRY_CAST(amount AS DECIMAL(12, 2)) AS amount,
       TRY_CAST(order_date AS DATE) AS order_date
FROM raw_orders

Integer division truncates, as in PostgreSQL: 1 / 2 is 0 and 1.0 / 2 is 0.5. To check the type of an expression, select arrow_typeof(expr). In the editor, each column header also shows its type.

Dates and timestamps

Interval arithmetic works as in PostgreSQL. ts - INTERVAL '7 days' moves a timestamp, and subtracting one date from another gives the number of days as an integer. There is no DATEDIFF, DATE_SUB, strftime or julianday; use subtraction and extract instead.

Text

The usual PostgreSQL string functions are present: upper, lower, trim, substr, split_part, replace, lpad, length, concat and the || operator. LIKE, ILIKE and the ~ regex operator work in filters.

regexp_replace(text, pattern, replacement) replaces only the first match. Add the 'g' flag to replace them all:

SELECT regexp_replace(phone, '[^0-9]', '', 'g') AS digits FROM contacts

regexp_like and regexp_match are also available. For fuzzy matching, levenshtein(a, b) is the only similarity function; there is no similarity. See Match records across systems for a worked example.

Hashing

sha224, sha256, sha384 and sha512 return binary. Wrap them in encode to get the hex string you would compare or join on:

SELECT encode(sha256(email), 'hex') AS email_hash FROM customers

digest(x, 'sha256') does the same with the algorithm as an argument and supports md5, sha224, sha384, sha512, blake2b, blake2s and blake3. md5(x) already returns hex text.

NULL ordering

NULLs sort as the largest value, as in PostgreSQL: last with ASC, first with DESC. A "top 10 by revenue" query therefore starts with the rows that have no revenue at all. Add NULLS LAST or filter them out:

SELECT customer, revenue FROM revenue_by_customer
ORDER BY revenue DESC NULLS LAST
LIMIT 10

JSON extraction

The JSON functions work on any text column that holds JSON. A webhook's payload is base64, so decode it first with CAST(decode(payload, 'base64') AS VARCHAR). JSON extraction covers paths, arrays and how to turn JSON fields into typed columns.

Pitfalls

• String functions do not accept numbers. trim(id) on an integer column fails with a signature error; write trim(CAST(id AS VARCHAR)).
• Format strings use % codes. to_char(x, 'YYYY') returns the text YYYY, and to_timestamp(x, 'DD.MM.YYYY') fails with a parse error. Write '%d.%m.%Y'.
• A function that does not exist fails with Invalid function, often with a suggestion (Did you mean 'date_bin'?). Check the DataFusion reference rather than the PostgreSQL manual.
• decode returns binary. Cast it to VARCHAR before passing it to a JSON or string function.

Rows in a result

A result in the editor or over MCP keeps the first 1,000 rows the query produces and reports the full row count. You do not need a small LIMIT just to keep the display short. The MCP execute_query tool returns up to 50 rows per page and pages the rest with get_query_result. To get more than 1,000 rows out, see Export.

Related