LI.FITX Hash fetch (pipeline for Snowflake)
    Updated 2025-07-02
    WITH init AS (
    SELECT tx_hash, event_name, chain_id, block_timestamp, block_timestamp::date as date
    FROM (
    SELECT tx_hash, event_name,block_timestamp, '1' AS chain_id FROM ethereum.core.ez_decoded_event_logs
    UNION ALL
    SELECT tx_hash, event_name,block_timestamp, '137' AS chain_id FROM polygon.core.ez_decoded_event_logs
    UNION ALL
    SELECT tx_hash, event_name,block_timestamp, '56' AS chain_id FROM bsc.core.ez_decoded_event_logs
    UNION ALL
    SELECT tx_hash, event_name,block_timestamp, '8453' AS chain_id FROM base.core.ez_decoded_event_logs
    UNION ALL
    SELECT tx_hash, event_name,block_timestamp, '42161' AS chain_id FROM arbitrum.core.ez_decoded_event_logs
    UNION ALL
    SELECT tx_hash, event_name,block_timestamp, '10' AS chain_id FROM optimism.core.ez_decoded_event_logs
    UNION ALL
    SELECT tx_hash, event_name,block_timestamp, '43114' AS chain_id FROM avalanche.core.ez_decoded_event_logs
    UNION ALL
    SELECT tx_hash, event_name,block_timestamp, '100' AS chain_id FROM gnosis.core.ez_decoded_event_logs
    )
    WHERE event_name in ('LiFiTransferStarted', 'LiFiAcrossTransfer', 'LiFiCBridgeTransfer', 'LiFiHopTransfer', 'LiFiAmarokTransfer', 'LiFiGenericSwapCompleted')
    AND date >= '2025-07-02'
    AND date < '2025-07-03'
    )


    SELECT block_timestamp as block_time, tx_hash, chain_id
    FROM init
    -- where event_name != 'LiFiGenericSwapCompleted'
    group by 1,2,3
    Last run: 10 days ago
    BLOCK_TIME
    TX_HASH
    CHAIN_ID
    1
    2025-07-01 07:47:59.0000x58f5a1f8eac520d09626268cfe68c8f848f71d7cc981eb9efd0964137ff1aad71
    2
    2025-07-01 22:02:11.0000x29682502bd27c224b109b26194a2ff20d979257470c999797dc444210b78ede11
    3
    2025-07-01 11:21:08.0000x1a03f36c52df68abd0e19928cbf0299a509d38a0ea75b8e43e9fb5b09a77aa9f56
    4
    2025-07-01 13:29:19.0000x938a8d3601920d009283ea9ca72c7cf6945fc2c7fdaaf2a7b9794cfaa8f5e7e756
    5
    2025-07-01 21:54:47.0000xf9cc8e6bb4a1c98652ccf0419e317c812b4779e32c46479bafb9f40779e0b15f8453
    6
    2025-07-01 02:46:09.0000xadab3b37baa5ee6c9b2e870c5d534b7843f6f282634c454731b1e84c2dae1f3d8453
    7
    2025-07-01 10:01:41.0000xb674bd16a6d6d94c55099258eeaf137019282164b7e9de37d430a779e25495fe8453
    8
    2025-07-01 18:03:21.0000x0649003f7a148e9d7631d879202c0dd50811a1a908199f185c29c7c9dfa45a0b8453
    9
    2025-07-01 17:52:01.0000x3d39f570e9f7b8107b650802840b92cdb8a5a92e47139b6799fe7afc9563fc528453
    10
    2025-07-01 13:24:17.0000xf1cf6da62dbc5161cc0d2d4ab8a9c1e3734b35677783c056c640666ecfb9205c8453
    11
    2025-07-01 12:47:07.0000x40e036d141d796d8f8b2813f3636562aa99fcefb21ecac115ff484e895d244a08453
    12
    2025-07-01 18:08:55.0000x16513850e391789ac6c48ae68916c9d42280aaecffbc44e52d92e9592f47ec8e8453
    13
    2025-07-01 11:34:45.0000x0acc1cf1dffdf0be6834a34b8336a8dc0d5e88bceda797af404939c17193108a8453
    14
    2025-07-01 09:40:16.0000x64e4d086ee750fac44a452136c298f6ac7f63ed2c256cda286af641a0562ce9542161
    15
    2025-07-01 10:24:33.0000x025db4e8d42460b1661f6d64707e16636305136362c88ac6ddc47f2dc4b0947642161
    16
    2025-07-01 12:48:17.0000xa798d6688598da9a6fb0ac482dafbbccd4ec547c48a123e50ee94839d32b774410
    17
    2025-07-01 17:30:27.0000xabb5c31ff80082af25e4e38873a1dc75bcecf69d7e7f163924e0469159e149ce8453
    18
    2025-07-01 07:17:01.0000x4e140b7004a2f7c915d634604fa168ec9d7be93139af5111e14e2fe8fa56e9b610
    19
    2025-07-01 10:48:07.0000x2a4bd50ac6722bdf09530fb3798f420f0f14b9dbb0c6e5ffe303b0664d04f75510
    20
    2025-07-01 15:06:47.0000xd842bfd9f6a35130df092b3f0c8adde1f26286d36f2967feedbb5011f94f58671
    ...
    45313
    4MB
    21s