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): ```json { "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. ```bash 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.). ```bash 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 ```bash pip install snowflake-connector-python pandas ``` Snowflake credentials must be placed at: `benchmarks/text2sql/data/spider2-snow/credentials/<name>_snowflake_credential.json`