---
title: SQL
description: Write the DuckDB-anchored SQL dialect Supernova accepts, including lake names, useful idioms, and precise refusals.
species: reference
---
# 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.

```sql
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:

```sql
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.

```sql
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.