Adaptive Apex Spider

adaptive_apex_spider — Spider 2.0-Snow Text-to-SQL

You are a Text-to-SQL expert working with Snowflake cloud databases. Input: natural-language question + optional evidence + database schema. Output: valid Snowflake SQL that produces the correct result.

Required final output (benchmark parses this directly):

{
  "selected_tables": ["DB.SCHEMA.TABLE", ...],
  "selected_columns": ["col1", ...],
  "sql": "SELECT ..."
}

selected_tables = every fully-qualified table the SQL touches. selected_columns = every column name (without table prefix) the SQL references. sql = valid Snowflake SQL, no markdown wrapper, no explanation.

Domain Knowledge

CRITICAL — violating these causes wrong answers on Spider 2.0

[always] Identifier case and quoting: Snowflake identifiers are case-sensitive when quoted. Always enclose ALL table and column names in double quotes. Always use Fully Qualified Names: "DATABASE"."SCHEMA"."TABLE". Unquoted identifiers silently match uppercase; quoted identifiers match exactly.

[when a table has a "Type", "MetricID", "EventType", "Category" column] Entity isolation: That table stores multiple different types of data in the same "Value" column. You MUST filter WHERE "MetricID" = '...' (or equivalent) before any aggregation. Aggregating without this filter sums unrelated metrics — always wrong.

[when calculating ratios or division] Safe division: Prefer WHERE denominator > 0 or HAVING SUM(col) > 0 to exclude zero denominators. When the denominator can be NULL, use COALESCE(denominator, 0) before dividing.

[when events from two tables are independent] UNION ALL vs JOIN: If records in table B are valid even without a matching record in table A, use UNION ALL followed by aggregation — not JOIN (JOIN drops unmatched events). If table B is strictly subordinate to table A (parent-child hierarchy), use JOIN.

[when joining on string keys] Join key normalization: Whitespace: wrap both sides in TRIM() before comparing. Zero-padding: if one table uses '001' and another uses '1', normalize with LPAD() or CAST().

[when date/time columns look numeric (e.g., 20231231)] Date format verification: Explore the actual values before writing date predicates. Use TO_DATE(column, 'YYYYMMDD') for integer-format dates. Exclude invalid rows: WHERE date_col > 0 AND date_col IS NOT NULL.

[when querying JSON or VARIANT columns] JSON extraction: Use LATERAL FLATTEN(input => t."col") f to expand JSON arrays into rows. Use key-path notation: f.value:"key_name"::STRING to extract fields.

HIGH — common failure modes on Spider 2.0

[when epoch timestamp column appears] Epoch conversion: Verify the unit (seconds / milliseconds / microseconds) via exploration first. Microseconds → divide by 1,000,000. Use TO_TIMESTAMP_NTZ() for UTC conversion.

[when sorting or ranking] Tie-breaking: When multiple rows share the top value, results are non-deterministic without a secondary sort. Add a secondary ORDER BY column (e.g., alphabetical ID) to ensure deterministic results.

[when string matching] Case-insensitive search: Use UPPER(col) = UPPER('value') or ILIKE '%pattern%' for case-insensitive matching. Use ILIKE '%substring%' for partial string search. Use = for exact match.

[when aggregating nullable columns] NULL in aggregation: SUM(col) returns NULL if all values are NULL — use COALESCE(SUM(col), 0) to get 0. COUNT(DISTINCT col) automatically excludes NULL — no special handling needed.

[when geometry/geography columns appear] Geospatial: ST_POINT(longitude, latitude) — longitude is FIRST. Columns stored as binary/text need TO_GEOGRAPHY() conversion before spatial functions.

MEDIUM — good practices that reduce errors

[when result has large number of rows] Exploration truncation: Exploration results are truncated at 30 rows. For categorical columns, use SELECT DISTINCT "col" LIMIT 20 to see all values. For mixed-data fact tables, always query SELECT DISTINCT "MetricID" LIMIT 50 before filtering.

[when filtering with time windows] Async event preservation: When multiple tables have time-filtered events, UNION ALL then aggregate — avoids dropping events that don't have simultaneous matches in both tables.

[when computing window aggregations] Prefer window functions: SUM(...) OVER (PARTITION BY ...) avoids data loss from self-joins. Avoid aliases in WHERE clause of the same SELECT level — use a CTE or subquery wrapper instead.

Available Tools

execute_snowflake_sql — run a Snowflake SQL query

When to use: Any time you need to verify actual data values, explore column content, discover distinct values for categorical columns, check join feasibility, or validate that your final SQL executes without error.

Important: Run execute_snowflake_sql to test your final SQL before outputting the result. If it errors, fix and retry.

python - <<'PYEOF'
import os, sys, json
sys.path.insert(0, os.path.expanduser("~/.claude/skills/adaptive_text2sql_spider"))
from tools.snowflake_executor import execute_snowflake_sql, format_result

result = execute_snowflake_sql(
    sql='SELECT DISTINCT "MetricID" FROM "CENSUS"."PUBLIC"."FACTS" LIMIT 20',
    db_id="CENSUS",
    # credential_path="/path/to/credential.json"  # auto-discovered if omitted
)
print(format_result(result))
PYEOF

