Turbo SQL Functions Reference

Overview

Turbo pipelines SQL transforms are powered by Apache DataFusion and include custom functions designed specifically for blockchain and Web3 data processing. These functions extend standard SQL with capabilities for:

Decoding EVM logs and Solana instructions
Working with 256-bit integers common in smart contracts
Processing blockchain-specific data formats
Advanced array and JSON manipulation
Cryptographic operations

All standard DataFusion SQL functions are also available. This reference focuses on Turbo-specific custom functions.

EVM & Ethereum Functions

evm_log_decode

Decodes an EVM event log using an ABI definition. Aliases: evm_decode_log, _gs_log_decode Signature:

evm_log_decode(abi_json, topics, data) → struct(event_signature, event_params)

Parameters:

Parameter	Type	Description
`abi_json`	string	The event ABI as a JSON array (not a Solidity event signature). Use `_gs_fetch_abi()` to fetch this dynamically.
`topics`	`LIST<VARCHAR>` or string	The log topics. Prefer a list of hex topics (the shape of the `topics` column in `edge.evm.logs`). A comma-separated string (e.g. `"0xddf252ad...,0x00000000...from,0x00000000...to"`) is still accepted as a legacy form.
`data`	string	Hex-encoded non-indexed event data

For curated EVM logs / raw_logs datasets, topics is a comma-separated string: topics LIKE '0xSIGNATURE%' matches the first topic, and SPLIT_INDEX(topics, ',', n) reads topic n (zero-based). For edge.evm.logs, topics is a list: use topics[1] for the event signature and topics[n] for other positions (one-based). Do not apply LIKE or SPLIT_INDEX directly to a list.

Returns: A struct with two fields:

event_signature (string): the event name (e.g. "Transfer")
event_params (array of strings): decoded parameter values in positional order

Parameters are returned by position, not by name. To access the third parameter, use decoded.event_params[3], not decoded['paramName'].

Example: Decode an ERC-20 Transfer event

SELECT
  evm_decode_log(
    '[{"anonymous":false,"inputs":[{"indexed":true,"name":"from","type":"address"},{"indexed":true,"name":"to","type":"address"},{"indexed":false,"name":"value","type":"uint256"}],"name":"Transfer","type":"event"}]',
    topics,
    data
  ) as decoded,
  decoded.event_signature as event_name,
  decoded.event_params[1] as from_address,
  decoded.event_params[2] as to_address,
  decoded.event_params[3] as value
FROM logs

Example: Using _gs_fetch_abi for dynamic ABI lookup Instead of hardcoding the ABI JSON, fetch it at runtime from Etherscan:

SELECT
  evm_decode_log(
    _gs_fetch_abi(
      'https://api.etherscan.io/api?module=contract&action=getabi&address=0x1F98431c8aD98523631AE4a59f267346ea31F984&apikey=YOUR_KEY',
      'etherscan'
    ),
    topics,
    data
  ) as decoded
FROM logs
WHERE topics LIKE '0x783cca1c%'  -- Curated logs: PoolCreated event signature

The decoder uses caching internally for performance. The same ABI can be reused across millions of records efficiently.

evm_trace_decode

Decodes EVM trace (call) data using an ABI definition, the trace counterpart of evm_log_decode. Aliases: evm_decode_trace, _gs_trace_decode Signature:

evm_trace_decode(abi_json, input, output) → struct(function_signature, inputs, outputs)

Parameters:

Parameter	Type	Description
`abi_json`	string	The function ABI as a JSON array. Use `_gs_fetch_abi()` to fetch this dynamically. Must not be `NULL`.
`input`	string	Hex-encoded calldata sent with the call (the `input` column on traces). A `NULL` input produces a `NULL` result.
`output`	string	Hex-encoded data returned by the call (the `output` column on traces). `NULL` is treated as empty.

Returns: A struct with three fields:

function_signature (string): the matched function name
inputs (array of strings): decoded input values in positional order
outputs (array of strings): decoded output values in positional order

The function is matched by its selector; partial ABIs that contain only the target function also work. If no function matches, or the ABI or data cannot be decoded, the result is NULL. Example: Decode traces

SELECT
  decoded.function_signature as function_name,
  decoded.inputs[1] as to_address,
  decoded.inputs[2] as value
FROM (
  SELECT
    evm_trace_decode(
      '[{"inputs":[{"name":"to","type":"address"},{"name":"value","type":"uint256"}],"name":"transfer","outputs":[{"name":"","type":"bool"}],"stateMutability":"nonpayable","type":"function"}]',
      input,
      output
    ) as decoded
  FROM traces
  WHERE input LIKE '0xa9059cbb%'  -- transfer(address,uint256) selector
) t

_gs_fetch_abi

Fetches an ABI JSON from a URL. Results are cached for the lifetime of the pipeline. Signature:

_gs_fetch_abi(url, source_type) → string

Parameters:

Parameter	Type	Values
`url`	string	Full URL to fetch ABI from
`source_type`	string	`'etherscan'` (extracts `result` field from Etherscan API response) or `'raw'` (uses response body directly as ABI JSON)

Returns: ABI JSON string that can be passed to evm_decode_log or _gs_decode_instruction_data Example: Fetch ABI from Etherscan

SELECT
  evm_decode_log(
    _gs_fetch_abi(
      'https://api.etherscan.io/api?module=contract&action=getabi&address=0x...&apikey=YOUR_KEY',
      'etherscan'
    ),
    topics,
    data
  ) as decoded
FROM logs

Example: Fetch raw ABI from URL

SELECT
  evm_decode_log(
    _gs_fetch_abi('https://example.com/abi.json', 'raw'),
    topics,
    data
  ) as decoded
FROM logs

The fetched ABI is cached internally for performance. The same URL will only be fetched once, even when processing millions of records.

_gs_eth_call

Performs an eth_call JSON-RPC request against an EVM node, ABI-encoding the inputs and ABI-decoding the outputs. Use this to enrich streaming rows with on-chain state at a specific block (token balances, totalSupply, oracle prices, contract metadata) without standing up a separate fetcher service. Signature:

_gs_eth_call(
  rpc_url,
  function_sig,
  address,
  args,
  block_number
  [, use_multicall]
  [, max_calls_per_multicall]
) -> ARRAY<VARCHAR>

Parameters:

Parameter	Type	Description
`rpc_url`	VARCHAR	JSON-RPC endpoint. Must be `http(s)://`. Private/loopback addresses are blocked by default.
`function_sig`	VARCHAR	Solidity human-readable signature with explicit return types: `'name(input_types)(output_types)'`, e.g. `'balanceOf(address)(uint256)'`, `'totalSupply()(uint256)'`.
`address`	VARCHAR	Target contract, `0x`-prefixed.
`args`	ARRAY<VARCHAR>	Call arguments as strings, in declared order. Use `NULL` for zero-arg functions.
`block_number`	BIGINT	Block tag to call against. `NULL` means `latest`.
`use_multicall`	BOOLEAN (optional)	Batch eligible rows in a single Multicall3 dispatch. Default: `false`.
`max_calls_per_multicall`	BIGINT (optional)	Chunk size for Multicall3 batching. Default: `50`.

Returns: An ARRAY<VARCHAR> of decoded output values, one element per declared output type. Index with [1] for the first return value. Returns NULL for any row whose call fails (bad signature, revert, RPC error, decode error); failures never tear down the batch.

The Multicall3 defaults (use_multicall = false, chunk size 50) are tuned for typical workloads. Only raise the chunk size if you’ve measured RPC throughput as your bottleneck; very large batches can hit per-request gas limits on the RPC node and start returning errors (which surface as NULL rows). When in doubt, leave the optional args off.

Example: Enrich ERC-20 transfers with totalSupply at the transfer’s block

