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 (U256/I256) 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`	string	Comma-separated hex topic values (e.g. `"0xddf252ad...,0x00000000...from,0x00000000...to"`)
`data`	string	Hex-encoded non-indexed event data

When filtering by event signature (the first topic), you can also use topics LIKE '0xSIGNATURE%' as an alternative to SPLIT_INDEX. This only works for matching the first topic; use SPLIT_INDEX when you need to access topics at other positions.

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%'  -- PoolCreated event signature

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

_gs_tx_decode

Decodes raw trace/transaction call data using an ABI definition, the trace counterpart of evm_log_decode. Signature:

_gs_tx_decode(abi_json, input, output) → struct(function, decoded_inputs, decoded_outputs)

Parameters:

Parameter	Type	Description
`abi_json`	string	The function ABI as a JSON array. Use `_gs_fetch_abi()` to fetch this dynamically.
`input`	string	Hex-encoded calldata sent with the message call (the `input` column on raw traces)
`output`	string	Hex-encoded data returned by the message call (the `output` column on raw traces)

Returns: A struct with three fields:

function (string): the matched function name
decoded_inputs (array of strings): decoded input values in positional order
decoded_outputs (array of strings): decoded output values in positional order

The decoder automatically matches the right function in the ABI; partial ABIs that contain only the target function also work. Example: Decode raw traces

SELECT
  _gs_tx_decode(
    _gs_fetch_abi(
      'https://api.etherscan.io/api?module=contract&action=getabi&address=0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4&apikey=YOUR_KEY',
      'etherscan'
    ),
    input,
    output
  ) as decoded
FROM traces

_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 U256>>

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, so use u256_to_string to render it or the u256_* helpers to do 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,
        u256_to_string(sub.b.balance) 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 (can be regular string or hex with “0x” prefix)

Returns: Hex-encoded hash with “0x” prefix 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. _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`	bigint (optional)	Chain ID for EIP-1191 chain-specific checksumming. 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

Example:

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

Large Numbers (U256 & I256)

Blockchain smart contracts frequently use 256-bit integers for token amounts, balances, and calculations. These functions provide precise arithmetic without JavaScript’s number precision limits.

U256 Functions (Unsigned 256-bit)

to_u256

Convert various types to U256. Signature:

to_u256(value) -> U256

Accepts: VARCHAR, INT64, UINT64, INT32, UINT32, INT16, UINT16, INT8, UINT8 Example:

sql: |
  SELECT
    to_u256('1000000000000000000') as one_eth_wei,
    to_u256(value) as value_u256
  FROM erc20_transfers

u256_to_string

Convert U256 back to decimal string. Signature:

u256_to_string(u256_value) -> VARCHAR

u256_add / u256_sub / u256_mul / u256_div / u256_mod

U256 arithmetic operations. Signatures:

u256_add(a, b) -> U256
u256_sub(a, b) -> U256
u256_mul(a, b) -> U256
u256_div(a, b) -> U256
u256_mod(a, b) -> U256

Automatic Operator Rewriting: Once values are cast to U256 or I256 types, you can use standard SQL operators (+, -, *, /, %) instead of explicit function calls. Turbo’s SQL preprocessor automatically rewrites these to the appropriate functions.

-- These are equivalent:
to_u256(value) / to_u256('1000000')
u256_div(to_u256(value), to_u256('1000000'))

-- Use the cleaner syntax:
to_u256(value) + to_u256(fee)  -- Automatically becomes u256_add()
to_u256(balance) * to_u256(2)  -- Automatically becomes u256_mul()

Example: Calculate Token Amounts in ETH

transforms:
  token_amounts_eth:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        from_address,
        to_address,
        value,
        -- Convert wei to ETH (divide by 10^18) - using clean operator syntax
        u256_to_string(
          to_u256(value) / to_u256('1000000000000000000')
        ) as amount_eth,
        -- Calculate 1% fee
        u256_to_string(
          to_u256(value) / to_u256('100')
        ) as fee_1_percent
      FROM erc20_transfers

Example: Sum Multiple Token Amounts

sql: |
  SELECT
    to_address,
    -- Add two transfer amounts - using clean operator syntax
    u256_to_string(
      to_u256(transfer1_value) + to_u256(transfer2_value)
    ) as total_received
  FROM combined_transfers

U256 division truncates (no decimals). For percentage calculations or conversions to smaller units, multiply first, then divide.Good: (amount * to_u256('100')) / to_u256('1000000') (multiply by 100 first)Bad: (amount / to_u256('1000000')) * to_u256('100') (loses precision)

I256 Functions (Signed 256-bit)

Similar to U256 but supports negative numbers. Available functions:

