SQL#

Supernova accepts a read-only SQL dialect anchored on DuckDB 1.4-LTS. Reference lake tables as titan.<schema>.<table> or <schema>.<table>; both forms resolve inside the organization lake.

with ranked as (
  select
    customer,
    amount,
    row_number() over (partition by customer order by created_at desc) as recency
  from titan.stripe.charges
  where status = 'succeeded' and not _deleted
)
select * exclude (recency)
from ranked
qualify recency = 1

Names and scope#

titan is a catalog alias, not a physical lake id. Physical ids cannot be written in query text. One-part relation names resolve only to CTEs and aliases in scope.

Use double quotes for identifiers that contain spaces or collide with keywords. String literals use single quotes.

Supported query shape#

The core includes CTEs, inner and outer joins, using, window functions, qualify, union all, union all by name, distinct, order by, limit, and offset. Star modifiers make wide source tables manageable:

select * exclude (metadata, _synced_at)
replace (lower(email) as email)
from titan.hubspot.contacts
where not _deleted

Lists use 1-based indexes. JSON supports -> and ->> with keys, indexes, and JSON paths. Higher-order list functions accept x -> expression and lambda x: expression forms.

Useful built-ins include date_trunc, strftime, strptime, date_diff, epoch_ms, split_part, regexp_extract, regexp_replace, string_agg, json_extract, unnest, generate_series, and list_aggregate.

Casts and nulls#

cast(value as type) fails when conversion fails. try_cast(value as type) returns null for that row. is not distinct from provides null-safe equality.

select try_cast(property_annual_revenue as decimal(18, 2)) as annual_revenue
from titan.hubspot.companies

Refused statements#

Queries cannot mutate lake state. insert, update, delete, merge, drop, alter, truncate, attach, copy, install, load, transaction statements, and non-macro create statements return a capability error naming the refused construct.

The dialect also refuses stored procedures, spatial types, external catalogs, and file auto-discovery. Use a source for ingestion, a model for a durable derived table, and a send for destination writes.