> Public product help. This page has no customer entries, projects, chat transcripts, API keys, or horizon contents.
> Reading that data requires `Authorization: Bearer` (a signed-in app session JWT, or a Headless access token).
> Do not call Supabase `/rest/v1` with a Headless JWT. Data reads go through `/casc/v1/headless/v1/rest`.

# SQL Transformations

A **SQL Transformation** is an ingestion source whose job is a SQL script run in a fresh,
per-run SQLite database. Each selected source group is attached as a read-only table; the script may use
multiple statements and TEMP scratch tables; the final `SELECT` result is written to this group
using the group’s **Deduplication** settings (same as every other source).

Create under **Settings → Ingestion** with source type **SQL Transformation**.
Preview in the editor; materialize with **Horizon → Data Ingestion**.
Step-based Transformations remain available — see [transform-ingestion](transform-ingestion.md).

## Source tables

- Each source needs an **alias** (SQL table name) and a **group** on the same horizon.
- Each table has columns `entry_id` and `raw` (JSON text of the entry’s raw blob).
- Read fields with SQLite JSON accessors: `raw->>'company_name'`, nested `raw->'address'->>'city'`, or `json_extract(raw, '$.address.city')`.
- Cast for arithmetic: `CAST(raw->>'hours' AS REAL)`.
- Explode arrays with `json_each(alias.raw, '$.items')`.
- Source tables are **read-only**. The destination is never written by the script — the runner inserts the final result.

## Script rules

- Dialect: **SQLite** (same engine family as SQL compute Views).
- Multiple statements allowed; `CREATE TEMP TABLE` / views and inserts into TEMP objects are fine.
- Forbidden: `ATTACH`, `DETACH`, `PRAGMA`, `VACUUM`, extensions, writes to source tables.
- The script must **end with a SELECT** (or `WITH … SELECT`). Column aliases become flat fields on each destination entry’s `raw`.
- Nothing persists between runs except the materialized destination rows.

## Simple example

```
`SELECT raw->>'name' AS name, CAST(raw->>'hours' AS REAL) AS hours FROM src`
```

## Writing results (deduplication)

The script always produces a batch of rows; the destination group’s Deduplication config decides whether
those rows are added, skipped, updated, or synced (including deleting keys absent from this run).
Configure key + mode on the group editor — not as a transform Output panel.
Prefer a natural key column in the SELECT if you use Skip, Update, or Sync.
See [api-ingestion](api-ingestion.md) for the four modes.

## Preview

Preview runs the full script in the ephemeral environment and returns rows without writing.
Save the group first. Preview uses tighter row caps than a full ingest.
If the SELECT returns more rows than the preview cap, the run still succeeds and returns a truncated sample
(with a note of the full row count) so you can validate columns and population — it does not fail with an empty error.

## When to use SQL vs step pipeline

- **SQL Transformation** — multi-source joins, windows, FILTER aggregates, CTEs, and report cards in one script.
- **Transformation (steps)** — guided explode / join / Views / aggregate / calculate without writing SQL.

Complex weekly report cards (time entries, rates, projects, holidays, overtime windows) are the target class for SQL Transformations.