Native transfers

This guide explains how to use raw traces datasets to create a Turbo pipeline that streams all native transactions for a chain into your database. The example uses ETH transfers on the Ethereum network, but the same logic applies to any EVM-compatible chain which has this dataset available. This guide is part of a series of tutorials on how you can export transfer data into your data warehouse. Here we will be focusing on native transfers, visit the following guides for other types of transfers:

What you’ll need

A basic understanding of Turbo pipelines. If you’re new to the product, start with the quickstart.
A basic understanding of SQL. Turbo SQL transforms run on Apache DataFusion.
A destination sink to write your data to.

Preface

Two types of accounts can interact with transactions:

Externally Owned Accounts (EOA): controlled by an actual user.
Contract Accounts: controlled by code.

Currently, transactions in a block can only be initiated by EOAs (this is something that could change in the future with the introduction of Account Abstraction). For instance, take block 16240000; you will see all transactions initiated belong to EOAs. A transaction initiated by an EOA can send value to another EOA as in this transaction. Alternatively, this EOA can call a smart contract’s method and optionally send value with it as in this transaction. Smart contracts can then call other smart contracts. They can alternatively send value directly to another EOA. These internal transactions initiated by smart contracts can optionally send native value along so it is important to consider them. In most chain explorers you can identify these internal transactions and their corresponding value transfers accessing Advanced view mode. All of these types of transactions (EOA initiated & internal transactions) are available in our raw traces dataset so we will use it as the source for our Turbo pipeline. You can see its data schema here.

Pipeline YAML

There is one transform in this configuration and we’ll explain how it works. If you copy and use this configuration file, make sure to update:

Your secret_name. If you already created a secret, you can find it via the CLI command goldsky secret list.
The schema and table you want the data written to, by default it writes to public.eth_transfers.

native-transfers.yaml

name: native-transfers
resource_size: s

sources:
  my_ethereum_raw_traces:
    type: dataset
    dataset_name: ethereum.raw_traces
    version: 1.1.0
    start_at: earliest

transforms:
  # ETH transfers transform
  ethereum_eth_transfers_transform:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        block_number,
        block_hash,
        block_timestamp,
        transaction_hash,
        transaction_index,
        from_address,
        to_address,
        CASE
          WHEN trace_address <> '' THEN 'Internal TX'
          ELSE 'EOA TX'
        END AS tx_type,
        -- ETHEREUM MAINNET ONLY: Apply 1e9 correction for blocks <= 17999551
        -- For other chains, use: COALESCE(TRY_CAST(value AS DECIMAL(38, 0)), 0) AS value
        CASE
          WHEN block_number <= 17999551 THEN COALESCE(TRY_CAST(value AS DECIMAL(38, 0)) / 1000000000, 0)
          ELSE COALESCE(TRY_CAST(value AS DECIMAL(38, 0)), 0)
        END AS value,
        call_type,
        trace_address,
        status
      FROM my_ethereum_raw_traces
      WHERE call_type <> 'delegatecall' AND value > 0 AND status = 1

sinks:
  # Postgres sink for Ethereum ETH transfers
  postgres_eth_transfers:
    type: postgres
    from: ethereum_eth_transfers_transform
    schema: public
    table: eth_transfers
    secret_name: <YOUR_SECRET>
    primary_key: id

Native transfers transform

We’ll start at the top.

Traces context columns

SELECT
    id,
    block_number,
    block_hash,
    block_timestamp,
    transaction_hash,
    transaction_index,
    from_address,
    to_address,

These are optional columns from this dataset which we include to give us some context around the actual transfer.

Transaction type

CASE
    WHEN trace_address <> '' THEN 'Internal TX'
    ELSE 'EOA TX'
END AS tx_type,

Here we look into the trace_address column to identify whether this is an initial EOA transaction or an internal one. This is also optional to include.

Token value

-- ETHEREUM MAINNET ONLY: Apply 1e9 correction for blocks <= 17999551
-- For other chains, use: COALESCE(TRY_CAST(value AS DECIMAL(38, 0)), 0) AS value
CASE
    WHEN block_number <= 17999551 THEN COALESCE(TRY_CAST(value AS DECIMAL(38, 0)) / 1000000000, 0)
    ELSE COALESCE(TRY_CAST(value AS DECIMAL(38, 0)), 0)
END AS value,

IMPORTANT: The CASE statement above with the 1e9 division is ONLY for Ethereum mainnet. If you’re working with other chains, replace the entire CASE statement with:

COALESCE(TRY_CAST(value AS DECIMAL(38, 0)), 0) AS value

This correction is needed because values before block 17999551 on Ethereum were incorrectly multiplied by 1e9 in the dataset. Other chain datasets do not have this issue.

If you’re coming from a Flink-based pipeline, note that Turbo’s DataFusion SQL doesn’t require backtick-quoting columns like value or data the way Flink did. See the SQL transforms documentation for the supported dialect.

Filter

call_type,
trace_address,
status

We include these values in the SELECT statement as we will be making use of them in the filter explained below:

WHERE
    call_type <> 'delegatecall' AND value > 0 AND status = 1

Here we filter based on:

call_type <> 'delegatecall': delegatecall is a type of function call where the called contract’s code is executed with the state of the calling contract, including storage and balance. In some cases, it can mistakenly carry over the value transfer of the original calling contract which would compromise our data quality due to value transfer duplications. As a result, we can safely leave them out of our resulting dataset as delegatecalls can never send value with them.
value > 0: we want to make sure we track transactions with actual native value.
status = 1: the raw traces dataset can contain traces which got reverted. With this filter, we make sure to consider only successful transactions.

Deploying the pipeline

To deploy this pipeline and start sinking native transfer data into your database simply execute:

goldsky turbo apply native-transfers.yaml

Can’t find what you’re looking for? Reach out to us at support@goldsky.com for help.

Turbo

Guides

Reference

Mirror (legacy)

What you’ll need

Preface

Pipeline YAML

Native transfers transform

Traces context columns

Transaction type

Token value

Filter

Deploying the pipeline

​What you’ll need

​Preface

​Pipeline YAML

​Native transfers transform

​Traces context columns

​Transaction type

​Token value

​Filter

​Deploying the pipeline

What you’ll need

Preface

Pipeline YAML

Native transfers transform

Traces context columns

Transaction type

Token value

Filter

Deploying the pipeline