transforms:
  transfers_with_supply:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        from_address,
        to_address,
        value,
        block_number,
        _gs_eth_call(
          'https://your-rpc-endpoint.example.com',
          'totalSupply()(uint256)',
          '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48',  -- USDC
          NULL,                                           -- no args
          CAST(block_number AS BIGINT)
        )[1] AS total_supply_at_block
      FROM erc20_transfers

Example: Look up an account balance with an argument

sql: |
  SELECT
    holder,
    _gs_eth_call(
      'https://your-rpc-endpoint.example.com',
      'balanceOf(address)(uint256)',
      '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48',
      ARRAY[holder],
      NULL                                                -- latest block
    )[1] AS usdc_balance
  FROM tracked_wallets

We recommend using a Goldsky Edge RPC endpoint as rpc_url for the best performance and reliability.

Output values are stringified element-by-element. Scalar return types (uint*, address, string, bytes, …) round-trip losslessly. For nested return types (string[], tuples), commas and brackets inside elements aren’t escaped; prefer one call per scalar field, or post-process the raw bytes, when you need strict re-parsing.

_gs_evm_get_balances

Reads native token balances (ETH on Ethereum, the native asset on any EVM chain) for a list of addresses at a given block, using eth_getBalance against an RPC endpoint you provide. Use it to fetch exact balances for addresses that just moved value, instead of polling every address every block. See the native balances guide for a complete pipeline. Signature:

_gs_evm_get_balances(rpc_url, addresses, block_number)
  -> ARRAY<STRUCT<address VARCHAR, balance DECIMAL(78, 0)>>

Parameters:

Parameter	Type	Description
`rpc_url`	VARCHAR	JSON-RPC endpoint. Must be `http(s)://`. Private and loopback addresses are blocked. We recommend a Goldsky Edge endpoint.
`addresses`	ARRAY<VARCHAR>	`0x`-prefixed addresses, any casing, for example `make_array(sender, recipient)`. Repeated or case-variant addresses within one call are looked up once.
`block_number`	BIGINT	Block to read the balance at. `NULL` means `latest`. Negative values are an error.

Returns: One element per input address, in input order. address is the address as you passed it; balance is the balance in wei as a 256-bit integer, the same type as the amount column of the native transfers dataset. Use CAST(balance AS TEXT) to render it and standard operators for arithmetic. A failed lookup (RPC error, timeout, malformed response) leaves that element’s balance as NULL and logs a warning; it never fails the row or the batch. An empty or NULL address list returns an empty list. Example: balances of both parties after each transfer Element access such as [1].balance is not supported on this list; use UNNEST in a subquery and read the struct fields from the outer query.

transforms:
  balances_after_transfer:
    type: sql
    primary_key: id
    sql: |
      SELECT
        sub.id,
        sub.block_number,
        sub.b.address                 AS address,
        CAST(sub.b.balance AS TEXT)   AS balance
      FROM (
        SELECT
          t.id,
          t.block_number,
          UNNEST(_gs_evm_get_balances(
            'https://edge.goldsky.com/standard/evm/1?key=YOUR_EDGE_KEY',
            make_array(t.sender, t.recipient),
            CAST(t.block_number AS BIGINT)
          )) AS b
        FROM native_transfers t
      ) sub

block_number gives the balance at the end of that block. Pass block_number - 1 for the balance before the transfer, or NULL for the current balance at the chain head.

Every row that reaches this function costs one RPC request per distinct address in its list. Filter rows down to the addresses you care about before calling it, for example with dynamic_table_check.

_gs_keccak256

Compute Keccak256 hash (same as Solidity’s keccak256). Signature:

_gs_keccak256(input) -> VARCHAR

Parameters:

input - String to hash. A 0x-prefixed input is hex-decoded first and the resulting bytes are hashed; any other string is hashed as UTF-8 text.

Returns: Hex-encoded hash with “0x” prefix, or NULL if a 0x-prefixed input is not valid hex. Example: Compute Event Signatures

sql: |
  SELECT
    _gs_keccak256('Transfer(address,address,uint256)') as transfer_signature,
    _gs_keccak256('Approval(address,address,uint256)') as approval_signature
  -- Results:
  -- 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef
  -- 0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925

Example: Verify Topic Matches

sql: |
  SELECT *
  FROM ethereum_logs
  WHERE SPLIT_INDEX(topics, ',', 0) = _gs_keccak256('Transfer(address,address,uint256)')

_gs_hex_to_byte / _gs_byte_to_hex

Convert between hex strings and bytes. Signatures:

_gs_hex_to_byte(hex_string) -> BINARY
_gs_byte_to_hex(bytes) -> VARCHAR

_gs_hex_to_byte accepts input with or without a 0x prefix; invalid or NULL input returns an empty byte array, not NULL. _gs_byte_to_hex returns a bare hex string (no 0x prefix).

Example:

sql: |
  SELECT
    _gs_hex_to_byte('0x1234') as bytes,
    _gs_byte_to_hex(data) as hex_string
  FROM ethereum_transactions

to_checksum_address

Converts a hex address to its EIP-55 checksummed form (mixed-case encoding). Optionally accepts a chain ID for EIP-1191 chain-specific checksums. Signature:

to_checksum_address(address) -> VARCHAR
to_checksum_address(address, chain_id) -> VARCHAR

Parameters:

Parameter	Type	Description
`address`	string	Hex address with or without `0x` prefix. Must be 40 hex characters.
`chain_id`	`UINT64` (optional)	Chain ID for EIP-1191 chain-specific checksumming, as an unsigned 64-bit integer. Omit for plain EIP-55.

Returns: A 0x-prefixed checksummed address, or NULL for invalid input. Example:

sql: |
  SELECT
    to_checksum_address(from_address) as from_checksummed,
    to_checksum_address(to_address) as to_checksummed
  FROM erc20_transfers

reverse_bytes32

Reverses the byte order of a 32-byte value. Useful for endianness conversion of blockchain hashes and 256-bit integers. Signature:

reverse_bytes32(value) -> BINARY(32)

Parameters:

Parameter	Type	Description
`value`	`FixedSizeBinary(32)`	A 32-byte value.

Returns: The same 32 bytes in reversed order. NULL is passed through. Example:

sql: |
  SELECT
    transaction_hash,
    reverse_bytes32(transaction_hash) as le_hash
  FROM ethereum_transactions

Solana Functions

Turbo pipelines include specialized functions for decoding Solana program instructions and analyzing transaction data. Many of the below are already used in our default solana.instructions dataset and other datasets.

_gs_decode_instruction_data (IDL Decode)

Decode Solana program instruction or event data using an IDL (Interface Definition Language) specification. This is the most flexible Solana decoding function - it works with any custom Solana program that has an IDL. Signature:

_gs_decode_instruction_data(idl_json, data) -> STRUCT<name: VARCHAR, value: VARCHAR>

Parameters:

idl_json - IDL specification as JSON string (can be fetched with _gs_fetch_abi())
data - Base58-encoded instruction data

Returns: Struct containing:

name - Instruction or event name (e.g., “initialize”, “swap”, “transfer”)
value - JSON string with decoded parameter values

How it works:

Parses the IDL JSON specification
Attempts to decode as an instruction first
Falls back to event decoding if instruction decoding fails
Returns both the name and decoded values

Example: Access Decoded Fields after decoding

sql: |
  WITH decoded AS (
    SELECT
      id,
      _gs_decode_instruction_data(
        _gs_fetch_abi('https://api.example.com/jupiter-v6.json', 'raw'),
        data
      ) as decoded_ix,
      accounts
    FROM solana_instructions
    WHERE program_id = 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4'
  )
  SELECT
    id,
    decoded_ix.name as instruction_name,
    -- Parse the JSON value to extract specific fields
    json_value(decoded_ix.value, '$.amountIn') as amount_in,
    json_value(decoded_ix.value, '$.minimumAmountOut') as min_amount_out,
    accounts[1] as user_account
  FROM decoded
  WHERE decoded_ix.name = 'sharedAccountsRoute'

