Stablecoin transfers

Goldsky’s ERC-20 Transfers datasets are a powerful primitive to enable you to build stablecoin-specific streaming pipelines, for both app and analytics use cases. In this guide, we’ll walk through several examples of how to build these pipelines.

What you’ll need

A mapping of the stablecoin contract addresses on the chains that you are interested in. We include popular stablecoins on major chains on a dedicated page.
A basic understanding of SQL (optional but helpful), specifically Flink v1.17.
A destination sink to write your data to.

Examples

One stablecoin on one chain

The ERC-20 Transfers datasets do a lot of the heavy lifting for us, indexing all token transfers for a given chain. Building stablecoin-specific datasets is therefore a matter of filtering the data to only include transfers for the stablecoin contracts of interest.our Mirror pipeline. This filtering can be done via YAML, or via the Web UI.

Via YAML

Create a standard pipeline using an ERC-20 Transfers dataset as the source, and a filter to only include transfers for the stablecoin contracts of interest.

Example pipeline for USDC transfers on Ethereum

name: usdc-transfers
version: 1
resource_size: s
apiVersion: 3
sources:
  usdc_transfers:
    dataset_name: ethereum.erc20_transfers
    version: 1.2.0
    type: dataset
    start_at: latest
    filter: address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
transforms: {}
sinks:
  postgres_sink:
    type: postgres
    table: usdc_transfers
    schema: public
    secret_name: <YOUR SECRET>
    from: usdc_transfers
use_dedicated_ip: false

This will output a stream of all transfers for the stablecoin contract in the filter. Because the underlying stream has all addresses, this outputs a schema with address as a column which may not be needed, given there’s only one address in the filter. To remove this column, you can add a transform to filter it out.

Example pipeline for USDC transfers on Ethereum, with transform to remove address

name: usdc-transfers
resource_size: s
apiVersion: 3
sources:
  usdc_transfers:
    type: dataset
    dataset_name: ethereum.erc20_transfers
    version: 1.2.0
    filter: address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
    start_at: latest
transforms:
  usdc_transfers_without_address:
    type: sql
    sql: >-
      select id, sender, recipient, amount, transaction_hash,
      block_hash, block_number, block_timestamp,
      transaction_index, log_index from usdc_transfers
    primary_key: id
sinks:
  postgres_sink:
    type: postgres
    secret_name: <YOUR_SECRET>
    from: usdc_transfers_without_address
    table: usdc_test
    schema: public

Via Web UI

In the Goldsky Web UI, navigate to the Pipelines tab, and click on the + New Pipeline button. Select the ERC-20 Transfers dataset as the source, and a filter to only include transfers for the stablecoin contracts of interest.

As in the YAML example above, you can use a transform block to remove the address column from the output.

Many stablecoins on one chain

For the remaining examples, we show only the YAML-based approach, though all can be created in the Web UI as well. If you are working with multiple chains especially, the YAML approach may be more convenient, as each chain in the Web UI will require it’s own block.

This is similar to the example above, just using an in () filter to include multiple stablecoin contracts rather than only one.

Example pipeline for USDC, USDT, and DAI transfers on Ethereum

name: stablecoin-transfers
version: 1
resource_size: s
apiVersion: 3
sources:
  stablecoin_transfers:
    dataset_name: ethereum.erc20_transfers
    version: 1.2.0
    type: dataset
    start_at: latest
    filter: >-
        address in
        ('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48',
        '0xdac17f958d2ee523a2206206994597c13d831ec7', 
        '0x6b175474e89094c44da98b954eedeac495271d0f')
transforms: {}
sinks:
  postgres_sink:
    type: postgres
    table: stablecoin_transfers
    schema: public
    secret_name: <YOUR SECRET>
    from: stablecoin_transfers
use_dedicated_ip: false

This will include a column called address which can be used in the downstream sink to filter or aggregate against a specific stablecoin, though for ease of use you might prefer to add a transformation that adds in the stablecoin symbol as a column.

Example pipeline for USDC, USDT, and DAI transfers on Ethereum, adding symbols

name: stablecoin-transfers
version: 1
resource_size: s
apiVersion: 3
sources:
  stablecoin_transfers:
    dataset_name: ethereum.erc20_transfers
    version: 1.2.0
    type: dataset
    start_at: latest
    filter: >-
        address in
        ('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48',
        '0xdac17f958d2ee523a2206206994597c13d831ec7', 
        '0x6b175474e89094c44da98b954eedeac495271d0f')
transforms:
  stablecoin_transfers_with_symbol:
    type: sql
    sql: >-
      select id, sender, recipient, amount, transaction_hash,
      block_hash, block_number, block_timestamp,
      transaction_index, log_index, address,
      case
        when address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
        then 'USDC'
        when address = '0xdac17f958d2ee523a2206206994597c13d831ec7'
        then 'USDT'
        when address = '0x6b175474e89094c44da98b954eedeac495271d0f'
        then 'DAI'
      end as symbol
    primary_key: id
sinks:
  postgres_sink:
    type: postgres
    secret_name: <YOUR_SECRET>
    from: stablecoin_transfers_with_symbol
    table: stablecoin_transfers
    schema: public
