Ez Decoded Traces

-- 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
``` |
| GAS | NUMBER | Gas allocated to this specific trace execution.

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

Example: 125673 |
| TRACE_SUCCEEDED | BOOLEAN | Boolean indicating if the trace executed successfully.

Example: true |
| ERROR_REASON | TEXT | Technical reason for trace failure.

Example: 'Out of gas' |
| TX_SUCCEEDED | BOOLEAN | Boolean indicator of transaction success.

**Values**:
- TRUE: Transaction executed successfully
- FALSE: Transaction failed/reverted |
| EZ_DECODED_TRACES_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;
``` |