Example: Decode with Inline IDL For simpler programs, you can provide the IDL directly:

sql: |
  SELECT
    id,
    _gs_decode_instruction_data(
      '{"instructions":[{"name":"initialize","accounts":[{"name":"authority","isMut":false,"isSigner":true}],"args":[{"name":"bump","type":"u8"}]}]}',
      data
    ) as decoded
  FROM solana_instructions

Performance: IDL decoders are cached internally. Reusing the same IDL JSON string (via _gs_fetch_abi()) across multiple rows is very efficient - the IDL is only parsed once.

When to use:

Custom Solana programs with available IDL specifications
Decoding instructions from DEXes, DeFi protocols, NFT marketplaces
Any program where you need flexible, schema-driven decoding

Use program-specific decoders instead for:

Solana system programs (Token, System, Stake, Vote, etc.) - use _gs_solana_decode_* functions below
Better performance for known programs with hardcoded decoders

_gs_solana_decode_token_program_instruction

Decode Solana Token Program (SPL Token) instructions. Signature:

_gs_solana_decode_token_program_instruction(data, accounts) -> STRUCT

data: Base58-encoded instruction data
accounts: List of account public keys for the instruction

Example: Decode Token Instructions

transforms:
  decoded_token_ops:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        signature,
        _gs_solana_decode_token_program_instruction(data, accounts) as decoded
      FROM solana_ix
      WHERE program_id = 'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'

_gs_solana_decode_system_program_instruction

Decode Solana System Program instructions (transfers, account creation, etc.). No IDL required. Signature:

_gs_solana_decode_system_program_instruction(data, accounts) -> STRUCT

data: Base58-encoded instruction data
accounts: List of account public keys for the instruction

Example: Decode System Program Instructions

transforms:
  system_decoded:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        block_slot,
        signature,
        _gs_solana_decode_system_program_instruction(data, accounts) as decoded
      FROM solana_ix
      WHERE program_id = '11111111111111111111111111111111'

_gs_solana_get_accounts

Build a unified list of accounts for a Solana transaction, annotated with source, writable, and signer flags. This is the same logic used to produce the accounts column on solana.instructions. Signature:

_gs_solana_get_accounts(
  account_keys,
  loaded_readonly,
  loaded_writable,
  num_required_signatures,
  num_readonly_signed_accounts,
  num_readonly_unsigned_accounts,
  source
) -> LIST<STRUCT<pubkey: VARCHAR, source: VARCHAR, writable: BOOLEAN, signer: BOOLEAN>>

Parameters:

account_keys - LIST<VARCHAR> of account pubkeys from the message header
loaded_readonly - LIST<VARCHAR> of readonly pubkeys loaded via address lookup tables
loaded_writable - LIST<VARCHAR> of writable pubkeys loaded via address lookup tables
num_required_signatures - UINT8 header field
num_readonly_signed_accounts - UINT8 header field
num_readonly_unsigned_accounts - UINT8 header field
source - VARCHAR tag stored on each resulting account record

_gs_solana_get_balance_changes

Pair a transaction’s unified account list with its preBalances / postBalances arrays to produce per-account SOL balance changes. Signature:

_gs_solana_get_balance_changes(accounts, pre_balances, post_balances)
  -> LIST<STRUCT<account: VARCHAR, before: UINT64, after: UINT64>>

Parameters:

accounts - LIST<STRUCT<pubkey, source, writable, signer>> (typically the output of _gs_solana_get_accounts)
pre_balances - LIST<UINT64> of lamport balances before the transaction
post_balances - LIST<UINT64> of lamport balances after the transaction

Example: Monitor Large Balance Changes

sql: |
  WITH balance_changes AS (
    SELECT
      signature,
      _gs_solana_get_balance_changes(
        _gs_solana_get_accounts(
          accountKeys,
          loadedReadonly,
          loadedWritable,
          numRequiredSignatures,
          numReadonlySignedAccounts,
          numReadonlyUnsignedAccounts,
          'transaction'
        ),
        preBalances,
        postBalances
      ) as changes
    FROM solana_transactions
  )
  SELECT
    signature,
    change.account,
    change.after - change.before as net_change
  FROM balance_changes
  CROSS JOIN UNNEST(changes) AS change
  WHERE ABS(CAST(change.after AS BIGINT) - CAST(change.before AS BIGINT)) > 1000000000  -- > 1 SOL

Other Solana Decoders

All program-specific decoders take (data, accounts) and follow the same pattern:

_gs_solana_decode_associated_token_program_instruction(data, accounts) - Decode ATA program
_gs_solana_decode_stake_program_instruction(data, accounts) - Decode staking operations
_gs_solana_decode_vote_program_instruction(data, accounts) - Decode vote program
_gs_solana_decode_bpf_loader_instruction(data, accounts) - Decode BPF loader
_gs_solana_decode_bpf_upgradeable_loader_instruction(data, accounts) - Decode upgradeable programs
_gs_solana_decode_address_lookup_table_instruction(data, accounts) - Decode address lookup tables

_gs_from_base58

Decode Base58 strings (common in Solana for addresses and signatures). Signature:

_gs_from_base58(base58_string) -> BINARY

Returns: The decoded bytes. Invalid or NULL input returns an empty byte array, not NULL. Example:

sql: |
  SELECT
    _gs_from_base58(account_address) as decoded_address
  FROM solana_accounts

_gs_solana_base58

Base58-encode raw Solana bytes. Strings that are already base58 are passed through unchanged, so the same expression works for raw and pinned schemas. Signature:

_gs_solana_base58(value) -> VARCHAR

Accepts: VARCHAR or BINARY. Example:

sql: |
  SELECT
    _gs_solana_base58(blockhash) as blockhash
  FROM raw_solana_blocks

_gs_solana_pin_token_balances

Normalize a preTokenBalances / postTokenBalances array from the raw block schema into the pinned schema, base58-encoding raw mint, owner and programId bytes. Already-pinned values pass through. Signature:

_gs_solana_pin_token_balances(balances) -> LIST<STRUCT<accountIndex, mint, owner, programId, uiTokenAmount>>

The input must be entirely the raw variant or entirely the pinned variant, not a mix of field types. Example:

sql: |
  SELECT
    _gs_solana_pin_token_balances(meta.preTokenBalances) as pre_token_balances
  FROM solana_transactions

_gs_flatten_balances

Flatten a pinned token-balance array by lifting uiTokenAmount.amount and uiTokenAmount.decimals onto each entry. Signature:

_gs_flatten_balances(token_balances) -> LIST<STRUCT<accountIndex, mint, owner, programId, amount, decimals>>

Returns: amount is a VARCHAR and decimals a UINT8. A NULL input list produces an empty list. Example:

sql: |
  SELECT
    _gs_flatten_balances(meta.preTokenBalances) as pre_balances
  FROM solana_transactions

_gs_decode_log_message

Decode Solana program log messages (e.g. Anchor events) using an IDL. Signature:

_gs_decode_log_message(idl_json, log_messages) -> STRUCT<name LIST<VARCHAR>, value LIST<VARCHAR>, program LIST<VARCHAR>>

Parameters:

Parameter	Type	Description
`idl_json`	string	The program IDL as JSON.
`log_messages`	`LIST<VARCHAR>`	The transaction’s log messages (an array column).

Returns: Parallel lists of decoded event names, values (JSON text) and emitting program IDs. Example:

sql: |
  SELECT
    _gs_decode_log_message(idl_json, meta.logMessages) as events
  FROM solana_transactions

_gs_solana_get_leader_info

Extract the block leader from a block’s rewards: the pubkey and lamports of the first Fee reward. Signature:

_gs_solana_get_leader_info(rewards) -> STRUCT<leader VARCHAR, leader_reward BIGINT>

