What’s in a transaction?

If you’ve ever made a transaction on Ethereum (or any smart contract enabled blockchain), then you’ve probably looked it up on a block explorer like etherscan.io and seen this heap of information:

Transaction Overview Tab

And if you tried looking at logs or traces (internal txs), you might have seen these confusing pages:

Logs tab (you’re lucky if they’re nicely decoded like this)

Traces tab (yeah it looks like a bunch of nonsense)

Learning to read the details of a transaction on block explorers will be the foundation for all your Ethereum data analysis and knowledge, so let’s cover all the pieces and how to work with them in SQL.

I’m only going over how to understand these concepts at a high level; if you want to learn to decode these by hand, then you’ll need to get familiar with how data is encoded (it’s the same for transactions/traces/logs) and how to use Dune’s bytearray/hex functions to go between different types.

By the end of this guide, you’ll be able to understand and navigate the data tables for any contract using this transaction table finder query:

Query Link (Plug in any tx hash, chain, and block number)

After you’ve learned the concepts in this guide, you should also learn to use my EVM quickstart dashboard to get started on any contract analysis.

How To Analyze Any Ethereum Protocol or Product In Five Minutes

Andrew Hong

·

2022年12月30日

Read full story

Transactions

Transactions are only the tip of data iceberg, all traces and logs are invoked AFTER the initial input data kicks off the top level function. Let’s first label all the fields you’ll see in the transaction page of the block explorer:

These are the same fields you’ll see when you query “ethereum.transactions” on Dune. The key item to learn to identify here is if the “to” is a contract or not. Normally, contracts will be clearly labelled. If it’s a contract, there should be “input data” which contain a function call.

transaction link

Out of all these concepts, the first one to learn well is an EOA versus a contract address. Contracts are deployed by EOAs, and can be called in the “to” field of a transaction. If you click on an address, the explorers will show on the top left if it’s a contract or an account. On dune you can join on the ethereum.creation_traces table to check if it’s a contract. Note that only EOAs can be the tx “from” signer.

It’s important to learn what data comes from directly onchain versus what data the explorer/frontends have added on top. Everything in the blockchain is represented as hex (sometimes called binary or bytes), so a 1inch swap call will have this input data string:

example transaction

The first 4 bytes (8 characters) is the “function signature”, which is the keccak hash of the function name and input types. Etherscan has a nice “decode” button for some contracts, giving you this readable form:

example transaction

As you can see, there are many variables packed together into that one long hex string from earlier. The way they are encoded follows the application binary interface (ABI) specification of smart contracts.

ABI’s are like API documentation for smart contracts (like OpenAPI specs), you can read more on the technical details here. Most developers will verify their ABI matches the contract and upload the ABI for everyone else to reference in decoding. Many contracts may be MEV/trading related, where the developer wants to keep things closed source and private - so we don’t get any decoding from them.

In Dune, we have decoded tables based on contract ABIs submitted to a contracts table (i.e. ethereum.contracts), functions and events are converted to bytes signatures (ethereum.signatures) which are then matched against traces and logs to give you decoded tables such as uniswap_v2_ethereum.Pair_evt_Swap which stores all swaps for all pair contracts created by the Uniswap v2 pair factory. You can filter for swaps on a specific pair by looking at the contract_address table for events.

On Dune, you would want to query this table for this function call oneinch_ethereum.AggregationRouterV6_call_swap. You’ll see this table name is at the top of the query results in the table finder at the start of the guide.

For the following sections on traces and logs, we’ll be using the same 1inch aggregator swap transaction. This is a good example because a router will swap tokens across numerous DEX contracts, so we’ll get a good diversity of traces and logs to investigate.

Logs

Let’s talk about event logs next. Logs can be emitted at any point in a function call. Devs typically will emit a log at the end of a function, after all transfers/logic are completed without errors. Let’s look at the uniswap v3 swap event emitted from the transaction earlier:

example transaction

You’ll see there is a topic0, topic1, topic2, and data field. topic0 is akin to the function signature, except it’s 32 bytes instead of just 4 bytes (still hashed the same way). Events can have “indexed” fields for faster data filtering, which can appear in topic1, topic2, or topic3. All other fields are encoded together in the “data” object. Again, they follow the same encoding rules as transactions and traces. The “28” is the index of the event in the whole block. It can sometimes be useful to join on when you want the first swap or transfer in a tx.