to_i256(value) - Convert to I256
i256_to_string(i256_value) - Convert to string
i256_add(a, b) / i256_sub(a, b) - Addition/subtraction
i256_mul(a, b) / i256_div(a, b) / i256_mod(a, b) - Multiplication/division/modulo
i256_neg(value) - Negate (multiply by -1)
i256_abs(value) - Absolute value

Example: Track Profit/Loss

sql: |
  SELECT
    address,
    -- Calculate profit/loss using clean operator syntax
    i256_to_string(
      to_i256(current_balance) - to_i256(initial_balance)
    ) as profit_loss,
    -- Get absolute value of change
    i256_to_string(
      i256_abs(
        to_i256(current_balance) - to_i256(initial_balance)
      )
    ) as abs_change
  FROM balances

I256 and U256 operators work the same way - standard operators (+, -, *, /, %) are automatically rewritten to their corresponding function calls by the SQL preprocessor.

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

_gs_generate_series

Generate an array of consecutive integers, 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>

Example: Expand one row per array element

sql: |
  SELECT
    id || '_' || CAST(t.idx AS STRING) as id,
    token_ids[t.idx] as token_id
  FROM batch_transfers
    CROSS JOIN UNNEST(
      _gs_generate_series(
        CAST(1 AS BIGINT),
        CAST(COALESCE(CARDINALITY(token_ids), 0) AS BIGINT)
      )
    ) AS t (idx)

_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_by

Split a string into an array by the space character. Signature:

_gs_split_string_by(string) -> LIST<VARCHAR>

Splits on spaces only. To split on another delimiter, replace it first (e.g. REPLACE(col, ',', ' ')) or use string_to_array, which accepts an arbitrary delimiter. Example: Split a decoded batch parameter

sql: |
  SELECT
    _gs_split_string_by(
      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 regex matches

regexp_replace(string, pattern, replacement) -> VARCHAR

regexp_count - Count regex matches

regexp_count(string, pattern) -> INT

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

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
locate(substring, string) - Find substring position
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

Query JSON documents using path expressions. Signature:

json_query(json_string, path) -> VARCHAR

Example: Extract NFT Metadata

transforms:
  nft_attributes:
    type: sql
    primary_key: token_id
    sql: |
      SELECT
        token_id,
        json_query(metadata, '$.name') as nft_name,
        json_query(metadata, '$.description') as description,
        json_query(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

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

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)
value - Value to check for existence

Returns: true if value exists in the table, false otherwise 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 using U256 with clean operator syntax
    u256_to_string(
      to_u256(evt.event_params[3]) / to_u256('1000000000000000000')
    ) 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

U256/I256 Operations

Large number operations are more expensive than native integers: - Convert to U256 only when needed for precision - Do as much filtering as possible before U256 operations - Chain operations to minimize conversions: u256_to_string(u256_div(...)) not u256_to_string(...) / u256_to_string(...)

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 U256/I256 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,
        u256_to_string(
          to_u256(evt.event_params[3]) / to_u256('1000000000000000000')
        ) 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 rarity trait
        json_exists(metadata, '$.attributes[?(@.trait_type == "Rarity")]') as has_rarity,
        -- 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

​_gs_tx_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

​Large Numbers (U256 & I256)

​U256 Functions (Unsigned 256-bit)

​to_u256

​u256_to_string

​u256_add / u256_sub / u256_mul / u256_div / u256_mod

​I256 Functions (Signed 256-bit)

​Array Processing Functions

​make_array

​array_filter_in

​to_large_list

​array_filter

​array_filter_first

​array_enumerate

​_gs_generate_series

​_gs_zip_arrays

​String & Encoding Functions

​string_to_array

​_gs_split_string_by

​SPLIT_INDEX

​Regular Expression Functions

​URL Functions

​Other String Functions

​JSON Functions

​json_query

​json_value

​parse_json / try_parse_json

​is_json

​json_object / json_array

​json_exists

​json_string

​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

_gs_tx_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

Large Numbers (U256 & I256)

U256 Functions (Unsigned 256-bit)

to_u256

u256_to_string

u256_add / u256_sub / u256_mul / u256_div / u256_mod

I256 Functions (Signed 256-bit)

Array Processing Functions

make_array

array_filter_in

to_large_list

array_filter

array_filter_first

array_enumerate

_gs_generate_series

_gs_zip_arrays

String & Encoding Functions

string_to_array

_gs_split_string_by

SPLIT_INDEX

Regular Expression Functions

URL Functions

Other String Functions

JSON Functions

json_query

json_value

parse_json / try_parse_json

is_json

json_object / json_array

json_exists

json_string

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