Ez Decoded Event Logs

SELECT
    block_timestamp,
    tx_hash,
    contract_address,
    contract_name,
    event_name,
    decoded_log:from::string AS from_address,
    decoded_log:to::string AS to_address,
    decoded_log:value::numeric AS amount,
    -- Convert to decimal (assuming 18 decimals)
    decoded_log:value::numeric / POW(10, 18) AS amount_decimal
FROM <blockchain_name>.core.ez_decoded_event_logs
WHERE contract_address = LOWER('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48') -- USDC
    AND event_name = 'Transfer'
    AND block_timestamp >= CURRENT_DATE - 7
    AND decoded_log:value::numeric > 1000000000 -- Over 1000 USDC
ORDER BY block_timestamp DESC;

SELECT
    block_timestamp,
    tx_hash,
    contract_address AS pool_address,
    event_name,
    decoded_log:sender::string AS sender,
    decoded_log:recipient::string AS recipient,
    decoded_log:amount0::numeric AS amount0,
    decoded_log:amount1::numeric AS amount1,
    decoded_log:sqrtPriceX96::numeric AS sqrt_price,
    decoded_log:liquidity::numeric AS liquidity,
    decoded_log:tick::integer AS tick
FROM <blockchain_name>.core.ez_decoded_event_logs
WHERE event_name = 'Swap'
    AND contract_address IN (
        SELECT address FROM dim_contracts
        WHERE contract_name ILIKE '%Uniswap V3%'
    )
    AND block_timestamp >= CURRENT_DATE - 1
LIMIT 100;

SELECT
    block_timestamp,
    tx_hash,
    contract_address,
    contract_name AS collection_name,
    decoded_log:from::string AS from_address,
    decoded_log:to::string AS to_address,
    decoded_log:tokenId::string AS token_id
FROM <blockchain_name>.core.ez_decoded_event_logs
WHERE event_name = 'Transfer'
    AND decoded_log:tokenId IS NOT NULL  -- Indicates ERC-721
    AND block_timestamp >= CURRENT_DATE - 1
ORDER BY block_timestamp DESC;

SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    event_name,
    COUNT(*) AS event_count,
    COUNT(DISTINCT decoded_log:minter::string) AS unique_users
FROM <blockchain_name>.core.ez_decoded_event_logs
WHERE contract_name ILIKE '%compound%'
    AND event_name IN ('Mint', 'Redeem', 'Borrow', 'RepayBorrow')
    AND block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

SELECT
    block_timestamp,
    tx_hash,
    event_name,
    decoded_log,
    ARRAY_SIZE(decoded_log:path) AS swap_hops,
    decoded_log:amountIn::numeric AS amount_in,
    decoded_log:amountOutMin::numeric AS min_amount_out
FROM <blockchain_name>.core.ez_decoded_event_logs
WHERE contract_address = LOWER('0x1111111254fb6c44bAC0beD2854e76F90643097d') -- 1inch
    AND event_name ILIKE '%swap%'
    AND block_timestamp >= CURRENT_DATE - 1;

-- Recent data
WHERE block_number >= (SELECT MAX(block_number) - 1000 FROM fact_blocks)

-- Historical analysis
WHERE block_number BETWEEN 15000000 AND 16000000

-- Join across tables
JOIN <blockchain_name>.core.fact_event_logs USING (block_number)

-- Time-based filtering (most efficient)
WHERE block_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP)

-- Hourly aggregations
DATE_TRUNC('hour', block_timestamp) AS hour

-- UTC date extraction
DATE(block_timestamp) AS block_date

-- Trace event execution flow
SELECT
    event_index,
    contract_address,
    topic_0,
    SUBSTRING(data, 1, 10) AS data_preview
