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