Fact Event Logs

-- Find all ERC-20 Transfer events in last 24 hours
SELECT
    block_timestamp,
    tx_hash,
    contract_address,
    topics[1] AS from_address_padded,
    topics[2] AS to_address_padded,
    data AS amount_hex,
    event_index
FROM <blockchain_name>.core.fact_event_logs
WHERE topic_0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
    AND ARRAY_SIZE(topics) = 3  -- ERC-20 has 3 topics
    AND block_timestamp >= CURRENT_DATE - 1
LIMIT 100;

-- Most active contracts by event count
SELECT
    contract_address,
    COUNT(*) AS event_count,
    COUNT(DISTINCT tx_hash) AS unique_txs,
    COUNT(DISTINCT topic_0) AS unique_event_types,
    MIN(block_timestamp) AS first_seen,
    MAX(block_timestamp) AS last_seen
FROM <blockchain_name>.core.fact_event_logs
WHERE block_timestamp >= CURRENT_DATE - 7
GROUP BY 1
ORDER BY 2 DESC
LIMIT 50;

-- Event patterns within transactions
SELECT
    tx_hash,
    COUNT(*) AS events_in_tx,
    COUNT(DISTINCT contract_address) AS contracts_touched,
    ARRAY_AGG(DISTINCT topic_0) AS event_signatures
FROM <blockchain_name>.core.fact_event_logs
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1
HAVING COUNT(*) > 10
ORDER BY 2 DESC
LIMIT 20;

-- 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)

-- 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;
``` |