Why some addresses not returning eth balance from BigQuery

Viewed 57

I've been playing around with BigQuery's Ethereum ETL. There's a table specifically for mapping wallet addresses to eth balances in gwei. Lots of addresses work just fine, but in the example below you'll find one famous wallet (Justin Beiber) that definitely has eth, but doesn't appear in the table

Anyone know if there are reasons for this like wallet age, or if it's just gaps in data in BigQuery? I'm happy to use other services to pull eth info for the missing addresses, but of course ideally I can get 100% from this source

function main() {

    // Import the Google Cloud client library
    const {BigQuery} = require('@google-cloud/bigquery');

    async function getWalletBalances() {
        // Create a client
        const bigqueryClient = new BigQuery();

        // The SQL query to run
        const sqlQuery = `SELECT eth_balance
            FROM \`bigquery-public-data.crypto_ethereum.balances\`
            WHERE address = '0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6'`;

        console.log(sqlQuery);
        const options = {
        query: 
            sqlQuery,
            // Location must match that of the dataset(s) referenced in the query.
            location: 'US',
            params: {
            },
        };

        // Run the query
        const [rows] = await bigqueryClient.query(options);

        console.log('Rows:');
        rows.forEach(row => console.log(row));
    }

    getWalletBalances();
  }

main();
2 Answers

It appears some addresses do not appear in the balances tables correctly. It may be due to an issue with addresses which have polygon and eth transactions. I have seen other examples of this phenomena.

However, wrapping the address condition in a substr- does get the correct balance. Unclear exactly why. If anyone has the answer it would be useful.

SELECT * FROM `bigquery-public-data.crypto_ethereum.balances` WHERE contains_substr(`address`, '0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6')
Related