Returns: Both fields are NULL when the block has no Fee reward. rewards must be an array column. Example:

sql: |
  SELECT
    slot,
    _gs_solana_get_leader_info(rewards).leader as leader
  FROM solana_blocks

Large Numbers (256-bit integers and wide decimals)

Smart contracts use 256-bit integers for token amounts and balances. Turbo stores these values, and any DECIMAL(p, s) with a precision above 76, as an arbitrary-precision decimal type, decimal_arb. Wide-integer dataset columns such as amount in erc20_transfers and native_transfers, and the balance returned by _gs_evm_get_balances, already have this type. You work with them using standard SQL operators and CAST; no conversion functions are needed.

The to_u256, to_i256, u256_*, i256_* and to_int64 functions have been removed, with no aliases. A transform that still calls one fails at planning with Invalid function. Rewrite it with the replacements below.

Removed	Replacement
`to_u256(x)`, `to_i256(x)`	`CAST(x AS DECIMAL(78, 0))`
`u256_to_string(x)`, `i256_to_string(x)`	`CAST(x AS TEXT)`
`u256_add` / `_sub` / `_mul` / `_div` / `_mod` and their `i256_*` twins	`a + b`, `a - b`, `a * b`, `a / b`, `a % b`
`i256_neg(x)`	`-x`
`i256_abs(x)`	`ABS(x)`
`to_int64(x)`	`CAST(CAST(x AS TEXT) AS BIGINT)`

Working with 256-bit values

Convert in. CAST(x AS DECIMAL(78, 0)) turns a decimal string, an integer, or an existing 256-bit column into a 256-bit integer. Signed values are fine. A string that isn’t a number fails the query; TRY_CAST(x AS DECIMAL(78, 0)) returns NULL instead.
Calculate. +, -, *, /, %, unary -, comparisons, ABS, COALESCE and GREATEST/LEAST all work directly on 256-bit values. A numeric literal next to one is exact however many digits it has, so amount / 1000000000000000000 and amount > 1000000000000000000000000 need no quoting.
Convert out. CAST(x AS TEXT) renders the value as a decimal string. To get a native number, go through text, for example CAST(CAST(x AS TEXT) AS BIGINT), or use decimal_arb_to_decimal128 for a DECIMAL(38, s). A direct CAST(x AS BIGINT), CAST(x AS DOUBLE) or CAST(x AS DECIMAL(38, s)) fails with Unsupported CAST from LargeBinary.
NULLs. A NULL input or operand gives NULL. It does not raise an error.

Division does not truncate. a / b keeps 18 fractional digits (or more, if a already has more), rounded half-to-even, so CAST('1500000000000000000' AS DECIMAL(78, 0)) / 1000000000000000000 is 1.500000000000000000. Division and modulo by zero raise an error.Casting a result that has non-zero fractional digits back to DECIMAL(78, 0) raises an error rather than rounding. For an integer quotient, subtract the remainder first: CAST((a - a % b) / b AS DECIMAL(78, 0)).

Example: Convert token amounts

transforms:
  token_amounts:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        sender,
        recipient,
        CAST(amount AS TEXT) AS amount_wei,
        -- 1.5 ETH is '1.500000000000000000'
        CAST(amount / 1000000000000000000 AS TEXT) AS amount_eth,
        -- 1% fee in wei, rounded down to a whole number
        CAST((amount - amount % 100) / 100 AS DECIMAL(78, 0)) AS fee_1_percent_wei
      FROM erc20_transfers

Example: Signed balance deltas

sql: |
  SELECT
    address,
    CAST(CAST(current_balance AS DECIMAL(78, 0)) - CAST(initial_balance AS DECIMAL(78, 0)) AS TEXT) AS profit_loss,
    CAST(ABS(CAST(current_balance AS DECIMAL(78, 0)) - CAST(initial_balance AS DECIMAL(78, 0))) AS TEXT) AS abs_change
  FROM balances

When a ClickHouse sink creates a table, it stores a 256-bit column as UInt256. Results derived from these columns, including a - b, keep that type, so a negative value fails the write. If a column can go negative, set it to Int256 in the sink’s schema_override.

decimal_arb functions

Operators and casts cover most needs. These functions handle explicit precision and scale, and conversions to and from other numeric types. Precision and scale arguments must be integer literals, with 1 <= precision <= 65535 and 0 <= scale <= precision. A NULL value gives NULL.

Function	Returns	Description
`to_decimal_arb_from_string(text, precision, scale)`	`decimal_arb`	Parse decimal text exactly. An invalid value, or one that doesn’t fit, raises an error.
`try_to_decimal_arb_from_string(text, precision, scale)`	`decimal_arb`	Like `to_decimal_arb_from_string`, but an invalid or non-fitting value returns `NULL`.
`to_decimal_arb_from_int(value, precision, scale)`	`decimal_arb`	Convert any signed or unsigned integer column exactly.
`to_decimal_arb_from_decimal128(value)` / `to_decimal_arb_from_decimal256(value)`	`decimal_arb`	Widen a `DECIMAL` column without changing its precision or scale.
`decimal_arb_to_decimal128(value, precision, scale)` / `decimal_arb_to_decimal256(value, precision, scale)`	`DECIMAL(precision, scale)`	Narrow to a native decimal (precision up to 38 or 76). Rounds half-to-even to `scale`. A value that doesn’t fit raises an error.
`decimal_arb_to_string(value)`	`VARCHAR`	Canonical decimal text. Same as `CAST(value AS TEXT)`.
`decimal_arb_rescale(value, precision, scale)`	`decimal_arb`	Re-encode at a new precision and scale without changing the value. Raises an error if that would drop significant digits. Also accepts a list of `decimal_arb`.
`decimal_arb_greatest(a, b, ...)` / `decimal_arb_least(a, b, ...)`	`decimal_arb`	Largest or smallest non-`NULL` argument. `GREATEST`/`LEAST` call these for you.
`decimal_arb_array_min(list)` / `decimal_arb_array_max(list)`	`decimal_arb`	Smallest or largest non-`NULL` element of a list column of `decimal_arb`. Empty or all-`NULL` lists give `NULL`.
`decimal_arb_array_sort(list[, 'ASC' \| 'DESC'[, 'NULLS FIRST' \| 'NULLS LAST']])`	same list type	Sort a list of `decimal_arb` numerically.
`legacy_wide_int_to_decimal_arb(value)`	`decimal_arb(78, 0)`	Upgrade a value in the old 256-bit format (32-byte `FixedSizeBinary` tagged as `u256` or `i256`) produced by an older plugin.

The operators are rewritten to decimal_arb_add, decimal_arb_sub, decimal_arb_mul, decimal_arb_div, decimal_arb_mod, decimal_arb_neg, decimal_arb_abs, decimal_arb_eq, decimal_arb_neq, decimal_arb_lt, decimal_arb_lte, decimal_arb_gt and decimal_arb_gte. You can call these directly, but the operators are clearer. SUM, MIN, MAX and AVG also accept decimal_arb, but streaming SQL transforms don’t run aggregates. Use the PostgreSQL aggregation sink to sum values instead. Example:

sql: |
  SELECT
    to_decimal_arb_from_string('1234567890.987654321098765432109876543210', 80, 30) AS precise,
    decimal_arb_to_decimal128(amount / 1000000, 38, 6) AS amount_usdc
  FROM erc20_transfers

coalesce_meta

Return the first non-null argument per row, like COALESCE, preserving the first argument’s field metadata. It only works on values in the old 256-bit format. For 256-bit columns, use COALESCE. Signature:

coalesce_meta(first, next, ...) -> BINARY(32)

Although the planner accepts any argument types, every argument must be a FixedSizeBinary(32) value of the same type at runtime. A decimal_arb argument fails with coalesce_meta currently supports only FixedSizeBinary(32).