To find the logic behind where and how this event was emitted, I’ll need to dive into the solidity code. I’ll click the linked address of the event, go to the contract tab, and search “emit swap” because I know that all events have “emit” right before they are invoked in the code.

This is the uniswapv3pool contract that is factory created for each pair.

I can see that this is emitted in line 786 of the contract, as part of the “swap” function.

Being able to navigate functions and events lineage across contracts will be a key skill you’ll need to pick up to accurately understand the lineage of the data you’re querying. You don’t need to learn solidity in depth to navigate these files, just know how to understand contract interfaces and when functions/events are called (function and emit are your keywords).

For in depth example of sleuthing the code for functions and events, check out this breakdown of Sudoswap contracts and data.

Using the table finder query from earlier, I can see that the table I should query for this swap is uniswap_v3_ethereum.Pair_evt_Swap and that it’s emitted after the swap() function is called.

Traces (ethereum.traces)

Traces can quickly become very difficult to navigate, because of how nested calls between different contracts get. Let’s first understand the types of traces:

• CREATE: this is a trace emitted when a new contract is deployed. You can deploy a contract directly at the top of a transaction, this will mean there is no “to” address in the transaction data. You can also deploy a contract within a function call, hence the existence of contract factories. Check out the ethereum.creation_traces table for a simpler view of these.
• DELEGATECALL: this goes on your mental “ignore” list when looking at a transaction. Think of this as forwarding a request from one server to a next without changing any logic. This is related to proxies and storage, you can @bansaltushar014/delegatecall-in-solidity-4138b121cbe">check out more details here.
• CALL: this is the most common and generic trace. A call can be just a transfer of ETH value without any contracts involved. It can also be any function call on any contract.
• STATICCALL: this is a function call that does NOT modify any state, and is purely used for calculations. Stuff like oracle price feeds, AMM price calculations, liquidation ratio checks, balance checks, etc all happen in staticcalls. Commonly seen in solidity as “view” or “pure” function types.

You’ll also need to understand the trace_address column/index. This is the [0,1,1,1,1] pattern you often see. Imagine it’s like bullet points, where the number of numbers in the array indicates the depth and order of the function calls.

A (null) —the transaction first input has a trace_address of []

CALLs B (0)

CALLs C (0,0)

CALLs D (1)

CALLs E (1,0)

  CALLs F (1,0,0)

CALLs G (1,1)

CALLs H (2)

As you can tell from our earlier internal transactions (traces) screenshot, etherscan is not a friendly place for viewing traces. I prefer to use phalcon blocksec instead, which unwraps the transaction like so:

Link to Explorer

This might look overwhelming, but it is actually a super easy way to explore all the functions, events, and arguments in the flow of a transaction. Once you’re able to understand this, then you can safely say you understand all the data in a transaction. Notice that my table finder query is an almost exact copy of this layout, I was largely inspired by them!

Note that on Dune, we automatically decode both transaction calls and traces of the same function name to the same table. You may be wondering if you can easily join events and traces/transactions in the nice ordering shown in phalcon. On Dune, you can join on transaction hash to generally tie data together, but you can’t join on any index to recreate the exact ordering of interactions. It’s an unfortunate limitation at the moment that requires a custom indexer.

Onwards, deeper into the dark forest of crypto

If you understand the concepts I’ve laid out in this guide, then you’re ready to dig deeper and write more complex queries. Navigating data across transactions using multiple different tools will be one of the most key skills you’ll need to excel in this space.

There are probably 10 different explorers I find myself using every week, and the number of the tools is 10 times that amount. I write an annual guide covering how the data tooling stack continues to evolve, and what you should use each tool for:

Guide Link

Disclaimer：

1. This article is reprinted from [cryptodatabytes], All copyrights belong to the original author [Andrew Hong]. If there are objections to this reprint, please contact the Gate Learn team, and they will handle it promptly.
2. Liability Disclaimer: The views and opinions expressed in this article are solely those of the author and do not constitute any investment advice.
3. Translations of the article into other languages are done by the Gate Learn team. Unless mentioned, copying, distributing, or plagiarizing the translated articles is prohibited.