Skip to main content

​
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

1

Typed row streams are produced

The normalizer or ABI decoder emits typed row streams for each output table.
2

SQL transform executes

If a SQL transform is configured for a given table, the query runs against that table’s row stream.
3

Result replaces the original stream

The query result becomes the new row stream for that table, replacing the original.
4

Stream flows to the destination

The final (possibly transformed) stream is delivered to the configured destination.

​
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. 
transforms:
  table_name: >
    SELECT ... FROM table_name WHERE ...

​
Supported Operations

The following SQL operations are available in transforms:
OperationExample
Column projectionSELECT col1, col2 FROM t
Column aliasesSELECT user AS depositor FROM t
FilteringWHERE amount != '0'
Logical operatorsWHERE a > 0 AND b < 100
INWHERE coin IN ('ETH', 'BTC')
LIKEWHERE user LIKE '0xabc%'
BETWEENWHERE height BETWEEN 100 AND 200
NULL checksWHERE value IS NOT NULL
Type castingCAST(amount AS DOUBLE)
ArithmeticSELECT gas_used * gas_price AS cost
String functionsLOWER(), SUBSTRING(), CONCAT(), TRIM()
ConditionalCASE WHEN side = 'buy' THEN 1 ELSE -1 END
Null handlingCOALESCE(value, '0'), NULLIF(value, '')

​
Unsupported Operations

The following operations are rejected at pipeline startup with a validation error:
OperationReason
JOINsRequires multiple sources
GROUP BY, aggregationsRequires stateful windowing
ORDER BYUnbounded sorting
UNION, INTERSECT, EXCEPTMulti-source set operations
SubqueriesComplexity
INSERT, UPDATE, DELETEDML not applicable
CREATE, ALTER, DROPDDL not applicable
HAVING, WINDOW, OVERWindowed aggregations
LIMIT, OFFSETBounded-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.
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.

​
Examples

​
Filter zero-value deposits

Remove deposit events where the amount is zero, and project only the columns you need. 
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. 
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. 
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. 
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. 
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')