Array Processing Functions

make_array

Build an array from individual values. Use it to pass several columns to a function that takes a list, such as _gs_evm_get_balances. Signature:

make_array(value1, value2, ...) -> ARRAY

All values must share a type. ARRAY[value1, value2] is an equivalent literal form. Example:

SELECT make_array(sender, recipient) AS parties
FROM native_transfers

array_filter_in

Filter array elements where a struct field matches any value in a provided list. This is particularly useful for preventing overflow panics when processing large nested arrays by filtering before unnesting. Signature:

array_filter_in(array, field_name, values_list) -> LIST

Parameters:

array - Array of structs to filter
field_name - Name of the struct field to match against (Utf8 string)
values_list - Array of values to match (supports Utf8 and Int64 types)

Returns: Filtered array containing only elements where the specified field matches any value in the list Use case: Preventing overflow panics When processing data with multiple nested unnest operations (e.g., transactions → operations → ledger_entry_changes), the row count can explode exponentially. In extreme cases, this causes an overflow panic in Arrow’s take function because Arrow uses i32 offsets internally for ListArray. When the total number of rows after unnesting exceeds ~2.1 billion, buffer offset calculations overflow. By filtering arrays before unnesting, you reduce both memory usage and processing time while avoiding these overflow issues. Example: Filter Stellar ledger entry changes

-- Before: unnest explodes ALL ledger_entry_changes, then filters
SELECT ... FROM operations, unnest(ledger_entry_changes) AS change
WHERE change.ledger_entry_type IN ('account', 'trustline')

-- After: filter FIRST, then unnest only matching elements
SELECT ... FROM operations, unnest(
  array_filter_in(ledger_entry_changes, 'ledger_entry_type', ARRAY['account', 'trustline'])
) AS change

Example: Filter Stellar operations by type

sql: |
  SELECT
    signature,
    unnest(
      array_filter_in(o.operation.ledger_entry_changes, 'ledger_entry_type', ARRAY['account', 'trustline'])
    ) AS change
  FROM stellar_transactions t,
       unnest(t.operations) AS o

to_large_list

Convert a List array (i32 offsets) to a LargeList array (i64 offsets). This allows processing arrays that would otherwise overflow the i32 offset limit during operations like unnest. Signature:

to_large_list(array) -> LARGE_LIST

Parameters:

array - List, LargeList, or FixedSizeList array to convert

Returns: LargeList array with i64 offsets (virtually unlimited capacity vs ~2.1B for i32) Use case: Safety net for large arrays Use to_large_list when you need ALL elements from an array but the arrays might be extremely large. This changes the offset type from i32 (~2.1B max) to i64, preventing overflow in Arrow’s take function. Example: Process large arrays safely

SELECT unnest(to_large_list(my_array_column)) AS element FROM table

Example: Stellar ledger entry changes

sql: |
  SELECT
    signature,
    unnest(to_large_list(o.operation.ledger_entry_changes)) AS change
  FROM stellar_transactions t,
       unnest(t.operations) AS o

When to use each function:

Function	Best for	Trade-off
`array_filter_in`	When you only need a subset of array elements	Reduces both memory and processing time
`to_large_list`	When you need ALL elements but arrays might be very large	Safety net with minimal overhead

array_filter

Filter array elements based on field value. Signature:

array_filter(array, field_name, value) -> LIST

Parameters:

array - Array of structs to filter
field_name - Name of field to match
value - Value to match against

Example: Filter Logs by Event Name

sql: |
  SELECT
    signature,
    -- Get only 'Transfer' events from instruction logs
    array_filter(
      instruction_logs,
      'event_name',
      'Transfer'
    ) as transfer_events
  FROM solana_transactions

Example: Filter Failed Instructions

sql: |
  SELECT
    slot,
    array_filter(
      transactions,
      'err',
      null
    ) as successful_transactions
  FROM solana_blocks

array_filter_first

Like array_filter but returns only the first matching element. Signature:

array_filter_first(array, field_name, value) -> STRUCT (or NULL)

Example:

sql: |
  SELECT
    signature,
    array_filter_first(
      instructions,
      'programId',
      'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'
    ) as first_token_instruction
  FROM solana_transactions

array_enumerate

Add index to each array element. Signature:

array_enumerate(array) -> LIST<STRUCT<index: INT, value: T>>

Example:

sql: |
  SELECT
    signature,
    array_enumerate(instructions) as indexed_instructions
  FROM solana_transactions

  -- Access with: indexed_instructions[1].index, indexed_instructions[1].value

array_struct_field

Project one string field out of every struct in an array, producing an array of strings with the same shape. NULL struct elements become NULL strings. Signature:

array_struct_field(array, field_name) -> LIST<VARCHAR>

field_name must be a non-null string literal (not a column), and the named field must be a VARCHAR field.

Example:

sql: |
  SELECT
    array_struct_field(accounts, 'pubkey') as pubkeys
  FROM solana_transactions

_gs_map_to_array_struct

Convert a MAP<VARCHAR, VARCHAR> into an array of key/value structs, for example to unnest the params map returned by the Solana program decoders. Signature:

_gs_map_to_array_struct(map) -> LIST<STRUCT<key VARCHAR, value VARCHAR>>

The map must have non-nullable VARCHAR keys and values. Example:

sql: |
  SELECT
    _gs_map_to_array_struct(decoded.params) as params
  FROM decoded_instructions

_gs_generate_series

Generate an array of integers from start to stop, inclusive of both endpoints. Combine with CROSS JOIN UNNEST for one-to-many row expansion, for example emitting one row per element of a decoded batch. Signature:

_gs_generate_series(start, stop) -> LIST<BIGINT>
_gs_generate_series(start, stop, step) -> LIST<BIGINT>

step is optional and defaults to 1. A negative step counts down (e.g. _gs_generate_series(5, 1, -2) returns [5, 3, 1]). A step of 0 raises an error, and a step pointing away from stop returns an empty array.

Pass start, stop and step either all as columns or all as literals. Mixing a literal with a column (for example a literal start of 1 with a column stop) fails on batches of more than one row. For a literal start, use DataFusion’s built-in generate_series(start, stop) instead.

Example: Expand one row per block in a range

sql: |
  SELECT
    range_id || '_' || CAST(t.block_number AS STRING) as id,
    t.block_number
  FROM block_ranges
    CROSS JOIN UNNEST(
      _gs_generate_series(start_block, end_block)
    ) AS t (block_number)

_gs_zip_arrays

Combine multiple arrays element-wise. Signature:

_gs_zip_arrays(array1, array2, ...) -> LIST<STRUCT>

Example:

sql: |
  SELECT
    _gs_zip_arrays(
      account_keys,
      pre_balances,
      post_balances
    ) as account_balance_changes
  FROM solana_transactions

String & Encoding Functions

string_to_array

Split string into array by delimiter. Signature:

string_to_array(string, delimiter) -> LIST<VARCHAR>

Example:

sql: |
  SELECT
    string_to_array(metadata_tags, ',') as tags_array
  FROM nft_metadata

_gs_split_string_to_array / _gs_split_string_by

Split a string into an array by a separator. _gs_split_string_by is an alias of _gs_split_string_to_array. Signatures:

_gs_split_string_to_array(string) -> LIST<VARCHAR>
_gs_split_string_to_array(string, separator) -> LIST<VARCHAR>

The separator argument is optional and defaults to a single space.

A literal separator only works when string is also a literal: combined with a column, it fails on batches of more than one row. To split a column, omit the separator and replace the delimiter with a space first (as below), or use string_to_array.

Example: Split a decoded batch parameter

sql: |
  SELECT
    _gs_split_string_to_array(
      REPLACE(TRIM(LEADING '[' FROM TRIM(TRAILING ']' FROM event_params[4])), ',', ' ')
    ) as token_ids
  FROM erc1155_batch_logs

