> ## Documentation Index
> Fetch the complete documentation index at: https://goldrush.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Transforms

> Apply optional per-table row-level filtering, projection, and computed columns using SQL after normalization or ABI decoding and before the destination.

## Overview

SQL transforms let you reshape, filter, and enrich row streams between the normalization/ABI decoding stage and the destination. Each transform is a standard SQL `SELECT` statement scoped to a single output table.

Transforms are optional. When no transform is configured for a table, the full row stream passes through to the destination unchanged.

## How It Works

<Steps>
  <Step title="Typed row streams are produced">
    The normalizer or ABI decoder emits typed row streams for each output table.
  </Step>

  <Step title="SQL transform executes">
    If a SQL transform is configured for a given table, the query runs against that table's row stream.
  </Step>

  <Step title="Result replaces the original stream">
    The query result becomes the new row stream for that table, replacing the original.
  </Step>

  <Step title="Stream flows to the destination">
    The final (possibly transformed) stream is delivered to the configured destination.
  </Step>
</Steps>

## Configuration

Define transforms in your pipeline configuration under the `transforms` key. Each key must match a normalizer or ABI decoder output table name, and the `FROM` clause in the query must reference the same table name.

```yaml theme={null}
transforms:
  table_name: >
    SELECT ... FROM table_name WHERE ...
```

## Supported Operations

The following SQL operations are available in transforms:

| Operation         | Example                                        |
| ----------------- | ---------------------------------------------- |
| Column projection | `SELECT col1, col2 FROM t`                     |
| Column aliases    | `SELECT user AS depositor FROM t`              |
| Filtering         | `WHERE amount != '0'`                          |
| Logical operators | `WHERE a > 0 AND b < 100`                      |
| IN                | `WHERE coin IN ('ETH', 'BTC')`                 |
| LIKE              | `WHERE user LIKE '0xabc%'`                     |
| BETWEEN           | `WHERE height BETWEEN 100 AND 200`             |
| NULL checks       | `WHERE value IS NOT NULL`                      |
| Type casting      | `CAST(amount AS DOUBLE)`                       |
| Arithmetic        | `SELECT gas_used * gas_price AS cost`          |
| String functions  | `LOWER()`, `SUBSTRING()`, `CONCAT()`, `TRIM()` |
| Conditional       | `CASE WHEN side = 'buy' THEN 1 ELSE -1 END`    |
| Null handling     | `COALESCE(value, '0')`, `NULLIF(value, '')`    |

## Unsupported Operations

The following operations are rejected at pipeline startup with a validation error:

| Operation                | Reason                      |
| ------------------------ | --------------------------- |
| JOINs                    | Requires multiple sources   |
| GROUP BY, aggregations   | Requires stateful windowing |
| ORDER BY                 | Unbounded sorting           |
| UNION, INTERSECT, EXCEPT | Multi-source set operations |
| Subqueries               | Complexity                  |
| INSERT, UPDATE, DELETE   | DML not applicable          |
| CREATE, ALTER, DROP      | DDL not applicable          |
| HAVING, WINDOW, OVER     | Windowed aggregations       |
| LIMIT, OFFSET            | Bounded-only operations     |

## Validation Rules

Every transform query is validated before the pipeline starts. A query must satisfy all of the following rules:

1. The query must start with `SELECT`.
2. The query must not contain any unsupported operations.
3. The `FROM` table must exist as a known output table from the normalizer or ABI decoder.
4. The `FROM` table must match the transform key (e.g., a transform keyed as `hl_deposits` must select `FROM hl_deposits`).
5. All columns referenced in the `WHERE` clause must exist in the table schema.

<Warning>
  If any validation rule fails, the pipeline will not start. Check the error message for details on which rule was violated and which table or column is affected.
</Warning>

## Examples

### Filter zero-value deposits

Remove deposit events where the amount is zero, and project only the columns you need.

```yaml theme={null}
transforms:
  hl_deposits: >
    SELECT block_number, block_time, user, amount
    FROM hl_deposits
    WHERE amount != '0'
```

### Rename and project columns

Rename `user` to `depositor` and cast `amount` from a string to a double-precision number.

```yaml theme={null}
transforms:
  hl_deposits: >
    SELECT block_number, block_time, user AS depositor,
           CAST(amount AS DOUBLE) AS amount_numeric
    FROM hl_deposits
```

### Filter by contract address

Keep only swap events emitted by a specific Uniswap V3 router contract.

```yaml theme={null}
transforms:
  evt_swap: >
    SELECT block_height, tx_hash, contract_address, sender,
           recipient, amount0, amount1
    FROM evt_swap
    WHERE contract_address = '0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45'
```

### Computed columns with arithmetic

Add a computed `txn_fee_sol` column that converts lamports to SOL, and filter out low-volume trades.

```yaml theme={null}
transforms:
  sol_dex_trades: >
    SELECT block_slot, tx_id, pool_address, base_mint, quote_mint,
           base_amount, quote_amount, price_usd, volume_usd,
           txn_fee_lamports * 0.000000001 AS txn_fee_sol
    FROM sol_dex_trades
    WHERE volume_usd > 100
```

### Conditional logic for trade direction

Map side codes to human-readable labels, provide a default for null `builder_fee`, and filter to specific coins.

```yaml theme={null}
transforms:
  hl_fills: >
    SELECT block_number, block_time, user_address, coin, px, sz, fee,
           CASE WHEN side = 'B' THEN 'buy'
                WHEN side = 'S' THEN 'sell'
                ELSE side END AS trade_side,
           COALESCE(builder_fee, '0') AS builder_fee
    FROM hl_fills
    WHERE coin IN ('ETH', 'BTC', 'SOL')
```