> ## Documentation Index > Fetch the complete documentation index at: https://docs.goldsky.com/llms.txt > Use this file to discover all available pages before exploring further. # Native transfers > Create a table containing transfers for the native token of a chain This guide explains how to use [Raw Traces Direct Indexing sources](/mirror/sources/direct-indexing) to create a Mirror pipeline that allows you to stream all native transactions for a chain into your database. In the example below, we will focus on ETH transfers on the Ethereum network but the same logic applies to any EVM-compatible chain which has this source available. This guide is part of a series of tutorials on how you can export transfer data into your datawarehouse. Here we will be focusing on ERC-20 Transfers, visit the following guides for other types of Transfers: * [ERC-20 Transfers](/mirror/guides/token-transfers/ERC-20-transfers) * [ERC-721 Transfers](/mirror/guides/token-transfers/ERC-721-transfers) * [ERC-1155 Transfers](/mirror/guides/token-transfers/ERC-1155-transfers) ## What you'll need 1. A basic understanding of the Mirror product's more [basic ETL use case](/mirror/guides/export-events-to-database). 2. A basic understanding of SQL, though we use the syntax and functionality of [Flink v1.17](https://nightlies.apache.org/flink/flink-docs-release-1.17/docs/dev/table/functions/systemfunctions/). 3. A destination sink to write your data to. ## Preface When it comes to identifying native transfers on a chain it's important to highlight that there are [two types of accounts](https://ethereum.org/en/developers/docs/accounts/) that 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 a EOAs (this is something that could change in the future with the introduction of [Account Abstraction](https://ethereum.org/en/roadmap/account-abstraction/)). For instance, take [block 16240000](https://etherscan.io/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](https://etherscan.io/tx/0x7498065db91e8543c6eafed286687fe8006b9ff90081153f769ad47ce115afc8). Alternatively, this EOA can call a smart contract's method and optionally send value with it as in [this transaction](https://etherscan.io/tx/0x7856bfef7e5da7b22fbdc2fa923bf29d040b2d1b3dbdb3b834dffdc06f4f0a17/advanced). 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](https://etherscan.io/tx/0x7856bfef7e5da7b22fbdc2fa923bf29d040b2d1b3dbdb3b834dffdc06f4f0a17#internal). 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 Mirror pipeline. You can see its data schema [here](/mirror/reference/schema/EVM-schemas#raw-traces). ## 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: 1. Your `secret_name`. If you already [created a secret](https://docs.goldsky.com/mirror/manage-secrets), you can find it via the [CLI command](https://docs.goldsky.com/reference/cli#secret) `goldsky secret list`. 2. The schema and table you want the data written to, by default it writes to `public.eth_transfers`. ```yaml native-transfers.yaml expandable theme={null} name: native-transfers apiVersion: 3 sources: my_ethereum_raw_traces: dataset_name: ethereum.raw_traces version: 1.1.0 type: dataset start_at: earliest transforms: ethereum_eth_transfers_transform: primary_key: id description: ETH Transfers transform 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(100)), 0) AS `value` CASE WHEN block_number <= 17999551 THEN COALESCE(TRY_CAST(`value` AS DECIMAL(100)) / 1e9, 0) ELSE COALESCE(TRY_CAST(`value` AS DECIMAL(100)), 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_ethereum.eth_transfers: type: postgres table: eth_transfers schema: public secret_name: description: "Postgres sink for ethereum.eth_transfers" from: ethereum_eth_transfers_transform ``` ### Native Transfers Transform We'll start at the top. #### Traces context columns ```sql theme={null} 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 ```sql theme={null} CASE WHEN trace_address <> '' THEN 'Internal TX' ELSE 'EOA TX' END AS tx_type, ``` Here we look into `trace_address` column to identify whether this an initial EOA transaction or an internal one. This is also optional to include. #### Token Value ```sql theme={null} -- ETHEREUM MAINNET ONLY: Apply 1e9 correction for blocks <= 17999551 -- For other chains, use: COALESCE(TRY_CAST(`value` AS DECIMAL(100)), 0) AS `value` CASE WHEN block_number <= 17999551 THEN COALESCE(TRY_CAST(`value` AS DECIMAL(100)) / 1e9, 0) ELSE COALESCE(TRY_CAST(`value` AS DECIMAL(100)), 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: ```sql theme={null} COALESCE(TRY_CAST(`value` AS DECIMAL(100)), 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. Some columns are surrounded by backticks, this is because they are reserved words in Flink SQL. Common columns that need backticks are: data, output, value, and a full list can be found [here](https://nightlies.apache.org/flink/flink-docs-release-1.17/docs/dev/table/sql/overview/#reserved-keywords). #### Filter ```sql theme={null} call_type, trace_address, status ``` We include these values in the SELECT statement as we will making use of them in the filter explained below: ```sql theme={null} WHERE call_type <> 'delegatecall' and `value` > 0 and status = 1; ``` Here we filter based on: * `call_type <> 'delegatecall'`: [delegatecall](https://www.educative.io/answers/what-is-delegatecall-in-ethereum) 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 ERC-20 transfer data into your database simply execute: `goldsky pipeline create --definition-path ` Can't find what you're looking for? Reach out to us at [support@goldsky.com](mailto:support@goldsky.com) for help.