SPLIT_INDEX

Split a string by a delimiter and return the element at a given index. This is particularly useful for extracting individual topics from the topics field in raw_logs datasets, which is a comma-separated string. Signature:

SPLIT_INDEX(string, delimiter, index) -> VARCHAR

Parameters:

string - The string to split
delimiter - The delimiter to split on
index - 0-based index of the element to return

Returns: The element at the given index, or NULL if the index is out of bounds. Example: Extract event signature from raw logs

sql: |
  SELECT *
  FROM base_logs
  WHERE SPLIT_INDEX(topics, ',', 0) = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

Example: Extract indexed parameters from topics

sql: |
  SELECT
    id,
    SPLIT_INDEX(topics, ',', 0) as event_signature,
    SPLIT_INDEX(topics, ',', 1) as indexed_param_1,
    SPLIT_INDEX(topics, ',', 2) as indexed_param_2
  FROM base_logs

Example: Distinguish ERC-721 from ERC-20 transfers

sql: |
  SELECT *
  FROM ethereum_logs
  WHERE SPLIT_INDEX(topics, ',', 0) = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
    AND SPLIT_INDEX(topics, ',', 3) IS NOT NULL  -- ERC-721 has 4 topics, ERC-20 has 3

Regular Expression Functions

Turbo pipelines include Flink-compatible regex functions: regexp_extract - Extract regex match groups

regexp_extract(string, pattern, group_index) -> VARCHAR

regexp_replace - Replace every regex match

regexp_replace(string, pattern, replacement) -> VARCHAR

The replacement is literal: $1 / \1 group references are not expanded, and there is no flags argument. regexp_count - Count regex matches

regexp_count(string, pattern) -> BIGINT

regexp_extract_all - Extract a capture group (default 1) from every match

regexp_extract_all(string, pattern[, group_index]) -> LIST<VARCHAR>

regexp_substr - Return the first whole match

regexp_substr(string, pattern) -> VARCHAR

regexp_instr - Position (1-based) of a match, 0 if none

regexp_instr(string, pattern[, start[, occurrence]]) -> BIGINT

An invalid pattern returns NULL from all of these functions. Example: Extract Address from Log Message

sql: |
  SELECT
    regexp_extract(
      log_message,
      'address: (0x[0-9a-fA-F]{40})',
      1
    ) as extracted_address
  FROM contract_logs

URL Functions

parse_url - Extract URL components

parse_url(url, component) -> VARCHAR
-- component: 'HOST', 'PATH', 'QUERY', 'PROTOCOL', etc.

url_encode / url_decode - URL encoding/decoding

url_encode(string) -> VARCHAR
url_decode(string) -> VARCHAR

Example:

sql: |
  SELECT
    parse_url(metadata_url, 'HOST') as host,
    parse_url(metadata_url, 'PATH') as path
  FROM nft_metadata

_gs_conv_base

Convert a number written as a string between bases 2–36. Signature:

_gs_conv_base(number, from_base, to_base) -> VARCHAR

Accepts: number as VARCHAR; from_base and to_base must both be INT32 columns or both be VARCHAR. Returns NULL for an invalid number or a base outside 2–36.

Pass literal bases as strings ('16', not 16). Pass all three arguments as literals or all three as columns: literal bases combined with a number column fail on batches of more than one row.

Example:

sql: |
  SELECT
    _gs_conv_base('FF', '16', '10') as decimal_value  -- '255'

base32_to_hex

Decode an RFC 4648 base32 string (padding optional) into a lowercase 0x-prefixed hex string. Signature:

base32_to_hex(encoded) -> VARCHAR

Returns: The hex string, or NULL for invalid or blank input. Example:

sql: |
  SELECT
    base32_to_hex('MZXW6YTBOI======') as hex  -- '0x666f6f626172'

bytes_to_hex_string

Convert a list of bytes into a lowercase 0x-prefixed hex string. Signature:

bytes_to_hex_string(bytes) -> VARCHAR

Accepts: LIST<UINT8>, or LIST<VARCHAR> of decimal byte strings ('0'–'255'). Returns: The hex string, or NULL for a NULL or empty list, a NULL element, or an invalid decimal byte. Example:

sql: |
  SELECT
    bytes_to_hex_string(make_array('15', '255')) as hex  -- '0x0fff'

gs_ss58_decode / gs_ss58_encode

Convert between Substrate/Polkadot SS58 addresses and 32-byte AccountId32 public keys. Signatures:

gs_ss58_decode(address) -> VARCHAR
gs_ss58_encode(pubkey_hex) -> VARCHAR
gs_ss58_encode(pubkey_hex, prefix) -> VARCHAR

Parameters:

Parameter	Type	Description
`address`	string	An AccountId32 SS58 address. The checksum is verified.
`pubkey_hex`	string	32-byte hex public key.
`prefix`	`UINT64` (optional)	SS58 network prefix, 0–16383. Defaults to `0` (Polkadot).

Returns: gs_ss58_decode returns the 0x-prefixed 32-byte key; gs_ss58_encode returns the SS58 address. Both return NULL for invalid input. Example:

sql: |
  SELECT
    gs_ss58_decode(signer) as signer_pubkey,
    gs_ss58_encode(signer_hex) as signer_address
  FROM polkadot_extrinsics

Arweave Tag Functions

Decode and index Arweave transaction tags. Signatures:

_gs_arweave_tags_decode(tags_json) -> LIST<STRUCT<name VARCHAR, value VARCHAR>>
_gs_arweave_string_array(tags) -> LIST<VARCHAR>

_gs_arweave_tags_decode takes a JSON array of base64url-encoded {"name", "value"} pairs and returns the decoded name/value structs.
_gs_arweave_string_array takes the decoded tag list and returns each tag as URL-escaped name#value text, which is convenient for searching.

Example:

sql: |
  SELECT
    _gs_arweave_tags_decode(tags_json) as tags,
    _gs_arweave_string_array(_gs_arweave_tags_decode(tags_json)) as tag_strings
  FROM arweave_transactions

Other String Functions

bin(number) - Convert to binary representation
translate(string, from_chars, to_chars) - Character translation
elt(index, string1, string2, ...) - Return element at index (1-based)
locate(substring, string[, start]) - Find substring position (1-based; 0 if not found)
instr(string, substring[, start[, occurrence]]) - Find the position of the nth occurrence (1-based; a negative start searches from the end)
split(string, delimiter) - Split into an array, keeping empty tokens
unhex(hex_string) - Decode hex string

Plus all standard SQL string functions: lower, upper, trim, substring, concat, replace, reverse, etc.

JSON Functions

Turbo pipelines have comprehensive JSON support compatible with Flink SQL.

json_query

Return the JSON object or array at a path, as JSON text. Signature:

json_query(json_string, path) -> VARCHAR

A scalar (string, number or boolean) at path returns NULL. Use json_value to extract scalars.

Paths support $, .key, [n] and ['key'] steps only; wildcards, recursive descent ($..) and filters are not supported. Example: Extract NFT Metadata

transforms:
  nft_attributes:
    type: sql
    primary_key: token_id
    sql: |
      SELECT
        token_id,
        json_value(metadata, '$.name') as nft_name,
        json_value(metadata, '$.description') as description,
        json_value(metadata, '$.image') as image_url,
        json_query(metadata, '$.attributes') as attributes_json
      FROM nft_tokens

json_value

Extract scalar value from JSON. Signature:

json_value(json_string, path) -> VARCHAR

Example:

sql: |
  SELECT
    json_value(metadata, '$.properties.category') as category,
    json_value(metadata, '$.properties.rarity') as rarity
  FROM nft_metadata

parse_json / try_parse_json

Parse JSON string to validate or convert. Signatures:

parse_json(json_string) -> JSON  -- Errors on invalid JSON
try_parse_json(json_string) -> JSON  -- Returns NULL on invalid JSON

