Spider 2.0 Benchmark

repository·main·Indexed 21 days ago

https://github.com/xlang-ai/spider2

A benchmark for evaluating Large Language Models (LLMs) on complex, real-world enterprise text-to-SQL workflows. It features large-scale data environments, multiple SQL dialects including BigQuery, Snowflake, and SQLite, and repository-level tasks using DBT. The project includes three task settings: Spider 2.0-Snow, Spider 2.0-Lite, and Spider 2.0-DBT, along with agent baselines such as Spider-Agent and Spider-Agent-Lite.

Tokens
177.1K
Snippets
454
Records
578
Agent score
69%

What's inside Spider 2.0

  1. Overview of Spider 2.0 Benchmark Settings

    main

    Spider 2.0 provides three distinct task settings for evaluating Large Language Models (LLMs) on enterprise-level text-to-SQL workflows. Choose a setting based on your task type, database requirements, and budget:

    SettingTask Type# ExamplesDatabasesCost
    Spider 2.0-SnowText-to-SQL task547Snowflake (547)NO COST!
    Spider 2.0-LiteText-to-SQL task547BigQuery (214), Snowflake (198), SQLite (135)Some cost incurred
    Spider 2.0-DBTCode agent task68DuckDB (DBT) (68)NO COST!

    Note that for Spider 2.0-Lite and Spider 2.0-Snow, the project provides ground-truth tables to facilitate quick benchmarking. When using these, you must indicate that you are using oracle tables.

  2. Understand the Spider 2.0-Lite Resource components

    main

    The Spider 2.0-Lite Resource provides the necessary context and connectivity for evaluating Text-to-SQL workflows. It is composed of three primary components:

    1. databases: Contains schema metadata for the various database environments used in Spider 2.0, including BigQuery, Local, and Snowflake.
    2. documentation: Provides official documentation for function descriptions and task-specific external knowledge required for reasoning.
    3. interface: Provides the programmatic interfaces required to execute queries against both cloud (BigQuery, Snowflake) and local databases.
  3. Data Requirements for Ethereum Blockchain Analysis

    main

    When performing Ethereum blockchain data analysis for Spider 2.0, the following analytical objectives and data requirements must be met. The analysis focuses on address activity, balances, token transactions, general transaction patterns, mining rewards, contract creation, and failure/bytecode analysis up to January 1, 2017.

    Core Analytical Domains

    1. Address Activity

    • Objective: Analyze activity patterns for each address.
    • Consistency Metric (R_active_hour): For addresses with >24 activities, calculate hourly consistency using the root mean square of the sum of cosines and sines of transaction hours, normalized by transaction count.
    • Active Days (active_days): Total count of days with activity.

    2. Balance Analysis

    • Objective: Compute net balance.
    • Methodology: Use successful transactions only. Exclude specific Ethereum call types (delegatecall, callcode, staticcall) unless they are null.
    • Calculation: (Tokens Received - Tokens Sent) - Transaction Fees.
    • Scaling: Convert from Wei to Ether by dividing by $10^{18}$.

    3. Token & General Transaction Analysis

    • Token Metrics: Track incoming/outgoing transaction counts (token_in_tnx, token_out_tnx), token types (token_in_type, token_out_type), and distinct counterparties (token_from_addr, token_to_addr).
    • General Metrics: Track trace counts (in_trace_count, out_trace_count), unique addresses (in_addr_count, out_addr_count), and non-zero value transfers (in_transfer_count, out_transfer_count).
    • Gas Analysis: For incoming "call" type transactions, compute average gas used (avg_gas_used) and standard deviation (std_gas_used).

    4. Mining, Contracts, and Failures

    • Mining: Calculate reward_amount (scaled by $10^{18}$) excluding transaction fees.
    • Contracts: Count contract_create_count and measure bytecode_size (length of bytecode).
    • Failures: Count failure_count (failed transactions initiated by the address).
  4. Understand Lap Types in Spider 2.0

    main

    In the Spider 2.0 dataset, laps are categorized into specific types to describe the context of a driver's performance or status during a race. When querying or analyzing race data, you can use these types to distinguish between active racing, starting position changes, and various forms of retirement.

    Race Laps

    • Race: Laps completed during the actual race period by participating drivers.

    Starting Position Types

    These types describe the relationship between a driver's qualifying position and their actual starting position on the grid:

    • Starting Position - Grid Increase: The driver started in a better position than they qualified.
    • Starting Position - Qualifying: The driver started in the exact position they achieved during qualifying.
    • Starting Position - Grid Drop: The driver started in a worse position than they qualified (e.g., due to penalties).

    Retirement Types

    These types indicate why a driver failed to complete the race:

    • Retirement (Driver Error): Exit due to driver mistakes, such as crashes or loss of control.
    • Retirement (Mechanical Problem): Exit due to technical or engine failures.
    • Retirement (Disqualification): Exit due to rule violations or regulatory exclusion.
  5. Calculate CNV and Cytoband Overlaps

    main

    When localizing Copy Number Variations (CNVs) within specific cytobands, use the following intersection formula to determine the length of the overlap. This ensures the measurement represents the actual intersected length of the CNV segment and the cytoband segment, preventing negative values when no overlap exists.

    Formula: Overlap = max(0, min(end_pos, hg38_stop) - max(start_pos, hg38_start))

    Parameters:

    • end_pos: The end position of the CNV segment.
    • hg38_stop: The stop position of the cytoband (hg38).
    • start_pos: The start position of the CNV segment.
    • hg38_start: The start position of the cytoband (hg38).
    Overlap = max(0, min(end_pos, hg38_stop) - max(start_pos, hg38_start))
  6. Understand the Sales-to-CMA Ratio metric

    main

    The Sales-to-CMA Ratio is a metric used to identify significant deviations in sales performance. It compares actual monthly sales against a Centered Moving Average (CMA).

    • CMA Calculation: A smoothed value calculated using two overlapping 12-month windows (5 months before/6 months after and 6 months before/5 months after).
    • Ratio Calculation: Actual Sales / CMA.
    • Interpretation: A ratio > 2 indicates that actual sales are more than twice the smoothed average, signaling significantly higher-than-average sales for that period.
  7. Understand the Overlap Ratio calculation for bank location data

    main

    The Spider 2.0 dataset uses an Overlap Ratio method to estimate the number of bank institutions per ZIP code. This method resolves the spatial mismatch between ZIP code boundaries and Census Block Groups through a three-step process:

    1. Geospatial Intersection: Calculate the area of intersection between a ZIP Code Area geometry and a Census Block Group geometry. The overlap ratio is defined as: $$ ext{overlap ratio} = \frac{\text{Intersection Area (ZIP $\cap$ Block Group)}}{\text{Total Area (Block Group)}}$$
    2. Bank Distribution: Distribute bank locations to Census Block Groups based on this ratio. The number of bank locations in a block group is proportionally assigned based on how much of that block group falls within a specific ZIP code.
    3. Aggregation: Aggregate the distributed values by ZIP code to determine the final count of bank institutions per postal code area.
  8. Understand the Timberland Acres data model

    main

    The Timberland Acres data model is used to analyze forest plots based on productivity and current status. It includes specific attributes for identification, regional analysis, and temporal tracking, as well as conditional area calculations.

    Key Attributes

    • plot_sequence_number: Unique identifier for each plot.
    • evaluation_type: Must be EXPCURR for current evaluations.
    • evaluation_group: Classification for organizing data by evaluation cycles.
    • evaluation_description: Details on the purpose/scope of the evaluation.
    • plot_state_code_name (state_name): The name of the state for regional reporting.
    • state_code: Numerical or textual code for filtering/aggregation.
    • inventory_year: The year of data collection.

    Area Calculations

    Area is calculated conditionally based on the proportion basis:

    • macroplot_acres: Used when proportion basis is 'MACR' and adjustment_factor_for_the_macroplot > 0.
      • Formula: p.expansion_factor * c.condition_proportion_unadjusted * p.adjustment_factor_for_the_macroplot
    • subplot_acres: Used when proportion basis is 'SUBP' and adjustment_factor_for_the_subplot > 0.
      • Formula: p.expansion_factor * c.condition_proportion_unadjusted * p.adjustment_factor_for_the_subplot

    Required Filter Conditions

    To ensure valid analysis, apply these filters:

    • condition_status_code = 1 (active conditions only)
    • reserved_status_code = 0 (exclude reserved plots)
    • site_productivity_class_code must be between 1 and 6.
  9. How bank institution density per ZIP code is calculated

    main

    Spider 2.0 uses a geospatial overlap method to estimate the number of bank institutions within specific postal code areas (ZIP codes). Because ZIP code boundaries and census block groups do not align perfectly, the system uses an overlap ratio to distribute bank location data accurately.

    The calculation follows three main steps:

    1. Geospatial Intersection: Calculate the area of intersection between ZIP Code Areas and Census Block Groups. The overlap ratio is defined as: $$\text{overlap ratio} = \frac{\text{Area of Intersection (ZIP $\cap$ Block Group)}}{\text{Total Area of Block Group}}$$

    2. Bank Location Distribution: Distribute the number of bank locations to census block groups based on the calculated overlap ratio. This ensures that bank institutions are proportionally assigned to the block groups that fall within a specific ZIP code.

    3. Aggregation: Aggregate the distributed values by ZIP code to determine the total number of bank institutions per postal code area.

  10. Use fivetran_utils cross-database compatibility macros

    main

    The fivetran_utils dbt package provides a suite of macros designed to enable cross-database compatibility for common SQL functions. These macros allow your dbt models to run seamlessly across different data warehouses such as BigQuery, Snowflake, Redshift, Postgres, and Databricks by abstracting away the specific syntax required by each engine.

    Key functional areas covered by these macros include:

    • Aggregations: array_agg, string_agg, max_bool, percentile
    • JSON Handling: json_extract, json_parse, pivot_json_extract
    • Date/Time Operations: timestamp_add, timestamp_diff
    • Type Conversion & Math: try_cast, ceiling
    • String & URL Manipulation: extract_url_parameter, wrap_in_quotes
    • Window Functions: first_value
  11. How CrossDomainEnabled enables interlayer messaging

    main

    The CrossDomainEnabled contract is a helper used by both L1 and L2 bridges to facilitate communication between layers. It relies on a messenger (an ICrossDomainMessenger) to send and receive messages.

    Security Model

    To prevent unauthorized messages from being processed, the contract uses the onlyFromCrossDomainAccount modifier. This modifier ensures that:

    1. The msg.sender is the authenticated messenger contract.
    2. The xDomainMessageSender() (the account that initiated the message on the other domain) matches the expected _sourceDomainAccount (e.g., the bridge contract on the other side).

    Sending Messages

    Use the sendCrossDomainMessage function to dispatch data to a target address on the other domain. This is typically used to trigger functions on the destination layer via the cross-domain messenger.

    contract CrossDomainEnabled {
        address public messenger;
    
        constructor(address _messenger) {
            messenger = _messenger;
        }
    
        modifier onlyFromCrossDomainAccount(address _sourceDomainAccount) {
            require(msg.sender == address(getCrossDomainMessenger()), "OVM_XCHAIN: messenger contract unauthenticated");
            require(getCrossDomainMessenger().xDomainMessageSender() == _sourceDomainAccount, "OVM_XCHAIN: wrong sender of cross-domain message");
            _;
        }
    
        function sendCrossDomainMessage(
            address _crossDomainTarget,
            uint32 _gasLimit,
            bytes memory _message
        ) internal {
            getCrossDomainMessenger().sendMessage(_crossDomainTarget, _message, _gasLimit);
        }
    }
  12. Quantify token and general transaction metrics

    main

    Perform detailed transaction analysis using the following metrics:

    Token Transactions

    • token_in_tnx / token_out_tnx: Count of incoming and outgoing token transactions.
    • token_in_type / token_out_type: Number of distinct token types involved.
    • token_from_addr / token_to_addr: Count of distinct counterparty addresses.

    General Transactions

    • in_trace_count / out_trace_count: Total number of incoming and outgoing transactions.
    • in_addr_count / out_addr_count: Number of unique incoming and outgoing addresses.
    • in_transfer_count / out_transfer_count: Count of incoming/outgoing transactions with non-zero values.
    • in_avg_amount / out_avg_amount: Average transaction amount, scaled by $10^{18}$ (Wei to Ether).
    • Gas Analysis: For incoming "call" type transactions, calculate avg_gas_used and std_gas_used (standard deviation).