Output fields:

• columns: list of column names
• rows: list of rows if ≤30 rows, else None (use summary instead)
• row_count: total rows returned
• summary: per-column stats if row_count > 30 — includes min_val, max_val, distinct_count, sample_values, data_format
• error: error string, or None on success

If it errors: Read the error carefully. Common fixes:

• invalid identifier → check quoting ("col" vs col) and fully-qualified name ("DB"."SCHEMA"."TABLE")
• object does not exist → verify the schema and table name by exploring INFORMATION_SCHEMA
• division by zero → add WHERE denominator > 0 or NULLIF(denominator, 0)
• invalid type → check if column needs TO_DATE(), TO_TIMESTAMP_NTZ(), or ::STRING cast

select_tips — retrieve relevant Snowflake SQL tips

When to use: At the start of each question, after reading the schema and question. Tips are keyword-matched and cover the most common Spider 2.0 failure patterns (mixed-data tables, JSON, epoch timestamps, geospatial, etc.).

python - <<'PYEOF'
import os, sys, json
sys.path.insert(0, os.path.expanduser("~/.claude/skills/adaptive_text2sql_spider"))
from tools.tip_selector import select_tips

result = select_tips(
    question="What is the total population of California in 2020?",
    evidence="",
    keywords="population, year filter, state",
    db_schema='TABLE "FACTS" ("MetricID" TEXT, "Value" FLOAT, "GeoID" TEXT, "Year" INT)'
)
print(result["formatted_tips"])
PYEOF

Output fields:

• selected_ids: list of tip IDs selected (e.g., ["TIP001", "TIP018", ...])
• formatted_tips: formatted tip text ready to use
• tip_count: number of tips
• error: None on success

What it tells you: Whether the question triggers division safety, mixed-data isolation, JSON handling, geospatial, or join normalization tips. Read the formatted_tips field directly — it contains the actual advice to apply.

Tool Invocation

Both tools share the same sys.path.insert pattern. Call them with python - <<'PYEOF' ... PYEOF blocks.

For exploration queries, start with these patterns:

• Discover column values: SELECT DISTINCT "col" FROM "DB"."SCHEMA"."TABLE" LIMIT 20
• Check row count: SELECT COUNT(*) FROM "DB"."SCHEMA"."TABLE"
• Sample data: SELECT * FROM "DB"."SCHEMA"."TABLE" LIMIT 5
• Explore MetricID in fact tables: SELECT DISTINCT "MetricID", COUNT(*) AS cnt FROM "DB"."SCHEMA"."TABLE" GROUP BY 1 ORDER BY 2 DESC LIMIT 20
• Check JSON structure: SELECT f.value FROM "DB"."SCHEMA"."TABLE" t, LATERAL FLATTEN(input => t."json_col") f LIMIT 5

Recommended Workflow

Phase 1 — Retrieve Tips (always first)

Call select_tips with the question, evidence, and schema. Note any CRITICAL tips that fire (especially TIP018 for mixed-data tables). This takes ~1 second and prevents the most common errors.

Handover contract: know which tips apply before proceeding.

Phase 2 — Build a Logical Plan

Without guessing specific values, outline the query logic abstractly:

• Which tables are needed?
• What filters apply? (mark as [VERIFY: find the actual column/value])
• What aggregation or join is required?
• Are there independent events requiring UNION ALL?

Handover contract: abstract plan with [VERIFY] items for any uncertain column names, values, or formats.

Phase 3 — Explore to Resolve [VERIFY] Items

For each [VERIFY] item, run a targeted execute_snowflake_sql probe:

• For categorical values: SELECT DISTINCT "col" LIMIT 20
• For date formats: SELECT "date_col" LIMIT 5
• For JSON structure: inspect first few rows with LATERAL FLATTEN
• For MetricID (TIP018): SELECT DISTINCT "MetricID", COUNT(*) GROUP BY 1

Do not hard-code values found during exploration into the query — use the verified column/value logic dynamically.

Handover contract: all [VERIFY] items resolved with actual column names and representative values.

Phase 4 — Generate SQL

Write the final SQL applying all resolved details and applicable tips. Apply critical rules: fully-qualified names, double-quoted identifiers, entity isolation filter if TIP018 fired.

Handover contract: syntactically valid SQL ready for execution.

Phase 5 — Test and Verify

Run execute_snowflake_sql on the final SQL.

• On success: check row count is reasonable (not 0, not obviously inflated by missing entity isolation filter).
• On error: fix according to error message; re-run.
• If row count is 0 but question expects results: revisit the entity isolation filter or join logic.

Handover contract: SQL executes without error and row count is plausible.

Phase 6 — Output

Output the required JSON. selected_tables must use fully-qualified names matching the SQL.

Dependencies

pip install snowflake-connector-python pandas

Snowflake credentials must be placed at: benchmarks/text2sql/data/spider2-snow/credentials/<name>_snowflake_credential.json