FROM <blockchain_name>.core.fact_event_logs
WHERE tx_hash = '0xabc...'
ORDER BY event_index;
``` |
| CONTRACT_ADDRESS | TEXT | Smart contract address that emitted this event or received the transaction.

**Key Points**:
- Always the immediate event emitter for logs
- May differ from transaction to_address
- Lowercase normalized format
- Never NULL for valid events |
| TOPICS | VARIANT | Array containing all indexed parameters of the event.

Example: ['0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef', '0x0000000000000000000000001234567890123456789012345678901234567890'] |
| TOPIC_0 | TEXT | Event signature hash - keccak256 of the event declaration.

Example: '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' |
| TOPIC_1 | TEXT | First indexed parameter of the event (if exists).

Example: '0x0000000000000000000000001234567890123456789012345678901234567890' |
| TOPIC_2 | TEXT | Second indexed parameter of the event (if exists).

Example: '0x000000000000000000000000abcdefabcdefabcdefabcdefabcdefabcdefabcd' |
| TOPIC_3 | TEXT | Third indexed parameter of the event (if exists).

Example: '0x0000000000000000000000000000000000000000000000000000000000000001' |
| DATA | TEXT | Hex-encoded non-indexed event parameters.

Example: '0x0000000000000000000000000000000000000000000000000de0b6b3a7640000' |
| EVENT_REMOVED | BOOLEAN | Boolean flag indicating if the event was removed due to chain reorganization.

Example: false |
| ORIGIN_FROM_ADDRESS | TEXT | The externally-owned account (EOA) or contract address that initiated the transaction.

**Key Points**:
- Always 42 characters (0x + 40 hex chars)
- Lowercase normalized in all tables
- Cannot be NULL for valid transactions
- For contract creation: sender of creation transaction

**Common Patterns**:
- EOA → EOA: Simple transfer
- EOA → Contract: User interaction
- Contract → Contract: Internal calls (see fact_traces)
- Known addresses: Exchange hot wallets, protocol deployers

**Query Examples**:
```sql
-- User activity analysis
SELECT from_address, COUNT(*) as tx_count
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 2 DESC;

-- New user detection
SELECT DISTINCT from_address
FROM <blockchain_name>.core.fact_transactions t1
WHERE NOT EXISTS (
    SELECT 1 FROM <blockchain_name>.core.fact_transactions t2
    WHERE t2.from_address = t1.from_address
    AND t2.block_number < t1.block_number
);
``` |
| ORIGIN_TO_ADDRESS | TEXT | The destination address for the transaction - either an EOA or contract address.

**Special Cases**:
- NULL: Contract creation transaction
- Contract address: Interacting with smart contract
- EOA address: Simple transfer or receiving funds

**Important Patterns**:
```sql
-- Contract deployments
WHERE to_address IS NULL

-- Popular contracts
SELECT to_address, COUNT(*) as interactions
FROM <blockchain_name>.core.fact_transactions
WHERE to_address IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

-- Direct transfers only
WHERE to_address NOT IN (SELECT address FROM dim_contracts)

-- Find all event types for a contract
SELECT DISTINCT event_name, COUNT(*) as occurrences
FROM ez_decoded_event_logs
WHERE contract_address = LOWER('0x...')
GROUP BY 1
ORDER BY 2 DESC;
``` |
| FULL_DECODED_LOG | VARIANT | Complete decoded event data including parameter names, values, types, and metadata.

Example: '{"event_name": "Transfer", "parameters": [{"name": "from", "type": "address", "value": "0x123...", "indexed": true}]}' |
| DECODED_LOG | OBJECT | Flattened JSON object containing decoded event parameters with their values.

Example: '{"from": "0x123...", "to": "0x456...", "value": "1000000000000000000"}' |
| CONTRACT_NAME | TEXT | Human-readable name of the smart contract emitting the event, joined from dim_contracts.

Example: 'USD Coin' |
| EZ_DECODED_EVENT_LOGS_ID | TEXT | Primary key - unique identifier for each row ensuring data integrity.

**Format**: Usually VARCHAR containing composite key generated using MD5 hash of the relevant columns.
**Example**: MD5(block_number, tx_hash, trace_index)

**Usage**:
- Deduplication in incremental loads
- Join operations for data quality checks
- Troubleshooting specific records

**Important**: Implementation varies by table - check table-specific documentation. |
| INSERTED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp when the record was first added to the Flipside database.

**Format**: TIMESTAMP_NTZ

**Use Cases**:
- Data freshness monitoring
- Incremental processing markers
- Debugging data pipeline issues
- SLA tracking

**Query Example**:
```sql
-- Check data latency
SELECT
    DATE_TRUNC('hour', block_timestamp) as block_hour,
    DATE_TRUNC('hour', inserted_timestamp) as insert_hour,
    AVG(TIMESTAMPDIFF('minute', block_timestamp, inserted_timestamp)) as avg_latency_minutes
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2;
``` |
| MODIFIED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp of the most recent update to this record.

**Format**: TIMESTAMP_NTZ

**Triggers for Updates**:
- Data corrections
- Enrichment additions
- Reprocessing for accuracy
- Schema migrations

**Monitoring Usage**:
```sql
-- Recently modified records
SELECT *
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
AND modified_timestamp >= CURRENT_DATE - 1;

-- Data quality tracking
SELECT
    DATE(modified_timestamp) as mod_date,
    COUNT(*) as records_updated,
    COUNT(DISTINCT block_number) as blocks_affected
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
GROUP BY 1
ORDER BY 1 DESC;
``` |