DataFusion SQL

Beetl's query engine is Apache DataFusion. Its SQL dialect is close enough to PostgreSQL that most queries you already know will run, and different enough that a handful will fail in ways that look like your query is wrong when it is the dialect. The failures cluster around date functions and catalog introspection. This page lists what is available, and strikes through what is not, so you can check before you debug.

Table format guide

Datasets are listed with their format (Delta, Csv, Parquet, Json). Most datasets have structured columns you can query directly.

Base64-encoded JSON payloads are ONLY present in datasets that have a payload column. Do not assume payload exists. When in doubt, discover the columns first:

SELECT * FROM schema.table LIMIT 1

Date and time functions

Available:

Function	What it does
`date_trunc('week', timestamp_col)`	Truncate to unit (year, month, week, day, hour, and so on).
`date_part('month', timestamp_col)`	Extract part as float.
`extract(MONTH FROM timestamp_col)`	Extract part as integer.
`CAST(col AS DATE)`, `CAST(col AS TIMESTAMP)`	Type conversion.
`now()`	Current timestamp.
`to_timestamp('2026-03-01', '%Y-%m-%d')`	Parse string to timestamp.
`col - INTERVAL '7 days'`	Interval arithmetic for date differences.
`CAST(date_col2 AS DATE) - CAST(date_col1 AS DATE)`	Date difference, returns an interval.

NOT available (will cause errors):

~~julianday()~~ SQLite only
~~strftime()~~ SQLite only
~~DATE_SUB()~~ MySQL only
~~DATEDIFF()~~ use interval subtraction instead
~~information_schema.tables~~ not enabled
~~SHOW TABLES~~ not supported with LIMIT
~~sqlite_master~~ SQLite only

The last three matter more than they look. There is no catalog introspection from SQL, so you cannot discover tables by querying the engine. Use the dataset list in the UI or over MCP instead.

JSON extraction

Only use these functions for tables with a payload column. Use the typed JSON getter functions from datafusion-functions-json:

Function	Returns
`json_get_str(json, 'key')`	String value.
`json_get_int(json, 'key')`	Integer value.
`json_get_float(json, 'key')`	Float value.
`json_get_bool(json, 'key')`	Boolean value.

For nested values, pass each path segment as a separate argument:

json_get_str(json_str, 'outer', 'inner')

Do not use JSONPath strings like '$.outer.inner'. They are not parsed as paths.

Do NOT use json_get() directly

json_get() returns a Union type that cannot be serialized. Always use the typed getters above.

Worked example

-- Only use this pattern when the table has a `payload` column
WITH decoded AS (
  SELECT CAST(decode(payload, 'base64') AS VARCHAR) as json_str
  FROM my_table
)
SELECT
  json_get_str(json_str, 'tag') as tag,
  json_get_str(json_str, 'metadata', 'source') as source,
  json_get_float(json_str, 'value') as value
FROM decoded

SQL syntax rules

Always use semicolon-free SQL. No trailing ;.
Use schema.table qualified names exactly as returned in qualified_query_name.
Default to LIMIT 100 unless specified otherwise.
Do not add LIMIT 5 or LIMIT 10 just because execute_query only previews 10 rows. Add a small LIMIT only when intentionally sampling, or when the user asks for limited output.

Table format guide

Date and time functions

JSON extraction

Worked example

SQL syntax rules

On this page