Example: Safe JSON Parsing

sql: |
  SELECT
    token_id,
    try_parse_json(metadata_string) as parsed_metadata,
    -- Check if JSON is valid
    CASE
      WHEN try_parse_json(metadata_string) IS NOT NULL THEN true
      ELSE false
    END as is_valid_json
  FROM nft_tokens

is_json

Check if string is valid JSON. Signature:

is_json(string) -> BOOLEAN

json_object / json_array

Construct JSON objects and arrays. Signatures:

json_object(key1, value1, key2, value2, ...) -> JSON
json_array(value1, value2, ...) -> JSON

Example: Build JSON Response

sql: |
  SELECT
    json_object(
      'address', from_address,
      'amount', value,
      'timestamp', block_timestamp
    ) as transfer_json
  FROM erc20_transfers

Null handling variants:

json_object_absent_on_null - Omit null fields
json_array_absent_on_null - Omit null elements

Other JSON Functions

json_quote(string) - Quote and escape a string as a JSON string literal
json_unquote(string) - Unescape a double-quoted JSON string; other text is returned unchanged
json(string) - Validate JSON text and re-serialize it compactly (errors on invalid JSON)
json_arrayagg_null_on_null(value) / json_arrayagg_absent_on_null(value) - Aggregate a group’s values into a JSON array, keeping or omitting NULLs
json_objectagg_null_on_null(key, value) / json_objectagg_absent_on_null(key, value) - Aggregate a group’s key/value pairs into a JSON object; NULL or duplicate keys raise an error

json_exists

Check if JSON path exists. Signature:

json_exists(json_string, path) -> BOOLEAN

Example:

sql: |
  SELECT *
  FROM nft_metadata
  WHERE json_exists(metadata, '$.attributes.rare')

json_string

Converts any value to its JSON-encoded string representation. Primitives are quoted; structs and arrays are serialized to JSON objects/arrays. Signature:

json_string(value) -> VARCHAR

Parameters:

Parameter	Type	Description
`value`	any	Any Arrow-supported value, including structs and arrays.

Returns: A JSON string. NULL input is passed through. Example:

sql: |
  SELECT
    json_string('hello')         as quoted_string,   -- '"hello"'
    json_string(42)              as number,          -- '42'
    json_string(struct(a => 1))  as struct_as_json   -- '{"a":1}'
  FROM events

_gs_json_objects_to_clickhouse_tuples

Format selected keys from a JSON array of objects as ClickHouse tuple literals, for example to load nested rows into a ClickHouse Array(Tuple(...)) column. Signature:

_gs_json_objects_to_clickhouse_tuples(json, keys) -> VARCHAR

Parameters:

Parameter	Type	Description
`json`	string	A JSON array of objects.
`keys`	`LIST<VARCHAR>` (non-null items)	The object keys to emit, in tuple order. The keys from the first row are used for every row.

Example:

sql: |
  SELECT
    _gs_json_objects_to_clickhouse_tuples(accounts_json, ARRAY['pubkey']) as account_tuples
  FROM transactions

Time Functions

now / current_time / current_date

Get current timestamp, time, or date. Signatures:

now() -> TIMESTAMP
current_time() -> TIME
current_date() -> DATE

These functions are volatile - they return different values on each call. Use them for adding processing timestamps, not for filtering historical data.

Example: Add Processing Timestamp

sql: |
  SELECT
    *,
    now() as processed_at,
    current_date() as processing_date
  FROM ethereum_transactions

date_part

Extract part of a timestamp. Signature:

date_part(part, timestamp) -> INT
-- part: 'year', 'month', 'day', 'hour', 'minute', 'second', 'epoch', etc.

Example: Analyze by Hour

sql: |
  SELECT
    date_part('hour', to_timestamp(blockTime)) as hour_of_day,
    date_part('epoch', to_timestamp(blockTime)) - blockTime as age_seconds
  FROM solana_blocks

to_timestamp / to_timestamp_micros

Convert Unix timestamp to TIMESTAMP. Signatures:

to_timestamp(seconds) -> TIMESTAMP
to_timestamp_micros(microseconds) -> TIMESTAMP

Example:

sql: |
  SELECT
    slot,
    to_timestamp(blockTime) as block_timestamp,
    to_timestamp_micros(blockTime * 1000000) as block_timestamp_precise
  FROM solana_blocks

Hash & ID Functions

_gs_xxhash

Fast non-cryptographic hash function (XXH3 128-bit). Returns a 32-character hex string. Signature:

_gs_xxhash(input) -> VARCHAR

Example: Create Deterministic IDs

sql: |
  SELECT
    _gs_xxhash(concat(transaction_hash, '_', log_index::VARCHAR)) as unique_id,
    transaction_hash,
    log_index
  FROM ethereum_logs

uuid7

Generates a UUIDv7, a time-ordered UUID that embeds the current timestamp. Useful as a primary key when you want inserts to be monotonically sorted by time. Signature:

uuid7() -> VARCHAR

Returns: A newly generated UUIDv7 string (e.g. 018f2a5c-1234-7abc-8def-0123456789ab). Called once per row, so every row gets a distinct value. Example:

sql: |
  SELECT
    uuid7() as event_id,
    transaction_hash,
    block_number
  FROM ethereum_logs

Dynamic Table Functions

dynamic_table_check

Check if a value exists in a dynamic table (async function). Signature:

dynamic_table_check(table_name, value) -> BOOLEAN

Parameters:

table_name - Reference name of the dynamic table (from your pipeline config). An unknown name fails at execution.
value - Value to check for existence: a string, or an array of strings (LIST<VARCHAR>)

Returns: true if the value exists in the table, false otherwise, and NULL for a NULL value. For an array, true if any non-null element exists in the table; an empty array returns false. See the Dynamic Tables documentation for complete details. Example:

transforms:
  tracked_wallets:
    type: dynamic_table
    backend_type: Postgres
    backend_entity_name: user_wallets
    secret_name: MY_POSTGRES

  filtered_transfers:
    type: sql
    primary_key: id
    sql: |
      SELECT *
      FROM erc20_transfers
      WHERE dynamic_table_check('tracked_wallets', from_address)
         OR dynamic_table_check('tracked_wallets', to_address)

Function Composition

Functions can be composed to build complex transformations. Example: Decode Log and Convert Value

sql: |
  WITH decoded AS (
    SELECT
      id,
      evm_decode_log(abi, topics, data) as evt,
      block_timestamp
    FROM ethereum_logs
  )
  SELECT
    id,
    evt.event_signature as event_name,
    evt.event_params[1] as from_address,
    evt.event_params[2] as to_address,
    -- Convert wei to ETH: event_params are strings, so cast to a 256-bit integer first
    CAST(
      CAST(evt.event_params[3] AS DECIMAL(78, 0)) / 1000000000000000000 AS TEXT
    ) as amount_eth,
    block_timestamp
  FROM decoded

Example: Filter Array and Decode First Match

sql: |
  SELECT
    signature,
    _gs_solana_decode_token_program_instruction(
      array_filter_first(
        instructions,
        'programId',
        'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'
      ).data,
      array_filter_first(
        instructions,
        'programId',
        'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'
      ).accounts
    ) as decoded_token_instruction
  FROM solana_transactions

Performance Considerations

Function Caching

Some functions use internal caching for performance:

evm_decode_log caches ABI decoders (reuse the same ABI string)
Regex functions cache compiled patterns
For best performance, use literal strings for patterns/ABIs when possible

Async Functions

dynamic_table_check is async and may perform I/O operations (database lookups). Use it strategically: - Good: Filter with WHERE clause using dynamic table - Avoid: Using in complex calculations or deeply nested expressions

256-bit Operations

256-bit arithmetic is more expensive than native integers:

