Fact Traces

-- Analyze internal ETH transfers
SELECT
    block_timestamp,
    tx_hash,
    trace_index,
    type,
    from_address,
    to_address,
    value,
    gas_used,
    trace_succeeded
FROM <blockchain_name>.core.fact_traces
WHERE value > 0
    AND type = 'CALL'
    AND trace_succeeded
    AND block_timestamp >= CURRENT_DATE - 1
ORDER BY value DESC
LIMIT 100;

-- Find failed internal transactions with reasons
SELECT
    tx_hash,
    trace_index,
    from_address,
    to_address,
    type,
    error_reason,
    revert_reason,
    gas,
    gas_used
FROM <blockchain_name>.core.fact_traces
WHERE NOT trace_succeeded
    AND block_timestamp >= CURRENT_DATE - 1
    AND error_reason IS NOT NULL
LIMIT 50;

-- Trace execution depth analysis
SELECT
    tx_hash,
    MAX(ARRAY_SIZE(trace_address)) AS max_depth,
    COUNT(*) AS total_traces,
    SUM(CASE WHEN trace_succeeded THEN 0 ELSE 1 END) AS failed_traces,
    SUM(value) AS total_value_transferred
FROM <blockchain_name>.core.fact_traces
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1
HAVING MAX(ARRAY_SIZE(trace_address)) > 3
ORDER BY 2 DESC;

-- Contract interaction patterns
WITH contract_calls AS (
    SELECT
        from_address AS caller,
        to_address AS callee,
        COUNT(*) AS call_count,
        SUM(value) AS total_value
    FROM <blockchain_name>.core.fact_traces
    WHERE type IN ('CALL', 'DELEGATECALL')
        AND block_timestamp >= CURRENT_DATE - 7
    GROUP BY 1, 2
)
SELECT * FROM contract_calls
WHERE call_count > 100
ORDER BY call_count DESC;

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

-- Daily native token transfer volume
SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    SUM(value) AS total_transferred,
    COUNT(*) AS transfer_count
FROM <blockchain_name>.core.fact_transactions
WHERE value > 0 AND tx_succeeded
GROUP BY 1;
``` |
| VALUE_PRECISE_RAW | TEXT | String representation of numeric values preserving exact precision without any adjustments.

**Format**: VARCHAR containing numeric string
**Purpose**: Prevents floating-point precision loss due to snowflake limitations
**Contains**: Raw blockchain values (usually in smallest unit)

**Example Values**:
- "1000000000000000000" = 1 ETH in Wei
- "50000000" = 50 USDC (6 decimals)

**Usage**:
```sql
-- Exact value comparisons
WHERE value_precise_raw = '1000000000000000000'

-- Conversion with precision
CAST(value_precise_raw AS NUMERIC(38,0)) / POW(10, 18) AS value_decimal
``` |
| VALUE_PRECISE | TEXT | String representation of numeric values adjusted for human readability while maintaining precision.

**Format**: VARCHAR containing decimal string
**Adjustments**: Converted from smallest unit to standard unit
**Purpose**: Human-readable values without precision loss

**Example Values**:
- "1.0" = 1 ETH (converted from Wei)
- "50.0" = 50 USDC (converted from 6 decimal places)

**Best Practices**:
```sql
-- Safe numeric operations
CAST(value_precise AS NUMERIC(38,18))

-- Filtering large values
WHERE CAST(value_precise AS NUMERIC(38,18)) > 1000

-- Aggregations
SUM(CAST(value_precise AS NUMERIC(38,18))) AS total_value
``` |
| VALUE_HEX | TEXT | Hexadecimal representation of transaction values as provided by the blockchain RPC.

**Format**: 0x-prefixed hex string
**Example**: "0xde0b6b3a7640000" = 1 ETH in Wei

**Use Cases**:
- Debugging RPC responses
- Verifying data transformations
- Handling special encoding cases

**Conversion Example**:
- Hex to decimal (conceptual - use built-in conversions)
- 0xde0b6b3a7640000 = 1000000000000000000 Wei = 1 ETH

**Note**: Most queries should use value or value_precise fields instead. |
| GAS | NUMBER | Gas allocated to this specific trace execution.

Example: 250000 |
| GAS_USED | NUMBER | Actual gas consumed by this trace execution.

Example: 125673 |
| 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;
``` |