Missing Blocks / Incremental Logic Issues

[austinFlipside]

There are thousands of missing blocks in the blocks table, please see my query below to confirm. I think there are two issues:

1. Chainwalkers is missing some blocks
2. Incremental loads are not set up properly to handle any blocks that ingested after block time
   • for example, look at block_id = 22647499
   • this block exists in chainwalkers, but was ingested 20 days after its block timestamp
   • this block does not exist in the blocks table
   • incremental logic loads off block_timestamp, not ingested_at
   • i recommend changing the incremental load filter to ingested_at in appropriate stg_ models
   • most of the time, newly ingested blocks will be new blocks, but by using ingested_at instead of block timestamp for the incremental, you can also pick up any missed blocks recently replayed and fill gaps

Gap test:

WITH source AS (
    SELECT

        BLOCK_ID,
        LAG(
            BLOCK_ID,
            1
        ) over (

            ORDER BY
                BLOCK_ID ASC
        ) AS prev_BLOCK_ID
    FROM
        (select distinct block_id from 
         mdao_harmony.prod.blocks
          where BLOCK_TIMESTAMP < CURRENT_DATE-1) 
)
SELECT

    prev_BLOCK_ID,
    BLOCK_ID,
    BLOCK_ID - prev_BLOCK_ID
    - 1 AS gap
FROM
    source
WHERE
    BLOCK_ID - prev_BLOCK_ID <> 1
ORDER BY
    prev_block_id DESC```