Use DECIMAL(78, 0) only for values that need it, such as token amounts
Do as much filtering as possible before 256-bit operations
Convert to text once, at the end: CAST(a / b AS TEXT), not arithmetic on text values

Array Operations

Array processing can be expensive on large arrays:

Filter arrays early in the pipeline
Use array_filter_first instead of array_filter when you only need one element
Consider if SQL filtering can reduce array size before processing

Best Practices

Use type-specific functions

Use 256-bit integers (DECIMAL(78, 0)) for token amounts, regular INT for counters and IDs

Validate data

Use try_parse_json instead of parse_json when data quality is uncertain

Minimize function calls

Store function results in CTEs when used multiple times:

sql WITH decoded         AS ( SELECT id, evm_decode_log(abi, topics, data) as evt FROM logs ) SELECT         evt.event_signature, evt.event_params[1], evt.event_params[2] FROM decoded

Handle NULLs

Many blockchain fields can be NULL - use COALESCE or NULL checks

Use appropriate hash functions

_gs_keccak256 for EVM-compatible hashing (event signatures, etc.)
_gs_xxhash for fast non-cryptographic hashing (IDs, deduplication)

Common Patterns

Pattern: Decode and Transform ERC-20 Transfers

transforms:
  decoded_erc20_transfers:
    type: sql
    primary_key: id
    sql: |
      WITH decoded AS (
        SELECT
          id,
          address as contract_address,
          transaction_hash,
          evm_decode_log(
            '[{"anonymous":false,"inputs":[{"indexed":true,"name":"from","type":"address"},{"indexed":true,"name":"to","type":"address"},{"indexed":false,"name":"value","type":"uint256"}],"name":"Transfer","type":"event"}]',
            topics,
            data
          ) as evt,
          block_number,
          block_timestamp
        FROM ethereum_logs
        WHERE SPLIT_INDEX(topics, ',', 0) = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
      )
      SELECT
        id,
        contract_address,
        transaction_hash,
        lower(evt.event_params[1]) as from_address,
        lower(evt.event_params[2]) as to_address,
        evt.event_params[3] as value_wei,
        CAST(
          CAST(evt.event_params[3] AS DECIMAL(78, 0)) / 1000000000000000000 AS TEXT
        ) as value_eth,
        block_number,
        block_timestamp
      FROM decoded

Pattern: Filter Solana Token Transfers

transforms:
  solana_token_transfers:
    type: sql
    primary_key: signature
    sql: |
      WITH token_ix AS (
        SELECT
          signature,
          slot,
          blockTime,
          array_filter(
            instructions,
            'programId',
            'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'
          ) as token_instructions
        FROM solana_transactions
      )
      SELECT
        signature,
        slot,
        to_timestamp(blockTime) as block_timestamp,
        _gs_solana_decode_token_program_instruction(
          token_instructions[1].data,
          token_instructions[1].accounts
        ) as decoded_instruction,
        array_length(token_instructions) as token_instruction_count
      FROM token_ix
      WHERE array_length(token_instructions) > 0

Pattern: Process NFT Metadata with JSON

transforms:
  nft_enriched:
    type: sql
    primary_key: token_id
    sql: |
      SELECT
        token_id,
        contract_address,
        -- Extract metadata fields safely
        COALESCE(
          json_value(metadata, '$.name'),
          'Unknown'
        ) as name,
        json_value(metadata, '$.description') as description,
        json_value(metadata, '$.image') as image_url,
        -- Check for at least one attribute (path filters such as [?(...)] are not supported)
        json_exists(metadata, '$.attributes[0]') as has_attributes,
        -- Validate metadata
        is_json(metadata) as valid_metadata
      FROM nft_tokens
      WHERE metadata IS NOT NULL
        AND is_json(metadata)

​Overview

​EVM & Ethereum Functions

​evm_log_decode

​evm_trace_decode

​_gs_fetch_abi

​_gs_eth_call

​_gs_evm_get_balances

​_gs_keccak256

​_gs_hex_to_byte / _gs_byte_to_hex

​to_checksum_address

​reverse_bytes32

​Solana Functions

​_gs_decode_instruction_data (IDL Decode)

​_gs_solana_decode_token_program_instruction

​_gs_solana_decode_system_program_instruction

​_gs_solana_get_accounts

​_gs_solana_get_balance_changes

​Other Solana Decoders

​_gs_from_base58

​_gs_solana_base58

​_gs_solana_pin_token_balances

​_gs_flatten_balances

​_gs_decode_log_message

​_gs_solana_get_leader_info

​Large Numbers (256-bit integers and wide decimals)

​Working with 256-bit values

​decimal_arb functions

​coalesce_meta

​Array Processing Functions

​make_array

​array_filter_in

​to_large_list

​array_filter

​array_filter_first

​array_enumerate

​array_struct_field

​_gs_map_to_array_struct

​_gs_generate_series

​_gs_zip_arrays

​String & Encoding Functions

​string_to_array

​_gs_split_string_to_array / _gs_split_string_by

​SPLIT_INDEX

​Regular Expression Functions

​URL Functions

​_gs_conv_base

​base32_to_hex

​bytes_to_hex_string

​gs_ss58_decode / gs_ss58_encode

​Arweave Tag Functions

​Other String Functions

​JSON Functions

​json_query

​json_value

​parse_json / try_parse_json

​is_json

​json_object / json_array

​Other JSON Functions

​json_exists

​json_string

​_gs_json_objects_to_clickhouse_tuples

​Time Functions

​now / current_time / current_date

​date_part

​to_timestamp / to_timestamp_micros

​Hash & ID Functions

​_gs_xxhash

​uuid7

​Dynamic Table Functions

​dynamic_table_check

​Function Composition

​Performance Considerations

​Best Practices

​Common Patterns

​Pattern: Decode and Transform ERC-20 Transfers

​Pattern: Filter Solana Token Transfers

​Pattern: Process NFT Metadata with JSON

Overview

EVM & Ethereum Functions

evm_log_decode

evm_trace_decode

_gs_fetch_abi

_gs_eth_call

_gs_evm_get_balances

_gs_keccak256

_gs_hex_to_byte / _gs_byte_to_hex

to_checksum_address

reverse_bytes32

Solana Functions

_gs_decode_instruction_data (IDL Decode)

_gs_solana_decode_token_program_instruction

_gs_solana_decode_system_program_instruction

_gs_solana_get_accounts

_gs_solana_get_balance_changes

Other Solana Decoders

_gs_from_base58

_gs_solana_base58

_gs_solana_pin_token_balances

_gs_flatten_balances

_gs_decode_log_message

_gs_solana_get_leader_info

Large Numbers (256-bit integers and wide decimals)

Working with 256-bit values

decimal_arb functions

coalesce_meta

Array Processing Functions

make_array

array_filter_in

to_large_list

array_filter

array_filter_first

array_enumerate

array_struct_field

_gs_map_to_array_struct

_gs_generate_series

_gs_zip_arrays

String & Encoding Functions

string_to_array

_gs_split_string_to_array / _gs_split_string_by

SPLIT_INDEX

Regular Expression Functions

URL Functions

_gs_conv_base

base32_to_hex

bytes_to_hex_string

gs_ss58_decode / gs_ss58_encode

Arweave Tag Functions

Other String Functions

JSON Functions

json_query

json_value

parse_json / try_parse_json

is_json

json_object / json_array

Other JSON Functions

json_exists

json_string

_gs_json_objects_to_clickhouse_tuples

Time Functions

now / current_time / current_date

date_part

to_timestamp / to_timestamp_micros

Hash & ID Functions

_gs_xxhash

uuid7

Dynamic Table Functions

dynamic_table_check

Function Composition

Performance Considerations

Best Practices

Common Patterns

Pattern: Decode and Transform ERC-20 Transfers

Pattern: Filter Solana Token Transfers

Pattern: Process NFT Metadata with JSON