use_dedicated_ip: false

One stablecoin on many chains

Streaming the same stablecoin on multiple chains is similar to the example above, just using multiple sources, one for each chain. You’ll need to use a transformation to add a chain identifier for each source. You can use a separate transformation for each chain (recommended) or a single transformation with a union all to combine the sources.

Example pipeline for USDC transfers on Ethereum and Arbitrum

name: usdc-transfers
version: 1
resource_size: s
apiVersion: 3
sources:
  usdc_transfers_ethereum:
    dataset_name: ethereum.erc20_transfers
    version: 1.2.0
    type: dataset
    filter: address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
    start_at: latest
  usdc_transfers_arbitrum:
    dataset_name: arbitrum_one.erc20_transfers
    version: 1.2.0
    type: dataset
    filter: address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
    start_at: latest
transforms:
  usdc_transfers_ethereum:
    type: sql
    sql: >-
      select id, sender, recipient, amount, transaction_hash,
      block_hash, block_number, block_timestamp,
      transaction_index, log_index,
      'ethereum' as chain_name
    primary_key: id
  usdc_transfers_arbitrum:
    type: sql
    sql: >-
      select id, sender, recipient, amount, transaction_hash,
      block_hash, block_number, block_timestamp,
      transaction_index, log_index,
      'arbitrum' as chain_name
    primary_key: id
sinks:
  postgres_sink_ethereum:
    type: postgres
    secret_name: <YOUR_SECRET>
    from: usdc_transfers_ethereum
    table: usdc_transfers
    schema: public
  postgres_sink_arbitrum:
    type: postgres
    secret_name: <YOUR_SECRET>
    from: usdc_transfers_arbitrum
    table: usdc_transfers
    schema: public
use_dedicated_ip: false

In some cases, the contract address for the same stablecoin on different networks may be different, which case you may want to retain the address column in the sink for better inspection.

Many stablecoins on many chains

As the most complex case, this combines the learnings from all of the cases above into a single pipeline, including both an in () filter and case when logic in the transform to add a symbol column.

Example pipeline for USDC, USDT, and DAI transfers on Ethereum and Arbitrum

name: stablecoin-transfers
version: 1
resource_size: s
apiVersion: 3
sources:
  stablecoin_transfers_ethereum:
    dataset_name: ethereum.erc20_transfers
    version: 1.2.0
    type: dataset
    filter: >-
        address in
        ('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48',
        '0xdac17f958d2ee523a2206206994597c13d831ec7', 
        '0x6b175474e89094c44da98b954eedeac495271d0f')
    start_at: latest
  stablecoin_transfers_arbitrum:
    dataset_name: arbitrum_one.erc20_transfers
    version: 1.2.0
    type: dataset
    filter: >-
        address in
        ('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48',
        '0xdac17f958d2ee523a2206206994597c13d831ec7', 
        '0x6b175474e89094c44da98b954eedeac495271d0f')
    start_at: latest
transforms:
  stablecoin_transfers_ethereum:
    type: sql
    sql: >-
      select id, sender, recipient, amount, transaction_hash,
      block_hash, block_number, block_timestamp,
      transaction_index, log_index, address,
      'ethereum' as chain_name,
      case
        when address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
        then 'USDC'
        when address = '0xdac17f958d2ee523a2206206994597c13d831ec7'
        then 'USDT'
        when address = '0x6b175474e89094c44da98b954eedeac495271d0f'
        then 'DAI'
      end as symbol
    primary_key: id
  stablecoin_transfers_arbitrum:
    type: sql
    sql: >-
      select id, sender, recipient, amount, transaction_hash,
      block_hash, block_number, block_timestamp,
      transaction_index, log_index, address,
      'arbitrum' as chain_name,
      case
        when address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
        then 'USDC'
        when address = '0xdac17f958d2ee523a2206206994597c13d831ec7'
        then 'USDT'
        when address = '0x6b175474e89094c44da98b954eedeac495271d0f'
        then 'DAI'
      end as symbol
    primary_key: id
sinks:
  postgres_sink_ethereum:
    type: postgres
    secret_name: <YOUR_SECRET>
    from: stablecoin_transfers_ethereum
    table: stablecoin_transfers
    schema: public
  postgres_sink_arbitrum:
    type: postgres
    secret_name: <YOUR_SECRET>
    from: stablecoin_transfers_arbitrum
    table: stablecoin_transfers
    schema: public
use_dedicated_ip: false

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

Mirror

Guides

Reference

What you’ll need

Examples

One stablecoin on one chain

Via YAML

Via Web UI

Many stablecoins on one chain

One stablecoin on many chains

Many stablecoins on many chains

​What you’ll need

​Examples

​One stablecoin on one chain

​Via YAML

​Via Web UI

​Many stablecoins on one chain

​One stablecoin on many chains

​Many stablecoins on many chains

What you’ll need

Examples

One stablecoin on one chain

Via YAML

Via Web UI

Many stablecoins on one chain

One stablecoin on many chains

Many stablecoins on many chains