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

# Transformations

Transformations combine, reshape, filter, calculate, and aggregate data from one or more ingestion groups
into a reusable destination group. Configure under **Settings → Ingestion** with source type
**Transformation**. Preview in the editor; materialize with **Horizon → Data Ingestion**
(same Ingest action as other sources). For multi-source SQL scripts in one job, see
**SQL Transformations** ([transform-sql-ingestion](transform-sql-ingestion.md)).

## Why use Transformations

- Explode nested arrays (e.g. Toggl `time_entries`) into one entry per nested item.
- Join multiple groups (time entries + projects + employees) for project reports.
- Filter and sort using **Zagger Views** (Settings → Views) — not PostgreSQL views.
- Run **SQL / compute Views** (SQLite `SELECT` on pipeline rows) for fiscal week, window totals, and overtime.
- Aggregate and calculate exact numeric fields without LLM prompt processing.

## Hybrid preview and materialize

- **Preview** — runs the pipeline and returns sample rows without writing.
- **Materialize (Ingest)** — writes pipeline rows into this group using the group’s **Deduplication** settings (Add / Skip / Update / Sync). See [api-ingestion](api-ingestion.md) (group-level dedupe) — the same contract applies to Transformations.

## Pipeline steps

| Step | Purpose |
| --- | --- |
| **Explode** | Expand a JSON array path into multiple rows; keep parent fields; optional field rename map. |
| **Join** | Inner or left join two source aliases on key fields. |
| **Filter** | Apply a Zagger View with `view_type = filter`. |
| **Sort** | Apply a Zagger View with `view_type = sort`. |
| **SQL compute** | Apply a Zagger View with `view_type = sql` (SQLite SELECT against `rows`). |
| **Aggregate** | Group by fields; sum / count / avg / min / max. |
| **Calculate** | Arithmetic expressions over numeric fields (e.g. labor cost). |

## Zagger Views in transforms

Create filter/sort/SQL Views in **Settings → Views**. Filter/sort use `raw->>'…'` fragments.
SQL compute Views use SQLite against table `rows`. Select those Views in the matching pipeline steps.
See [views](views.md).

## Fiscal week and overtime example

1. Join time entries with policy fields such as `week_start_dow` (or include them on each row).
2. Create a SQL / compute View that selects `fiscal_week`, `hours_before_entry`, `regular_hours`, and `overtime_hours` using `date()`, `strftime`, and `SUM() OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)`.
3. Add a Transformation **SQL compute** step that applies that View.
4. Optionally Filter/Sort on `fiscal_week` with additional Zagger Views.
5. Preview, then Ingest to materialize.

## Toggl explode example

1. Ingest the compact API payload into a raw group (nested `time_entries`).
2. Create a Transformation group with one source alias pointing at that raw group.
3. Add an **Explode** step: `array_path = time_entries`, field map `id → time_entry_id`.
4. Preview, then Ingest to materialize one entry per time entry (parent row is not kept).

## Project report example

1. Sources: exploded Toggl, projects, employees (aliases).
2. Join on `project_id` / `employee_id`.
3. Optional Filter View for date range.
4. Aggregate hours; Calculate labor cost, budget remaining, margin.
5. Materialize with group Deduplication set to Update or Sync and a natural unique key (e.g. project + period).

## Related

- [api-ingestion](api-ingestion.md) — ingest and deduplicate API feeds before transforming.
- [views](views.md) — create Zagger Views used by Filter/Sort steps.
- [ingestion-sources](ingestion-sources.md) — all source types including Transformation.